The above income statement is automatically generated using the financial model made in excel file. For this purpose we have used account formula to calculate the items of an income state. The formulas that have been used in income statement are:
Goods available for sales = Opening Stock + Purchase
Cost of Goods Sold = Opening Stock + Purchase - Closing stock
Gross Profit = Turnover - Cost of Goods Sold
Total Operating expenses = sum of all operating expenses (given)
Net Profit = Gross Profit - Total Operating expenses.
Criteria for distributing operating cost
Department A
Department B
Department C
Department D
Floor area (Square Metres)
5000
3000
2000
8000
Space by Volume (Cubic Metres)
10000
6000
4000
16000
Number of Staff
10
12
20
35
Average number of customer per month
400
300
200
350
Fixed assets at cost
200000
150000
60000
75000
Annual rate of depreciation
15%
20%
10%
20%
Turnover
57500
53500
101850
115750
The above table shows the criteria of distributing the operating expenses on the basis of floor area, space by volume, number of staff, number of customers, fixed asset at cost, annual percentage of depreciation and turnover.
Criteria for distributing operating cost
Weight
Department A
Department B
Department C
Department D
Floor area (Square Metres)
0.28
0.17
0.11
0.44
Space by Volume (Cubic Metres)
0.28
0.17
0.11
0.44
Number of Staff
0.13
0.16
0.26
0.45
Average number of customer per month
0.32
0.24
0.16
0.28
Fixed assets at cost
0.41
0.31
0.12
0.15
Annual rate of depreciation
15%
20%
10%
20%
Turnover
0.17
0.16
0.31
0.35
The above table shows the weight allocated to each department on the basis of Floor area (Square Metres), Space by Volume (Cubic Metres), Number of Staff, Average number of customer per month, fixed assets at cost, Annual rate of depreciation and Turnover. For this purpose we have calculated weight as below
Floor area (Square Metres) = Floor area of department (Square Metres) / sum of area of all department
Space by Volume (Cubic Metres) = Space by Volume (Cubic Metres) of department/ sum of Space by Volume (Cubic Metres) department
Number of Staff = Number of Staff of Department/ sum number of staff all departments
Average number of customer per month = Average number of customer per month of department/ sum Average number of customer per month for all departments
Fixed assets at cost = Fixed assets at cost each department/ Total fixed asset of all departments
Turnover = Turnover of department/ total turnover of all departments
Distribution of operating cost
Total cost
Depart A
Depart B
Depart C
Depart D
Advertising (Turnover)
28000
4900
4559
8679
9863
Gas and Electricity ( space by volume)
16000
4444
2667
1778
7111
Rent (Floor area)
18000
5000
3000
2000
8000
General administration cost ( turn over)
12500
2187
2035
3874
4403
General manager salary (number of customer)
30000
9600
7198
4800
8400
Medical expenses (number of staff)
6500
844
1011
1688
2955
Laundry and Cleaning (number of customers)
8500
2720
2039
1360
2380
Maintenance cost (space by volume)
7000
1944
1167
778
3111
Insurance (number of customers)
5000
1600
1200
800
1400
Miscellaneous Costs (turnover)
3000
525
488
930
1057
Pension Costs (number of staff)
26000
3377
4045
6753
11818
Depreciation expense
30000
30000
6000
15000
Above table shows the allocation of operating expenses on the basis of the criteria of the organization. All operating expenses are allocated to each department by using following formulas:
Advertising = total advertising expenses x weights (turnover
Gas and Electricity = Total Gas and Electricity Cost x weight (Space by Volume)
Rent = Total rent x weight (Floor area)
General administration cost = Total General Administration Cost x weight (Turnover)