Weighted Factors for Projection

[marcheese]

Hello,

I've created a regression time-series model to forecast future sales. As I've taken historical sales data from 2007-2014 to project sales for 2015, there is technically equal weighting being placed on 2007 as there is for 2014. However, common sense will you tell that more recent trends will likely tell the story of future sales, so I was wondering if there was a way to weight recent years heavier in my model.

I cannot determine if the weight or weights should be applied to the time factor, the slope, or the trend, or even how to apply them. For some clarification regarding the attached:
t = time
MA = moving average
CMA = centered moving average
St = seasonality
Tt = trend (y=mx+b)

Any help, tips or advice would be greatly appreciated!

Thanks!

Weighted Regression Example.xlsx

[mcmahobt]

This could be possible if you had a setup for weighted averages. Do you care what the weights are? Or are they inconsequential, as long as the most recent years are being taken into account more heavily? Either case, how far back would you like your moving weighted average to go (if not all the way back to 2007)?

This is a helpful post about creating moving weighted averages, which can go back from 2 years, 3 years, ...n years.

http://blog.excelmasterseries.com/20...te-simple.html

Hope this helps!

[marcheese]

Thanks for the response.

This is something I have already considered using, however, it is not practical to use in my model because I am applying my moving average over 4 sets of quarters. As there is seasonality in the business, it does not make sense to weight a more recent quarter heavier than another quarter as the results in each quarter vary significantly. This is why the numbers are deseasonalized in my current forecast when applying the trends.

[shg]

Exponential moving average?

http://www.investopedia.com/terms/e/ema.asp

[shg]

duplicate deleted ...

[marcheese]

EMA wouldn't work as it only works for data with no trend.

I'm looking into double and triple EMA at the moment as this appears more practical.

[mcmahobt]

Could you not just weight the past x number of quarters? Or are you trying to break this out by year?