a. What is the company's projected external financing required in 2021, if all variables stay the same and there is a 15% growth in sales? How does this
number compare to the 2020 projection? (See this analysis for 2021a in the spreadsheet)
b. Perform a sensitivity analysis on this projection. How does the company's projected external financing required change if the ratio of cost of goods sold to
net sales declines to 80 percent? (assuming growth of revenues is 10% for 2021) If you link your assumptions in the pro forma to the cells containing the
assumptions in the spreadsheet, it will be easy to change one number and see the resulting change. (See this analysis for 2021b in the spreadsheet)
c. Perform a scenario analysis on this projection. How does the company's projected external financing required change if a severe recession occurs in 2021?
Assume net sales decline 10 percent (i.e. 90% of 2020 sales), cost of goods sold rises to 92.5 percent of net sales due to price cutting, and current assets
increase to 36.5 percent of net sales as management fails to cut purchases promptly in response to declining sales. (See this analysis for 2021c in the
spreadsheet)
A B D E F $ 30,000 10% 10.00% 86.00% 12.00% 660 $ $ $ $ 760 100 $ $ 100 $ 4 Net sales 5 Growth rate in sales 6 Cost of goods soldet sales 7 Gen., sell, and admin. expenseset sales 8 Long-term debt 9 Current portion long-term debt 10 Interest rate 11 Tax rate 12 Dividends Paid/earnings after tax 13 Current assetset sales 14 Net fixed assets 15 Current liabilitieset sales 16 Owners' equity 15.00% 80% 12.00% 560 100 10.00% 33.00% 40.00% 30.00% 10.00% 33.00% 40.00% 30.00% 280 $ 14.50% $ 270 14.50% $ 1,730 2020 $ 33,000.00 $ 28,380.00 2021a $37,950 $32,637.00 $5,313 $4,554.00 17 INCOME STATEMENT 18 Year 19 Net sales 20 Cost of good sold 21 Gross profit 22 Gen., sell,and admin. exp. 23 Interest expense 24 Earnings before tax 25 Tax 26 Earnings after tax (Net Income) 27 Dividends paid 28 Additions to retained earnings 2021c $29,700 $27,473 $2,228 $3,564.00 $343 $ 4,620.00 $ 3,960.00 $ 76.00 2021b $41,745 $33,396.00 $8,349 $5,009.40 $343 $2,997 $2,007.71 $989 $395.55 $593 $343 $ $416 $278.70 $ 584.00 192.72 391.28 156.51 $ $ $137 $54.91 ($1,680) ($1,125.28) ($554) ($221.70) ($333) $ 234.77 $82 $ 9,900.00 $ 280.00 $ 11,385.00 $ 12,523.50 $ 270 $ 270 $10,841 270 $ 29 BALANCE SHEET 30 Current assets 31 Net fixed assets 32 Total assets 33 Current liabilities 34 Long-term debt 35 Equity 36 Total liabilities and shareholders' equity 37 38 EXTERNAL FUNDING REQUIRED 39 $ 10,180.00 $ 11,655.00 $ 12,793.50 $ 11,110.50 $ 4,785.00 $5,503 $6,053 $4,307 $ 660.00 $ 560 $ 560 $ 560 $ 1,964.77 2,047 2,640 2,308 $ 7,409.77 $8,110 $9,253 $7,174 $0.00 $ 2,770.23 $ 3,545.12 $ 3,540.02 $ 3,936.09 40 $ 10.180.00 $ 11.655.00 $12.793.50 $11.110.50 A B D E F $ 30,000 10% 10.00% 86.00% 12.00% 660 $ $ $ $ 760 100 $ $ 100 $ 4 Net sales 5 Growth rate in sales 6 Cost of goods soldet sales 7 Gen., sell, and admin. expenseset sales 8 Long-term debt 9 Current portion long-term debt 10 Interest rate 11 Tax rate 12 Dividends Paid/earnings after tax 13 Current assetset sales 14 Net fixed assets 15 Current liabilitieset sales 16 Owners' equity 15.00% 80% 12.00% 560 100 10.00% 33.00% 40.00% 30.00% 10.00% 33.00% 40.00% 30.00% 280 $ 14.50% $ 270 14.50% $ 1,730 2020 $ 33,000.00 $ 28,380.00 2021a $37,950 $32,637.00 $5,313 $4,554.00 17 INCOME STATEMENT 18 Year 19 Net sales 20 Cost of good sold 21 Gross profit 22 Gen., sell,and admin. exp. 23 Interest expense 24 Earnings before tax 25 Tax 26 Earnings after tax (Net Income) 27 Dividends paid 28 Additions to retained earnings 2021c $29,700 $27,473 $2,228 $3,564.00 $343 $ 4,620.00 $ 3,960.00 $ 76.00 2021b $41,745 $33,396.00 $8,349 $5,009.40 $343 $2,997 $2,007.71 $989 $395.55 $593 $343 $ $416 $278.70 $ 584.00 192.72 391.28 156.51 $ $ $137 $54.91 ($1,680) ($1,125.28) ($554) ($221.70) ($333) $ 234.77 $82 $ 9,900.00 $ 280.00 $ 11,385.00 $ 12,523.50 $ 270 $ 270 $10,841 270 $ 29 BALANCE SHEET 30 Current assets 31 Net fixed assets 32 Total assets 33 Current liabilities 34 Long-term debt 35 Equity 36 Total liabilities and shareholders' equity 37 38 EXTERNAL FUNDING REQUIRED 39 $ 10,180.00 $ 11,655.00 $ 12,793.50 $ 11,110.50 $ 4,785.00 $5,503 $6,053 $4,307 $ 660.00 $ 560 $ 560 $ 560 $ 1,964.77 2,047 2,640 2,308 $ 7,409.77 $8,110 $9,253 $7,174 $0.00 $ 2,770.23 $ 3,545.12 $ 3,540.02 $ 3,936.09 40 $ 10.180.00 $ 11.655.00 $12.793.50 $11.110.50
