434 CHAPTER 11 Financial Planning and Forecasting Financial Statements
The Booth Company’s sales are forecasted to increase from $1,000 in 2002 to $2,000 in 2003.
Here is the December 31, 2002, balance sheet:
Cash $ 100 Accounts payable $ 50
Accounts receivable 200 Notes payable 150
Inventories 200 Accruals 50
Net fixed assets 500 Long-term debt 400
Common stock 100
Retained earnings 250
Total assets $1,000 Total liabilities and equity $1,000Booth’s fixed assets were used to only 50 percent of capacity during 2002, but its current assets
were at their proper levels. All assets except fixed assets increase at the same rate as sales, and
fixed assets would also increase at the same rate if the current excess capacity did not exist.
Booth’s after-tax profit margin is forecasted to be 5 percent, and its payout ratio will be 60 per-
cent. What is Booth’s additional funds needed (AFN) for the coming year?Spreadsheet Problem
Start with the partial model in the file Ch 11 P10 Build a Model.xlsfrom the textbook’s web
site. Cumberland Industries’ financial planners must forecast the company’s financial results for
the coming year. The forecast will be based on the percent of sales method, and any additional
funds needed will be obtained by using a mix of notes payable, long-term debt, and common
stock. No preferred stock will be issued. Data for the problem, including Cumberland Indus-
tries’ balance sheet and income statement, can be found in the spreadsheet problem for Chap-
ter 9. Use these data to answer the following questions.
a.Cumberland Industries has had the following sales since 1997. Assuming the historical trend
continues, what will sales be in 2003?
Year Sales
1997 $129,215,000
1998 180,901,000
1999 235,252,000
2000 294,065,000
2001 396,692,000
2002 455,150,000
Base your forecast on a spreadsheet regression analysis of the 1997–2002 sales. By what percent-
age are sales predicted to increase in 2003 over 2002? Is the sales growth rate increasing or de-
creasing?
b.Cumberland’s management believes that the firm will actually experience a 20 percent in-
crease in sales during 2003. Construct the 2003 pro forma financial statements. Cumberland
will not issue any new stock or long-term bonds. Assume Cumberland will carry forward its
current amounts of short-term investments and notes payable, prior to calculating AFN. As-
sume that any additional funds needed (AFN) will be raised as notes payable (if AFN is nega-
tive, Cumberland will purchase additional short-term investments). Use an interest rate of 9
percent for short-term debt (and for the interest income on short-term investments) and a rate
of 11 percent for long-term debt. No interest is earned on cash. Use the beginning of year
debt balances to calculate net interest expense. Assume dividends grow at an 8 percent rate.
c.Now create a graph that shows the sensitivity of AFN to the sales growth rate. To make this
graph, compare the AFN at sales growth rates of 5, 10, 15, 20, 25, and 30 percent.
d.Calculate net operating working capital (NOWC), total operating capital, NOPAT, and oper-
ating cash flow (OCF) for 2002 and 2003. Also, calculate the free cash flow (FCF) for 2003.
e.Suppose Cumberland can reduce its inventory to sales ratio to 5 percent and its cost to sales
ratio to 83 percent. What happens to AFN and FCF?11–10
BUILD A MODEL: FORECASTING
FINANCIAL STATEMENTS11–9
ADDITIONAL FUNDS NEEDED