seasonal data forecasting

[jaypatel]

Hi

I have seasonal data trends, which affects sales and costs at different times of the year.
I have approx 4 years worth of data, but is there any forecasting formula or code that can be used. I dont think =trend or =forecast works as its working on linear regression which is not suitable.

Cheers

Jay

 

[texasalynn]

Give a small sample of your data with the problem you want to analyze, with the expected results

 

[jaypatel]

Hi

data below, thanks for looking at it for me......

3 46315
4 73988
5 36376.25
6 44339
7 50464.3
8 39216.75
9 66692.5
10 28188.5
11 31035.5
12 34091.4
1 50013.75
2 54490.17
3 61043.5
4 36234
5 32990.18
6 132538
7 113655.74
8 121530.4
9 118084
10 141589.7
11 88103
12 105116.6
1 85692
2 36124
3 109339.7
4 78361.8
5 69923.4
6 100629.28
7 101855.2
8 131135.98
9 72305.1
10 70810.23
11 47383
12 85528.1
1 93157.38
2 63322.31
3 73272

of course, the month on the left, and the sales in the right hand side.

cheers

Jay

 

[texasalynn]

ok but what are you wanting to do?

 

[jaypatel]

sorry, should have mentioned.....

predict months 4 through to 12.....

I have other sales data, relating to the same period, but for a different office, so my thinking is, keep it simple with 1 set of data.

Regards

Jay

 

[shg]

There's a workbook with a forecast that includes your data at http://www.box.net/shared/5gi9g8x23p on sheet Example 2.

Please take a few minutes to read http://www.exceluser.com/solutions/seasonality-sales.htm before you ask any questions. The calculation of seasonal index is taken directly from there.

 

[Exceler8]

Sorry to open an old post - but quick question.

In the workbook in the attached link, cell G11 has this formula:
=IF(C11="Actual", E11 / INDEX($M$5:$M$16, MONTH(D11)), IF(C11="Forecast", Slope*B11 + Offset / INDEX($M$5:$M$16, MONTH(D11)), "???"))

Shouldn't the forumulas be using the SeasIndex? Perhaps I'm missing something, but thought I'd ask

=IF(C11="Actual", E11 / INDEX($N$5:$N$16, MONTH(D11)), IF(C11="Forecast", Slope*B11 + Offset / INDEX($N$5:$N$16, MONTH(D11)), "???"))