Please solve this in excle or stata to produce three periods’ forecast of CPI va
ID: 3262502 • Letter: P
Question
Please solve this in excle or stata
to produce three periods’ forecast of CPI values beyond the historical series provided. In doing this example answer the following questions:
a) What “smoothing” model do you think is best suited for this forecasting and why?
b) Are the “smoothing” methods appropriate in forecasting the CPI time series?
c) If not determine, which order AR model bests suits the data.
yearqrt cpi 1960q1 19.28 1960q2 19.4 1960q3 19.45 1960q4 19.56 1961q1 19.57 1961q2 19.58 1961q3 19.68 1961q4 19.7 1962q1 19.74 1962q2 19.83 1962q3 19.91 1962q4 19.96 1963q1 19.99 1963q2 20.03 1963q3 20.18 1963q4 20.23 1964q1 20.28 1964q2 20.32 1964q3 20.41 1964q4 20.47 1965q1 20.51 1965q2 20.66 1965q3 20.75 1965q4 20.84 1966q1 21.01 1966q2 21.22 1966q3 21.43 1966q4 21.59 1967q1 21.63 1967q2 21.78 1967q3 22.01 1967q4 22.19 1968q1 22.43 1968q2 22.68 1968q3 22.96 1968q4 23.24 1969q1 23.5 1969q2 23.91 1969q3 24.25 1969q4 24.59 1970q1 24.95 1970q2 25.36 1970q3 25.64 1970q4 25.98 1971q1 26.17 1971q2 26.47 1971q3 26.74 1971q4 26.88 1972q1 27.09 1972q2 27.31 1972q3 27.56 1972q4 27.81 1973q1 28.2 1973q2 28.82 1973q3 29.45 1973q4 30.14 1974q1 30.99 1974q2 31.86 1974q3 32.83 1974q4 33.79 1975q1 34.4 1975q2 34.94 1975q3 35.69 1975q4 36.26 1976q1 36.61 1976q2 37.06 1976q3 37.65 1976q4 38.08 1977q1 38.75 1977q2 39.58 1977q3 40.16 1977q4 40.6 1978q1 41.29 1978q2 42.36 1978q3 43.36 1978q4 44.24 1979q1 45.34 1979q2 46.9 1979q3 48.45 1979q4 49.86 1980q1 51.81 1980q2 53.68 1980q3 54.69 1980q4 56.12 1981q1 57.61 1981q2 58.94 1981q3 60.63 1981q4 61.5 1982q1 62 1982q2 62.95 1982q3 64.14 1982q4 64.27 1983q1 64.24 1983q2 65.05 1983q3 65.83 1983q4 66.4 1984q1 67.12 1984q2 67.86 1984q3 68.59 1984q4 69.09 1985q1 69.55 1985q2 70.38 1985q3 70.9 1985q4 71.53 1986q1 71.71 1986q2 71.53 1986q3 72.06 1986q4 72.47 1987q1 73.28 1987q2 74.23 1987q3 75.07 1987q4 75.72 1988q1 76.17 1988q2 77.13 1988q3 78.16 1988q4 78.97 1989q1 79.84 1989q2 81.16 1989q3 81.81 1989q4 82.6 1990q1 84.02 1990q2 84.87 1990q3 86.34 1990q4 87.74 1991q1 88.46 1991q2 88.99 1991q3 89.69 1991q4 90.36 1992q1 91 1992q2 91.74 1992q3 92.46 1992q4 93.12 1993q1 93.91 1993q2 94.63 1993q3 95 1993q4 95.66 1994q1 96.27 1994q2 96.88 1994q3 97.74 1994q4 98.2 1995q1 99 1995q2 99.88 1995q3 100.32 1995q4 100.8 1996q1 101.72 1996q2 102.72 1996q3 103.27 1996q4 104.01 1997q1 104.71 1997q2 105.13 1997q3 105.55 1997q4 105.96 1998q1 106.25 1998q2 106.81 1998q3 107.23 1998q4 107.6 1999q1 108.02 1999q2 109.07 1999q3 109.75 1999q4 110.42 2000q1 111.52 2000q2 112.7 2000q3 113.6 2000q4 114.21 2001q1 115.3 2001q2 116.5 2001q3 116.66 2001q4 116.33 2002q1 116.75Explanation / Answer
We use 4-period moving average for forecasting:
excel formula:
It gives less RMSE of 1.79
formula:
We used 4-period simple moving average since data is given quarterly.
yearqrt cpi 4period-smoothing 1960q1 19.28 1960q2 19.4 1960q3 19.45 1960q4 19.56 1961q1 19.57 19.42 1961q2 19.58 19.50 1961q3 19.68 19.54 1961q4 19.7 19.60 1962q1 19.74 19.63 1962q2 19.83 19.68 1962q3 19.91 19.74 1962q4 19.96 19.80 1963q1 19.99 19.86 1963q2 20.03 19.92 1963q3 20.18 19.97 1963q4 20.23 20.04 1964q1 20.28 20.11 1964q2 20.32 20.18 1964q3 20.41 20.25 1964q4 20.47 20.31 1965q1 20.51 20.37 1965q2 20.66 20.43 1965q3 20.75 20.51 1965q4 20.84 20.60 1966q1 21.01 20.69 1966q2 21.22 20.82 1966q3 21.43 20.96 1966q4 21.59 21.13 1967q1 21.63 21.31 1967q2 21.78 21.47 1967q3 22.01 21.61 1967q4 22.19 21.75 1968q1 22.43 21.90 1968q2 22.68 22.10 1968q3 22.96 22.33 1968q4 23.24 22.57 1969q1 23.5 22.83 1969q2 23.91 23.10 1969q3 24.25 23.40 1969q4 24.59 23.73 1970q1 24.95 24.06 1970q2 25.36 24.43 1970q3 25.64 24.79 1970q4 25.98 25.14 1971q1 26.17 25.48 1971q2 26.47 25.79 1971q3 26.74 26.07 1971q4 26.88 26.34 1972q1 27.09 26.57 1972q2 27.31 26.80 1972q3 27.56 27.01 1972q4 27.81 27.21 1973q1 28.2 27.44 1973q2 28.82 27.72 1973q3 29.45 28.10 1973q4 30.14 28.57 1974q1 30.99 29.15 1974q2 31.86 29.85 1974q3 32.83 30.61 1974q4 33.79 31.46 1975q1 34.4 32.37 1975q2 34.94 33.22 1975q3 35.69 33.99 1975q4 36.26 34.71 1976q1 36.61 35.32 1976q2 37.06 35.88 1976q3 37.65 36.41 1976q4 38.08 36.90 1977q1 38.75 37.35 1977q2 39.58 37.89 1977q3 40.16 38.52 1977q4 40.6 39.14 1978q1 41.29 39.77 1978q2 42.36 40.41 1978q3 43.36 41.10 1978q4 44.24 41.90 1979q1 45.34 42.81 1979q2 46.9 43.83 1979q3 48.45 44.96 1979q4 49.86 46.23 1980q1 51.81 47.64 1980q2 53.68 49.26 1980q3 54.69 50.95 1980q4 56.12 52.51 1981q1 57.61 54.08 1981q2 58.94 55.53 1981q3 60.63 56.84 1981q4 61.5 58.33 1982q1 62 59.67 1982q2 62.95 60.77 1982q3 64.14 61.77 1982q4 64.27 62.65 1983q1 64.24 63.34 1983q2 65.05 63.90 1983q3 65.83 64.43 1983q4 66.4 64.85 1984q1 67.12 65.38 1984q2 67.86 66.10 1984q3 68.59 66.80 1984q4 69.09 67.49 1985q1 69.55 68.17 1985q2 70.38 68.77 1985q3 70.9 69.40 1985q4 71.53 69.98 1986q1 71.71 70.59 1986q2 71.53 71.13 1986q3 72.06 71.42 1986q4 72.47 71.71 1987q1 73.28 71.94 1987q2 74.23 72.34 1987q3 75.07 73.01 1987q4 75.72 73.76 1988q1 76.17 74.58 1988q2 77.13 75.30 1988q3 78.16 76.02 1988q4 78.97 76.80 1989q1 79.84 77.61 1989q2 81.16 78.53 1989q3 81.81 79.53 1989q4 82.6 80.45 1990q1 84.02 81.35 1990q2 84.87 82.40 1990q3 86.34 83.33 1990q4 87.74 84.46 1991q1 88.46 85.74 1991q2 88.99 86.85 1991q3 89.69 87.88 1991q4 90.36 88.72 1992q1 91 89.38 1992q2 91.74 90.01 1992q3 92.46 90.70 1992q4 93.12 91.39 1993q1 93.91 92.08 1993q2 94.63 92.81 1993q3 95 93.53 1993q4 95.66 94.17 1994q1 96.27 94.80 1994q2 96.88 95.39 1994q3 97.74 95.95 1994q4 98.2 96.64 1995q1 99 97.27 1995q2 99.88 97.96 1995q3 100.32 98.71 1995q4 100.8 99.35 1996q1 101.72 100.00 1996q2 102.72 100.68 1996q3 103.27 101.39 1996q4 104.01 102.13 1997q1 104.71 102.93 1997q2 105.13 103.68 1997q3 105.55 104.28 1997q4 105.96 104.85 1998q1 106.25 105.34 1998q2 106.81 105.72 1998q3 107.23 106.14 1998q4 107.6 106.56 1999q1 108.02 106.97 1999q2 109.07 107.42 1999q3 109.75 107.98 1999q4 110.42 108.61 2000q1 111.52 109.32 2000q2 112.7 110.19 2000q3 113.6 111.10 2000q4 114.21 112.06 2001q1 115.3 113.01 2001q2 116.5 113.95 2001q3 116.66 114.90 2001q4 116.33 115.67 2002q1 116.75 116.20 forecast value 116.56