Go back to problem 12 and build a financial planning model based on percentage of sales for Eagle Sports Supply using a spreadsheet similar to Figure 19.2.

a. Assume 2013 sales grow at the sustainable growth rate calculated in problem 13 and produce the pro forma income statement and balance sheet. Is the forecasted capital structure consistent with 2012 capital structure?
b. Assuming 2013 sales grow at the internal growth rate calculated in problem 13, produce the pro forma income statement and balance sheet. Is the required external financing zero, the same as what you found in problem 13? Is the forecasted net income the same as the forecasted net income in problem 13? What is going on?
c. Calculate an alternative growth rate using the following formula.

The ROE is the same as the one used in the other growth rate formulas. It is based on beginning equity, 2012 net income/2011 equity. Use this new growth rate to produce the 2013 pro forma statements. What is the required external financing? How does it compare to the result in 34(b)?

Transcribed Image Text:

в с Н 1 Model Inputs Base Year Formula for Column G Income Statement 2014 2015 2016 3 Sales growth rate 0.4 2,420.0 =F3"(1+$B$3) 2,178.0 =G3*$B$8 2,200.0 Revenue 2,000 0.1 4 Tax rate 1,800 1,980.0 Cost of goods sold 5 Interest rate 6 NWC/sales 7 Fixed assets/sales 0.4 8 COGS/sales 200 220.0 242.0 =G3-G4 0.1 EBIT 46.4 =$B$5*F20 0.1 40.0 Interest expense 40 Earnings before taxes 160 180.0 =G5-G6 195.6 =$B$4*G7 0.9 Taxes 64 72.0 78.2 96 108.0 117.4 =G7-G8 9 Payout ratio 2/3 Net income Dividends Addition to retained earnings =G9*$B$9 =G9-G10 10 64 72.0 78.2 11 32 36.0 39.1 12 Balance Sheet (year-end) 13 14 Assets: Net operating working capital Property, plant & equipment Total assets 242.0 =$B$6*G3 968.0 =$B$7*G3 15 200 220.0 800 880.0 16 1,100.0 =G15+G16 17 1,000 1,210.0 18 Liabilities and equity: 19 Long-term debt (note a) Shareholders' equity (note b) Total liab. & share. equity 20 400 464.0 534.9 =F20+G24 21 675.1 =F21+G11 600 636.0 1,210.0=G20+G21 1,000 1,100.0 23 Required external financing 70.9 24 64.0 =G17-F17-G11 25 26 27 Notes 28 (a) Long-term debt, the balancing item, increases by required external financing. 29 (b) Shareholders' equity equals its value in the previous year plus addition to earnings retained for the year. 30 plowback ratio x return on equity 2012 assets 2011 equity Growth rate plowback ratio x return on equity