Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast

Cumberland Industries' financial planners must forecast the company's financial results for the coming year.  The forecast for many items will be based on sales, and any additional funds needed will be obtained as notes payable.

a.  Assuming the historical trend continues, what will sales be in 2008?  Base your forecast on a spreadsheet regression analysis of the 2002-2007 sales data above, and include the summary output of the regression in your answer.  By what percentage are sales predicted to increase in 2008 over 2007?  Is the sales growth rate increasing or decreasing?

Here are the company's historical sales.  Hint: Use the Trend function to forecast sales for 2008.

b.   Cumberland's management believes that the firm will actually experience a 20 percent increase in sales during 2008.  Construct 2008 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 that dividends grow at an 8 percent rate.
c.   Now create a graph depicting the sensitivity of AFN for the coming year to the sales growth rate.  To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%, 20%, 25%, and 30%.
d.   Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2007  and 2008.  Also, calculate the FCF for 2008.
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?

Transcribed Image Text:

Year Sales Growth Rate 2002 |=C13+1 =C14+1 =C15+1 =C16+1 =C17+1 |=C18+1 129215000 180901000 235252000 294065000 396692000 455150000 |=TREND(D13:D18,C13:C18,C19) |=D14/D13-1 =D15/D14-1 |=D16/D15-1 =D17/D16-1 |=D18/D17-1 New Scenario Input Inv. / Sales Costs / Sales Base Case =G69 0.05 =H48 0.83 21377.62 -2498.51615600008 FCF =C150 AFN =H88