A spreadsheet containing R&E Supplies 2022 pro forma financial forecast, as shown in Table 3.5, is available

Question:

A spreadsheet containing R&E Supplies’ 2022 pro forma financial forecast, as shown in Table 3.5, is available for download from McGraw-Hill’s Connect or your course instructor (see the Preface for more information). Using the spreadsheet information presented next, and the modified equations determined in Problem 8 earlier, extend the forecast for R&E Supplies contained in Table 3.5 through 2023.

R&E Supplies Assumptions for 2023 ($ thousands) 30.0% Tax rate 86.0 % Dividend/earnings after tax Current

a. What is R&E’s projected external financing required in 2023? How does this number compare to the 2022 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 2023? 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.

Data from Problem 8

Table 3.5 presents a computer spreadsheet for estimating R&E Supplies’ external financing required for 2022. The text mentions that with modifications to the equations for equity and net sales, the forecast can easily be extended through 2023. By referencing the appropriate cells, write the modified equations for equity and net sales for 2023.

TABLE 3.5 Forecasting with a Spreadsheet: Pro Forma Financial Forecast for R&E Supplies, Inc., December 31, 25 Tax 26 Earnings after tax 27 Dividends paid 28 Additions to retained earnings 29 30 31 Current assets