The Beyond the Page features for this chapter include the spreadsheets for Dynamic Mattress?s long-term plan. How would its financial plan change if the dividend payout ratio were cut to 40%? Use the revised model to generate a new financial plan for 2021. Show how the financial statements given in Spreadsheet 18.1 would change. What would be required external financing?

Transcribed Image Text:

SPREADSHEET 18.1 A long-term planning model for Dynamic Mattress A н Model Inputs for Income Formula for years 2019 and beyond Statement 2017 2018 2019 2020 2021 2022 column G 2200.0 2,640.0 3,801.6 4,561.9 F3(1+$B$3) Growth rate (%) 20% Revenue 3,168.0 Tax rate (%) 3,497.5 4,197.0 G3'$B$8 50% Cost of goods sold 2024.0 2,428.8 2,914.6 Depreclation 42.8 F17'$B$10 Interest rate on 10% 23.5 24.8 29.7 35.6 debt (%) 322.2 G3-G4-G5 NWC/sales 152.5 223.7 0.110 EBIT 186.5 268.5 0.125 15.8 $B$5'F21 Flxed assets/sales Interest expense 6.0 9.0 24.0 34.0 177.5 COGS/sales 0.920 Earnings 146.5 207.9 244.4 288.1 G6-G7 before taxes Taxes at 50% 73.3 122.2 144.1 | $B$4"G8 9. Payout ratio 0.600 88.7 104.0 10 Depreclation/fixed 0.090 88.7 Net Income 73.3 104.0 122.2 144.1 G8-G9 assets 62.4 86.4 G10'$B$9 11 Dividends 46.8 53.2 73.3 35.5 57.6 | G10-G11 12 Relnvested 26.5 41.6 48.9 earnings 13 14 Balance Sheet Formula for 2019 2020 2022 (year-end) 2017 2018 2021 column G 15 Assets 501.8 $B$6G3 16 Net working capital 192.0 242.0 290.4 348.5 418.2 570.2 $B$7"G3 1,072.1 G16+G17 17 Net fixed assets 268.5 275.0 330.0 396.0 475.2 18 Total net assets 460.5 517.0 620.4 744.5 893.4 19 20 Llablitles and Equlty 240.4 461.5 | F21+G32 610.6 | F22+G12 21 Long-term debt" 60.0 90.0 157.9 340.4 22 Shareholders' 400.5 427.0 462.5 504.1 553.0 equity 893.4| 1,072.1 G21+G22 460.5 23 Total llabilitles 517.0 620.4 744.5 and shareholders' equity 24 25 Sources and Uses of Funds Operating cash flow (NI + Dep) 133.7 26 96.8 113.5 157.9 186.8 G5+G10 27 Increase In 50.0 48.4 58.1 69.7 83.6 G16-F16 working capital 114.8 137.8 G17-F17+G5 28 Investments In 30.0 79.8 95.7 flxed assets 53.2 29 Dividends 46.8 62.4 73.3 86.4 G11 SUM(G27:G29) 30 Total uses of cash 126.8 181.4 216.2 257.9 307.9 31 Required external financing 32 30.1 67.9 82.5 100.0 121.0 G30-G26 33 34 Financlal Ratios 0.43 G21/G23 0.17 35 Debt ratio 0.25 0.32 0.38 Interest coverage 25.4 36 20.7 14.2 11.2 9.5 G6/G7