How to use triple exponential smoothing to forecast in Excel

Question

I have been burdened with the task of coming up with a forecast plan for my company. I have no experience and am VERY new to the whole forecasting scene. As of right now, my company has no plans of investing in any forecasting software so my only tool is Excel. I've tried to do some research online myself and it seems that this triple smoothing method would be a great asset, but I'm a little confused and I guess I don't really understand the equations.

Below I have provided 3 years worth of sales for one item. We forecast in periods (4 weeks = 1 period). So there are 13 periods in one year. When we forecast, we have to forecast out 6 periods into the future, please help me use the triple smoothing technique to accomplish this.

Period 10 2009  69,088
Period 11 2009  83,400
Period 12 2009  75,735
Period 13 2009  79,526
Period 01 2010  81,005
Period 02 2010  94,013
Period 03 2010  90,567
Period 04 2010  94,568
Period 05 2010  101,687
Period 06 2010  93,540
Period 07 2010  84,249
Period 08 2010  91,280
Period 09 2010  78,531
Period 10 2010  89,465
Period 11 2010  83,341
Period 12 2010  87,106
Period 13 2010  65,636
Period 01 2011  79,632
Period 02 2011  89,722
Period 03 2011  87,483
Period 04 2011  99,228
Period 05 2011  113,215
Period 06 2011  96,057
Period 07 2011  95,475
Period 08 2011  92,466
Period 09 2011  103,529
Period 10 2011  94,515
Period 11 2011  76,146
Period 12 2011  81,736
Period 13 2011  80,174
Period 01 2012  81,437
Period 02 2012  102,695
Period 03 2012  120,775
Period 04 2012  97,058
Period 05 2012  119,921
Period 06 2012  102,311
Period 07 2012  109,498
Period 08 2012  110,318
Period 09 2012  98,103

Period 10 2012  
Period 11 2012  
Period 12 2012  
Period 13 2012  
Period 01 2013  
Period 02 2013  
Period 03 2013  

Answer 1

This isn't an exact answer to your question, but... you are definitely best off spending a bit of time to do learn some R basics and use something like Rob Hyndman's forecast package to do this. This will let you try a number of robust forecasting procedures and choose appropriate parameters, all within a state of the art computing environment with good graphics built in.

To get you started, here is how simple it is to have a go with your data in R. Investing a little time for the understanding you need of data management in R will be worth while because it will let you grapple with the real underlying issues of how to treat your time series, which methods to use, how to treat any seasonality, etc.

install.packages("forecast", dependencies=TRUE)
library(forecast)

x <- ts(c(69088,83400,75735,79526,81005,94013,90567,94568,101687,93540,84249,
91280,78531,89465,83341,87106,65636,79632,89722,87483,99228,113215,96057,
95475,92466,103529,94515,76146,81736,80174,81437,102695,120775,97058,
119921,102311,109498,110318,98103), frequency=13, start=c(2009, 10))

par(mfrow=c(3,1))
plot(ses(x,6), bty="l")
plot(holt(x,6), bty="l")
plot(hw(x,6), bty="l")

Answer 2

I had similar problems. What made it easier for me was that I took software called Anaplan. A job that would have taken months took me a few days

Answer 3

Your data can be easly modeled using a seaonal model of the form

    Y(T) =  168.16                                                                         
   +[X1(T)][(+ 28.8257)]                 :PULSE           2012/  3
   +[X2(T)][(- 14.3322)]                 :PULSE           2010/ 13
   +[X3(T)][(+ 15.0558)]                 :PULSE           2011/  9
   +[X4(T)][(+ 13.6610)]                 :PULSE           2012/  8
  +     [(1-  .945B** 13)]**-1  [A(T)]

Note that this is simply an equation which uses .945 * the value 13 periods ago and can be restated as y= 9.3 + .945*y(t-13) . Analysis suggested 4 unusual points which you might want to focus on to identify any omitted "information/cause series" like promotion/price actrivity.

A plot of the actual fit and forecasts . In my opinion the reason that Peter's holt-winter's additive seaonal model didn't capture the seasonality is his model was deterministic in nature not adaptive. Sometimes a deterministic model is appropriate, sometimes it is not . The data will tEll you which model is appropriate. In addition his model.procedure believed the 4 questionable data points rather than challenging them for "consistency wirt expectations".

The forecasts for the next 7 periods are

98.59 81.24 86.52 85.05 86.24 106.32 96.17

. the r-square for the model is .754 with an MSE of 56.7 . This automatic analysis was obtained using AUTOBOX a program that I have helped develop. Improved forecasting accuracy can save money. Hope this helps.