Join Wayne Winston for an in-depth discussion in this video Exploring why some forecasts are better than others, part of Excel Data Analysis: Forecasting.
- In this video we're going to look at some forecasts and try to understand why some forecasts are better than other forecasts. So if you want to follow along with me, you should go to the chapter two, video one folder, and open up the file, accuracy biased start. And so what do we see in this file? We see hypothetical sales of a product listed in column D, and then four different forecasts for the sales of each of those six months are listed in columns E, F, G, and H. And so we want to try and understand which of these forecasts are better than others.
And the key concept you've gotta start with is the concept of error or residual. These terms are used to mean the same thing in the forecast literature, and I'd say 50 percent of the time, people will use the word 'residual', 50 percent of the time people will use the word 'error', so you need to be familiar with both words. And it's very simple, the error in a forecast, or the residual, is the actual value that occurs minus the forecast. And so let's create the errors for each of these four forecasts, and I think it'll be clearer to you which of these forecasts are better than others, and that will lead us into our discussion of accuracy and bias.
So we'll have to do a little bit with Excel formulas here and you'll have to know the copy command. So you'll have to know that when you dollar sign a letter or number and you copy a formula, then basically that letter or number doesn't change, but we'll make that clear as we go through our formulas. So I want to find the error for every month, for each of the four forecasts. So let's start with the first forecast for month one, and then we can copy the formula. That's the beauty of Excel, the copy command. Some people think the copy command is one of the greatest inventions since the wheel. So now what we do is we take the actual sales in month one, that's right here.
Now we're going to copy this formula across and down, so let's think about that. When you copy it down, you want the number to change, but you don't want to letter to change. So you want to dollar sign the letter, so there's a trick you might want to use to dollar sign the letters and numbers, hitting the f4 key. So I'm hitting the f4 key, you can see I cycle through my dollar signs, I hope you know that trick. And if you didn't it's one of the more useful Excel tricks you can be taught. So when you hit the f4 key, you see we're cycling through, here we see the letter and the number dollar sign, then we see the number, then we see the letter, then we see nothing and then we see everything.
So with the f4 key you can cycle through any combination of where the dollar sign is, or where it is not. Now what I need to do here is dollar sign column D. So I cycle through with that four till I dollar sign column D, because when I copy this formula across, I will always be picking out column D for the actual. Then I want to subtract off the forecast. Now nothing gets dollar signed there in the E3, because when I copy this formula down I want both the letter and number to change. So I hit enter to put this formula in that cell.
Now to copy this formula, I do control-C and then I'll select the whole range and control-V, and that copies the formula. Ok, so now we see the error for each month for each forecast, so now let's qualitatively look at these errors and try to and figure out what's going on. I think you see for forecasts one and two, were always off by two units, and for forecasts three and four, we're always off by five units. Forecasts one and two are more accurate, and will make this more precise later in this chapter, than forecasts three and four.
And again that's because we're off by two units in forecasts one and two. If we look at forecasts three and four, we see we're always off by five units so you might think these forecasts are sort of identical in quality, but that's not the case. We see with forecast three, sometimes we're too high, and sometimes we're too low. Now with forecast four, we see the error is always negative. Now what does it mean for the error always be negative? It means the actual is less than the forecast, which means the forecast is too high. And in the business world, it's fairly common that the forecast is too high, we'll have more on this later in the chapter.
Forecast four exhibits what we call a 'bias'. In other words, we're systematically biased in the sense that our errors are negative, which means that our forecasts were too high. The purpose of the rest of this chapter is to define quantitative levels of accuracy and bias, so you can understand what forecasters mean when they are trying to assess the quality of a forecast. In the next video, we'll start with the most basic measure of accuracy, which is the MAD, or the mean absolute deviation.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Plotting and displaying time-series data
- Creating a moving average chart
- Accounting for errors and bias
- Using and interpreting trendlines
- Modeling exponential growth
- Calculating compound annual growth rate (CAGR)
- Analyzing the impact of seasonality
- Introducing the ratio-to-moving-average method
- Forecasting with multiple regression