Exponential Weighted Moving Average Method Using Excel

One more simple Time series technique for a quick prediction where, the next data point is highly dependant upon the previous data point, and your overall data points lie within a band, then Exponential weighted Moving average is a great option.

The formula for prediction would be

New Pred= (Alpha*Average(Of all observed data points))+((1-alpha)*previous data point)

why is this model called as exponential weighted moving average, well look at the formula

Didn’t get it , wait let me explain

New Pred= (Alpha*Average(Of all observed data points))+((1-alpha)*(Alpha*Average(Of all observed data points))+((1-alpha)*(Alpha*Average(Of all observed data points))+((1-alpha)*(prev data point))))

Got it right its like inception :P, one value due to another value due to another value.

So its not very complicated right?? well its a bit complicated cause you will have to figure out the best alpha value. alpha must be (0<alpha<1)

Exactly so thanks to solver functionality in excel this problem is also solved.(You will need RMSE to figure out the best alpha value)

So I have taken a data set and solved it in excel step by step you can download the file below

ewma_solution

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s