Forecasting Functions

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

I have a spreadsheet that has 280 part numbers listed
vertically and 20 columns showing monthly unit sales from
Dec-01 up to July-03. I'd like to forecast sales for Aug-
Dec. I know there is FORECAST and TREND function but I'm
in no way a stats person. I can't seem to get them to
work. Can someone give me some direction in plain english?
I'm becoming adept at Excel but stats/calculus/algebra
will always be my bane!! Help!

I'm using Excel 2000

Much appreciated!

Cameron
 
Cameron -
I have a spreadsheet that has 280 part numbers listed vertically and 20
Click to expand...
columns showing monthly unit sales from Dec-01 up to July-03. I'd like to
forecast sales for Aug-Dec. I know there is FORECAST and TREND function but
I'm in no way a stats person. I can't seem to get them to work. Can someone
give me some direction in plain english? I'm becoming adept at Excel but
stats/calculus/algebra will always be my bane!! Help! I'm using Excel 2000 <

I recommend that you first _Look At The Data_ using an Excel Line chart. You
may identify seasonal variation or other systematic patterns, requiring a
more elaborate model. If you see only linear time trend, then the simplest
use of FORECAST or TREND may be appropriate.

- Mike Middleton, www.usfca.edu/~middleton
 
Paul Corrado said:
Cameron,

In simple terms, these functions work off of a Linear Regression using the
Least Squares Method. They take the known X's and Y's and develop an
equation for that line in the form of Y= mX + b. They then take the X
value you wish to use as a predictor and calculate the expected value of Y.
If you are not familiar with Linear Regression you should spend a little
time to do some research on the Web. (I found this site and it seems to do
a decent job
http://cne.gmu.edu/modules/dau/stat/regression/linregsn/linregsn_frm.html)

In your case.

Known X's are the independent variables. For example if you were predicting
Sales base on Advertising Expense, Advertising Expense would be the Known X.

Known Y's are the dependent variables. Returning to the previous example of
predicting sales based on advertising expense, Sales would be the known Y's.

Continuing with the example, the "X" or "New X" called for in the Function
would be the forecasted advertising expense.

I should also point out that it seems you only have Months (X's) and Sales
(Y's) information. As a general rule, time itself is not the best choice
for X and would not be my choice for the independent variable. If I had no
other potential X's to choose from I would use another method, other than a
regression analysis, to develop a forecast.

HTH

PC
Click to expand...


Cameron ...

If you have time series data it is quite unwise to assume that one
trend is all that is required. You might have level shifts , or local
time trends or seasonal pulses or one time anomalies ( pulses ) ... i
strongly suggest that you not use EXCEL but rather look into FreeFore
http://www.autobox.com/freef.exe

and AUTOBOX at http://www.autobox.com


regards

dave reilly
AFS
 
Back
Top