Integrated_Model_Exercises
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Introduction
2
The model shown on the following "Base model" tab in this spreadseet is an example exercise from our Financial Modelling course ..
3
4
Financial modelling course
5
6
This spreadsheet is read-only. An editable version is at the following address. You will need a Google account to use the copy-and-editable version
7
8
Edtable version
9
10
The exercise on the following tab is an introduction to modelling integrated financial statements. Complete the exercise by designing appropirate formulae for the coloured cells. Tags marked blue on the left of the sheet will turn yellow when your answers are correct.
11
You may find the information below helpful in completing the exercise.
12
13
About the assumptions section
14
15
AssumptionInterpretation
16
17
Annual sales growthThis determines the percentage growth in sales from one year to the next.
18
Current assets / yearly salesThe enterprise requires assets in order to operate. The current assets required to generate sales is assumed to be a fixed proportion of the sales.
19
[This is a fairly crude assumption. A more realistic assumption might be that current assets include both a fixed component that is independent of business volumes and another, variable, component that is dependent on business volumes.]
20
Current liabilities / yearly salesCurrent liabilities are assumed to be a fixed proportion of yearly sales.
21
[Again, this is a crude assumption. A more realistic assumption would be to link current liabilities to the expenses side of the business rather than to the revenue side.]
22
Net fixed assets / yearly salesNet fixed assets are assumed to be a fixed proportion of yearly sales. Net fixed assets = Fixed assets at cost + accumulated depreciation.
23
[A more accurate assumption might involve breaking sales into volume and price components (i.e. sales = volume * price) and modelling the linkage between fixed assets and volume and price.]
24
Cost of goods sold / salesThe cost of goods sold is assumed to be a fixed percentage of the amount of sales.
25
Depreciation rateDepreciation in any year is the gross fixed assets at the beginning of the year (i.e. as at the end of the preceding year) times the depreciation rate.
26
[A more realistic treatment of depreciation would take into account the timing and disposal of assets during the year: An asset purchased halfway through the year would depreciate only half as much as would one purchased at the start of the year.]
27
28
Completing the model
29
30
Income statement
31
Represent revenues as positive numbers and expenses as negative numbers.
32
For the depreciation calculation depreciate net fixed assets as at the end of the preceding year.
33
When calculating interest income use the balance sheet cash as at the end of the preceding year.
34
Similarly, when calculating interest expense, use the debt on the balance sheet as at the start of the year (i.e. as at the end of the preceding year).
35
36
Balance sheet
37
Cash in any period should equal cash in the prior period plus the net increase in cash obtained further down the model in the Cash flows section.
38
39
In the fixed assets section first calculate accumulated depreciation. Accumulated depreciation in a period is the sum of the accumulated depreciation in the prior period plus the depreciation in the current period. Next, calculate net (i.e. depreciated) fixed assets by multiplying sales revenue by the "Net fixed assets / yearly sales" assumption. Having calculated both accumulated depreciation and net fixed assets you can calculate "at cost" fixed assets.
40
41
Cash flow statement
42
In the "Changes in working capital" section the figure in the row titled "less increase in current assets" should be positive if current assets have decreased over the past year and should be negative if they have increased. In the row titled "plus increase in current liabilities" the figure should be positive if current liabilities have increased over the past year and should be negative if they have decreased.
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...
 
 
 
About this model
Base model
__ans__