Forecasting

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi all,

I'm trying to do some basic forecasting to attempt to predict the amount of
products that will be brought in for service based on the number of units
sold, to attempt to establish the capacity we require in the service centre.

I have a planned sales profile (F12:AE12) with peaks and troughs as our
sales campaigns hit. The actual sales figures (E12:N12)currently, will be
filled as the weeks go by and will obviously end up at AE12
too.

I have tried using the function 'forecast' however that appears to be
linear, and does not reflect the sales profile I have in (F12:AE12) does
anyone have any suggestions?

thanks

tom
 
Tom -
I'm trying to do some basic forecasting to attempt to predict the amount
Click to expand...
of products that will be brought in for service based on the number of units
sold, to attempt to establish the capacity we require in the service centre.
I have a planned sales profile (F12:AE12) with peaks and troughs as our
Click to expand...
sales campaigns hit. The actual sales figures (E12:N12)currently, will be
filled as the weeks go by and will obviously end up at AE12 too.
I have tried using the function 'forecast' however that appears to be
Click to expand...
linear, and does not reflect the sales profile I have in (F12:AE12) does
anyone have any suggestions?

The FORECAST worksheet function is limited to one X variable. You may want
to see how current Service Demand depends on current Units Sold and on
lagged Units Sold (and maybe on other factors, like Sales Campaigns). You
could use the LINEST worksheet function, which handles multiple X variables,
or you could use the Regression tool of Excel's Analysis ToolPak. Although
these methods are limited to a linear combination of variables, some of
those variables could be transformed data (like squared Units Sold), thereby
allowing you to model curvilinear relationships. You could also use
categorical X variables (Yes/No) to indicate Sales Campaigns. My book, "Data
Analysis Using Microsoft Excel: Updated for Office XP," has several chapters
devoted to time series modeling.

- Mike Middleton, www.mikemiddleton.com
 
TREND is to LINEST as FORECAST is to SLOPE and INTERCEPT. It directly
gives you predicted values for a model that may involve multiple
predictor variables. Note that you have to supply the set of predictor
variables and the basic form of the model. If you are looking for
artificial intelligence that will take a pile of data and magically
produce a model without knowledge on your part, then you have the wrong
tool.

Jerry
 
Back
Top