FORECASTING SALES (please let this be a worksheet function)

[MIVELD]

Hi I have recently been given the task by my boss to forecast one of ou
key customers monthly usage until monthly until end of 2006,

I am starting with this customers indidual branches monthly usage fo
the past 3 years, Some branches will have opened and closed during thi
time, And i am looking to be able to predict monthly usage for the nex
18 months

Has anyone got any idea's on the best way to forcast within excel, I a
currently using trend (fomulae given to me on this sight) but people ar
saying this is not the best way

Exponentionally has been mentioned by a few of my collegues but i hav
no idea of the difference between that and linear.

Thanks

A

 

[mangesh_yadav]

For exponential, you could do the following. Plot a graph (XY) for on
city say london with 1 to 12 on X axis. Then select the line plotted
right click and select Add trendline. This will give you variou
options to plot the trend line, including linear or exponential. In th
options tab, select the boxes for "display equation on chart" and th
Rsquare. Click ok. You will be able to extrapolate the future sale
given the month from 13 onwards to 18. The Rsquare tells how good th
fit is. Rsquare of 1 is the prefect fit.


Manges

 

[MIVELD]

Thaks for the guidance, good stuff.

How do i get the data chart to show predicted figures so targets can be
set for future months

Thanks
Al

 

[mangesh_yadav]

Once you get the equation, calculate the forecasted values, and add the
in your chart

Manges

 

[Mike Middleton]

MIVELD -

First, before thinking about linear time trend or exponential smoothing or
some other functional form, "look at the data." That is, plot monthly usage
versus time. Since you have three years of monthly data, you should be able
to see if there is a repetitive seasonal pattern. If so, you should include
the expected seasonal variation in your forecasts. Chapter 20, Time Series
Seasonality, of my book, Data Analysis Using Microsoft Excel, describes
three methods: regression with indicator variables, autoregression, and
classical time series decomposition.

- Mike
www.mikemiddleton.com