Calculating future sales?

[unknown01011980]

Hello.

I need help in calculating future sales.

I have sales data of the past 32 months and i need to predict sales in the next 16 months. Sales of the same month but diferent year are prety much equal . On the other hand there is a big diference in the sales in summer and winther. Sales have its peak in summer monts and for a few days around New Year and aftherwards the fell prety much quickly. Whats is the best way to predict the sales for the next monts?

I have tried to calculate the trend in excel with the method of least-squeares and i got some numbers. But the predicted sale in the summer months was 30 to 40% lower than the actual sale in this monts in the past 2 years, and the predicted sales in the winter monts was around 20% higher than the actual sales in this monts in the past 2 years.

So can you give me some advise how to predict sales in the next 16 monts, and give me a tutorial in excel how to do that.

 

[Andrew Poulsom]

Welcome to MrExcel.

This may help you:

http://www.exceluser.com/solutions/seasonality-sales.htm

 

[SydneyGeek]

It sounds like you need to trend the common months (ie, the Januaries, then the Februaries, etc) and then assemble the forecast from those trend figures. So, high level...

Build a table with numbers 1 to 12 down the left edge, and years 2009 to 2014 across the top.
Use SUMPRODUCT() to populate that table from your actual data, matching the month number and year.
Clean out any zeros (they are missing data) and then use the TREND() function across the rows to get the remaining months of the forecast.
Finally, use SUMPRODUCT() to pull the forecast figures back from the table.

Denis

 

[unknown01011980]

It sounds like you need to trend the common months (ie, the Januaries, then the Februaries, etc) and then assemble the forecast from those trend figures. So, high level...

Build a table with numbers 1 to 12 down the left edge, and years 2009 to 2014 across the top.
Use SUMPRODUCT() to populate that table from your actual data, matching the month number and year.
Clean out any zeros (they are missing data) and then use the TREND() function across the rows to get the remaining months of the forecast.
Finally, use SUMPRODUCT() to pull the forecast figures back from the table.

Denis

I dont get it.
Iv made that table and january of the diferent year are in the same raw. Now in the raw i have data from jan 2009, jan 2010 and jan 2011. Now, how can i apply trend on this numbers to fill the cell that represent jan 2012? Could you detaly explain to me?

Thanks.

 

[SydneyGeek]

Setup:

A2:BH2 is months from Jan-09 to Dec-13
A3:AG3 is actual data from Jan-09 to Sep-11

B8:B19 is numbers 1-12 (months)
C7:G11 is 2009-2013 (years)

First pull the actuals into the table.
C8 is =SUMPRODUCT((MONTH($A$2:$AG$2)=$B8)*(YEAR($A$2:$AG$2)=C$7)*$A$3:$AG$3)
Fill down and across, then delete any 0 results.

Now calculate the trends.
F8 is =TREND($C8:E8,$C$7:E$7,F$7)
Copy and psate into all blank cells in the table. That gives you the forecast values.

Finally, pull these forecasts back into the original series.
AH3 is =SUMPRODUCT(($B$8:$B$19=MONTH(AH2))*($C$7:$G$7=YEAR(AH2))*$C$8:$G$19)
Fill right.

Plot the data to check the projection.

Denis

 

[SydneyGeek]

How did you go with this?

Denis