9. A spreadsheet containing R&E Supplies 2018 pro forma financial forecast, as shown in Table 3.5, Using the spreadsheet information presented next, and the modified equations determined in question 8 earlier, extend the forecast for R&E Supplies contained in Table 3.5 through 2019. R&E Supplies Assumptions for 2019 (S thousands) Growth rate in net sales 30.0% Tax rate 45.0% Cost of goods sold/net sales 86.0% Gen, sell, & admin. Dividend/earnings after tax 50.0% Current assets/net sales 29.0% expenses/net sales 11.0% Net fixed assets $270 Long-term debt $560 Current liabilities/net sales 14.4% Current portion long-term debt $100 Interest rate 10.0% a. What is R&E's projected external financing required in 2019? How does this number compare to the 2018 projection? b. Perform a sensitivity analysis on this projection. How does R&E's projected external financing required change if the ratio of cost of goods sold to net sales declines from 86.0 percent to 84.0 percent? c. Perform a scenario analysis on this projection. How does R&E's projected external financing required change if a severe recession occurs in 2019? Assume net sales decline 5 percent, cost of goods sold rises to 88 percent of net sales due to price cutting, and current assets increase to 35 percent of net sales as management fails to cut purchases promptly in response to declining sales B C 1 2 Year 2017 Actual 2018 2019 3 Net sales $20,613 4 Growth rate in net sales 25.0% 5 Cost of goods sold/net sales 86.0% 6 Gen., sell., and admin. expenses/net sales 12.0% 7 Long-term debt $ 760 $660 8 Current portion long-term debt $ 100 $100 9 Interest rate 10.0% 10 Tax rate 45.0% 11 Dividend/earnings after tax 50.0% 12 Current assets/net sales 29.0% 13 Net fixed assets $280 14 Current liabilities/net sales 14.5% 15 Owners' equity $1,730 16 INCOME STATEMENT 17 18 Year Equations 2018 Forecast Forecast 2018 2019 19 Net sales = 83+ B3 C4 $25,766 20 Cost of goods sold = C5C19 22,159 21 Gross profit =C19-C20 3,607 22 Gen., sell., and admin. exp. = C6C19 3,092 23 Interest expense =C9(C7+C8+C40) 231 24 Earnings before tax =C21-C22-C23 285 25 Tax