∑ ∞ FCF WACC
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 refl ects the market value. Then at the end of year 5 the equity in the fi rm is worth Stock + Accumulated retained earnings = 2,255. The return on the equity investment (ROE) is calculated as follows:
1
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 1 2 3 4 5
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
With these parameters the project cannot pay off its debt
56
RETURN ON EQUITY (ROE)
Year 0 1 2 3 4 5
Equity cash flow -1,100 - - - - 2,255<-- =G22+G36+G37
RETURN ON EQUITY (ROE) 15.44% <-- =IRR(B58:G58)
Note that this equity return increases as the equity investment decreases.10 Consider the case where the fi rm initially borrows 1,500 and the equity owners invest 600:
56
RETURN ON EQUITY (ROE)
Year 0 1 2 3 4 5
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:
61
Data table: ROE as a function of initial 21.70% <-- =B59 , data table header equity investment 2,000 10.80%
1,800 11.43%
ROE as a Function of Initial Equity Investment
0%
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 fi rm, we can compute the return on equity (ROE) of an investor who purchases the fi rm at date 0 at its imputed equity valuation, gets fi 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.
3.11 Conclusion
Pro forma modeling is one of the basic skills of corporate fi nancial analysis—a devious combination of fi nance, the implementation of accounting rules, and spreadsheet skills. In order to be useful, fi 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 fi 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 fi 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 fi nance. If you’re
1
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 1,210 1,331 1,464 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 108 118 129 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
like many fi nancial modelers, you’ll see that there are some aspects of accounting that you’ve forgotten!)
2. The model of section 3.1 includes costs of goods sold but not selling, general, and administrative (SG&A) expenses. Suppose that the fi rm has $200 of these expenses each year, irrespective of the level of sales.
a. Change the model to accommodate this new assumption. Show the resulting profi t and loss statements, balance sheets, the free cash fl ows, and valuation.
b. Do a data table in which you show the sensitivity of the equity value to the level of SG&A. Let SG&A vary from $0 per year to $600 per year.
3. Suppose that in the model of section 3.1 the fi xed assets at cost for years 1–5 are 100 percent of sales (in the current model, it is net fi xed assets which are a function of sales). Change the model accordingly. Show the resulting profi t and loss state-ments, balance sheets, and free cash fl ows for years 1–5. (Assume that in year 0, the fi xed assets accounts are as shown in section 3.2. Note that since year 0 is given—it is the current situation of the fi rm, whereas years 1–5 are the predictions for the future—there is no need for the year-0 ratios to conform to the predicted ratios for years 1–5.)
4. Referring again to the model of section 3.2, suppose that the fi xed assets at cost follow the following step function:
Fixed assets as cost
Sales
if Sales 1,200 1,200 < Sales 1,400 Sales>>
⎧
⎨⎪
⎩⎪ 1 400,
Incorporate this function into the model.
5. Consider the model in section 3.6 (where debt is the plug).
a. Suppose that the fi rm has 1,000 shares and that it decides to pay, in year 1, a dividend per share of 15 cents. In addition, suppose that it wants this dividend per share to grow in subsequent years by 12 percent per year. Incorporate these changes into the pro forma model.
b. Do a sensitivity analysis in which you show the effect on the debt/equity ratio of the annual growth rate of dividends. Vary this rate from 0 to 18 percent, in steps of 2 percent. For this exercise, defi ne debt as net debt (i.e., debt minus cash and marketable securities). (Note that since the WACC is equal to 20 percent, the growth rate must be less than 20 percent.)
6. In the model of section 3.6, assume that the fi rm needs to have minimum cash balances of 25 at the end of each year. Introduce this constraint into the model.
7. In the valuation exercise of section 3.4, the terminal value is calculated using a Gordon dividend model on the cash fl ows. Replace this terminal value by the year-5 book value of debt plus equity. In making this change, you are essentially assuming that the book value correctly predicts the market value.
8. Repeat exercise 7, but this time replace the terminal value by an EBITDA ratio times year-5 anticipated EBITDA. Show a graph of the equity value of the fi rm as a function of the assumed year-5 EBITDA ratio, varying this ratio from 6–14.
9. In the project fi nance pro forma of section 3.9 it is assumed that the fi rm pays off its initial debt of $1,000 in equal installments of principal over fi ve years. Change
this assumption and assume instead that the fi rm pays off its debt in equal payments of interest and principal over fi ve years. Hint: You have to use the PMT function to fi nd the annual payments; then set up a loan table (as in Chapter 1) to split the annual payments into an interest and repayment of principal. Alternatively you can use the functions PPMT and IPMT discussed in Chapter 33.
10. This problem introduces the concept of “sustainable dividends”: The fi rm whose fi nancials are illustrated in the following spreadsheet wishes to maintain cash bal-ances of 80 over the next fi ve years. It also desires neither to issue additional stock nor to make any changes in its current level of debt. As a result, dividends are the plug in the balance sheet. Model this situation (note that for some parameter levels you may get “negative dividends,” indicating that there is no sustainable level of dividends).
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%
Ye sts of good
Recall that the free cash fl ow (FCF) is the amount of cash generated by the fi rm under the assumption that it has no debt. Thus, to calculate the FCFs of the fi rm, we calculate the profi t and loss statement that it would have had (including tax carryforwards) if it had no interest payments:
Earnings before interest and taxes (EBIT) -100 400
Interest -50 -80
Profit before tax -150 320 <-- =SUM(C3:C4)
Loss carryforward 0 -150 <-- =IF(B9<0,B9,0)
Taxable income -150 170 <-- =SUM(C5:C6)
Taxes (30%) 0 -51 <-- =-MAX(0,0.3*C7)
Profit after tax -150 119 <-- =C8+C7