[SOLVED] Financial Analysis and Modeling

This assignment involves performing various financial analyses and modeling tasks, including calculating sums and averages of multiple sets of data, applying percentages, and using if statements with conditions. The goal is to provide a comprehensive solution that covers all aspects of the assignment, making it easy for students to follow and learn from.

REPORT

Table of Contents
TASK 1............................................................................................................................................1
A...................................................................................................................................................1
B...................................................................................................................................................1
TASK 2............................................................................................................................................2

TASK 1
A
Sports Emporium is retail outlet established in west London, Unite Kingdom. They deal
in various sports equipments which includes sports shoes, cricket bat, baseball bat and all the
sports related goods and services. They provide sports equipments in various schools and
colleges of London. They also have their outlet in the main market of London which is the main
sources of income for the company. In the year total revenue generated by it, is 444254.55 and
company earned a profit of 277515.88, it also has various sources of income which include its
Share capital, bank loans, bonds and debentures.
B
Following is the profit and loss record of past one year:
1

25000
42000
38000
Investment
Capital
Stock
Operation money
19
12
10
Sources of Income
share capital
Bank loans
Bonds and
Debentures
Expenses
Staff Salaries
Rent & Rates
Insurances
Repairs & Maintenance
Telephone
Advertising & Promotions
Miscellaneous Expenses
Loan Interest
Depreciation
TASK 2
In the preparation of above sale and profit and loss statement various formulas of Excel
are used, auto sum function is used due to large number and complexity of the data. It is very
difficult to calculate each and every months total separately, sum function can easily calculate
the complex data. Average function is used to calculate the average of sales and different
2

expenses incurred by company in every month. With the help of this function it is easy to
calculate the average sales and expenses of a company. Percentage formal is used to find out the
percentage of gross profit which is earned on total revenue generated by Sport Emporium. Pie
chart is used to show the distribution of the sources of income, various investments and all the
expense which are occurred in the past one year. Charts are used to enhance the presentation of
the data and it can be easily interpreted.
3

ANNEXURE
Column
2
Colum
n3
Colum
n4 Column5
Colum
n6
Colum
n7
Colum
n8
Colum
n9
Column
10
Column
11
Column
12
Column
13
Column1
4 Column15
Januar
y
Februa
ry March April May June July August
Septemb
er October
Novemb
er
Decemb
er Total Average
11500
=
B7+B7*
5%
=
C7+C7
*20%
=
D7+D7*2
0%
=
E7+E7*
20%
=
F7+F7*
20%
=
G7+G7
*20%
=
H7+H7
*20%
=
I7+I7*2
0%
=
J7+J7*2
0%
=
K7+K7*
20%
=
L7+L7*
20%
=
SUM(B7:
M7)
=
AVERAGE(
B7:M7)
3000
=
B8+B8*
10%
=
C8+C8
*10%
=
D8+D8*1
0%
=
E8+E8*
10%
=
F8+F8*
10%
=
G8+G8
*10%
=
H8+H8
*10%
=
I8+I8*1
0%
=
J8+J8*1
0%
=
K8+K8*
10%
=
L8+L8*
10%
=
SUM(B8:
M8)
=
AVERAGE(
B8:M8)
3500
=
B9+B9*
10%
=
C9+C9
*10%
=
D9+D9*1
0%
=
E9+E9*
10%
=
F9+F9*
10%
=
G9+G9
*10%
=
H9+H9
*10%
=
I9+I9*1
0%
=
J9+J9*1
0%
=
K9+K9*
10%
=
L9+L9*
10%
=
SUM(B9:
M9)
=
AVERAGE(
B9:M9)
1500
=
B10+B1
0*10%
=
C10+C
10*10
%
=
D10+D10
*10%
=
E10+E1
0*10%
=
F10+F1
0*10%
=
G10+G
10*10
%
=
H10+H
10*10
%
=
I10+I10
*10%
=
J10+J10
*10%
=
K10+K1
0*10%
=
L10+L1
0*10%
=
SUM(B1
0:M10)
=
AVERAGE(
B10:M10)
=
IF(B7=
"";"";
+SUM(
B7-B8-
B9-
B10))
=
IF(C7=
"";"";
+SUM(
C7-C8-
C9-
C10))
=
IF(D7=
"";"";
+SUM(
D7-D8-
D9-
D10))
= IF(E7=
"";"";
+SUM(E
7-E8-E9-
E10))
=
IF(F7=
"";"";
+SUM(
F7-F8-
F9-
F10))
=
IF(G7=
"";"";
+SUM(
G7-G8-
G9-
G10))
=
IF(H7=
"";"";
+SUM(
H7-H8-
H9-
H10))
=
IF(I7=
"";"";
+SUM(
I7-I8-
I9-I10))
= IF(J7=
"";"";
+SUM(J
7-J8-J9-
J10))
=
IF(K7=
"";"";
+SUM(
K7-K8-
K9-
K10))
=
IF(L7=
"";"";
+SUM(L
7-L8-L9-
L10))
=
IF(M7=
"";"";
+SUM(
M7-M8-
M9-
M10))
=
SUM(B1
1:M11)
=
AVERAGE(
B11:M11)
=
SUM(B1
2:M12)
=
AVERAGE(
B12:M12)
300
=
B14+B1
4*0.08
=
C14+C
14*0.08
=
D14+D14
*0.08
=
E14+E1
4*0.08
=
F14+F1
4*0.08
=
G14+G
14*0.08
=
H14+H
14*0.08
=
I14+I14
*0.08
=
J14+J14
*0.08
=
K14+K1
4*0.08
=
L14+L1
4*0.08
=
SUM(B1
4:M14)
=
AVERAGE(
B14:M14)
120
=
B15+B1
5*0.08
=
C15+C
15*0.08
=
D15+D15
*0.08
=
E15+E1
5*0.08
=
F15+F1
5*0.08
=
G15+G
15*0.08
=
H15+H
15*0.08
=
I15+I15
*0.08
=
J15+J15
*0.08
=
K15+K1
5*0.08
=
L15+L1
5*0.08
=
SUM(B1
5:M15)
=
AVERAGE(
B15:M15)
145
=
B16+B1
6*0.08
=
C16+C
16*0.08
=
D16+D16
*0.08
=
E16+E1
6*0.08
=
F16+F1
6*0.08
=
G16+G
16*0.08
=
H16+H
16*0.08
=
I16+I16
*0.08
=
J16+J16
*0.08
=
K16+K1
6*0.08
=
L16+L1
6*0.08
=
SUM(B1
6:M16)
=
AVERAGE(
B16:M16)
50
=
B17+B1
7*0.08
=
C17+C
17*0.08
=
D17+D17
*0.08
=
E17+E1
7*0.08
=
F17+F1
7*0.08
=
G17+G
17*0.08
=
H17+H
17*0.08
=
I17+I17
*0.08
=
J17+J17
*0.08
=
K17+K1
7*0.08
=
L17+L1
7*0.08
=
SUM(B1
7:M17)
=
AVERAGE(
B17:M17)
80
=
B18+B1
=
C18+C
=
D18+D18
=
E18+E1
=
F18+F1
=
G18+G
=
H18+H
=
I18+I18
=
J18+J18
=
K18+K1
=
L18+L1
=
SUM(B1
=
AVERAGE(
4

8*0.08 18*0.08 *0.08 8*0.08 8*0.08 18*0.08 18*0.08 *0.08 *0.08 8*0.08 8*0.08 8:M18) B18:M18)
350
=
B19+B1
9*0.05
=
C19+C
19*0.05
=
D19+D19
*0.05
=
E19+E1
9*0.05
=
F19+F1
9*0.05
=
G19+G
19*0.05
=
H19+H
19*0.05
=
I19+I19
*0.05
=
J19+J19
*0.05
=
K19+K1
9*0.05
=
L19+L1
9*0.05
=
SUM(B1
9:M19)
=
AVERAGE(
B19:M19)
130
=
B20+B2
0*0.05
=
C20+C
20*0.05
=
D20+D20
*0.05
=
E20+E2
0*0.05
=
F20+F2
0*0.05
=
G20+G
20*0.05
=
H20+H
20*0.05
=
I20+I20
*0.05
=
J20+J20
*0.05
=
K20+K2
0*0.05
=
L20+L2
0*0.05
=
SUM(B2
0:M20)
=
AVERAGE(
B20:M20)
100 100 100 100 100 100 100 100 100 100 100 100
=
SUM(B2
1:M21)
=
AVERAGE(
B21:M21)
30 30 30 30 30 30 30 30 30 30 30 30
=
SUM(B2
2:M22)
=
AVERAGE(
B22:M22)
=
SUM(B
14:B22)
=
SUM(C
14:C22)
=
SUM(D
14:D22)
=
SUM(E14
:E22)
=
SUM(F
14:F22)
=
SUM(G
14:G22
)
=
SUM(H
14:H22
)
=
SUM(I
14:I22)
=
SUM(J1
4:J22)
=
SUM(K
14:K22)
=
SUM(L1
4:L22)
=
SUM(M
14:M22)
=
SUM(B2
3:M23)
=
AVERAGE(
B23:M23)
=
IF(B11
=
"";"";
+SUM(
B11-
B23))
=
B24+B2
4*0.005
=
C24+C
24*0.00
5
=
D24+D24
*0.005
=
E24+E2
4*0.005
=
F24+F2
4*0.005
=
G24+G
24*0.00
5
=
H24+H
24*0.00
5
=
I24+I24
*0.005
=
J24+J24
*0.005
=
K24+K2
4*0.005
=
L24+L2
4*0.005
=
SUM(B2
4:M24)
=
AVERAGE(
B24:M24)
100
=
B25+B2
5*0.005
=
C25+C
25*0.00
5
=
D25+D25
*0.005
=
E25+E2
5*0.005
=
F25+F2
5*0.005
=
G25+G
25*0.00
5
=
H25+H
25*0.00
5
=
I25+I25
*0.005
=
J25+J25
*0.005
=
K25+K2
5*0.005
=
L25+L2
5*0.005
=
SUM(B2
5:M25)
=
AVERAGE(
B25:M25)
=
IF(B24
=
"";"";
+SUM(
B24+B2
5))
=
IF(C24
=
"";"";
+SUM(
C24+C
25))
=
IF(D24
=
"";"";
+SUM(
D24+D
25))
=
IF(E24=
"";"";
+SUM(E
24+E25))
=
IF(F24
=
"";"";
+SUM(
F24+F2
5))
=
IF(G24
=
"";"";
+SUM(
G24+G
25))
=
IF(H24
=
"";"";
+SUM(
H24+H
25))
=
IF(I24=
"";"";
+SUM(
I24+I25
))
=
IF(J24=
"";"";
+SUM(J
24+J25))
=
IF(K24=
"";"";
+SUM(
K24+K2
5))
=
IF(L24=
"";"";
+SUM(L
24+L25)
)
=
IF(M24
= "";"";
+SUM(
M24+M
25))
=
SUM(B2
6:M26)
=
AVERAGE(
B26:M26)
@B11/
B7%
@C11/
C7%
@D11/
D7%
@E11/
E7%
@F11/
F7%
@G11/
G7%
@H11/
H7%
@I11/
I7%
@J11/
J7%
@K11/
K7%
@L11/
L7%
@M11/
M7%
@N11/
N7% @O11/O7%
@B26/
B7%
@C26/
C7%
@D26/
D7%
@E26/
E7%
@F26/
F7%
@G26/
G7%
@H26/
H7%
@I26/
I7%
@J26/
J7%
@K26/
K7%
@L26/
L7%
@M26/
M7%
@N26/
N7% @O26/O7%
5