A B C

1 SETTING UP THE FINANCIAL STATEMENT MODEL

2 Sales growth 10%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash and marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 40%
12
13 Year 0 1
14 Income statement
15 Sales 1,000
16 Costs of goods sold (500)
17 Interest payments on debt (32)
18 Interest earned on cash and marketable securities 6
19 Depreciation (100)
20 Profit before tax 374
21 Taxes (150)
22 Profit after tax 225
23 Dividends (90)
24 Retained earnings 135
25
26 Balance sheet
27 Cash and marketable securities 80
28 Current assets 150
29 Fixed assets
30 At cost 1,070
31 Depreciation (300)
32 Net fixed assets 770
33 Total assets 1,000
34
35 Current liabilities 80
36 Debt 320
37 Stock 450
38 Accumulated retained earnings 150
39 Total liabilities and equity 1,000
 D
AL STATEMENT
1 MODEL
2
3
4
5
6
7
8
9
10
11
12
13
14
15 #VALUE!
16 #VALUE!
17 #VALUE!
18 #VALUE!
19 #VALUE!
20 #VALUE!
21 #VALUE!
22 #VALUE!
23 #VALUE!
24 #VALUE!
25
26
27 #VALUE!
28 #VALUE!
29
30 #VALUE!
31 #VALUE!
32 #VALUE!
33 #VALUE!
34
35 #VALUE!
36 #VALUE!
37 #VALUE!
38 #VALUE!
39 #VALUE!
 A B C D E
1 FIRST FINANCIAL MODEL
2 Sales growth 10%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash and marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 40%
12
13 Year 0 1 2 3
14 Income statement
15 Sales 1,000
16 Costs of goods sold (500)
17 Interest payments on debt (32)
18 Interest earned on cash and marketable securities 6
19 Depreciation (100)
20 Profit before tax 374
21 Taxes (150)
22 Profit after tax 225
23 Dividends (90)
24 Retained earnings 135
25
26 Balance sheet
27 Cash and marketable securities 80
28 Current assets 150
29 Fixed assets
30 At cost 1,070
31 Depreciation (300)
32 Net fixed assets 770
33 Total assets 1,000
34
35 Current liabilities 80
36 Debt 320
37 Stock 450
38 Accumulated retained earnings 150
39 Total liabilities and equity 1,000
40
41
42 Year 0 1 2 3
43 Free cash flow calculation
44 Profit after tax
45 Add back depreciation
46 Subtract increase in current assets
47 Add back increase in current liabilities
48 Subtract increase in fixed assets at cost
49 Add back after-tax interest on debt
 A B C D E
50 Subtract after-tax interest on cash and+A14 mkt. securities
51 Free cash flow
52
53 CONSOLIDATED STATEMENT OF CASH FLOWS: RECONCILING THE CASH
54 Cash flow from operating activities
55 Profit after tax
56 Add back depreciation
57 Adjust for changes in net working capital:
58 Subtract increase in current assets
59 Add back increase in current liabilities
60 Net cash from operating activities
61
62 Cash flow from investing activities
63 Aquisitions of fixed assets--capital expenditures
64 Purchases of investment securities 0 0 0
65 Proceeds from sales of investment securities 0 0 0
66 Net cash used in investing activities
67
68 Cash flow from financing activities
69 Net proceeds from borrowing activities
70 Net proceeds from stock issues, repurchases
71 Dividends paid
72 Net cash from financing activities
73
74 Net increase in cash and cash equivalents
75 Check: changes in cash and mkt. securities
 F G H I J K L
1
2
3
4
5
6
7
8
9
10
11
12
13 4 5
14
15 #VALUE!
16 #VALUE!
17 #VALUE!
18 #VALUE!
19 #VALUE!
20 #VALUE!
21 #VALUE!
22 #VALUE!
23 #VALUE!
24 #VALUE!
25
26
27 #VALUE!
28 #VALUE!
29
30 #VALUE!
31 #VALUE!
32 #VALUE!
33 #VALUE!
34
35 #VALUE!
36 #VALUE!
37 #VALUE!
38 #VALUE!
39 #VALUE!
40
41
42 4 5
43
44 #VALUE!
45 #VALUE!
46 #VALUE!
47 #VALUE!
48 #VALUE!
49 #VALUE!
 F G H I J K L
50 #VALUE!
51 #VALUE!
52
S: RECONCILING
53 THE CASH BALANCES
54
55 #VALUE!
56 #VALUE!
57
58 #VALUE!
59 #VALUE!
60 #VALUE!
61
62
63 #VALUE! quisitions of fixed assets
64 0 0 <-- Not in our model purchases of investment securities
65 0 0 <-- Not in our model proceeds from sales of investment securities
66 #VALUE! net cash used in investing activities
67
68
69 #VALUE!
70 #VALUE! net proceeds from stock issues, repurchases
71 #VALUE!
72 #VALUE!
73
74 #VALUE!
75 #VALUE!
 A B C D E F G H I J K
1 FIRST FINANCIAL MODEL
2 Sales growth 10%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash and marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 40%
12
13 Year 0 1 2 3 4 5
14 Income statement
15 Sales 1,000 1,100 1,210 1,331 1,464 1,611
16 Costs of goods sold (500) (550) (605) (666) (732) (805)
17 Interest payments on debt (32) (32) (32) (32) (32) (32)
18 Interest earned on cash and marketable securities 6 9 14 20 26 33
19 Depreciation (100) (117) (137) (161) (189) (220)
20 Profit before tax 374 410 450 492 538 587
21 Taxes (150) (164) (180) (197) (215) (235)
22 Profit after tax 225 246 270 295 323 352
23 Dividends (90) (98) (108) (118) (129) (141)
24 Retained earnings 135 148 162 177 194 211
25
26 Balance sheet
27 Cash and marketable securities 80 144 213 289 371 459
28 Current assets 150 165 182 200 220 242
29 Fixed assets
30 At cost 1,070 1,264 1,486 1,740 2,031 2,364
31 Depreciation (300) (417) (554) (715) (904) (1,124)
32 Net fixed assets 770 847 932 1,025 1,127 1,240
33 Total assets 1,000 1,156 1,326 1,513 1,718 1,941
34
35 Current liabilities 80 88 97 106 117 129
36 Debt 320 320 320 320 320 320
37 Stock 450 450 450 450 450 450
38 Accumulated retained earnings 150 298 460 637 830 1,042
39 Total liabilities and equity 1,000 1,156 1,326 1,513 1,718 1,941
40
41 Year 0 1 2 3 4 5
42 Free cash flow calculation
43 Profit after tax 246 270 295 323 352 #VALUE!
44 Add back depreciation 117 137 161 189 220 #VALUE!
45 Subtract increase in current assets (15) (17) (18) (20) (22) #VALUE!
46 Add back increase in current liabilities 8 9 10 11 12 #VALUE!
47 Subtract increase in fixed assets at cost (194) (222) (254) (291) (333) #VALUE!
48 Add back after-tax interest on debt 19 19 19 19 19 #VALUE!
49 Subtract after-tax interest on cash and mkt. securities (5) (9) (12) (16) (20) #VALUE!
50 Free cash flow 176 188 201 214 228 #VALUE!
51
52
53 Valuing the firm
54 Weighted average cost of capital 20%
55 Long-term free cash flow growth rate 5% <-- real growth 2% + inflation 3%?
56
57 Year 0 1 2 3 4 5
58 FCF 176 188 201 214 228
59 Terminal value #VALUE!
60 Total 176 188 201 214 228
61
62 Enterprise value, present value of row 60 #VALUE! enterprise value, present value of row
63 Add in initial (year 0) cash and mkt. securities #VALUE! add in initial cash and mkt. securities
64 Asset value in year 0 #VALUE! asset value in year 0
65 Subtract out value of firm's debt today #VALUE! subtract out value of firm's debt today
66 Equity value #VALUE!
67
68 Cash and marketable securities as negative debt
69 NPV of row 60 = enterprise value #VALUE!
70 Net year 0 debt: debt minus cash #VALUE!
71 Equity value #VALUE!
72
73
74 Valuing the firm--using mid-year discounting
75 Weighted average cost of capital 20%
76 Long-term free cash flow growth rate 5%
77
78 Year 0 1 2 3 4 5
79 FCF 176 188 201 214 228
80 Terminal value #VALUE!
81 Total 176 188 201 214 228
82
83 Enterprise value, NPV of row 81 #VALUE!
84 Add in initial (year 0) cash and mkt. securities #VALUE!
85 Asset value in year 0 #VALUE!
86 Subtract out value of firm's debt today #VALUE!
87 Equity value #VALUE!
88
89
90 Data table: The effect of sales growth (cell B2) on equity valuation
91 Growth #VALUE!
92 0%
Equity value

93 2% Sales Growth and Equity Value

1120
94 4%
1110
95 6%
96 8% 1100
97 10% 1090
98 12% 1080
99 14%
1070
100 16%
101 1060
102 1050
103 1040
104 0% 2% 4% 6% 8% 10% 12% 14% 16%
105 Sales growth
106
107 WACC ¯
108 =IF(B75<=B76,"nmf",B87) 10% 12% 14% 16% 18% 20% 22% 24% 26%
109 0%
110 Long-term FCF growth rate --> 2%
111 4%
112 6%
113 8%
114 10%
115 12%
116 14%
117 16%
118
119 Note: Data tables are discussed in Chapter 31.
 A B C D E
1 NEGATIVE CASH BALANCES: ILLUSTRATION
2 Sales growth 20% <-- Increased from 10%
3 Current assets/Sales 20% <-- Increased from 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 80% <-- Increased from 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash and marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 50% <-- Increased from 40%
12
13 Year 0 1 2 3
14 Income statement
15 Sales 1,000 1,200 1,440 1,728
16 Costs of goods sold (500) (600) (720) (864)
17 Interest payments on debt (40) (40) (40) (40)
18 Interest earned on cash and marketable securities 6 4 (0) (6)
19 Depreciation (100) (124) (156) (194)
20 Profit before tax 366 440 524 624
21 Taxes (147) (176) (210) (249)
22 Profit after tax 220 264 314 374
23 Dividends (110) (132) (157) (187)
24 Retained earnings 110 132 157 187
25
26 Balance sheet
27 Cash and marketable securities 80 28 (36) (113)
28 Current assets 200 240 288 346
29 Fixed assets
30 At cost 1,100 1,384 1,732 2,157
31 Depreciation (300) (424) (580) (774)
32 Net fixed assets 800 960 1,152 1,382
33 Total assets 1,080 1,228 1,404 1,615
34
35 Current liabilities 80 96 115 138
36 Debt 400 400 400 400
37 Stock 450 450 450 450
38 Accumulated retained earnings 150 282 439 626
39 Total liabilities and equity 1,080 1,228 1,404 1,615
40
41 Year 0 1 2 3
42 Free cash flow calculation
43 Profit after tax
44 Add back depreciation
45 Subtract increase in current assets
46 Add back increase in current liabilities
47 Subtract increase in fixed assets at cost
48 Add back after-tax interest on debt
49 Subtract after-tax interest on cash and mkt. securities
 A B C D E
50 Free cash flow
 F G H
TRATION 1
2
3
4
5
6
7
8
9
10
11
12
13 4 5
14
15 2,074 2,488
16 (1,037) (1,244)
17 (40) (40)
18 (13) (21)
19 (242) (299)
20 742 884
21 (297) (354)
22 445 530
23 (223) (265)
24 223 265
25
26
27 (209) (325)
28 415 498
29
30 2,675 3,306
31 (1,016) (1,315)
32 1,659 1,991
33 1,865 2,163
34
35 166 199
36 400 400
37 450 450
38 849 1,114
39 1,865 2,163
40
41 4 5
42
43 #VALUE!
44 #VALUE!
45 #VALUE!
46 #VALUE!
47 #VALUE!
48 #VALUE!
49 #VALUE!
 F G H
50 #VALUE!
 A B C D E
1 NO NEGATIVE CASH BALANCES
2 Sales growth 20% <-- Increased from 10%
3 Current assets/Sales 20% <-- Increased from 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 80% <-- Increased from 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash and marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 50% <-- Increased from 40%
12
13 Year 0 1 2 3
14 Income statement
15 Sales 1,000 1,200 1,440 1,728
16 Costs of goods sold (500) (600) (720) (864)
17 Interest payments on debt (40) (20) - -
18 Interest earned on cash and marketable securities 6 3 - -
19 Depreciation (100) (124) (156) (194)
20 Profit before tax 366 459 564 670
21 Taxes (147) (184) (226) (268)
22 Profit after tax 220 275 339 402
23 Dividends (110) (138) (169) (201)
24 Retained earnings 110 138 169 201
25
26 Balance sheet
27 Cash and marketable securities 80
28 Current assets 200 240 288 346
29 Fixed assets
30 At cost 1,100 1,384 1,732 2,157
31 Depreciation (300) (424) (580) (774)
32 Net fixed assets 800 960 1,152 1,382
33 Total assets 1,080 1,200 1,440 1,728
34
35 Current liabilities 80 96 115 138
36 Debt 400
37 Stock 450 450 450 450
38 Accumulated retained earnings 150 288 457 658
39 Total liabilities and equity 1,080 834 1,022 1,246
40
41 Year 0 1 2 3
42 Free cash flow calculation
43 Profit after tax
44 Add back depreciation
45 Subtract increase in current assets
46 Add back increase in current liabilities
47 Subtract increase in fixed assets at cost
48 Add back after-tax interest on debt
49 Subtract after-tax interest on cash and mkt. securities
 A B C D E
50 Free cash flow
 F G H
CASH BALANCES
1
2
3
4
5
6
7
8
9
10
11
12
13 4 5
14
15 2,074 2,488
16 (1,037) (1,244)
17 - -
18 - -
19 (242) (299)
20 795 945
21 (318) (378)
22 477 567
23 (239) (284)
24 239 284
25
26
27 #VALUE!
28 415 498
29
30 2,675 3,306
31 (1,016) (1,315)
32 1,659 1,991
33 2,074 2,488
34
35 166 199
36 #VALUE!
37 450 450
38 896 1,180
39 1,512 1,829
40
41 4 5
42
43
44
45
46
47
48
49
 A B C D E

1
TARGET DEBT-EQUITY RATIO
Cash is fixed, ratio of debt/equity changes in each year
2 Sales growth 10%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Net fixed assets/Sales 77%
6 Costs of goods sold/Sales 50%
7 Depreciation rate 10%
8 Interest rate on debt 10.00%
9 Interest paid on cash & marketable securities 8.00%
10 Tax rate 40%
11 Dividend payout ratio 60%
12
13 Year 0 1 2 3
14 Income statement
15 Sales 1,000 1,100 1,210 1,331
16 Costs of goods sold (500) (550) (605) (666)
17 Interest payments on debt (32) (16) - -
18 Interest earned on cash & marketable securities 6 6 6 6
19 Depreciation (100) (117) (137) (161)
20 Profit before tax 374 424 474 511
21 Taxes (150) (169) (190) (204)
22 Profit after tax 225 254 284 306
23 Dividends (135) (153) (171) (184)
24 Retained earnings 90 102 114 123
25
26 Balance sheet
27 Cash and marketable securities 80 80 80 80
28 Current assets 150 165 182 200
29 Fixed assets
30 At cost 1,070 1,264 1,486 1,740
31 Depreciation (300) (417) (554) (715)
32 Net fixed assets 770 847 932 1,025
33 Total assets 1,000 1,092 1,193 1,305
34
35 Current liabilities 80 88 97 106
36 Debt 320
37 Stock 450
Initial (year 0)
38 Accumulated retained earnings 150 252 365 488
debt/equity ratio:
39 Total liabilities and equity =B36/(B37+B38) 1,000 340 462 594
40
41 Target debt-equity ratio 0.53 0.40 0.35 0.30
42
43 Year 0 1 2 3
44 Free cash flow calculation
45 Profit after tax 254 284 306
46 Add back depreciation 117 137 161
47 Subtract increase in current assets (15) (17) (18)
 A B C D E
48 Add back increase in current liabilities 8 9 10
49 Subtract increase in fixed assets at cost (194) (222) (254)
50 Add back after-tax interest on debt 10 0 0
51 Subtract after-tax interest on cash & mkt. securities (4) (4) (4)
52 Free cash flow 176 188 201
 F G H
TY RATIO1
changes in each year
2
3
4
5
6
7
8
9
10
11
12
13 4 5
14
15 1,464 1,611
16 (732) (805)
17 - -
18 6 6
19 (189) (220)
20 550 592
21 (220) (237)
22 330 355
23 (198) (213)
24 132 142
25
26
27 80 80
28 220 242
29
30 2,031 2,364
31 (904) (1,124)
32 1,127 1,240
33 1,427 1,562
34
35 117 129
36 #VALUE!
37 #VALUE!
38 620 762
39 737 891
40
41 0.30 0.30
42
43 4 5
44
45 330 355
46 189 220
47 (20) (22)
 F G H
48 11 12
49 (291) (333)
50 0 0
51 (4) (4)
52 214 228
 A B C D E

1
PROJECT FINANCE
No dividends, debt repayment schedule fixed, net fixed assets const
2 Sales growth 15%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Costs of goods sold/Sales 45%
6 Depreciation rate 10%
7 Interest rate on debt 10.00%
8 Interest paid on cash and marketable securities 8.00%
9 Tax rate 40%
10 Dividend payout ratio 0% <-- No dividends until all the debt is paid off
11
12 Year 0 1 2 3
13 Income statement
14 Sales 1,150 1,323 1,521
15 Costs of goods sold (518) (595) (684)
16 Interest payments on debt (50) 0 0
17 Interest earned on cash and marketable securities 0 0 0
18 Depreciation (200) (200) (200)
19 Profit before tax 383 527 636
20 Taxes (153) (211) (255)
21 Profit after tax 230 316 382
22 Dividends 0 0 0
23 Retained earnings 230 316 382
24
25 Balance sheet
26 Cash and marketable securities 0
27 Current assets 200 173 198 228
28 Fixed assets
29 At cost 2,000 2,000 2,000 2,000
30 Depreciation 0
31 Net fixed assets 2,000 2,000 2,000 2,000
32 Total assets 2,200 2,173 2,198 2,228
33
34 Current liabilities 100 92 106 122
35 Debt 1,000
36 Stock 1,100 1,100 1,100 1,100
37 Accumulated retained earnings 0 230 546 928
38 Total liabilities and equity 2,200 1,422 1,752 2,149
39
40
41 FREE CASH FLOW CALCULATION
42 Year 0 1 2 3
43 Profit after tax 230 316 382
44 Add back depreciation 200 200 200
45 Subtract increase in current assets 28 (26) (30)
46 Add back increase in current liabilities (8) 14 16
47 Subtract increase in fixed assets at cost 0 0 0
 A B C D E
48 Add back after-tax interest on debt 30 0 0
49 Subtract after-tax interest on cash and mkt. securities 0 0 0
50 Free cash flow 479 504 568
 F G H

NANCE 1
fixed, net fixed assets constant
2
3
4
5
6
7
8
9
s until all the debt
10 is paid off
11
12 4 5
13
14 1,749 2,011
15 (787) (905)
16 0 0
17 0 0
18 (200) (200)
19 762 906
20 (305) (362)
21 457 544
22 0 0
23 457 544
24
25
26 #VALUE!
27 262 302
28
29 2,000 2,000
30 #VALUE!
31 2,000 2,000 <-- NFA don't change
32 2,262 2,302
33
34 140 161
35 #VALUE!
36 1,100 1,100
37 1,385 1,929
38 2,625 3,190
39
40
41
42 4 5
43 457 544
44 200 200
45 (34) (39) Cash flow generated
18 21 by depreciation
46
equals capital
47 0 0
expenditures.
 Cash flow generated
by depreciation
equals capital
F G H
expenditures.
48 0 0
49 0 0
50 641 725
 A B C D E

1
PROJECT FINANCE
With these parameters the project cannot pay off its debt
2 Sales growth 15%
3 Current assets/Sales 15%
4 Current liabilities/Sales 8%
5 Costs of goods sold/Sales 55%
6 Depreciation rate 10%
7 Interest rate on debt 10.00%
8 Interest paid on cash and marketable securities 8.00%
9 Tax rate 40%
10 Dividend payout ratio 0% <-- No dividends until all the debt is paid off
11
12 Year 0 1 2 3
13 Income statement
14 Sales 1,150 1,323 1,521
15 Costs of goods sold (633) (727) (836)
16 Interest payments on debt (90) (70) (50)
17 Interest earned on cash and marketable securities 82 173 192
18 Depreciation (105) (11) (12)
19 Profit before tax 405 687 814
20 Taxes (162) (275) (326)
21 Profit after tax 243 412 488
22 Dividends 0 0 0
23 Retained earnings 243 412 488
24
25 Balance sheet
26 Cash and marketable securities 0 2,062 2,262 2,537
27 Current assets 200 173 198 228
28 Fixed assets
29 At cost 2,000 105 116 129
30 Depreciation 0 (105) (116) (129)
31 Net fixed assets 2,000
32 Total assets 2,200 2,235 2,461 2,765
33
34 Current liabilities 100 92 106 122
35 Debt 1,000 800 600 400
36 Stock 1,100 1,100 1,100 1,100
37 Accumulated retained earnings 0 243 655 1,144
38 Total liabilities and equity 2,200 2,235 2,461 2,765
39
40
41 FREE CASH FLOW CALCULATION
42 Year 0 1 2 3
43 Profit after tax 243 412 488
44 Add back depreciation 105 11 12
45 Subtract increase in current assets 28 (26) (30)
46 Add back increase in current liabilities (8) 14 16
47 Subtract increase in fixed assets at cost 1895 (11) (12)
 A B C D E
48 Add back after-tax interest on debt 90 70 50
49 Subtract after-tax interest on cash and mkt. securities (82) (173) (192)
50 Free cash flow 2270 297 333
51 Note that the cash flow generated by
52 depreciation equals the increase in fixed
53 assets at cost.
54
 F G H

NANCE 1
ct cannot pay off its debt
2
3
4
5
6
7
8
9
s until all the debt
10 is paid off
11
12 4 5
13
14 1,749 2,011
15 (962) (1,106)
16 (30) (10)
17 217 250
18 (14) (15)
19 961 1,130
20 (384) (452)
21 577 678
22 0 0
23 577 678
24
25
26 2,898 3,358 #VALUE!
27 262 302
28
29 142 157
30 (142) (157)
31 <-- NFA don't change
32 3,160 3,659
33
34 140 161
35 200 0
36 1,100 1,100
37 1,720 2,398
38 3,160 3,659
39
40
41
42 4 5
43 577 678
44 14 15
45 (34) (39)
46 18 21
47 (14) (15)
 F G H
48 30 10
49 (217) (250)
50 373 420
51
52
53
54