Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

118 Chapter 3

107

108

109

110

111

112

113

114

115

116

117

BACDEFGHIJ

WACC

1,108.37

10% 12% 14% 16% 18% 20% 22% 24% 26%

2,038.12 25.093,1 40.066,1 1,188.82 1,032.29 907.35 805.37 720.58 649.00

Growth rate of sales --> 2%

2,447.00 43.265,1 69.519,1 1,310.08 1,121.12 974.36 857.11 761.31 681.59

3,128.45 98.208,1 48.992,2 1,471.75 1,235.34 1,058.12 920.35 810.19 720.10

4,491.36 27.361,2 56.939,2 1,698.09 1,387.62 1,165.81 999.40 869.93 766.32

8,580.08 90.567,2 62.912,4 2,037.61 1,600.82 1,309.39 1,101.03 944.61 822.81

10%

nmf 8,058.09 3,967.84 2,603.47 1,920.62 1,510.41 1,236.55 1,040.62 893.42

12%

nmf nmf 7,576.07 3,735.18 2,453.61 1,811.94 1,426.27 1,168.64 984.20

14%

nmf nmf nmf 7,130.34 3,519.60 2,314.48 1,710.85 52.501,1 68.743,1

16%

nmf nmf nmf nmf 6,717.58 3,319.58 2,185.15 17.472,1 07.616,1

=IF(B75<=B76,"nmf",B87)

7. If the growth rate > WACC, then Terminal value =

FCF

WACC

∗+

=∞

∞

∑

(

()

Long-term FCF growth)

. This problem was also discussed in

Chapter 2 in the context of the Gordon dividend model.

3.7 Debt as a Plug

In the model that we have shown, cash and marketable securities

were the plug and debt was a constant. However, for some values of the

model parameters, you can get negative cash and marketable securities.

Consider the following example, which is still the same model, but—as

indicated on the spreadsheet itself—with some different parameter

values:

Another variation is to calculate the effect on equity valuation of both

the long-term FCF growth and the WACC. Here, however, you have to

be careful: Examining the terminal value equation

Terminal value =

Long-term FCF growth)

Long-term FCF

FCF

WACC

∗+

−

ggrowth

will show you that this calculation only makes sense if the WACC is

greater than the growth rate.

To overcome this problem we deﬁ ne the

data table cell B107 (the calculation on which the data table does its

sensitivity analysis) in the following way:

119 Financial Statement Modeling

BACDEF

Sales growth 20% <-- Increased from 10%

Current assets/Sales 20% <-- Increased from 15%

Current liabilities/Sales 8%

Net fixed assets/Sales 80% <-- Increased from 77%

Costs of goods sold/Sales 50%

Depreciation rate 10%

Interest rate on debt 10.00%

Interest paid on cash and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 50% <-- Increased from 40%

Year 0 1 2 3 4 5

Income statement

Sales 1,000 1,200 1,440 1,728 2,074 2,488

Costs of goods sold (500) (600) )027( (864) (1,037) (1,244)

Interest payments on debt (40) (40) )04( (40) (40) (40)

Interest earned on cash and marketable securities 6 4 (0) (6) (13) (21)

Depreciation (100) (124) )651( (194) (242) (299)

Profit before tax 366 425044 624 742 884

Taxes (147) (176) )012( (249) (297) (354)

Profit after tax 220 413462 374 445 530

Dividends (110) (132) )751( (187) (223) (265)

Retained earnings 110 751231 187 223 265

Balance sheet

Cash and marketable securities 80 28 (36) (113) (209) (325)

Current assets 200 882042 346 415 498

Fixed assets

At cost 1,100 1,384 1,732 2,157 2,675 3,306

Depreciation (300) (424) )085( (774) (1,016) (1,315)

Net fixed assets 800 960 1,152 1,382 1,659 1,991

Total assets 1,080 1,228 1,404 1,615 1,865 2,163

Current liabilities 80 96 831511 166 199

Debt 400 004004 400 400 400

Stock 450 054054 450 450 450

Accumulated retained earnings 150 934282 626 849 1,114

Total liabilities and equity 1,080 1,228 1,404 1,615 1,865 2,163

NEGATIVE CASH BALANCES: ILLUSTRATION

Given these changes, the cash and marketable securities account (row

27) turns negative by year 2, a result which is obviously illogical. However,

the economic meaning of these negative numbers is clear: Given the

increased sales growth, increased current-asset and ﬁ xed-asset require-

ments, and increased dividend payouts, the ﬁ rm needs more ﬁ nancing.

What we want is a model which recognizes that

•

Cash cannot be less than zero.

•

When the ﬁ rm needs additional ﬁ nancing, it borrows money.

8. If you examine the model as it now stands, you will see that it implicitly assumes that

this extra ﬁ nancing comes at the cost of the cash and marketable securities. If we con-

sider this account a kind of checking account with interest, then the model implicitly

assumes that the ﬁ rm can ﬁ nance overdrafts from this account at the same rate of

interest as it is being paid on the account.

120 Chapter 3

BACDEFHG

Sales growth 20% <-- Increased from 10%

Current assets/Sales 20% <-- Increased from 15%

Current liabilities/Sales 8%

Net fixed assets/Sales 80% <-- Increased from 77%

Costs of goods sold/Sales 50%

Depreciation rate 10%

Interest rate on debt 10.00%

Interest paid on cash and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 50% <-- Increased from 40%

Year 0 12345

Income statement

Sales 1,000 1,200 1,440 1,728 2,074 2,488

Costs of goods sold (500) (600) (720) (864) (1,037) (1,244)

Interest payments on debt (40) (40) (42) )74((56))76(

Interest earned on cash and marketable securities 6 -14--

Depreciation (100) (124) (156) (194) (242) (299)

Profit before tax 366 425044937226878

Taxes (147) (176) (209) (249) (296) (351)

Profit after tax 220 413462344373725

Dividends (110) (132) (157) (187) (222) (263)

Retained earnings 110 751231222781362

Balance sheet

Cash and marketable securities 80 28 0 0 0 0 <-- =G39-G28-G32

Current assets 200 882042514643894

Fixed assets

At cost 1,100 1,384 1,732 2,157 2,675 3,306

Depreciation (300) (424) (580) (774) (1,016) (1,315)

Net fixed assets 800 960 1,152 1,382 1,659 1,991

Total assets 1,080 1,228 1,440 1,728 2,074 2,488

Current liabilities 80 96 115 661831991

Debt 400 634004016415 728 <-- =MAX(G28+G32-G35-G37-G38,F36)

Stock 450 054054054054054

Accumulated retained earnings 150 934282748626 1,111

Total liabilities and equity

1,080 1,228 1,440 1,728 2,074 2,488

NO NEGATIVE CASH BALANCES

Here is the model:

The equations for cash (row 27) and debt (row 36) are indicated for

the year-5 entries. What they do, in accounting terms is the following:

Cash and marketable securities remains the plug in the model.

The debt on the balance sheet conforms to the following test:

•

Current assets + Net ﬁ xed assets > Current liabilities + Last year’s debt

+ stock + Accumulated retained earnings?

In this case even if cash and marketable securities are equal to 0, we

need to increase debt balances in order to ﬁ nance the ﬁ rm’s productive

activities.

•

Current assets + Net ﬁ xed assets < Current liabilities + Last year’s debt

+ Stock + Accumulated retained earnings?

If this relation holds, then there is no need to increase debt, and, in

fact, the ﬁ rm has to have positive cash and marketable securities as a

balancing item, and the fact that we have made cash the plug will take

care of this concern.

121 Financial Statement Modeling

•

In Excel programming terms, this formula becomes (for the year 5,

but each previous year has the same type of equation) Max

(G28+G32-G35-G37-G38,F36).

As shown in the exercises to this chapter, the model can easily accom-

modate a situation in which there are minimum cash balances.

3.8 Incorporating a Target Debt/Equity Ratio into a Pro Forma

Another change we might want to make in our model relates to the plug.

Suppose that the ﬁ rm has a target ratio of debt to equity: In each of the

years 1–5, it wants debt and equity on the balance sheet to conform to

a certain ratio. This situation is illustrated in the following example:

BACDEFG

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 paid on cash & marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 60%

Year 0 1 2 3 4 5

Income statement

Sales 1,000 1,100 1,210 116,1464,1133,1

Costs of goods sold (500) (550) )506()666( (732) (805)

Interest payments on debt (32) )03()92()82((29))23(

Interest earned on cash & marketable securities 6 66666

Depreciation (100) (117) )731()161( (189) (220)

Profit before tax 374 409 125384544 560

Taxes (150) (164) )871()391( (208) (224)

Profit after tax 225 246 3

13092762 336

Dividends (135) (147) )061()471( (188) (202)

Retained earnings 90 98 521611701 134

Balance sheet

Cash and marketable securities 80 0808080808

Current assets 150 165 022002281 242

Fixed assets

At cost 1,070 1,264 1,486 463,2130,2047,1

Depreciation (300) (417)

)455()517( (904) (1,124)

Net fixed assets 770 847 932 1,025 042,1721,1

Total assets 1,000 1,092 1,193 265,1724,1503,1

Current liabilities 80 7988711601 129

Debt 320 287 203672482 331 <-- =G41*(G37+G38)

Stock 450 469 214154754 372 <-- =G33-G35-G36-G38

Accumulated retained earnings 150 248 695174553 730

Total liabilities and equity 1,000 1,092 1,193 265,1724,1503,1

Target debt-equity ratio 0.53 0.40 0.35 0.30 0.30 0.30

TARGET DEBT-EQUITY RATIO

ash is fixed, ratio of debt/equity changes in each year

Initial (year 0)

debt/equity ratio:

=B36/(B37+B38)

122 Chapter 3

Row 41 of the spreadsheet shows the target debt/equity ratio in each

of years 1–5. The ﬁ rm wants to lower its current debt/equity ratio of

53 percent to 30 percent over the next two years. The relevant changes

to the equations of our initial model are the following:

•

Debt = Target debt/equity ratio

(Stock + Retained earnings)

•

Stock = Total assets − Current liabilities − Debt − Accumulated retained

earnings

Note that the ﬁ rm will issue new debt years 4 and 5; in year 1 the stock

account grows (indicating that new equity is issued), whereas in sub-

sequent years stock decreases (indicating a repurchase of equity).

3.9 Project Finance: Debt Repayment Schedules

Here is another use for pro forma modeling: In a typical case of project

ﬁ nance, the ﬁ rm borrows money in order to ﬁ nance a project. The

borrowing often comes with strings attached:

•

The ﬁ rm is not allowed to pay any dividends until the debt is paid

off.

•

The ﬁ rm is not allowed to issue any new equity.

•

The ﬁ rm must pay back the debt over a speciﬁ ed period.

The following simpliﬁ ed example uses a variation of the version of

our basic model with cash balances. A new ﬁ rm or project is set up; in

year 0,

•

The ﬁ rm has assets of 2,200, which are ﬁ nanced with 200 of current

liabilities, 1,100 of equity, and 1,000 of debt.

•

The debt must be paid off in equal installments of principal over the

next ﬁ ve years. Until the debt is paid off, the ﬁ rm is not allowed to pay

dividends (if there is extra cash, this will go into a cash and marketable

securities account).

123 Financial Statement Modeling

The debt repayment terms are incorporated into the model by simply

specifying the debt balances at the end of each year. Since the ﬁ rm is

assumed to issue no new equity (in accordance with the covenants on

the lending), it follows that the model’s plug cannot be on the liabilities

side of the balance sheet. In our model the plug is the cash and market-

able securities account.

The model incorporates one other assumption often made about ﬁ xed

assets: It assumes that the net ﬁ xed assets stay constant over the life of

the project. Essentially this assumption means that the depreciation

accurately reﬂ ects the capital maintenance of the ﬁ xed assets. As you can

see from looking at rows 29–31, this means that the ﬁ xed assets at cost

BACDEFGH

Sales growth 15%

Current assets/Sales 15%

Current liabilities/Sales 8%

Costs of goods sold/Sales 45%

Depreciation rate 10%

Interest rate on debt 10.00%

Interest paid on cash and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 0% <-- No dividends until all the debt is paid off

Year 012345

Income statement

Sales 1,150 1,323 1,521 1,749 2,011

Costs of goods sold (518) (595) (684) (787) (905)

Interest payments on debt (90) (70) (50) (30) (10)

Interest earned on cash and marketable securities 1 3 9 21 40

Depreciation (211) (233) (257) (284) (314)

Profit before tax 333 428 539 669 822

Taxes (133) (171) (216) (268) (329)

Profit after tax 200 257 323 401 493

Dividends 0 0 0 0 0

Retained earnings 200 257 323 401 493

Balance sheet

Cash and marketable securities 0 19 64 173 359 633 <-- =G38-G27-G31

Current assets 200 173 198 228 262 302

Fixed assets

At cost 2,000 2,211 2,443 2,700 2,985 3,299

Depreciation 0 (211) (443) (700) (985) (1,299) <-- =F30-$B$6*(G29+F29)/2

Net fixed assets 2,000 2,000 2,000 2,000 2,000 2,000 <-- NFA don't change

Total assets

2,200 2,192 2,262 2,401 2,621 2,935

Current liabilities 100 92 106 122 140 161

Debt 1,000 800 600 400 200 0 <-- =F35-$B$35/5

Stock 1,100 1,100 1,100 1,100 1,100 1,100

Accumulated retained earnings 0 200 456 780 1,181 1,674

Total liabilities and equity

2,200 2,192 2,262 2,401 2,621 2,935

PROJECT FINANCE

No dividends, debt repayment schedule fixed, net fixed assets constant

124 Chapter 3

In this example, the ﬁ rm has no problem in making its debt principal

repayments. As credit analysts, we might be interested in how the ﬁ rm’s

ability to meet its payments is affected by the various parameter values.

In the following example we have increased the ratio of costs of goods

sold (COGS) to sales. With the new parameter values, the ﬁ rm can no

longer meet its debt repayments in years 1–3. This fact can be seen in

the pro forma: In years 1–4 the balances of cash and marketable securi-

ties are negative, indicating that—in order to make the repayment of the

loan principal—the ﬁ rm had to borrow money.

BACDEFGH

FREE CASH FLOW CALCULATION

Year 012345

Profit after tax 200 257 323 401 493

Add back depreciation 211 233 257 284 314

Subtract increase in current assets 28 (26) (30) (34) (39)

Add back increase in current liabilities (8) 14 16 18 21

Subtract increase in fixed assets at cost (211) (233) (257) (284) (314)

Add back after-tax interest on debt 54 42 30 18 6

Subtract after-tax interest on cash and mkt. securities (0) (2) (6) (13) (24)

Free cash flow

273 285 334 391 457

Cash flow generated

by depreciation

equals capital

expenditures.

9. From the point of view of corporate ﬁ nance, positive balances of cash are like negative

balances of debt. Thus, when the cash is negative, it is equivalent to the ﬁ rm having

borrowed money.

grow each year by the increase in asset depreciation. It also means that

in there is no net cash ﬂ ow from depreciation:

125 Financial Statement Modeling

3.10 Calculating the Return on Equity

We can use the pro forma models illustrated in this chapter to compute

the anticipated return on equity. Look at the previous example: Equity

owners in the project have to pay 1,100 in year 0. During years 1–4 they

get no payoffs, but in year 5 they own the company. Suppose that the

book value of the assets accurately reﬂ ects the market value. Then at the

end of year 5 the equity in the ﬁ rm is worth Stock + Accumulated

retained earnings = 2,255. The return on the equity investment (ROE)

is calculated as follows:

BACDEFGH

Sales growth 15%

Current assets/Sales 15%

Current liabilities/Sales 8%

Costs of goods sold/Sales 55%

Depreciation rate 10%

Interest rate on debt 10.00%

Interest paid on cash and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 0% <-- No dividends until all the debt is paid off

Year 0 12345

Income statement

Sales 1,150 1,323 1,521 1,749 2,011

Costs of goods sold (633) (727) (836) (962) (1,106)

Interest payments on debt (90) (70) (50) (30) (10)

Interest earned on cash and marketable securities (2) (6) (7) (4) 4

Depreciation (211) (233) (257) (284) (314)

Profit before tax 215 287 370 469 585

Taxes (86) (115) (148) (187) (234)

Profit after tax 129 172 222 281 351

Dividends 0 0 0 0 0

Retained earnings 129 172 222 281 351

Balance sheet

Cash and marketable securities 0 (52) (92) (83) (18) 114 <-- =G38-G27-G31, the plug

Current assets 200 173 198 228 262 302

Fixed assets

At cost 2,000 2,211 2,443 2,700 2,985 3,299

Depreciation 0 (211) (443) (700) (985) (1,299)

Net fixed assets 2,000 2,000 2,000 2,000 2,000 2,000 <-- NFA don't change

Total assets

2,200 2,121 2,107 2,145 2,244 2,416

Current liabilities 100 92 106 122 140 161

Debt 1,000 800 600 400 200 0

Stock 1,100 1,100 1,100 1,100 1,100 1,100

Accumulated retained earnings 0 129 301 523 804 1,155

Total liabilities and equity

2,200 2,121 2,107 2,145 2,244 2,416

PROJECT FINANCE

ith these parameters the project cannot pay off its debt

BACDEFGH

RETURN ON EQUITY (ROE)

Year 012345

Equity cash flow -1,100 - - - - 2,255 <-- =G22+G36+G37

RETURN ON EQUITY (ROE) 15.44% <-- =IRR(B58:G58)

126 Chapter 3

Note that this equity return increases as the equity investment

decreases.

Consider the case where the ﬁ rm initially borrows 1,500 and

the equity owners invest 600:

BACDEFGH

RETURN ON EQUITY (ROE)

Year 012345

Equity cash flow -600 - - - - 1,602 <-- =G22+G36+G37

RETURN ON EQUITY (ROE) 21.70% <-- =IRR(B58:G58)

As the following data table and graph show, the less the initial equity

investment, the greater the equity return:

BACDEFGHI

Data table: ROE as a function of initial

21.70% <-- =B59 , data table header

equity investment

2,000 10.80%

1,800 11.43%

1,600 12.19%

1,400 13.14%

1,200 14.36%

1,000 15.98%

800 18.26%

600 21.70%

400 27.61%

200 40.76%

ROE as a Function of Initial Equity Investment

10%

15%

20%

25%

30%

35%

40%

45%

02505007501,0001,2501,5001,7502,000

Equity investment

ROE

3.10.1 The ROE in Our First Full Model

The model in sections 3.2–3.4 has annual dividends. If we use the midyear

discounting explained in section 3.5.3 to value the ﬁ rm, we can compute

the return on equity (ROE) of an investor who purchases the ﬁ rm at

date 0 at its imputed equity valuation, gets ﬁ ve years of dividends, and

sells it for the imputed terminal value of the equity:

10. Interesting but not surprising: As the equity investment goes down, the project

becomes more leveraged and hence more risky for the equity investors. The increased

return should compensate the equity holders for this extra risk. The really interesting

question (not answered here) is whether the increased return is in fact a compensation

for the riskiness.

127 Financial Statement Modeling

3.11 Conclusion

Pro forma modeling is one of the basic skills of corporate ﬁ nancial

analysis—a devious combination of ﬁ nance, the implementation of

accounting rules, and spreadsheet skills. In order to be useful, ﬁ nancial

models must match the situation at hand, but they must also be simple

enough so that the user can easily understand why the results happen

(be they valuations, creditworthiness, or simply commonsense predic-

tions of how a ﬁ rm or project might look several years down the road).

Exercises

1. Here’s a basic exercise that will help you understand what’s going on in the model-

ing of ﬁ nancial statements. Replicate the model in section 3.1. That is, enter the

correct formulas for the cells and see that you get the same results as the book.

(This turns out to be more of an exercise in accounting than in ﬁ nance. If you’re

BACDEFHG

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 paid on cash and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 40%

Year 0 1 2 3 4 5

Income statement

Sales 1,000 1,100 464,1133,1012,1 1,611

Valuing the firm (mid-year discounting)

Weighted average cost of capital 20%

Long-term free cash flow growth rate 5%

Year 0 1 2 3 4 5

FCF 176 188 201 214 228

Terminal value 1,598 <-- =G58*(1+B55)/(B54-B55)

Total 176 188 201 214 1,826

Enterprise value, NPV of row 60 1,348 <-- =NPV(B54,C60:G60)*(1+B54)^0.5

Add in initial (year 0) cash and mkt. securities 80 <-- =B27

Asset value, year 0 1,428 <-- =B63+B62

Subtract out value of firm's debt today (320) <-- =-B36

Equity value 1,108 <-- =B64+B65

RETURN ON EQUITY (ROE)

Year 0 1 2 3 4 5

Projected dividends (1,108) 98 921811801 141

Anticipated equity value, year 5 1,737 <-- Terminal value + year 5 cash - year 5 debt

Equity cash flow (1,108) 98 108 118 129 1,878 <-- =SUM(G71:G72)

RETURN ON EQUITY (ROE) 18.29% <-- =IRR(B73:G73)

COMPUTING THE ROE IN THE FIRST FINANCIAL MODEL