Forecasting and Business Analysis
Writer Pro
copy_of_movingaverages-1.xlsIntroduction
| Forecasting and Business Analysis | |
| Copyright UPmarket Software Services. This file must not be used without permission. |
The Excel Moving Average Function in Analysis Tools
You can automatically calculate moving averages in Excel using the Moving Average function in the Analysis ToolPak. First check that the Analysis ToolPak is turned on. Click on the Tools menu. The bottom item should be Data Analysis. If it isn't then you will need to turn the ToolPak on. Go to the Tools Menu, click on Add-Ins then check the box next to Analysis ToolPak. After a short period you should now be able to access Data Analysis under the Tools menu.
When you open Data Analysisyou will see a long list of statistical methods that you can access. Go down the list and click Moving Average. A screen will appear a little like that below. (It may vary depending upon the version of Excel you are using).
The screen below shows the input for a three period (Interval) moving average.
You need to enter the cell range for the input data and also the destination of the output. If you include a label in the first row of your data, you should check the Labels in First Row box. When it is all entered - click OK and the moving averages will be calculated.
Follow the Example
Go to the sheet ''Your Try'' and use the Excel Data Analysis, Moving Average Add-in to calculate 3 and 5 period moving averages.
The input dialog box on the left shows the inputs needed for the 3 period moving average. The calculations, including a one period forecast are shown on the Moving Average Solution sheet, together with a chart and Root Mean Square (RMS) errors indicating the "best" estimate. Try to follow the calculations for the RMS errors. The Simple MA Example sheet shows the concept of how the moving average is calculated.
Your Try
| Period | Actual |
| 1980Q1 | 243.529 |
| 1980Q2 | 232.129 |
| 1980Q3 | 219.844 |
| 1980Q4 | 210.585 |
| 1981Q1 | 205.624 |
| 1981Q2 | 219.938 |
| 1981Q3 | 231.738 |
| 1981Q4 | 224.549 |
| 1982Q1 | 233.729 |
| 1982Q2 | 244.012 |
| 1982Q3 | 259.075 |
| 1982Q4 | 259.160 |
| 1983Q1 | 235.686 |
| 1983Q2 | 237.483 |
| 1983Q3 | 242.444 |
| 1983Q4 | 234.117 |
| 1984Q1 | 230.830 |
| 1984Q2 | 229.758 |
| 1984Q3 | 243.576 |
| 1984Q4 | 246.140 |
| 1985Q1 | 257.428 |
| 1985Q2 | 250.814 |
| 1985Q3 | 238.397 |
| 1985Q4 | 207.214 |
| 1986Q1 | 187.909 |
| 1986Q2 | 169.855 |
| 1986Q3 | 155.852 |
| 1986Q4 | 160.431 |
| 1987Q1 | 153.217 |
| 1987Q2 | 142.653 |
| 1987Q3 | 147.010 |
| 1987Q4 | 135.656 |
| 1988Q1 | 127.964 |
| 1988Q2 | 125.710 |
| 1988Q3 | 133.697 |
| 1988Q4 | 125.185 |
| 1989Q1 | 128.577 |
| 1989Q2 | 137.959 |
| 1989Q3 | 142.297 |
| 1989Q4 | 143.139 |
| 1990Q1 | 148.070 |
| 1990Q2 | 155.385 |
| 1990Q3 | 145.051 |
| 1990Q4 | 130.918 |
| 1991Q1 | 133.988 |
| 1991Q2 | 138.358 |
| 1991Q3 | 136.339 |
| 1991Q4 | 129.478 |
| 1992Q1 | 128.695 |
| 1992Q2 | 130.388 |
| 1992Q3 | 124.928 |
| 1992Q4 | 123.021 |
| 1993Q1 | 120.929 |
| 1993Q2 | 110.056 |
| 1993Q3 | 105.678 |
| 1993Q4 | 108.274 |
| 1994Q1 | 107.657 |
| 1994Q2 | 103.259 |
| 1994Q3 | 99.056 |
| 1994Q4 | 98.866 |
| 1995Q1 | 96.108 |
| 1995Q2 | 84.487 |
| 1995Q3 | 94.161 |
| 1995Q4 | 101.539 |
| 1996Q1 | 105.827 |
| Created by Peter Rossini © 2000 UPmarket Software Services | |
| Created by Peter Rossini © 1999 UniSA |
Peter Rossini:
This is the actual value for this quarter. DO NOT include this in your calculation then you can tets the quality of your one period forecast
Moving Average Solution
| Period | Actual | Three Quarter Moving Average | Three Quarter Moving Average Forecast | Five-Quarter Moving Average | Five-Quarter Moving Average Forecast | Sq Error 3Q forecast | Sq Error 5Q forecast |
| 1980Q1 | 243.529 | 0.000 | 0.000 | 0.000 | 0.000 | ||
| 1980Q2 | 232.129 | 0.000 | 0.000 | 0.000 | 0.000 | ||
| 1980Q3 | 219.844 | 231.834 | 0.000 | 0.000 | 0.000 | 0.000 | |
| 1980Q4 | 210.585 | 220.853 | 231.834 | 0.000 | 451.520 | 0.000 | |
| 1981Q1 | 205.624 | 212.018 | 220.853 | 222.342 | 0.000 | 231.912 | 0.000 |
| 1981Q2 | 219.938 | 212.049 | 212.018 | 217.624 | 222.342 | 62.732 | 5.780 |
| 1981Q3 | 231.738 | 219.100 | 212.049 | 217.546 | 217.624 | 387.657 | 199.205 |
| 1981Q4 | 224.549 | 225.408 | 219.100 | 218.487 | 217.546 | 29.692 | 49.045 |
| 1982Q1 | 233.729 | 230.005 | 225.408 | 223.116 | 218.487 | 69.233 | 232.325 |
| 1982Q2 | 244.012 | 234.097 | 230.005 | 230.793 | 223.116 | 196.187 | 436.660 |
| 1982Q3 | 259.075 | 245.605 | 234.097 | 238.621 | 230.793 | 623.917 | 799.860 |
| 1982Q4 | 259.160 | 254.082 | 245.605 | 244.105 | 238.621 | 183.729 | 421.867 |
| 1983Q1 | 235.686 | 251.307 | 254.082 | 246.332 | 244.105 | 338.425 | 70.880 |
| 1983Q2 | 237.483 | 244.110 | 251.307 | 247.083 | 246.332 | 191.103 | 78.312 |
| 1983Q3 | 242.444 | 238.538 | 244.110 | 246.770 | 247.083 | 2.774 | 21.522 |
| 1983Q4 | 234.117 | 238.015 | 238.538 | 241.778 | 246.770 | 19.542 | 160.088 |
| 1984Q1 | 230.830 | 235.797 | 238.015 | 236.112 | 241.778 | 51.619 | 119.859 |
| 1984Q2 | 229.758 | 231.568 | 235.797 | 234.926 | 236.112 | 36.470 | 40.373 |
| 1984Q3 | 243.576 | 234.721 | 231.568 | 236.145 | 234.926 | 144.184 | 74.816 |
| 1984Q4 | 246.140 | 239.825 | 234.721 | 236.884 | 236.145 | 130.386 | 99.900 |
| 1985Q1 | 257.428 | 249.048 | 239.825 | 241.546 | 236.884 | 309.877 | 422.048 |
| 1985Q2 | 250.814 | 251.461 | 249.048 | 245.543 | 241.546 | 3.119 | 85.888 |
| 1985Q3 | 238.397 | 248.880 | 251.461 | 247.271 | 245.543 | 170.659 | 51.068 |
| 1985Q4 | 207.214 | 232.142 | 248.880 | 239.999 | 247.271 | 1736.028 | 1604.563 |
| 1986Q1 | 187.909 | 211.173 | 232.142 | 228.352 | 239.999 | 1956.529 | 2713.326 |
| 1986Q2 | 169.855 | 188.326 | 211.173 | 210.838 | 228.352 | 1707.205 | 3421.946 |
| 1986Q3 | 155.852 | 171.205 | 188.326 | 191.845 | 210.838 | 1054.561 | 3023.438 |
| 1986Q4 | 160.431 | 162.046 | 171.205 | 176.252 | 191.845 | 116.086 | 986.865 |
| 1987Q1 | 153.217 | 156.500 | 162.046 | 165.453 | 176.252 | 77.951 | 530.620 |
| 1987Q2 | 142.653 | 152.100 | 156.500 | 156.402 | 165.453 | 191.739 | 519.831 |
| 1987Q3 | 147.010 | 147.627 | 152.100 | 151.833 | 156.402 | 25.911 | 88.202 |
| 1987Q4 | 135.656 | 141.773 | 147.627 | 147.793 | 151.833 | 143.297 | 261.682 |
| 1988Q1 | 127.964 | 136.877 | 141.773 | 141.300 | 147.793 | 190.688 | 393.205 |
| 1988Q2 | 125.710 | 129.777 | 136.877 | 135.799 | 141.300 | 124.694 | 243.048 |
| 1988Q3 | 133.697 | 129.124 | 129.777 | 134.007 | 135.799 | 15.369 | 4.417 |
| 1988Q4 | 125.185 | 128.197 | 129.124 | 129.642 | 134.007 | 15.513 | 77.835 |
| 1989Q1 | 128.577 | 129.153 | 128.197 | 128.227 | 129.642 | 0.144 | 1.135 |
| 1989Q2 | 137.959 | 130.574 | 129.153 | 130.226 | 128.227 | 77.546 | 94.720 |
| 1989Q3 | 142.297 | 136.278 | 130.574 | 133.543 | 130.226 | 137.437 | 145.719 |
| 1989Q4 | 143.139 | 141.132 | 136.278 | 135.431 | 133.543 | 47.078 | 92.083 |
| 1990Q1 | 148.070 | 144.502 | 141.132 | 140.008 | 135.431 | 48.140 | 159.734 |
| 1990Q2 | 155.385 | 148.865 | 144.502 | 145.370 | 140.008 | 118.440 | 236.440 |
| 1990Q3 | 145.051 | 149.502 | 148.865 | 146.788 | 145.370 | 14.544 | 0.102 |
| 1990Q4 | 130.918 | 143.785 | 149.502 | 144.513 | 146.788 | 345.365 | 251.870 |
| 1991Q1 | 133.988 | 136.652 | 143.785 | 142.682 | 144.513 | 95.975 | 110.767 |
| 1991Q2 | 138.358 | 134.421 | 136.652 | 140.740 | 142.682 | 2.909 | 18.700 |
| 1991Q3 | 136.339 | 136.228 | 134.421 | 136.931 | 140.740 | 3.677 | 19.369 |
| 1991Q4 | 129.478 | 134.725 | 136.228 | 133.816 | 136.931 | 45.567 | 55.544 |
| 1992Q1 | 128.695 | 131.504 | 134.725 | 133.372 | 133.816 | 36.361 | 26.227 |
| 1992Q2 | 130.388 | 129.520 | 131.504 | 132.652 | 133.372 | 1.245 | 8.902 |
| 1992Q3 | 124.928 | 128.004 | 129.520 | 129.966 | 132.652 | 21.090 | 59.654 |
| 1992Q4 | 123.021 | 126.112 | 128.004 | 127.302 | 129.966 | 24.827 | 48.227 |
| 1993Q1 | 120.929 | 122.959 | 126.112 | 125.592 | 127.302 | 26.867 | 40.615 |
| 1993Q2 | 110.056 | 118.002 | 122.959 | 121.864 | 125.592 | 166.496 | 241.374 |
| 1993Q3 | 105.678 | 112.221 | 118.002 | 116.922 | 121.864 | 151.881 | 262.000 |
| 1993Q4 | 108.274 | 108.003 | 112.221 | 113.592 | 116.922 | 15.579 | 74.795 |
| 1994Q1 | 107.657 | 107.203 | 108.003 | 110.519 | 113.592 | 0.119 | 35.219 |
| 1994Q2 | 103.259 | 106.397 | 107.203 | 106.985 | 110.519 | 15.555 | 52.705 |
| 1994Q3 | 99.056 | 103.324 | 106.397 | 104.785 | 106.985 | 53.880 | 62.860 |
| 1994Q4 | 98.866 | 100.394 | 103.324 | 103.422 | 104.785 | 19.879 | 35.039 |
| 1995Q1 | 96.108 | 98.010 | 100.394 | 100.989 | 103.422 | 18.368 | 53.502 |
| 1995Q2 | 84.487 | 93.153 | 98.010 | 96.355 | 100.989 | 182.872 | 272.325 |
| 1995Q3 | 94.161 | 91.585 | 93.153 | 94.536 | 96.355 | 1.016 | 4.813 |
| 1995Q4 | 101.539 | 93.396 | 91.585 | 95.032 | 94.536 | 99.075 | 49.048 |
| 1996Q1 | 105.827 | 93.396 | 95.032 | ||||
| RMS ERROR | 14.464 | 18.297 | |||||
| Created by Peter Rossini © 2000 UPmarket Software Services |
Moving Average Solution
Actual
Three Quarter Moving Average Forecast
Five-Quarter Moving Average Forecast
Year and Quarter
Actual Data and the Three-Quarter Moving Average Forecast
Simple MA Example
| Period | Actual | Calculation | Three Quarter Moving Average | Three Quarter Moving Average Forecast | Period | Actual | Three Quarter Moving Average | Three Quarter Moving Average Forecast | Five-Quarter Moving Average | Five-Quarter Moving Average Forecast | Sq Error 3Q forecast | Sq Error 5Q forecast | |
| 1980Q1 | 243.529 | MISSING | MISSING | 1980Q1 | 243.529 | Missing | Missing | Missing | Missing | Missing | Missing | ||
| 1980Q2 | 232.129 | (243.529+232.129+219.844)/3 | MISSING | MISSING | 1980Q2 | 232.129 | Missing | Missing | Missing | Missing | Missing | Missing | |
| 1980Q3 | 219.844 | (232.129+219.844+210.585)/3 | 231.834 | MISSING | 1980Q3 | 219.844 | 231.834 | Missing | Missing | Missing | Missing | Missing | |
| 1980Q4 | 210.585 | (219.844+210.585+205.624)/3 | 220.853 | 231.834 | 1980Q4 | 210.585 | 220.853 | 231.834 | Missing | Missing | 451.520 | Missing | |
| 1981Q1 | 205.624 | (210.585+205.624+219.938)/3 | 212.018 | 220.853 | 1981Q1 | 205.624 | 212.018 | 220.853 | 222.342 | Missing | 231.912 | Missing | |
| 1981Q2 | 219.938 | (205.624+219.938+231.738)/3 | 212.049 | 212.018 | 1981Q2 | 219.938 | 212.049 | 212.018 | 217.624 | 222.342 | 62.732 | 5.780 | |
| 1981Q3 | 231.738 | (219.938+231.738+224.549)/3 | 219.100 | 212.049 | 1981Q3 | 231.738 | 219.100 | 212.049 | 217.546 | 217.624 | 387.657 | 199.205 | |
| 1981Q4 | 224.549 | (231.738+224.549+233.729)/3 | 225.408 | 219.100 | 1981Q4 | 224.549 | 225.408 | 219.100 | 218.487 | 217.546 | 29.692 | 49.045 | |
| 1982Q1 | 233.729 | (224.549+233.729+244.012)/3 | 230.005 | 225.408 | |||||||||
| 1982Q2 | 244.012 | 234.097 | 230.005 | 1995Q2 | 84.487 | 93.153 | 98.010 | 96.355 | 100.989 | 182.872 | 272.325 | ||
| 234.097 | 1995Q3 | 94.161 | 91.585 | 93.153 | 94.536 | 96.355 | 1.016 | 4.813 | |||||
| 1995Q4 | 101.539 | 93.396 | 91.585 | 95.032 | 94.536 | 99.075 | 49.048 | ||||||
| 1996Q1 | 105.827 | 93.396 | 95.032 | ||||||||||
| Created by Peter Rossini © 2000 UPmarket Software Services | |||||||||||||
| RMS ERROR | 14.464 | 18.297 |
Centered Moving Average Example
| Year | Quarter | Time Index | Y | Moving Average | Centred Moving Average | Seasonal Factor | |
| Year 1 | First Quarter | 1 | 10 | Missing | Missing | ||
| Year 1 | Second Quarter | 2 | 18 | Missing | Missing | ||
| Year 1 | Third Quarter | 3 | 20 | 15.00 | 15.25 | 1.31 | 20/15.25=1.31 |
| Year 1 | Fourth Quarter | 4 | 12 | 15.50 | 15.75 | 0.76 | 12/15.75=0.76 |
| Year 2 | First Quarter | 5 | 12 | 16.00 | Missing | ||
| Year 2 | Second Quarter | 6 | 20 | Missing | Missing | ||
| Created by Peter Rossini © 2000 UPmarket Software Services |
