CHAPTER 12 FORECASTING Period # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Day Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sales $6,050 $7,200 $4,800 $3,200 $4,700 $7,300 $6,750 $6,200 $7,560 $5,140 $3,340 $5,000 $7,610 $7,150 $6,500 $7,850 $5,200 $3,590 $5,260 $8,010 $7,600 CHAPTER 12 FORECASTING Period # Day From Regression Table Sales/Trend Sales Trend Value Ratio $5,523 $5,571 $5,619 $5,666 $5,714 $5,762 $5,810 $5,857 $5,905 $5,953 $6,000 $6,048 $6,096 $6,144 $6,191 $6,239 $6,287 $6,335 $6,382 $6,430 $6,478 $6,526 $6,573 $6,621 $6,669 $6,717 $6,764 $6,812 1.095 1.292 0.854 0.565 0.823 1.267 1.162 1.059 1.280 0.863 0.557 0.827 1.248 1.164 1.050 1.258 0.827 0.567 0.824 1.246 1.173 1 Sunday $6,050 2 3 4 5 6 7 8 9 10 11 12 13 Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday $7,200 $4,800 $3,200 $4,700 $7,300 $6,750 $6,200 $7,560 $5,140 $3,340 $5,000 $7,610 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday $7,150 $6,500 $7,850 $5,200 $3,590 $5,260 $8,010 $7,600 28 Seasonal Indices Sunday Monday Tuesday Wednesday Thursday Friday Saturday 1.068 1.277 0.848 0.563 0.824 1.254 1.166 Trend * Seasonal Index (Sales - Reverse Forecast)^2 Seasonal lndex * Trend Value Seasonal Index Reverse Forecast Squared Error Forecast 1.068 1.277 0.848 0.563 0.824 1.254 1.166 1.068 1.277 0.848 0.563 0.824 1.254 1.166 1.068 1.277 0.848 0.563 0.824 1.254 1.166 1.068 1.277 0.848 0.563 0.824 1.254 1.166 $5,898 $7,114 $4,766 $3,188 $4,711 $7,223 $6,776 $6,255 $7,540 $5,050 $3,376 $4,986 $7,642 $7,165 $6,612 $7,967 $5,333 $3,564 $5,262 $8,061 $7,555 $23,044 $7,440 $1,142 $134 $120 $5,866 $658 $3,033 $381 $8,155 $1,328 $183 $1,048 $237 $12,534 $13,741 $17,738 $651 $4 $2,634 $2,011 $6,969 $8,394 $5,617 $3,753 $5,538 $8,480 $7,945 TREND LINE SUMMARY OUTPUT Regression Statistics Multiple R 0.192952573 R Square 0.037230696 Adjusted R Square -0.013441373 Standard Error 1545.481756 Observations 21 ANOVA df Regression Residual Total Intercept Period # 1 19 20 Coefficients 5475.333333 47.74025974 SS MS F Significance F 1754931.948 1754931.948 0.734738022 0.40203447 45381763.29 2388513.857 47136695.24 Standard Error t Stat 699.3401511 7.829284969 55.69529647 0.857168608 P-value 2.30542E-07 0.40203447 Lower 95% Upper 95% Lower 95.0% Upper 95.0% 4011.597575 6939.069092 4011.597575 6939.069092 -68.83133548 164.311855 -68.83133548 164.311855 $9,000 $8,000 $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 0 5 10 Actual Sales History 15 Reverse Forecast 20 Forecast 25 Linear (Actual Sales History) 30 Period # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Day Sales Sunday $6,050 Monday $7,200 Tuesday $4,800 Wednesday $3,200 Thursday $4,700 Friday $7,300 Saturday $6,750 Sunday $6,200 Monday $7,560 Tuesday $5,140 Wednesday $3,340 Thursday $5,000 Friday $7,610 Saturday $7,150 Sunday $6,500 Monday $7,850 Tuesday $5,200 Wednesday $3,590 Thursday $5,260 Friday $8,010 Saturday $7,600 Sunday Monday Tuesday Wednesday Thursday Friday Saturday Seasonal Indices Sunday 1.068 Monday 1.277 Tuesday 0.848 Wednesday 0.563 Thursday 0.824 Friday 1.254 Saturday 1.166 CHAPTER 12 BUSINESS FORECASTING AND GRADING RUBRIC A brewery runs three 8-hour shifts. The most experienced workers are on the day shift, moderately experienced workers are on the evening shift, and the least experienced workers are on the midnight shift. New equipment was installed in June, and the Production Manager hopes that each shift will improve its productivity levels. The brewery computes a proprietary production level metric, and below are the data for the average productivity levels since May using that metric. Day Evening Midnight May 10 9.8 9.7 June 12 11 10.2 July 12.2 11 10.2 August 13 12.5 11.1 Compute the forecast for the average productivity levels for each shift in September. Compute using the full time series method based only on the above information. Solve the following using Excel. 1. What is the equation of the trend line for the model. Write the equation here. 2 pts. 2. If there are seasonal indices, write them here (season and corresponding index). If not, then briefly explain below why there are no seasonal indices in this problem. 3 pts. 3. What is the forecast for the “evening shift” time frame for the September. Write your answer below. 1 pt. 4. What is the error factor in the model. Write that value here. 1 pt. 5. Develop a graph showing plots of the original data, the regression line through that data, the forecast of the actual data, and the forecast of the next month. 2 pts. 6. Why is it advisable to perform a “reverse forecast.” 1 pt. 7. Suppose you are briefing this forecast model to a senior manager in your company. Suppose the senior manager asks you for a forecast for December, based on your model. Describe your response to this request. Do not actually calculate an answer; just describe either what you would do to respond or how you would respond. 2 pts.
Purchase answer to see full attachment