A spreadsheet containing R&E Supplies’s 2015 pro forma financial forecast as shown i...

A spreadsheet containing R&E Supplies’s 2015 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 this spreadsheet, the information presented below, and the modified equations determined in question 6 above, extend the forecast for R&E Supplies contained in Table 3.5 through 2016.

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

The following three problems demonstrate that pro forma forecasts, cash budgets, and cash flow forecasts all yield the same estimated need for external financing—provided you don’t make any mistakes. For problems 8, 9, and 10, you may ignore the effect of added borrowing on interest expense.