Share
Explore BrainMass

Spreadsheet forecasting problem

I need to use excel to solve the problems.... Needs to be familiar with excel spreadsheets. Like in prob one you need to use the trend function in the formula bar to forecast sales in 2004.

Attachments

Solution Preview

Please see the attached file "forecast.xls" for details. I've also "copy and paste" into the body of this reply just in case you have problems in openning the file.
<br>
<br> 1/9/04
<br>
<br>Solution - 'Chapter 8. Ch 08-10 Build a Model
<br>
<br>Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast will be based o the percent of sales method, and any additional funds needed will be obtained as notes payable.
<br>
<br>
<br>
<br>a. Assuming the historical trend continues, what will sales be in 2004? Base your forecast on a spreadsheet regression
<br> analysis of the 1998-2003 sales data above, and include the summary output of the regression in your answer. By what
<br> percentage are sales predicted to increase in 2004 over 2003? Is the sales growth rate increasing or decreasing?
<br>
<br>Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2004.
<br>
<br>
<br> Year Sales Growth Rate
<br> 1998 129,215,000
<br> 1999 180,901,000 40.0%
<br> 2000 235,252,000 30.0%
<br> 2001 294,065,000 25.0%
<br> 2002 396,692,000 34.9%
<br> 2003 455,150,000 14.7%
<br> 2004 515,465,267
<br>
<br>
<br> % Increase in Predicted Sales for 2004 over 2003:
<br>
<br>2003Sales 455,150,000
<br>2004 Sales 515,465,267
<br>
<br>% increase 13.25% Note: This growth rate has been declining over time.
<br>
<br>
<br>b. Cumberland's management believes that the firm will actually experience a 20 percent increase in sales during 2004. Construct 2004 pro forma financial ...

$2.19