Statistics
Questions are in Assignment 3 folder.
Chapter 9 Forecasting Techniques
Instructions: Please submit your work in one single Excel file with one tab/worksheet for each problem.
Forecasting Models for Stationary Time Series
1. (40 points) The Excel file Closing Stock Prices provides data for four stocks and the Dow Jones Industrial Average over a one-month period.
a. Develop a spreadsheet model for forecasting each of the stock prices using a simple two-period and three-period moving average. Compute MAD to determine which model is better?
b. Develop a spreadsheet model for forecasting each of the stock prices using simple exponential smoothing with a smoothing constant of 0.1 and 0.5. Compute MAD to determine which model is better?
Forecasting Models for Time Series with a Linear Trend
2. (20 points) Consider the data in the Excel file Consumer Price Index.
a. Use double exponential smoothing to find forecasts for the next two years.
b. Use simple linear regression to find forecasts for the next two years.
Forecasting Time Series with Seasonality
3. (20 points) Develop a multiple regression model with categorical variables that incorporate seasonality for forecasting for the data in the Excel file Coal Consumption.
Regression Forecasting with Causal Variables
4. (20 points) Data in the Excel file Microprocessor Data shows the demand for one type of chip used in industrial equipment from a small manufacturer.
a. Construct a chart of the data. What appears to happen when a new chip is introduced?
b. Develop a causal regression model to forecast demand that includes both time and the introduction of a new chip as explanatory variables.
Data
| Closing Stock Prices | |||||||||||||||||||
| Two-period | Three period | ||||||||||||||||||
| Date | IBM | INTC | CSCO | GE | DJ Industrials Index |
Date | IBM | INTC | CSCO | GE | DJ Industrials Index |
Date | IBM | INTC | CSCO | GE | DJ Industrials Index |
||
| 9/3/10 | $127.58 | $18.43 | $21.04 | $15.39 | 10447.93 | 9/3/10 | 9/3/10 | ||||||||||||
| 9/7/10 | $125.95 | $18.12 | $20.58 | $15.44 | 10340.69 | 9/7/10 | 9/7/10 | ||||||||||||
| 9/8/10 | $126.08 | $17.90 | $20.64 | $15.70 | 10387.01 | 9/8/10 | $126.77 | $18.28 | $20.81 | $15.42 | $10,394.31 | 9/8/10 | |||||||
| 9/9/10 | $126.36 | $18.00 | $20.61 | $15.91 | 10415.24 | 9/9/10 | $126.02 | $18.01 | $20.61 | $15.57 | $10,363.85 | 9/9/10 | $126.54 | $18.15 | $20.75 | $15.51 | $10,391.88 | ||
| 9/10/10 | $127.99 | $17.97 | $20.62 | $15.98 | 10462.77 | 9/10/10 | $126.22 | $17.95 | $20.63 | $15.81 | $10,401.13 | 9/10/10 | $126.13 | $18.01 | $20.61 | $15.68 | $10,380.98 | ||
| 9/13/10 | $129.61 | $18.56 | $21.26 | $16.25 | 10544.13 | 9/13/10 | $127.18 | $17.99 | $20.62 | $15.95 | $10,439.01 | 9/13/10 | $126.81 | $17.96 | $20.62 | $15.86 | $10,421.67 | ||
| 9/14/10 | $128.85 | $18.74 | $21.45 | $16.16 | 10526.49 | 9/14/10 | $128.80 | $18.26 | $20.94 | $16.12 | $10,503.45 | 9/14/10 | $127.99 | $18.18 | $20.83 | $16.05 | $10,474.05 | ||
| 9/15/10 | $129.43 | $18.72 | $21.59 | $16.34 | 10572.73 | 9/15/10 | $129.23 | $18.65 | $21.36 | $16.21 | $10,535.31 | 9/15/10 | $128.82 | $18.42 | $21.11 | $16.13 | $10,511.13 | ||
| 9/16/10 | $129.67 | $18.97 | $21.93 | $16.23 | 10594.83 | 9/16/10 | $129.14 | $18.73 | $21.52 | $16.25 | $10,549.61 | 9/16/10 | $129.30 | $18.67 | $21.43 | $16.25 | $10,547.78 | ||
| 9/17/10 | $130.19 | $18.81 | $21.86 | $16.29 | 10607.85 | 9/17/10 | $129.55 | $18.85 | $21.76 | $16.29 | $10,583.78 | 9/17/10 | $129.32 | $18.81 | $21.66 | $16.24 | $10,564.68 | ||
| 9/20/10 | $131.79 | $18.93 | $21.75 | $16.55 | 10753.62 | 9/20/10 | $129.93 | $18.89 | $21.90 | $16.26 | $10,601.34 | 9/20/10 | $129.76 | $18.83 | $21.79 | $16.29 | $10,591.80 | ||
| 9/21/10 | $131.98 | $19.14 | $21.64 | $16.52 | 10761.03 | 9/21/10 | $130.99 | $18.87 | $21.81 | $16.42 | $10,680.74 | 9/21/10 | $130.55 | $18.90 | $21.85 | $16.36 | $10,652.10 | ||
| 9/22/10 | $132.57 | $19.01 | $21.67 | $16.50 | 10739.31 | 9/22/10 | $131.89 | $19.04 | $21.70 | $16.54 | $10,757.33 | 9/22/10 | $131.32 | $18.96 | $21.75 | $16.45 | $10,707.50 | ||
| 9/23/10 | $131.67 | $18.98 | $21.53 | $16.14 | 10662.42 | 9/23/10 | $132.28 | $19.08 | $21.66 | $16.51 | $10,750.17 | 9/23/10 | $132.11 | $19.03 | $21.69 | $16.52 | $10,751.32 | ||
| 9/24/10 | $134.11 | $19.42 | $22.09 | $16.66 | 10860.26 | 9/24/10 | $132.12 | $19.00 | $21.60 | $16.32 | $10,700.87 | 9/24/10 | $132.07 | $19.04 | $21.61 | $16.39 | $10,720.92 | ||
| 9/27/10 | $134.65 | $19.24 | $22.11 | $16.43 | 10812.04 | 9/27/10 | $132.89 | $19.20 | $21.81 | $16.40 | $10,761.34 | 9/27/10 | $132.78 | $19.14 | $21.76 | $16.43 | $10,754.00 | ||
| 9/28/10 | $134.89 | $19.51 | $21.86 | $16.44 | 10858.14 | 9/28/10 | $134.38 | $19.33 | $22.10 | $16.55 | $10,836.15 | 9/28/10 | $133.48 | $19.21 | $21.91 | $16.41 | $10,778.24 | ||
| 9/29/10 | $135.48 | $19.24 | $21.87 | $16.36 | 10835.28 | 9/29/10 | $134.77 | $19.37 | $21.99 | $16.44 | $10,835.09 | 9/29/10 | $134.55 | $19.39 | $22.02 | $16.51 | $10,843.48 | ||
| 9/30/10 | $134.14 | $19.20 | $21.90 | $16.25 | 10788.05 | 9/30/10 | $135.19 | $19.37 | $21.87 | $16.40 | $10,846.71 | 9/30/10 | $135.01 | $19.33 | $21.95 | $16.41 | $10,835.15 | ||
| 10/1/10 | $135.64 | $19.32 | $21.91 | $16.36 | 10829.68 | 10/1/10 | $134.81 | $19.22 | $21.89 | $16.31 | $10,811.67 | 10/1/10 | $134.84 | $19.32 | $21.88 | $16.35 | $10,827.16 | ||
| MAD | |||||||||||||||||||
| 9/8/10 | 0.685 | 0.375 | 0.17 | 0.284 | 7.3 | ||||||||||||||
| 9/9/10 | 0.345 | 0.01 | 0 | 0.34 | 51.39 | ||||||||||||||
| 9/10/10 | 1.77 | 0.02 | 0.005 | 0.175 | 61.645 | ||||||||||||||
| 9/13/10 | 2.435 | 0.572 | 0.645 | 0.305 | 105.125 | ||||||||||||||
| 9/14/10 | 0.05 | 0.4765 | 0.51 | 0.045 | 23.04 | ||||||||||||||
| 9/15/10 | 0.2 | 0.0715 | 0.235 | 0.135 | 37.42 | ||||||||||||||
| 9/16/10 | 0.53 | 0.24 | 0.41 | 0.02 | 45.22 | ||||||||||||||
| 9/17/10 | 0.64 | 0.035 | 0.103 | 0.005 | 24.07 | ||||||||||||||
| 9/20/10 | 1.86 | 0.04 | 0.1465 | 0.29 | 152.28 | ||||||||||||||
| 9/21/10 | 0.99 | 0.27 | 0.1665 | 0.1 | 80.295 | ||||||||||||||
| 9/22/10 | 0.685 | 0.025 | 0.025 | 0.035 | 18.015 | ||||||||||||||
| 9/23/10 | 0.605 | 0.095 | 0.125 | 0.37 | 87.75 | ||||||||||||||
| 9/24/10 | 1.99 | 0.428 | 0.49 | 0.34 | 159.395 | ||||||||||||||
| 9/27/10 | 1.76 | 0.0335 | 0.3 | 0.03 | 50.7 | ||||||||||||||
| 9/28/10 | 0.51 | 0.176 | 0.237 | 0.105 | 21.99 | ||||||||||||||
| 9/29/10 | 0.71 | 0.13 | 0.1165 | 0.075 | 0.19 | ||||||||||||||
| 9/30/10 | 1.045 | 0.1725 | 0.0335 | 0.15 | 58.66 | ||||||||||||||
| 10/1/10 | 0.83 | 0.1 | 0.025 | 0.055 | 18.015 | ||||||||||||||
| MAD | 0.98 | 0.1816666667 | 0.2079444444 | 0.1588333333 | 55.6944444444 |
ESM
| Closing Stock Prices | ||||||||||||
| ALPHA | 0.1 | |||||||||||
| Date | IBM | INTC | CSCO | GE | DJ Industrials Index |
Date | IBM | INTC | CSCO | GE | DJ Industrials Index |
|
| 9/3/10 | $127.58 | $18.43 | $21.04 | $15.39 | 10447.93 | 9/3/10 | $127.58 | $18.43 | $21.04 | $15.39 | 10447.93 | |
| 9/7/10 | $125.95 | $18.12 | $20.58 | $15.44 | 10340.69 | 9/7/10 | $127.58 | $18.43 | $21.04 | $15.39 | 10447.93 | |
| 9/8/10 | $126.08 | $17.90 | $20.64 | $15.70 | 10387.01 | 9/8/10 | $127.42 | $18.40 | $20.99 | $15.40 | $10,437.21 | |
| 9/9/10 | $126.36 | $18.00 | $20.61 | $15.91 | 10415.24 | 9/9/10 | $127.28 | $18.35 | $20.96 | $15.43 | $10,432.19 | |
| 9/10/10 | $127.99 | $17.97 | $20.62 | $15.98 | 10462.77 | 9/10/10 | $127.19 | $18.31 | $20.92 | $15.48 | $10,430.49 | |
| 9/13/10 | $129.61 | $18.56 | $21.26 | $16.25 | 10544.13 | 9/13/10 | $127.27 | $18.28 | $20.89 | $15.53 | $10,433.72 | |
| 9/14/10 | $128.85 | $18.74 | $21.45 | $16.16 | 10526.49 | 9/14/10 | $127.50 | $18.31 | $20.93 | $15.60 | $10,444.76 | |
| 9/15/10 | $129.43 | $18.72 | $21.59 | $16.34 | 10572.73 | 9/15/10 | $127.64 | $18.35 | $20.98 | $15.65 | $10,452.93 | |
| 9/16/10 | $129.67 | $18.97 | $21.93 | $16.23 | 10594.83 | 9/16/10 | $127.82 | $18.39 | $21.04 | $15.72 | $10,464.91 | |
| 9/17/10 | $130.19 | $18.81 | $21.86 | $16.29 | 10607.85 | 9/17/10 | $128.00 | $18.45 | $21.13 | $15.77 | $10,477.90 | |
| 9/20/10 | $131.79 | $18.93 | $21.75 | $16.55 | 10753.62 | 9/20/10 | $128.22 | $18.48 | $21.20 | $15.83 | $10,490.90 | |
| 9/21/10 | $131.98 | $19.14 | $21.64 | $16.52 | 10761.03 | 9/21/10 | $128.58 | $18.53 | $21.26 | $15.90 | $10,517.17 | |
| 9/22/10 | $132.57 | $19.01 | $21.67 | $16.50 | 10739.31 | 9/22/10 | $128.92 | $18.59 | $21.30 | $15.96 | $10,541.56 | |
| 9/23/10 | $131.67 | $18.98 | $21.53 | $16.14 | 10662.42 | 9/23/10 | $129.28 | $18.63 | $21.33 | $16.01 | $10,561.33 | |
| 9/24/10 | $134.11 | $19.42 | $22.09 | $16.66 | 10860.26 | 9/24/10 | $129.52 | $18.67 | $21.35 | $16.03 | $10,571.44 | |
| 9/27/10 | $134.65 | $19.24 | $22.11 | $16.43 | 10812.04 | 9/27/10 | $129.98 | $18.74 | $21.43 | $16.09 | $10,600.32 | |
| 9/28/10 | $134.89 | $19.51 | $21.86 | $16.44 | 10858.14 | 9/28/10 | $130.45 | $18.79 | $21.50 | $16.12 | $10,621.49 | |
| 9/29/10 | $135.48 | $19.24 | $21.87 | $16.36 | 10835.28 | 9/29/10 | $130.89 | $18.86 | $21.53 | $16.16 | $10,645.16 | |
| 9/30/10 | $134.14 | $19.20 | $21.90 | $16.25 | 10788.05 | 9/30/10 | $131.35 | $18.90 | $21.57 | $16.18 | $10,664.17 | |
| 10/1/10 | $135.64 | $19.32 | $21.91 | $16.36 | 10829.68 | 10/1/10 | $131.63 | $18.93 | $21.60 | $16.18 | $10,676.56 |
Data
| Residential and Commercial Coal Consumption, TBtu | |
| Month | Amount |
| Jan-90 | 16.50 |
| Feb-90 | 15.17 |
| Mar-90 | 12.75 |
| Apr-90 | 12.31 |
| May-90 | 8.34 |
| Jun-90 | 8.63 |
| Jul-90 | 12.37 |
| Aug-90 | 11.53 |
| Sep-90 | 9.47 |
| Oct-90 | 9.57 |
| Nov-90 | 14.44 |
| Dec-90 | 24.50 |
| Jan-91 | 19.94 |
| Feb-91 | 13.99 |
| Mar-91 | 12.50 |
| Apr-91 | 9.32 |
| May-91 | 7.63 |
| Jun-91 | 7.44 |
| Jul-91 | 9.86 |
| Aug-91 | 8.92 |
| Sep-91 | 7.38 |
| Oct-91 | 8.17 |
| Nov-91 | 15.65 |
| Dec-91 | 20.07 |
| Jan-92 | 16.98 |
| Feb-92 | 13.45 |
| Mar-92 | 12.15 |
| Apr-92 | 12.29 |
| May-92 | 7.42 |
| Jun-92 | 6.83 |
| Jul-92 | 10.96 |
| Aug-92 | 9.09 |
| Sep-92 | 8.51 |
| Oct-92 | 8.49 |
| Nov-92 | 14.83 |
| Dec-92 | 21.15 |
| Jan-93 | 15.21 |
| Feb-93 | 14.75 |
| Mar-93 | 11.81 |
| Apr-93 | 14.11 |
| May-93 | 7.42 |
| Jun-93 | 9.61 |
| Jul-93 | 9.76 |
| Aug-93 | 8.78 |
| Sep-93 | 6.63 |
| Oct-93 | 8.88 |
| Nov-93 | 14.92 |
| Dec-93 | 21.17 |
| Jan-94 | 19.74 |
| Feb-94 | 15.47 |
| Mar-94 | 11.39 |
| Apr-94 | 10.51 |
| May-94 | 7.73 |
| Jun-94 | 9.20 |
| Jul-94 | 10.53 |
| Aug-94 | 9.06 |
| Sep-94 | 6.65 |
| Oct-94 | 7.80 |
| Nov-94 | 12.49 |
| Dec-94 | 18.39 |
| Jan-95 | 14.74 |
| Feb-95 | 13.23 |
| Mar-95 | 9.91 |
| Apr-95 | 10.38 |
| May-95 | 6.73 |
| Jun-95 | 6.75 |
| Jul-95 | 9.16 |
| Aug-95 | 9.21 |
| Sep-95 | 6.19 |
| Oct-95 | 7.85 |
| Nov-95 | 16.64 |
| Dec-95 | 23.84 |
| Jan-96 | 16.05 |
| Feb-96 | 13.31 |
| Mar-96 | 12.11 |
| Apr-96 | 11.42 |
| May-96 | 8.77 |
| Jun-96 | 7.45 |
| Jul-96 | 10.19 |
| Aug-96 | 9.76 |
| Sep-96 | 7.70 |
| Oct-96 | 7.86 |
| Nov-96 | 15.27 |
| Dec-96 | 18.33 |
| Jan-97 | 18.61 |
| Feb-97 | 13.53 |
| Mar-97 | 11.47 |
| Apr-97 | 12.94 |
| May-97 | 8.53 |
| Jun-97 | 7.61 |
| Jul-97 | 11.28 |
| Aug-97 | 9.68 |
| Sep-97 | 8.12 |
| Oct-97 | 8.67 |
| Nov-97 | 14.79 |
| Dec-97 | 20.15 |
| Jan-98 | 16.56 |
| Feb-98 | 13.53 |
| Mar-98 | 13.53 |
| Apr-98 | 11.58 |
| May-98 | 8.03 |
| Jun-98 | 9.46 |
| Jul-98 | 10.75 |
| Aug-98 | 10.28 |
| Sep-98 | 8.04 |
| Oct-98 | 12.27 |
| Nov-98 | 14.02 |
| Dec-98 | 17.52 |
| Jan-99 | 16.00 |
| Feb-99 | 11.99 |
| Mar-99 | 6.96 |
| Apr-99 | 10.02 |
| May-99 | 6.14 |
| Jun-99 | 7.15 |
| Jul-99 | 9.72 |
| Aug-99 | 7.85 |
| Sep-99 | 5.73 |
| Oct-99 | 7.27 |
| Nov-99 | 10.13 |
| Dec-99 | 17.54 |
| Jan-00 | 15.04 |
| Feb-00 | 11.21 |
| Mar-00 | 8.70 |
| Apr-00 | 9.92 |
| May-00 | 6.65 |
| Jun-00 | 6.73 |
| Jul-00 | 8.12 |
| Aug-00 | 8.31 |
| Sep-00 | 6.87 |
| Oct-00 | 5.45 |
| Nov-00 | 11.29 |
| Dec-00 | 18.21 |
Coal Consumption
32874 32905 32933 32964 32994 33025 33055 33086 33117 33147 33178 33208 33239 33270 33298 33329 33359 33390 33420 33451 33482 33512 33543 33573 33604 33635 33664 33695 33725 33756 33786 33817 33848 33878 33909 33939 33970 34001 34029 34060 34090 34121 34151 34182 34213 34243 34274 34304 34335 34366 34394 34425 34455 34486 34516 34547 34578 34608 34639 34669 34700 34731 34759 34790 34820 34851 34881 34912 34943 34973 35004 35034 35065 35096 35125 35156 35186 35217 35247 35278 35309 35339 35370 35400 35431 35462 35490 35521 35551 35582 35612 35643 35674 35704 35735 35765 35796 35827 35855 35886 35916 35947 35977 36008 36039 36069 36100 36130 36161 36192 36220 36251 36281 36312 36342 36373 36404 36434 36465 36495 36526 36557 36586 36617 36647 36678 36708 36739 36770 36800 36831 36861 16.495457999999999 15.170311999999999 12.748545 12.306327 8.3354750000000006 8.6308410000000002 12.373495 11.533300000000001 9.4710590000000003 9.5660830000000008 14.441103999999999 24.497757 19.935203999999999 13.989250999999999 12.49736 9.3156289999999995 7.632072 7.4356470000000003 9.861864 0000000006 8.9212980000000002 7.382161 8.1688799999999997 15.654849 20.067626000000001 16.979825999999999 13.451544999999999 12.148491999999999 12.294813 7.4159069999999998 6.8304590000000003 10.961838999999999 9.0867850000000008 8.5098640000000003 8.4877059999999993 14.834148000000001 21.154781 15.210972 14.750581 11.811070000000001 14.105847000000001 7.4241190000000001 9.6142489999999992 9.7584 8.7772360000000003 6.629346 8.8834940000000007 14.920049000000001 21.167484999999999 19.735977999999999 15.465037000000001 11.389132999999999 10.512998 7.728288 9.1988760000000003 10.533937 9.0554679999999994 6.6530009999999997 7.7992179999999998 12.494142 18.394863000000001 14.741944999999999 13.230409 9.9050480000000007 10.377329 6.7255599999999998 6.7518459999999996 9.1634849999999997 9.2130039999999997 6.191554 7.854349 16.642444000000001 23.837672000000001 16.048622999999999 13.305263 12.105051 11.421255 8.7658210000000008 7.4523710000000003 10.186434 9.7550899999999992 7.6978749999999998 7.8624980000000004 15.266729 18.329734999999999 18.614497 13.534178000000001 11.465892 12.936538000000001 8.5319540000000007 7.6079080000000001 11.276628000000001 9.6805570000000003 8.1191689999999994 8.673349 14.794259 20.146958000000001 16.560002000000001 13.527293999999999 13.527293999999999 11.584885 8.0278220000000005 9.4636940000000003 10.751757 10.284291 8.0404409999999995 12.265948 14.018243999999999 17.522787999999998 15.997857 11.989145000000001 6.9644110000000001 10.016489999999999 6.1374230000000001 7.1470209999999996 9.7196610000000003 7.8545379999999998 5.7267340000000004 7.2719139999999998 10.134529000000001 17.544969999999999 15.042154999999999 11.209559 8.6996990000000007 9.9220210000000009 6.649864 6.72905 8.1212540000000004 8.3120309999999993 6.8676250000000003 5.4469799999999999 11.293424999999999 18.211103000000001
Data
| Consumer Price Index – All Urban Consumers | Bureau of Labor Statistics | |||
| alpha | beta | |||
| 0.6 | 0.4 | |||
| Year | Annual CPI | Level a | Trend b | Forecast |
| 2003 | 184.0 | 184.0 | 4.90000000000001 | |
| 2004 | 188.9 | 188.9 | 4.9 | |
| 2005 | 195.3 | 194.7 | 5.26 | 193.8 |
| 2006 | 201.6 | 200.944 | 5.6536 | 199.96 |
| 2007 | 207.342 | 207.04424 | 5.832256 | 206.5976 |
| 2008 | 215.303 | 214.3323984 | 6.41461696 | 212.876496 |
| 2009 | 214.537 | 217.021006144 | 4.9242132736 | 220.74701536 |
| 2010 | 218.056 | 219.611687767 | 3.9908006134 | 221.9452194176 |
| 2011 | 224.939 | 224.4043953522 | 4.3115634021 | 223.6024883804 |
| 2012 | 229.594 | 229.2427835017 | 4.5222933011 | 228.7159587542 |
| 2013 | 233.7650768028 | |||
| 2014 | 238.2873701038 |
Bureau of Labor Statistics
Source: Bureau of Labor Statistics Generated on: October 4, 2010 (01:33:45 PM)
Data
| Microprocessor Data | ||
| Demand | Month | New Chip Introduced |
| 3949 | 1 | 0 |
| 3544 | 2 | 0 |
| 6138 | 3 | 0 |
| 7752 | 4 | 0 |
| 10700 | 5 | 1 |
| 7766 | 6 | 0 |
| 5482 | 7 | 0 |
| 8789 | 8 | 0 |
| 13103 | 9 | 1 |
| 7326 | 10 | 0 |
| 8669 | 11 | 0 |
| 7219 | 12 | 0 |
| 7456 | 13 | 0 |
| 9744 | 14 | 0 |
| 14561 | 15 | 1 |
| 8564 | 16 | 0 |
| 12214 | 17 | 0 |
| 19020 | 18 | 1 |
| 14751 | 19 | 0 |
| 14269 | 20 | 0 |
| 14896 | 21 | 0 |
| 15359 | 22 | 0 |
| 16527 | 23 | 0 |
| 17029 | 24 | 0 |
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-12 hours? PAY FOR YOUR FIRST ORDER AFTER COMPLETION..
Get Answer Over WhatsApp
Order Paper Now
