Question: 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

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 - Multiple RegressionThe average price per gallon of gasoline in

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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Q:

OPERATION RESEACH Question #1 (a). A-Grade Manufacturers produces three mixtures of sand, pebbles, and rocks for eventual sale in 20kg bags to new homebuyers who want to expand and beautify their...

Q:

The average price per gallon of gasoline in major U.S. cities for each month during a three-year period are shown in the following table: (a) Using exponential smoothing with a = 0.45, what is the...

Q:

43 The average price per gallon of gasoline in major U.S. cities for each month during a three-year period are shown in the following table: MONTH YEAR 1 YEAR 2 YEAR 3 January $2.972 $3.301 $3.472...

Q:

Question # 3. The average price per gallon of gasoline in major U.S. cities for each month during the years 2014-2016 are shown in the table below: Month 2014 2015 January 0.972 1.301 February 0.955...

Q:

The average price per gallon of gasoline in major U.S. cities for each month during the years 2014- 2016 are shown in the table below: Month 2014 2015 0.972 1.301 0.955 1.369 1.541 0.991 1.117 1.178...

Q:

They go in order starting from top left to right. The page numbers are in top. I couldn't post more than one picture so I had to put them together. please do what you can to solve these. | Page -MATH...

Q:

Homework Assignment #3 Impact fees 1 of 1 Due: Tuesday, November 5 (at the beginning of class) Total points 20 points 1. Impact fee program on sewer facilities (20 points) The current population of...

Q:

Hello, this is an academic assignment. Answer the question between 250 and 300 words. Question: Can you think of any alliances that you think AT&T or Apple should consider today to help them achieve...

Q:

Above is an example of different company 1. Create a Flowchart of Nintendo's current process. (Include quantities and timing). Will UpVote if labeled correctly. Current Challenge Major retailers are...

Q:

Instuctor's Annotated Edition TENTH EDITION Understandable Statistics Concepts and Methods Charles Henry Brase Regis University Corrinne Pellillo Brase Arapahoe Community College Australia Brazil...

Q:

When using moving-average smoothing to generate forecasts of cycle factors, the researcher should be wary of spurious cycles caused by heteroscedasticity. spurious cycles caused by serial...

Q:

In a study if our critical value for tis 2.35 and our calculated t statistic is 2.012, what should we conclude? O A) reject the null hypothesis. B) we should check our calculations. C) we should run...

Q:

Johnson and Johnson, the healthcare company, incurs the following costs. Classify each of these cost items based on the part of the value - chain these costs are related to . Payment of booth...

Q:

In Trump v. Hawaii (Travel Ban case), the Supreme Court upheld Proclamation No. 9645, banning entry into the United States of foreign nationals from certain designated countries. Briefly explain the...

Q:

Improving creative problem-solving ability.

Q:

1. Discuss the following statements: (a) A circle becomes a straight line when viewed on a very small scale. (b) Breaking patterns creates change.

Q:

2. Develop your own loop models for the following conditions. At least one model shall be developed for each of the points under (a-j) a) If A shows anger towards B, what will Bs reaction be? b) What...