Use a spreadsheet like that in Figure 19.2 to answer the following questions about Yummy Food:

a. What would be required external financing each year if the growth rate is 15% and the dividend payout ratio is 60% in 2015 and 20 I6?
b. Given the assumptions in part (a), what would be the amount of debt and equity issued if the firm wants to maintain its debt-equity ratio at a level of two-thirds?
c. What formulas would you put in cells H20 and H21 (as well as the corresponding cells in columns F and G) of the spreadsheet in Figure I9.2 to maintain the debt-equity ratio at two-thirds, while forcing the balance sheet to balance (that is, forcing debt + equity = total assets)?

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