Forecast Sales with Seasonality

[Ben Fernando]

Hi,

Trying to project next year's sales (monthly) taking in to considerations
seasonality effect. Usually summer months are lower sales than the rest of
the year. I want to take in to effect last 2 years (2002 &2003), and project
2005. Is there a function or macro that I could use to forecast this.

Sincere thanks ahead of time for your time and kind assistance.

Thanks,
BF

 

[Guest]

In Excel Help, search for this:
"Project future values and perform regression analysis"

These functions should help:
FORECAST: Returns a value along a linear trend
TREND: Returns values along a linear trend
GROWTH: Returns values along an exponential trend
LINEST: Returns the parameters of a linear trend
LOGEST: Returns the parameters of an exponential trend

You may need to install the Analysis ToolPak Add-in

Good luck

 

[Alex Delamain]

not that I know of (which doesn't mean it doesn't exist!)

The best I can think of would be to do it in stages.

1) First calculate the average monthly sales for each year.
2) Then compare each month to its year's average - ie Jan 02 as
percentage of 2002 average, Jan 03 as percent of 2003 monthly averag
etc.
Averaging the January, february etc figures will show how each mont
relates to each of the other months (for example january might be 75
of the monthly average and July 130%)

Finally project the future monthly average using the annual average
calculated earlier (step1) and apply the monthly weightings to th
projected figure.

Hope this is of some us