Linear Trend - Multiple Regression

The average price per gallon of gasoline in major U.S. cities for each month during the years 1999-2001 are shown in the table below:

Month	1999	2000	2001
January	0.972	1.301	1.472
February	0.955	1.369	1.484
March	0.991	1.541	1.447
April	1.117	1.506	1.564
May	1.178	1.498	1.729
June	1.148	1.617	1.640
July	1.189	1.593	1.482
August	1.255	1.510	1.427
September	1.280	1.582	1.531
October	1.274	1.559	1.362
November	1.264	1.555	1.263
December	1.298	1.489	1.131

Forecast the average gasoline price for January 2002 using linear trend analysis.

1. What is the linear equation that best fits the data?
2. Graph the data.
3. What is the forecast for the average gasoline price for January 2002?
4. What is the MAPE for this method?
5. Isthismethodmoreaccuratethanexponentialsmoothingforthisdataset?

Need to use excel om/qm the software provided as an excel plugin any one can download. http://wps.prenhall.com/bp_heizer_opsmgmt_10/147/37737/9660837.cw/index.html

I completed 1st 3 questions but stuck with this one.

http://wps.prenhall.com/bp_heizer_opsmgmt_10/147/37737/9660837.cw/index.html

Linear Trend The average price per gallon of gasoline in major U.S. cities for each month during the years 1999-2001 are shown in the table below: Month January February March April May June July August September October November December 1999 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.280 1.274 1.264 1.298 2000 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.510 1.582 1.559 1.555 1.489 2001 1.472 1.484 1.447 1.564 1.729 1.640 1.482 1.427 1.531 1.362 1.263 1.131 Forecast the average gasoline price for January 2002 using linear trend analysis. a. b. c. d. e. What is the linear equation that best fits the data? Graph the data. What is the forecast for the average gasoline price for January 2002? What is the MAPE for this method? Is this method more accurate than exponential smoothing for this data set? Linear Trend The average price per gallon of gasoline in major U.S. cities for each month during the years 1999-2001 are shown in the table below: Month January February March April May June July August September October November December 1999 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.280 1.274 1.264 1.298 2000 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.510 1.582 1.559 1.555 1.489 2001 1.472 1.484 1.447 1.564 1.729 1.640 1.482 1.427 1.531 1.362 1.263 1.131 Forecast the average gasoline price for January 2002 using linear trend analysis. a. b. c. d. e. What is the linear equation that best fits the data? Graph the data. What is the forecast for the average gasoline price for January 2002? What is the MAPE for this method? Is this method more accurate than exponential smoothing for this data set? Weighted Avg Forecasting Moving averages - 3 period moving average Enter the past demands in the data area Enter the past demands in the data area Num pds Data Period Freshmen fall Freshmen Winter Frehmen Spring Sophmore fall Sophmore winter Sophmore spring Junior fall Junior winter Junior spring Next period 3 Forecasting Forecasts and Error Analysis Forecast Error Absolute Demand 2.2 2.7 2.5 2.4 3 2.7 2.5 3.6 3.2 3.1 Squared 4 Abs Pct Err 3.5 3 2.466666667 2.5333333333 2.6333333333 2.7 2.7333333333 2.9333333333 Total Average -0.066666667 0.066666667 0.004444444 02.78% 0.466666667 0.466666667 0.217777778 15.56% 0.066666667 0.066666667 0.004444444 02.47% -0.2 0.2 0.04 08.00% 0.866666667 0.866666667 0.751111111 24.07% 0.266666667 0.266666667 0.071111111 08.33% 1.4 1.9333333333 1.0888888889 61.21% 0.233333333 0.322222222 0.181481481 10.20% Bias MAD MSE MAPE SE 0.521749195 2.5 Value 2 1.5 1 0.5 0 1 2 3 4 5 6 Time Demand Forecast 7 8 9 Weighted Moving Avg Forecasting Weighted moving averages - 3 period moving average Enter the data in the shaded area. Enter weights in Enter the data in the shaded area. Enter weights in INCREASING order from top to bottom. INCREASING order from top to bottom. Data Period Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12 Next period Demand Forecasts and Error Analysis Forecast Error Absolute Weights 17 22 25 16 28 23 19 20 17 25 33 32 31.16666667 Forecasting Squared Abs Pct Err 35 1 2 3 30 22.66666667 -6.666666667 6.666666667 44.44444444 41.67% 20 8 8 64 28.57% 23.5 -0.5 0.5 0.25 02.17% 23.5 -4.5 4.5 20.25 23.68% 21.83333333 -1.833333333 1.8333333333 3.361111111 09.17% 20.16666667 -3.166666667 3.166666667 10.02777778 18.63% 18.33333333 6.666666667 6.666666667 44.44444444 26.67% 21.5 11.5 11.5 132.25 34.85% 27.66666667 4.3333333333 4.3333333333 18.77777778 13.54% Total 13.83333333 47.16666667 337.8055556 198.95% Average 1.537037037 5.2407407407 37.53395062 22.11% Bias MAD MSE MAPE SE 6.946793254 25 20 Value 15 10 5 0 1 2 3 4 5 6 7 8 Time Demand Forecast 9 10 11 12 Exponential Smoothing Enter alpha (between 0 and 1), enter the past demands in the shaded column then enter a Enter alpha (between 0 and 1), enter the past demands in the shaded column then enter a starting forecast. If the starting forecast is not in the first period then delete the error starting forecast. If forecast is not smoothing Forecastingall rowsthe startingExponentialin the first period then delete the error analysis for all rows above the starting forecast. analysis for above the starting forecast. Alpha Data Period Q1 1 Q1 2 Q1 3 Q1 4 Q1 5 Q1 6 Q1 7 Q1 8 Q1 9 Q1 10 Q1 11 Q1 12 Q1 13 Q1 14 Q1 15 Q1 16 Next period 1.517669336 Alpha should be between 0 and 1 Forecasts and Error Analysis Demand Forecast Error Absolute Squared Abs Pct Err 8635 8635 0 0 0 00.00% 8700 8635 65 65 4225 00.75% 8802 8733.648507 68.35149314 68.35149314 4671.926614 00.78% 8975 8837.383472 137.6165279 137.6165279 18938.30875 01.53% 9113 9046.239857 66.76014332 66.76014332 4456.916736 00.73% 9196 9147.559679 48.44032091 48.44032091 2346.46469 00.53% 9334 9221.076069 112.9239312 112.9239312 12751.81424 01.21% 9546 9392.457257 153.5427435 153.5427435 23575.37407 01.61% 9671 9625.48437 45.51562988 45.51562988 2071.672564 00.47% 9846 9694.562046 151.4379541 151.4379541 22933.45394 01.54% 9893 9924.394785 -31.39478519 31.39478519 985.6325373 00.32% 9983 9876.747882 106.2521176 106.2521176 11289.5125 01.06% 10038 10038.00346 -0.003463216 0.003463216 1.19939E-005 00.00% 10081 10037.99821 43.0017928 43.0017928 1849.154184 00.43% 10109 10103.26071 5.739290458 5.739290458 32.93945496 00.06% 10188 10111.97105 76.02894532 76.02894532 5780.400526 0.0074626 Total 1049.212642 1112.009139 115908.5708 11.75% Average 65.57579011 69.50057116 7244.285676 00.73% Bias MAD MSE MAPE SE 90.9900194 10227.35785 Forecasting 10500 10000 9500 Value 9000 8500 8000 7500 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Time 8635 8635 Linear Trend Forecasting Multiple regression Enter the data in the shaded area. To get a forecast use the shaded Enter the data in the shaded area. To get a forecast use the shaded data area at the bottom left of the sheet. data area at the bottom left of the sheet. Data Y Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Year 2 Year 3 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.28 1.274 1.264 1.298 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.51 1.582 1.559 1.555 1.489 1.472 1.484 1.447 1.564 1.729 1.64 1.482 1.427 1.531 1.362 1.263 1.131 Err:502 Err:502 Coefficients Err:502 Err:502 Forecast Err:502 Forecasts and Error Analysis Forecast Error Absolute Squared Abs Pct Err Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Total Err:502 Err:502 Err:502 Err:502 Average Err:502 Err:502 Err:502 Err:502 Bias MAD MSE MAPE SE Err:502 13 Correlation #VALUE! Test Forecasting Multiple regression Enter the data in the shaded area. To get a forecast use the shaded Enter the data in the shaded area. To get a forecast use the shaded data area at the bottom left of the sheet. data area at the bottom left of the sheet. Data Y Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Year 2 Year 3 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.28 1.274 1.264 1.298 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.51 1.582 1.559 1.555 1.489 1.472 1.484 1.447 1.564 1.729 1.64 1.482 1.427 1.531 1.362 1.263 1.131 Err:502 Err:502 Coefficients Err:502 Err:502 Forecast Err:502 Forecasts and Error Analysis Forecast Error Absolute Squared Abs Pct Err Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Total Err:502 Err:502 Err:502 Err:502 Average Err:502 Err:502 Err:502 Err:502 Bias MAD MSE MAPE SE Err:502 13 Correlation #VALUE! Linear Trend The average price per gallon of gasoline in major U.S. cities for each month during the years 1999-2001 are shown in the table below: Month January February March April May June July August September October November December 1999 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.280 1.274 1.264 1.298 2000 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.510 1.582 1.559 1.555 1.489 2001 1.472 1.484 1.447 1.564 1.729 1.640 1.482 1.427 1.531 1.362 1.263 1.131 Forecast the average gasoline price for January 2002 using linear trend analysis. a. b. c. d. e. What is the linear equation that best fits the data? Graph the data. What is the forecast for the average gasoline price for January 2002? What is the MAPE for this method? Is this method more accurate than exponential smoothing for this data set? Weighted Avg Forecasting Moving averages - 3 period moving average Enter the past demands in the data area Enter the past demands in the data area Num pds Data Period Freshmen fall Freshmen Winter Frehmen Spring Sophmore fall Sophmore winter Sophmore spring Junior fall Junior winter Junior spring Next period 3 Forecasting Forecasts and Error Analysis Forecast Error Absolute Demand 2.2 2.7 2.5 2.4 3 2.7 2.5 3.6 3.2 3.1 Squared 4 Abs Pct Err 3.5 3 2.466666667 2.5333333333 2.6333333333 2.7 2.7333333333 2.9333333333 Total Average -0.066666667 0.066666667 0.004444444 02.78% 0.466666667 0.466666667 0.217777778 15.56% 0.066666667 0.066666667 0.004444444 02.47% -0.2 0.2 0.04 08.00% 0.866666667 0.866666667 0.751111111 24.07% 0.266666667 0.266666667 0.071111111 08.33% 1.4 1.9333333333 1.0888888889 61.21% 0.233333333 0.322222222 0.181481481 10.20% Bias MAD MSE MAPE SE 0.521749195 2.5 Value 2 1.5 1 0.5 0 1 2 3 4 5 6 Time Demand Forecast 7 8 9 Weighted Moving Avg Forecasting Weighted moving averages - 3 period moving average Enter the data in the shaded area. Enter weights in Enter the data in the shaded area. Enter weights in INCREASING order from top to bottom. INCREASING order from top to bottom. Data Period Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12 Next period Demand Forecasts and Error Analysis Forecast Error Absolute Weights 17 22 25 16 28 23 19 20 17 25 33 32 31.16666667 Forecasting Squared Abs Pct Err 35 1 2 3 30 22.66666667 -6.666666667 6.666666667 44.44444444 41.67% 20 8 8 64 28.57% 23.5 -0.5 0.5 0.25 02.17% 23.5 -4.5 4.5 20.25 23.68% 21.83333333 -1.833333333 1.8333333333 3.361111111 09.17% 20.16666667 -3.166666667 3.166666667 10.02777778 18.63% 18.33333333 6.666666667 6.666666667 44.44444444 26.67% 21.5 11.5 11.5 132.25 34.85% 27.66666667 4.3333333333 4.3333333333 18.77777778 13.54% Total 13.83333333 47.16666667 337.8055556 198.95% Average 1.537037037 5.2407407407 37.53395062 22.11% Bias MAD MSE MAPE SE 6.946793254 25 20 Value 15 10 5 0 1 2 3 4 5 6 7 8 Time Demand Forecast 9 10 11 12 Exponential Smoothing Enter alpha (between 0 and 1), enter the past demands in the shaded column then enter a Enter alpha (between 0 and 1), enter the past demands in the shaded column then enter a starting forecast. If the starting forecast is not in the first period then delete the error starting forecast. If forecast is not smoothing Forecastingall rowsthe startingExponentialin the first period then delete the error analysis for all rows above the starting forecast. analysis for above the starting forecast. Alpha Data Period Q1 1 Q1 2 Q1 3 Q1 4 Q1 5 Q1 6 Q1 7 Q1 8 Q1 9 Q1 10 Q1 11 Q1 12 Q1 13 Q1 14 Q1 15 Q1 16 Next period 1.517669336 Alpha should be between 0 and 1 Forecasts and Error Analysis Demand Forecast Error Absolute Squared Abs Pct Err 8635 8635 0 0 0 00.00% 8700 8635 65 65 4225 00.75% 8802 8733.648507 68.35149314 68.35149314 4671.926614 00.78% 8975 8837.383472 137.6165279 137.6165279 18938.30875 01.53% 9113 9046.239857 66.76014332 66.76014332 4456.916736 00.73% 9196 9147.559679 48.44032091 48.44032091 2346.46469 00.53% 9334 9221.076069 112.9239312 112.9239312 12751.81424 01.21% 9546 9392.457257 153.5427435 153.5427435 23575.37407 01.61% 9671 9625.48437 45.51562988 45.51562988 2071.672564 00.47% 9846 9694.562046 151.4379541 151.4379541 22933.45394 01.54% 9893 9924.394785 -31.39478519 31.39478519 985.6325373 00.32% 9983 9876.747882 106.2521176 106.2521176 11289.5125 01.06% 10038 10038.00346 -0.003463216 0.003463216 1.19939E-005 00.00% 10081 10037.99821 43.0017928 43.0017928 1849.154184 00.43% 10109 10103.26071 5.739290458 5.739290458 32.93945496 00.06% 10188 10111.97105 76.02894532 76.02894532 5780.400526 0.0074626 Total 1049.212642 1112.009139 115908.5708 11.75% Average 65.57579011 69.50057116 7244.285676 00.73% Bias MAD MSE MAPE SE 90.9900194 10227.35785 Forecasting 10500 10000 9500 Value 9000 8500 8000 7500 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Time 8635 8635 Linear Trend Forecasting Multiple regression Enter the data in the shaded area. To get a forecast use the shaded Enter the data in the shaded area. To get a forecast use the shaded data area at the bottom left of the sheet. data area at the bottom left of the sheet. Data Y Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Year 2 Year 3 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.28 1.274 1.264 1.298 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.51 1.582 1.559 1.555 1.489 1.472 1.484 1.447 1.564 1.729 1.64 1.482 1.427 1.531 1.362 1.263 1.131 Err:502 Err:502 Coefficients Err:502 Err:502 Forecast Err:502 Forecasts and Error Analysis Forecast Error Absolute Squared Abs Pct Err Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Total Err:502 Err:502 Err:502 Err:502 Average Err:502 Err:502 Err:502 Err:502 Bias MAD MSE MAPE SE Err:502 13 Correlation #VALUE! Test Forecasting Multiple regression Enter the data in the shaded area. To get a forecast use the shaded Enter the data in the shaded area. To get a forecast use the shaded data area at the bottom left of the sheet. data area at the bottom left of the sheet. Data Y Year 1 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Year 2 Year 3 0.972 0.955 0.991 1.117 1.178 1.148 1.189 1.255 1.28 1.274 1.264 1.298 1.301 1.369 1.541 1.506 1.498 1.617 1.593 1.51 1.582 1.559 1.555 1.489 1.472 1.484 1.447 1.564 1.729 1.64 1.482 1.427 1.531 1.362 1.263 1.131 Err:502 Err:502 Coefficients Err:502 Err:502 Forecast Err:502 Forecasts and Error Analysis Forecast Error Absolute Squared Abs Pct Err Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Total Err:502 Err:502 Err:502 Err:502 Average Err:502 Err:502 Err:502 Err:502 Bias MAD MSE MAPE SE Err:502 13 Correlation #VALUE