FORECASTING SALES (please let this be a worksheet function)
 

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

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

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

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

Thanks

Al


--
MIVELD
------------------------------------------------------------------------
MIVELD's Profile: http://www.excelforum.com/member.php...o&userid=24562
View this thread: http://www.excelforum.com/showthread...hreadid=381536

For exponential, you could do the following. Plot a graph (XY) for one
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 various
options to plot the trend line, including linear or exponential. In the
options tab, select the boxes for "display equation on chart" and the
Rsquare. Click ok. You will be able to extrapolate the future sales
given the month from 13 onwards to 18. The Rsquare tells how good the
fit is. Rsquare of 1 is the prefect fit.


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381536

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


--
MIVELD
------------------------------------------------------------------------
MIVELD's Profile: http://www.excelforum.com/member.php...o&userid=24562
View this thread: http://www.excelforum.com/showthread...hreadid=381536

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

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381536

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

"MIVELD" wrote in
message ...

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

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

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

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

Thanks

Al


--
MIVELD
------------------------------------------------------------------------
MIVELD's Profile:
http://www.excelforum.com/member.php...o&userid=24562
View this thread: http://www.excelforum.com/showthread...hreadid=381536