Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 8: Financial planning models page 17
Excel note—solving circular references
Financial statement models in Excel always involve cells that are mutually dependent. In
our model, for example, the interest earned on cash depends on the profits of the firm, but the
profits depend on the interest earned on cash. Another example of mutual dependence in our
model involves the fixed asset accounts: Fixed assets at cost are the sum of net fixed assets plus
accumulated depreciation, but accumulated depreciation is a function of the fixed assets at cost.
As a result of these inevitable mutual dependencies, the solution of the model depends on
the ability of Excel to solve circular references. To make sure your spreadsheet recalculates, you
have to go to the Tools|Options|Calculation box and click Iteration. If you open a spreadsheet
that involves iteration, and if this box is not clicked, you will see the following Excel error
message:
Depending on where you are in Excel when you open the file with the circular references,
you may get a slightly different version of the above message. Whatever message you see, get
out of it by pressing Cancel and go to Tools|Options|Calculation|Iteration. In this dialog box
click the box labeled Iteration:
PFE, Chapter 8: Financial planning models page 18
8.3. Extending the model to years 2 and beyond
Now that you have the model set up, you can extend it by copying the columns:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
ABCDEFG
Sales growth 10%
Current assets/Sales 15%
Current liabilities/Sales 8%
Net fixed assets/Sales 77%
Costs of goods sold/Sales 50%
Depreciation rate 10%
Interest rate on debt 10.00%
Interest earned on cash balances 8.00%
Tax rate 40%
Dividend payout ratio 40%
Year 012345
Income statement
Sales 1,000 1,100 1,210 1,331 1,464 1,611
Costs of goods sold (500) (550) (605) (666) (732) (805)
Depreciation (100) (117) (137) (161) (189) (220)
Interest payments on debt (32) (28) (20) (12) (4) -
Interest earned on cash and marketable securities 6 6 5 4 4 8
Profit before tax 374 411 452 496 544 594
Taxes (150) (164) (181) (199) (217) (237)
Profit after tax 225 247 271 298 326 356
Dividends (90) (99) (109) (119) (130) (142)
Retained earnings 135 148 163 179 196 214
Balance sheet
Cash 80 64
54 51 55 146
Current assets 150 165 182 200 220 242
Fixed assets
At cost 1,070 1,264 1,486 1,740 2,031 2,364
Depreciation (300) (417) (554) (715) (904) (1,124)
Net fixed assets 770 847 932 1,025 1,127 1,240
Total assets 1,000 1,076 1,168 1,276 1,402 1,628
Current liabilities 80 88 97 106 117 129
Debt 320 240 160 80 0 0
Stock 450 450 450 450 450 450
Accumulated retained earnings 150 298 461 640 835 1,049
Total liabilities and equity 1,000 1,076 1,168 1,276 1,402 1,628
WHIMSICAL TOENAILS--FINANCIAL MODEL
The most common Excel mistake to make in the transition between the two-columned
financial model and this one is the failure to mark the model parameters with dollar signs. If you
commit this error, you will get zeros in places where there should be numbers.
3
3
If this paragraph is mysterious to you, change the model by putting in the following mistake: In cell C28, write the
formula =C15*B3 (instead of the correct formula =C15*$B$3). Then copy cell C28 to D28:G28. Now
you’llunderstand the importance of dollarizing the correct cell references!
PFE, Chapter 8: Financial planning models page 19
Understanding the model—doing some sensitivity analysis
The financial model we’ve built shows that our firm’s profits after tax will grow from
$225 in year 0 to $352 in year 5. The balances of cash grow from $80 to $459, the firm’s total
assets grow to $1,941, and so on … .
We can use the model to do some
sensitivity analysis. For example, what would happen
to profits if the growth rate of sales were to be 8 per cent instead of 10 per cent and if the cost of
goods sold were to be 55 per cent of sales instead of the 50 per cent currently in the model?
Given our Excel model, we simply have to make the relevant changes in the parameters in cells
B2 and B6. Our intuition is that these performance changes will make the firm’s financial results
worse, and this is indeed confirmed in the model, as shown below:
PFE, Chapter 8: Financial planning models page 20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
ABCDEFG
Sales growth 8% <-- Changed from 10%
Current assets/Sales 15%
Current liabilities/Sales 8%
Net fixed assets/Sales 77%
Costs of goods sold/Sales 55% <-- Changed from 50%
Depreciation rate 10%
Interest rate on debt 10.00%
Interest earned on cash balances 8.00%
Tax rate 40%
Dividend payout ratio 40%
Year 012345
Income statement
Sales 1,000 1,080 1,166 1,260 1,360 1,469
Costs of goods sold (550) (594) (642) (693) (748) (808)
Depreciation (100) (116) (135) (156) (181) (208)
Interest payments on debt (32) (28) (20) (12) (4) -
Interest earned on cash and marketable securities 6 6 4 3 2 4
Profit before tax 324 348 374 401 429 457
Taxes (130) (139) (150) (160) (172) (183)
Profit after tax 195 209 224 241 258 274
Dividends (78) (83) (90) (96) (103) (110)
Retained earnings 117 125 135 144 155 165
Balance sheet
Cash 80 58 40 26 16 89
Current assets 150 162 175 189 204 220
Fixed assets
At cost 1,070 1,247 1,449 1,677 1,935 2,227
Depreciation (300) (416) (551) (707) (888) (1,096)
Net fixed assets 770 832 898 970 1,048 1,131
Total assets 1,000 1,052 1,113 1,185 1,268 1,441
Current liabilities 80 86 93 101 109 118
Debt 320 240 160 80 0 0
Stock 450 450 450 450 450 450
Accumulated retained earnings 150 275 410 554 709 873
Total liabilities and equity 1,000 1,052 1,113 1,185 1,268 1,441
WHIMSICAL TOENAILS MODEL WITH SOME CHANGES
If you compare the model above to our previous version of the model, you’ll see that the
firm’s sales growth has slowed (from 10 percent to 8 percent) and that its sales have become
more expensive (cost of goods sold is 55 percent of sales instead of 50 percent). The result is
that profits after taxes (row 22) are lower than before. Cash balances (row 27) are also lower
than in the previous version of the model.
PFE, Chapter 8: Financial planning models page 21
8.4. Free cash flow (FCF): measuring the cash produced by the firm’s
operations
In this section we use our model to measure the firm’s projected free cash flow (FCF).
We discussed the concept of free cash flow in Chapter 7 (page000). A good way to think of FCF
is that it is the amount of cash the firm would produce if it had no debt whatsoever. This is
equivalent to the amount of cash produced by the firm if the shareholders have to finance
all of
the operations of the firm. For short, we’ll say that the FCF is a measure of the
cash produced by
the firm’s operations
.
The FCF is the measure on which we base our valuation of the firm. We gave an
example of this in Chapter 7 (p000, where we used the future predicted FCFs of Anytown Travel
Services to value the company). In Chapter 9 we return to this topic and show how a financial
planning model’s predictions of FCFs can be used to value a company.
In this section we merely use the financial planning model to project the firm’s future
free cash flows. Before we do so, however, let’s recap the definition and the terminology we
use.
The definition of the free cash flow is:
PFE, Chapter 8: Financial planning models page 22
Defining the Free Cash Flow (FCF)
Profit after taxes This is the basic measure of the profitability of the
business, but it is an accounting measure that includes
financing flows (such as interest), as well as non-cash
expenses such as depreciation. Profit after taxes does not
account for changes in the firm’s working capital or
purchases of new fixed assets, both of which can be
important cash drains on the firm.
+ Depreciation This non-cash expense is added back to the profit after tax.
- Increase in current assets When the firm’s sales increase, more investment is needed
in inventories, accounts receivable, etc. This increase in
current assets is not an expense for tax purposes (and is
therefore ignored in the profit after taxes), but it is a cash
drain on the company. Note that our use of the term
“current assets” is slightly different from the standard
accounting usage—see the discussion following this table.
+ Increase in current liabilities An increase in the sales often causes an increase in
financing related to sales (such as accounts payable or taxes
payable). This increase in current liabilities—when related
to sales—provides cash to the firm. Since it is directly
related to sales, we include this cash in the free cash flow
calculations. Note that our use of the term “current
liabilities” is slightly different from the standard accounting
usage—see the discussion following this table.
- Increase in fixed assets at cost
(also called “capital
expenditures”—CAPEX)
An increase in fixed assets (the long-term productive assets
of the company) is a use of cash, which reduces the firm’s
free cash flow.
+ after-tax interest payments (net) FCF is an attempt to measure the cash produced by the
business activity of the firm. To neutralize the effect of
interest payments on the firm’s profits, we:
•
Add back the after-tax cost of interest on debt
(
after-tax since interest payments are tax-
deductible),
•
Subtract out the after-tax interest payments on cash.
FCF = sum of the above The free cash flow measures the cash produced by the
firm’s operations.
Here is the FCF calculation for Whimsical Toenails.
PFE, Chapter 8: Financial planning models page 23
42
43
44
45
46
47
48
49
50
51
ABCDEFG
Free cash flow calculation
Year 012345
Profit after tax 247 271 298 326 356
Add back depreciation 117 137 161 189 220
Subtract increase in current assets (15) (17) (18) (20) (22)
Add back increase in current liabilities 8 9 10 11 12
Subtract increase in fixed assets at cost (194) (222) (254) (291) (333)
Add back after-tax interest on debt 17 12 7 2 0
Subtract after-tax interest on cash (3) (3) (3) (3) (5)
Free cash flow 176 188 201 214 228
The FCFs in row 51 are substantially lower than the firm’s profits after taxes in row 44.
The major reasons for this are the large capital expenditure (row 48) which outweigh the cash
effect of the depreciation (row 45).
The FCF calculations are sensitive to the model assumptions. Suppose that Whimsical
Toenail’s sales growth is 8% (instead of 10%) and that its cost of goods sold is 55% of sales
(instead of 50%). You might suspect that these negative changes in the model assumptions will
make Whimsical’s future projected FCFs substantially lower, and you’re right:
1
2
3
4
5
6
7
8
9
10
11
41
42
43
44
45
46
47
48
49
50
51
ABCDEFG
Sales growth 8% <-- Changed from 10%
Current assets/Sales 15%
Current liabilities/Sales 8%
Net fixed assets/Sales 77%
Costs of goods sold/Sales 55% <-- Changed from 50%
Depreciation rate 10%
Interest rate on debt 10.00%
Interest earned on cash balances 8.00%
Tax rate 40%
Dividend payout ratio 40%
Free cash flow calculation
Year 012345
Profit after tax 209 224 241 258 274
Add back depreciation 116 135 156 181 208
Subtract increase in current assets (12) (13) (14) (15) (16)
Add back increase in current liabilities 6 7 7 8 9
Subtract increase in fixed assets at cost (177) (201) (228) (258) (292)
Add back after-tax interest on debt 17 12 7 2 0
Subtract after-tax interest on cash (3) (2) (2) (1) (3)
Free cash flow 155 161 168 174 180
WHIMSICAL TOENAILS MODEL WITH SOME CHANGES
PFE, Chapter 8: Financial planning models page 24
Excel Note: Hiding rows
In the above example we’ve hidden rows 12-40. To do this in Excel:
•
Mark the rows you want to hide.
•
Right-click on the mouse and click Hide
Here’s what the screen looks like:
Marking the rows and clicking Unhide reverses the action.
PFE, Chapter 8: Financial planning models page 25
8.5. Reconciling the cash balances—the consolidated statement of cash flows
The free cash flow calculation is different from the “consolidated statement of cash
flows” that is a part of every accounting statement (see Chapter 7, page000). The FCF
calculation shows you how much cash is produced by the firm’s operations. On the other hand,
the purpose of the accounting statement of cash flows is to explain the increase in the cash
accounts in the balance sheet as a function of the cash flows from the firm’s operating, investing,
and financing activities. Here’s the consolidated statement of cash flows for our model:
PFE, Chapter 8: Financial planning models page 26
1
2
3
4
5
6
7
8
9
10
11
12
13
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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
ABCDEFGH
Sales growth 10%
Current assets/Sales 15%
Current liabilities/Sales 8%
Net fixed assets/Sales 77%
Costs of goods sold/Sales 50%
Depreciation rate 10%
Interest rate on debt 10.00%
Interest earned on cash balances 8.00%
Tax rate 40%
Dividend payout ratio 40%
Year 012345
Balance sheet
Cash 80 64 54 51 55 146
Current assets 150 165 182 200 220 242
Fixed assets
At cost 1,070 1,264 1,486 1,740 2,031 2,364
Depreciation (300) (417) (554) (715) (904) (1,124)
Net fixed assets 770 847 932 1,025 1,127 1,240
Total assets
1,000 1,076 1,168 1,276 1,402 1,628
Current liabilities 80 88 97 106 117 129
Debt 320 240 160 80 0 0
Stock 450 450 450 450 450
450
Accumulated retained earnings 150 298 461 640 835 1,049
Total liabilities and equity 1,000 1,076 1,168 1,276 1,402 1,628
CONSOLIDATED STATEMENT OF CASH FLOWS--RECONCILING THE CASH BALANCES
Cash flow from operating activities
Profit after tax 247 271 298 326 356
Add back depreciation 117 137 161 189 220
Adjust for changes in net working capital:
Subtract increase in current assets (15) (17) (18) (20) (22)
Add back increase in current liabilities 8 9 10 11 12
Net cash from operating activities 356 401 451 506 566
Cash flow from investing activities
Aquisitions of fixed assets--capital expenditures (194) (222) (254) (291) (333)
Purchases of investment securities 00000
Proceeds from sales of investment securities 00000
Net cash used in investing activities (194) (222) (254) (291) (333)
Cash flow from financing activities
Net proceeds from borrowing activities -80 -80 -80 -80 0
Net proceeds from stock issues, repurchases 00000
Dividends paid (99) (109) (119) (130) (142)
Net cash from financing activities (179) (189) (199) (210) (142)
Net increase in cash and cash equivalents -16 -10 -3 4 91
Cash balances at end of previous year 80 64 54 51 55
Cash balances at end of current year 64 54 51 55 146
WHIMSICAL TOENAILS--reconciliation of cash balances
Note that the profit and loss statement and FCF statement have been hidden
=C73+C67+C61
=C27
=C75+C76
This number should be
e
q
ual to cell C27 .
Row 77 checks that the ending balances in the cash accounts derived through the
consolidated statement of cash flows match those derived in row 27 of the balance sheets (which