Spreadsheet Problem

The Millionaires Brain

Think Like A Millionaire

Get Instant Access



Start with the partial model in the file Ch 11 P10 Build a Model.xls from 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 Industries' balance sheet and income statement, can be found in the spreadsheet problem for Chapter 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?






2000 2001 2002

$129,215,000 180,901,000 235,252,000 294,065,000 396,692,000 455,150,000

Base your forecast on a spreadsheet regression analysis of the 1997-2002 sales. By what percentage are sales predicted to increase in 2003 over 2002? Is the sales growth rate increasing or decreasing?

b. Cumberland's management believes that the firm will actually experience a 20 percent increase 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. Assume that any additional funds needed (AFN) will be raised as notes payable (if AFN is negative, 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 operating 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?

Sue Wilson, the new financial manager of Northwest Chemicals (NWC), an Oregon pro

ducer of specialized chemicals for use

in fruit orchards, must prepare a financial forecast for

2003. NWC's 2002 sales

were $2 billion, and the marketing department is forecasting a 25

percent increase for 2003

. Sue thinks the company was operating at full capacity in 2002, but

she is not sure about this. The 2002 financial statements, plus some other data, are shown





of Sales

of Sales

Cash and securities $ 20


Accounts payable and

Accounts receivable 240


accruals $ 100 5%

Inventories 240


Notes payable 100

Total current assets $ 500

Total current liabilities $ 200

Net fixed assets 500


Long-term debt 100

Total assets $1,000

Common stock 500

Retained earnings 200

Total liabilities and equity $1,000



of Sales



Cost of goods sold (COGS)



Sales, general, and administrative costs (SGA)



Earnings before interest and taxes

$ 100.00



Earnings before taxes

$ 90.00

Taxes (40%)


Net income

$ 54.00

Dividends (40%)


Addition to retained earnings

$ 32.40


NWC Industry

Profit margin

2.70 4.00

Return on equity

7.71 15.60

Days sales outstanding (365 days)

43.80 days 32.00 days

Inventory turnover

8.33X 11.00X

Fixed assets turnover

4.00 5.00


30.00% 36.00%

Times interest earned

10 X 9.40X

Current ratio

2.50 3.00

Return on invested capital (NOPAT/ Operating capital)

6.67% 14.00%

See Ch 11 Show.ppt for a

PowerPoint presentation of the Mini Case and Ch 11 Mini Case.xls for detailed calculations.

Assume that you were recently hired as Wilson's assistant, and your first major task is to help her develop the forecast. She asked you to begin by answering the following set of questions.

a. Describe three ways that pro forma statements are used in financial planning.

b. Explain the steps in financial forecasting.

c. Assume (1) that NWC was operating at full capacity in 2002 with respect to all assets, (2) that all assets must grow proportionally with sales, (3) that accounts payable and accruals will also grow in proportion to sales, and (4) that the 2002 profit margin and dividend payout will be maintained. Under these conditions, what will the company's financial requirements be for the coming year? Use the AFN equation to answer this question.

d. How would changes in these items affect the AFN: (1) sales increase? (2) the dividend payout ratio increases? (3) the profit margin increases? (4) the capital intensity ratio increases? and (5) NWC begins paying its suppliers sooner? (Consider each item separately and hold all other things constant.)

e. Briefly explain how to forecast financial statements using the percent of sales approach. Be sure to explain how to forecast interest expenses.

f. Now estimate the 2003 financial requirements using the percent of sales approach. Assume (1) that each type of asset, as well as payables, accruals, and fixed and variable costs, will be the same percent of sales in 2003 as in 2002; (2) that the payout ratio is held constant at 40 percent; (3) that external funds needed are financed 50 percent by notes payable and 50 percent by long-term debt (no new common stock will be issued); (4) that all debt carries an interest rate of 10 percent; and (5) interest expenses should be based on the balance of debt at the beginning of the year.

g. Why does the percent of sales approach produce a somewhat different AFN than the equation approach? Which method provides the more accurate forecast?

h. Calculate NWC's forecasted ratios, and compare them with the company's 2002 ratios and with the industry averages. Calculate NWC's forecasted free cash flow and return on invested capital (ROIC).

i. Based on comparisons between NWC's days sales outstanding (DSO) and inventory turnover ratios with the industry average figures, does it appear that NWC is operating efficiently with respect to its inventory and accounts receivable? Suppose NWC were able to bring these ratios into line with the industry averages and reduce its SGA/Sales ratio to 33 percent. What effect would this have on its AFN and its financial ratios? What effect would this have on free cash flow and ROIC?

j. Suppose you now learn that NWC's 2002 receivables and inventories were in line with required levels, given the firm's credit and inventory policies, but that excess capacity existed with regard to fixed assets. Specifically, fixed assets were operated at only 75 percent of capacity.

(1) What level of sales could have existed in 2002 with the available fixed assets?

(2) How would the existence of excess capacity in fixed assets affect the additional funds needed during 2003?

k. The relationship between sales and the various types of assets is important in financial forecasting. The percent of sales approach, under the assumption that each asset item grows at the same rate as sales, leads to an AFN forecast that is reasonably close to the forecast using the AFN equation. Explain how each of the following factors would affect the accuracy of financial forecasts based on the AFN equation: (1) economies of scale in the use of assets and (2) lumpy assets.

Was this article helpful?

0 0
7 Ways To Success While You Sleep

7 Ways To Success While You Sleep

By setting up various online operations, you can generate perpetual income. Let Your Wealth Increase Through Successful Online Ventures While You Sleep and Live Like a King. Learn How to Implement and Management Your Own Online Empire. Worldwide ecommerce sales to consumers are now at around 1.5 trillion annually and still growing at a rapid rate as more and more people spend for more products and services.

Get My Free Ebook

Post a comment