Analysis Toolpak- Exponential Smoothing.pdf

TRANSCRIPT So using the same data set, lets now explore the idea of exponential smoothing. It’s also a rather simple forecasting method and it’s an upgrade of sorts from a simple average moving method. Now the flaw in the simple average moving method that we looked at earlier is the fact that it treats the last end data points equally while completely ignoring every single data point prior to that. Now the simple exponential smoothing method fixes this by discounting the past in a very gradual fashion. Now to put it simply the exponential smoothing method gives you the option of giving recent observations relatively more weight than older observations with the freedom to tweak this weightage based on the factor between 0 and 1 that you can use. Now let’s now dive straight right into this and use the analysis toolpak to explore this forecasting method. And I am also going to generate a couple of charts for us to be able to understand exactly how the smoothing method works. So back to the data set - So for every single day across a 365 days’ period, we have the mean temperature in degree Celsius for New York City. Now what I am going to do is use the data analysis toolpak and use exponential smoothing. So again it asks me for an input range. I am going to say B2 to B367. If we had 1|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT selected all the way from B1, we could have checked labels to indicate that there was a header. Now the output range, I’d like it to be column C. I don’t want a chart output yet, I am going to do that on my own. Now there’s a damping factor you can use and it’s typically a factor between 0 and 1. Bear with me for a minute I am just going to use the value of 0.3 here and then I’ll explain exactly how this factor affects your forecast. So with just this much I am going to say ok. Let’s now call this our forecast. Now there we have it. Without us having to input too much information, the exponential smoothing method has been carried out by Excel using the analysis toolpak. Now let’s look at column C and let’s try and look at the exact formula that Excel has used to be able to understand this better and also understand our damping factor that we just used. So the first forecast of course is N/A, there was no value to use for the forecast. So let’s jump quickly to the value in cell C3. So C3 just basically says = B2. Which means when we were forecasting the temperature for 27th March, the only data point we had was the temperature for 26th March which means that there is nothing really that we could do. So we knew the temperature the previous day was 11 degrees, so therefore our forecast was also 11 degrees. Now let’s look at the next 2|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT forecast, which look a little more complicated. So now we are at 27th March. We know that the temperature on day 1 was 11 degrees, the temperature on day 2 was 6 degrees. So now let’s understand how the exponential smoothing method gives us a forecast of 7.5 degrees for 28th March. So it basically says 0.7*B3+0.3*C3. So what exactly does that mean? If you remember, when we used the analysis toolpak, we gave Excel a damping factor of 0.3. So it seems to have applied that factor here. Which means the forecast for 28th March seems to be a weighted average of B3 and C3. Now specifically what does that mean? It’s a weighted average of the last data point that we saw which was 27th March where the value was 6 and it also weighs in the previous forecast that was used, which was basically the value in cell C3 - 30% weightage was given. So this 7.5 is a combination of 30% of the previous forecast and 70% of the latest data point, which is 6 degrees. Now what exactly does this mean? It means that every time we make a forecast, that forecast is heavily dependent, 70% dependent on the last data point that we have. But 30% of that forecast comes from the previous forecast which in turn is a combination of every single data point before that. Which takes us back to our original description of 3|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT exponential smoothing which is that it basically gives recent observations some weight but it gradually also gives weightage to every single data point in the forecast, unlike the moving average method which beyond the interval n, did not give any weightage or did not consider any of those data points at all. This method is considering every single data point in the system but gradually it gives more weightage to the later data points and lesser weightage to the older data points without completely disregarding them. I hope I haven’t confused you with that sentence. Let’s look at another example, the next data point. 0.7*B4 + 0.3*C4, which means 30% of the weightage came from the previous forecast which in turn had a 30% weightage of the previous forecast. You see what I mean? And 70% of the forecast here came from the data point that we just saw - which was B4. Now what I want to do is graphically represent this- actually before I do that I am going to work on a different exercise. I want to be able to mess with this damping factor. Right now its 3 so I am kind of stepping outside the analysis toolpak and being able to replicate this myself, just so we can then mess around with this data and look at how it affects our forecast. There is a reason I am doing this and you’ll see that when we look at the chart. So now what we 4|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT have here is 0.7*B3+0.3*C3. Let’s look at the first forecast. What I just want to do is replace this with the damping factor here. There we go. This gives us the same number. Let’s lock in this value and extend this all the way. So now as you can see here, the exercise what we just did does not affect any of our forecast, but it gives me the freedom to change this and watch the forecast value has changed dynamically. Let’s move this back to 0.3. Let’s move this over so I can compute an error matrix. What was the error in each of these forecasts? So again I want the absolute difference between these two values and I am going to expand this all the way to the bottom. Now I am going to look at this graphically. The first thing I am going to do is draw a chart between columns B and C just to see exactly how my forecast trails the actual value. So I think the best way to do this would be by using a line chart. So let’s first draw this line chart here. Now looks little complicated but bear with me for a second. Let’s call this actual versus forecast. So we see the blue line is the mean temperature and the orange line is the forecast. What I also want to do real quick is - quickly chart my error number. And I just want this number on one graph. So I am going to insert a quick column chart here which helps me understand exactly how badly 5|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT or how large my error is across each of these data points. Looks like the maximum error was around 13 degrees at some point, so it must have been a large fluctuation here. We can dig into that later. Now what I want to do is be able to change this damping factor and see how it affects the forecast. As you can see here with a damping factor of 0.3, it means 70% of each forecast came from the immediately preceding data point and not from all the data points in history whereas 30% came from the previous forecast, which is reflected in our graph. As you can see here, the orange line seems to take almost the same shape although it’s slightly staggered it takes the same shape as the base mean temperature. Which means the forecast is almost trailing the actual data points. Now while this looks pretty on a graph, what it really means is that the irregularities or the day to day highs and lows in the temperature are affecting our forecast, which may not be such a good thing in the long run although it looks pretty on a graph. Let me explain this to you. What I am going to now do is change this factor and let’s reverse it. Let’s use the damping factor of 0.7, which means 70% of my forecast comes from all the previous historics where only 30% comes from the latest data point. So what we see here is that while the error matrix may have gone up, 6|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT the orange graph of the forecast does not fluctuate as much, does not jump up and down along with fluctuations in the mean temperature. Now what this helps us do is identify the actual underlying trend in the forecast without looking at the day to day fluctuations. If I take this a notch higher and use the value of let’s say 0.9, you can see that the orange line gets smoothened out even more - Which means now if I just wanted to know a macro trend across 365 days without actually worrying about the day to day fluctuations, the orange line gives me a much better picture of how that average temperature increased and then decreased and then increased again. So it must have been summer, dropped to winter and then as you can see drops to below zero, so this must have been winter. Also as you can see that these fluctuations in winter, there must have been a cold front, there must have been a heat wave, I am not entirely sure about the weather patterns, but they cause larger errors in our forecast as you can see here. But if we are looking to use exponential smoothing just to understand the underlying trend without having the outliers or irregularities that affect us too much, then the orange line gives us a really good view on the actual trend in the data that we can then 7|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES

TRANSCRIPT use for future predictions without looking at irregularities too much. That was the exponential smoothing method, another extremely powerful forecasting technique. Like I said, these techniques can get more complicated. There is single exponential smoothing, double exponential smoothing, triple exponential smoothing and there’s also other more complicated methods that help you isolate the base value, the trend and the seasonality in case you have seasonal data and there are multiple techniques that you could use but all of them use the exact same framework that we have just used here. You prepare the data, you forecasted, you look at the error and then understand how your forecast is performing over time. And when you look at it graphically like this, you then have the freedom to change the damping factor, change specific inputs that you give to the forecasting techniques and then arrive at the data that you are finally looking to get from that forecasting technique. I hope this was useful and I hope you can use it to run forecasting exercises on various other datasets that you might be using based on the domains you are working in.

8|Page © Jigsaw Academy Education Pvt Ltd

MY CLASS NOTES