1. Download the following file, Homework 2 - Forecasting.xlsPreview the document

Question

1. Download the following file, Homework 2 - Forecasting.xlsPreview the document. Cut and paste the data from this file to your own spreadsheet. 2. Perform a Forecast for February 1, 2005 using the 6 methods discussed in class: Naïve, Simple Moving Average, Weighted Moving Average, Exponential Smoothing, Regression (including needed charts), and Regression Tool. 3. Create a summary sheet comparing the following methods: Naïve, SMA, WMA, ES, ES with Solver, Regression. 4. On the summary sheet, include a recommendation of the method you would use along with justification. 5. Using Solver, an Excel tool, minimize the MAD cell in the ES forecast by changing the Alpha. (Remember the rules for Alpha?) 6. When you have completed the assignment submit it to the appropriate drop box on the course shell. Needed Information SMA: Perform the forecast for 5 periods WMA weights: W1=3.5, W2=2.5, W3=2, W4=1 ES: Alpha = .5

Forecast Information: xls

Usage 1-Jan-2005 2056 2-Jan-2005 2014 3-Jan-2005 1995 4-Jan-2005 1875 5-Jan-2005 2135 6-Jan-2005 2098 7-Jan-2005 2051 8-Jan-2005 2198 9-Jan-2005 1935 10-Jan-2005 1899 11-Jan-2005 2054 12-Jan-2005 2130 13-Jan-2005 2155 14-Jan-2005 2065 15-Jan-2005 2144 16-Jan-2005 2130 17-Jan-2005 2189 18-Jan-2005 1972 19-Jan-2005 2058 20-Jan-2005 1981 21-Jan-2005 2055 22-Jan-2005 2002 23-Jan-2005 2064 24-Jan-2005 2113 25-Jan-2005 2141 26-Jan-2005 2059 27-Jan-2005 2065 28-Jan-2005 2005 29-Jan-2005 2164 30-Jan-2005 2122 31-Jan-2005 2095 1-Feb-2005

Explanation / Answer

I have also prepared spreedsheet but unable to attach it here.  

Forecast ABS Error Squared Error Percent Error Index Date Value Navie SMA WMA ES Regression Navie SMA WMA ES Regression Navie SMA WMA ES Regression Navie SMA WMA ES Regression 1 1-Jan-05 2056 2065 2033.098554 23 524 0.011139 2 2-Jan-05 2014 2056 2060.564516 2035.40356 42 47 21 1764 2168 458 0.020854 0.02312 0.010627 3 3-Jan-05 1995 2014 2037.282258 2037.708565 19 42 43 361 1788 1824 0.009524 0.021194 0.021408 4 4-Jan-05 1875 1995 2016.141129 2040.013571 120 141 165 14400 19921 27229 0.064 0.075275 0.088007 5 5-Jan-05 2135 1875 17634 1945.570565 2042.318576 260 15499 189 93 67600 240219001 35884 8590 0.12178 7.259485 0.088726 0.043411 6 6-Jan-05 2098 2135 2015 18164 2040.285282 2044.623582 37 83 16066 58 53 1369 6889 258116356 3331 2849 0.017636 0.039561 7.657769 0.027509 0.025442 7 7-Jan-05 2051 2098 2023 18425.5 2069.142641 2046.928587 47 28 16375 18 4 2209 762 268124250 329 17 0.022916 0.013457 7.983667 0.008846 0.001985 8 8-Jan-05 2198 2051 2031 18568.5 2060.071321 2049.233593 147 167 16371 138 149 21609 27956 267993270 19024 22131 0.066879 0.076069 7.447907 0.062752 0.067683 9 9-Jan-05 1935 2198 2071 19151.5 2129.03566 2051.538598 263 136 17217 194 117 69169 18605 296407872 37650 13581 0.135917 0.070491 8.897416 0.100277 0.060227 10 10-Jan-05 1899 1935 2083 18467.5 2032.01783 2053.843604 36 184 16569 133 155 1296 34003 274515192 17694 23977 0.018957 0.097104 8.724855 0.070046 0.08154 11 11-Jan-05 2054 1899 2036 17931 1965.508915 2056.14861 155 18 15877 88 2 24025 317 252079129 7831 5 0.075463 0.008666 7.729796 0.043082 0.001046 12 12-Jan-05 2130 2054 2027 18004.5 2009.754458 2058.453615 76 103 15875 120 72 5776 10527 251999750 14459 5119 0.035681 0.048169 7.452817 0.056453 0.03359 13 13-Jan-05 2155 2130 2043 18323 2069.877229 2060.758621 25 112 16168 85 94 625 12499 261404224 7246 8881 0.011601 0.051879 7.502552 0.0395 0.043731 14 14-Jan-05 2065 2155 2035 18874.5 2112.438614 2063.063626 90 30 16810 47 2 8100 924 282559290 2250 4 0.043584 0.014722 8.140194 0.022973 0.000938 15 15-Jan-05 2144 2065 2061 18929 2088.719307 2065.368632 79 83 16785 55 79 6241 6956 281736225 3056 6183 0.036847 0.038899 7.828825 0.025784 0.036675 16 16-Jan-05 2130 2144 2110 19106.5 2116.359654 2067.673637 14 20 16977 14 62 196 416 288201552 186 3885 0.006573 0.009577 7.970188 0.006404 0.029261 17 17-Jan-05 2189 2130 2125 19100 2123.179827 2069.978643 59 64 16911 66 119 3481 4122 285981921 4332 14166 0.026953 0.029328 7.725445 0.030069 0.054372 18 18-Jan-05 1972 2189 2137 19339.5 2156.089913 2072.283648 217 165 17368 184 100 47089 27093 301630056 33889 10057 0.110041 0.083469 8.807049 0.093352 0.050854 19 19-Jan-05 2058 1972 2100 18778.5 2064.044957 2074.588654 86 42 16721 6 17 7396 1764 279575120 37 275 0.041788 0.020408 8.124636 0.002937 0.008061 20 20-Jan-05 1981 2058 2099 18641 2061.022478 2076.89366 77 118 16660 80 96 5929 13830 277555600 6404 9196 0.038869 0.059364 8.409894 0.040395 0.048407 21 21-Jan-05 2055 1981 2066 18211.5 2021.011239 2079.198665 74 11 16157 34 24 5476 121 261032492 1155 586 0.03601 0.005353 7.862044 0.01654 0.011776 22 22-Jan-05 2002 2055 2051 18233 2038.00562 2081.503671 53 49 16231 36 80 2809 2401 263445361 1296 6321 0.026474 0.024476 8.107393 0.017985 0.039712 23 23-Jan-05 2064 2002 2014 18164.5 2020.00281 2083.808676 62 50 16101 44 20 3844 2540 259226100 1936 392 0.030039 0.024419 7.80063 0.021316 0.009597 24 24-Jan-05 2113 2064 2032 18320 2042.001405 2086.113682 49 81 16207 71 27 2401 6561 262666849 5041 723 0.02319 0.038334 7.670137 0.033601 0.012724 25 25-Jan-05 2141 2113 2043 18614.5 2077.500702 2088.418687 28 98 16474 63 53 784 9604 271376202 4032 2765 0.013078 0.045773 7.694302 0.029659 0.024559 26 26-Jan-05 2059 2141 2075 18906 2109.250351 2090.723693 82 16 16847 50 32 6724 256 283821409 2525 1006 0.039825 0.007771 8.182127 0.024405 0.015407 27 27-Jan-05 2065 2059 2076 18849 2084.125176 2093.028699 6 11 16784 19 28 36 117 281702656 366 786 0.002906 0.00523 8.127845 0.009262 0.013573 28 28-Jan-05 2005 2065 2088 18770 2074.562588 2095.333704 60 83 16765 70 90 3600 6956 281065225 4839 8160 0.029925 0.041596 8.361596 0.034695 0.045054 29 29-Jan-05 2164 2005 2077 18439 2039.781294 2097.63871 159 87 16275 124 66 25281 7639 264875625 15430 4404 0.073475 0.040388 7.520795 0.057402 0.030666 30 30-Jan-05 2122 2164 2087 18775.5 2101.890647 2099.943715 42 35 16654 20 22 1764 1239 277339062 404 486 0.019793 0.016588 7.848021 0.009477 0.010394 31 31-Jan-05 2095 2122 2083 18912 2111.945323 2102.248721 27 12 16817 17 7 729 144 282811489 287 53 0.012888 0.005728 8.027208 0.008088 0.00346 32 1-Feb-05 2095 2090 18970.5 2103.472662 2104.553726 MAD 83 73 16502 75 62 MSE 11403 7855 272498566 8493 5956 MAPE 0.040449 0.035262 7.957948 0.036704 0.030172 As MAP of Regression model is minimum regression model is best fit. So the forecast for 1-FEB-05 is 2104.55 W1 3.5 W2 2.5 W3 2 W4 1 a 2030.793548 b 2.305005562

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.