Problem set A solutions

Problem set A solutions - BUSI 403 Problem Set A Fall 2008...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 Problem 1 a. Use a simple 3-month moving average to forecast sales for months 4 to 13. What are the Mean Absolute Deviation (MAD) and the Mean Square Error (MSE) of the forecast for months 4 to 12? Forecast for month 4=(Sales in month 1+ Sales in month 2+ Sales in month 3)/3 Month 1 2 3 4 5 6 7 8 9 10 11 12 13 3 Month SMA Forecast Absolute Error Squared Error 31.33 9.33 10.00 22.33 56.67 69.00 23.00 20.33 14.33 981.78 87.11 100.00 498.78 3,211.11 4,761.00 529.00 413.44 205.44 460.67 475.67 482.00 489.67 496.33 519.00 551.00 571.67 584.67 588.33 MAD 28.48 MSE 1,198.63 b. Provide a forecast using a 3-month weighted average method with weights of 0.6, 0.3 and 0.1 (most recent, next most recent, and third most recent month, respectively) for months 4 to 13. What are the MAD and MSE of the forecasts for months 4 to 12 with this new method? Forecast for month 4 = 0.1*Sales in month 1+0.3*Sales in month 2+0.6*Sales in month 3 Month 3 Month SMA Absolute Error Squared Error 4 5 6 7 8 9 10 11 12 13 465.9 482.5 485.5 489.9 503.3 534.6 569.9 576.1 586.2 594.4 26.1 2.5 6.5 22.1 49.7 53.4 4.1 15.9 12.8 681.21 6.25 42.25 488.41 2,470.09 2,851.56 16.81 252.81 163.84 MAD 21.456 MSE 774.80 1 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 c. Which forecasting method is more accurate? Which of these two methods would you recommend to Honda to forecast sales? The 3-month weighted average method is more accurate as both MAD and MSE are lower than those obtained from simple moving average method. Therefore the 3-month weighted average method would be recommended. Problem 2 a. Compute 3 sets of simple exponential smoothing forecasts for months 2 to 13, with alpha (α) values of 0.25, 0.5 and 0.75. Assume the forecast for month 1 is 447. Alpha Month 1 2 3 4 5 6 7 8 9 10 11 12 13 0.25 Sales 447 466 469 492 485 492 512 553 588 574 592 599 0.75 Forecast Forecast Forecast 447.00 447.00 447.00 447.00 447.00 447.00 451.75 456.50 461.25 456.06 462.75 467.06 465.05 477.38 485.77 470.04 481.19 485.19 475.53 486.59 490.30 484.64 499.30 506.57 501.73 526.15 541.39 523.30 557.07 576.35 535.98 565.54 574.59 549.98 578.77 587.65 562.24 588.88 596.16 2 0.5 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 b. To visualize the difference between forecasts and actual sales, plot the actual sales as well as 3 simple exponential smoothing forecasts computed in (a) for months 1 to 12 on one graph. Which value of α produces the most responsive forecast? Actual Sales And Forecast 650 600 Sales 550 Alpha=0.25 Alpha=0.5 500 Alpha=0.75 450 400 1 2 3 4 5 6 7 Month 8 9 10 11 12 When α=0.75 the forecast tracks the actual sales more closely and is more responsive to demand fluctuation. Lower α tends to smooth out demand variation and resulting in a smoother line. For this data set, since the sales across different months exhibit growing trend, a higher α value allows the forecast to track sales more accurately. c. Calculate the MAD and Mean Absolute Percentage Error (MAPE) using the forecast errors from months 2-12 for each of these three sets of forecasts. Which α value seems to be most appropriate? α=0.25 Month Sales 1 2 3 4 5 6 7 8 9 10 11 12 447 466 469 492 485 492 512 553 588 574 592 599 Forecast Absolute Error 447.00 451.75 456.06 465.05 470.04 475.53 484.64 501.73 523.30 535.98 549.98 19.00 17.25 35.94 19.95 21.96 36.47 68.36 86.27 50.70 56.02 49.02 MAD 41.90 3 Abspolute Percentage Error 4.08% 3.68% 7.30% 4.11% 4.46% 7.12% 12.36% 14.67% 8.83% 9.46% 8.18% MAPE 7.66% BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 α=0.5 Month 1 2 3 4 5 6 7 8 9 10 11 12 Forecast 447 466 469 492 485 492 512 553 588 574 592 599 Absolute Error 447.00 456.50 462.75 477.38 481.19 486.59 499.30 526.15 557.07 565.54 578.77 19.00 12.50 29.25 7.63 10.81 25.41 53.70 61.85 16.93 26.46 20.23 MAD 25.80 Abspolute Percentage Error 4.08% 2.67% 5.95% 1.57% 2.20% 4.96% 9.71% 10.52% 2.95% 4.47% 3.38% MAPE 4.77% α=0.75 Month 1 2 3 4 5 6 7 8 9 10 11 12 Forecast 447 466 469 492 485 492 512 553 588 574 592 599 447.00 461.25 467.06 485.77 485.19 490.30 506.57 541.39 576.35 574.59 587.65 Absolute Error 19.00 7.75 24.94 0.77 6.81 21.70 46.43 46.61 2.35 17.41 11.35 MAD 18.65 Abspolute Percentage Error 4.08% 1.65% 5.07% 0.16% 1.38% 4.24% 8.40% 7.93% 0.41% 2.94% 1.90% MAPE 3.47% α=0.75 performs the best as its forecast has the smallest MAD and MAPE among three α values. Therefore, an alpha value of 0.75 is the most appropriate. 4 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 Problem 3 a. Use double (trend-adjusted) exponential smoothing method to forecast sales for months 2 to 13, assuming S1=447 and T1=6 and set α=0.75 and β=0.5. Month Sales 1 2 3 4 5 6 7 8 9 10 11 12 13 447 466 469 492 485 492 512 553 588 574 592 599 Forecast Level 447.00 462.75 470.16 488.82 489.43 493.17 508.67 544.54 582.93 583.93 593.96 600.98 Forecast Trend 6.00 10.88 9.14 13.90 7.26 5.50 10.50 23.19 30.79 15.89 12.96 9.99 Forecast 447 453.00 473.63 479.30 502.73 496.69 498.67 519.16 567.73 613.72 599.82 606.92 610.97 b. Create a plot showing the actual sales, the double exponential smoothing forecasts, and the forecasts obtained using simple exponential smoothing in Problem 2 with α=0.75 for months 1 to 12 on the same graph. 5 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 c. Compute MAD and MAPE for months 2 through 12 for the double exponential smoothing forecasts and compare them with those of simple exponential smoothing in problem 2(c). Based on your calculations, is single or double exponential smoothing a better approach in this specific case? Why? Month Sales 1 2 3 4 5 6 7 8 9 10 11 12 447 466 469 492 485 492 512 553 588 574 592 599 Forecast (Double) 453.00 473.63 479.30 502.73 496.69 498.67 519.16 567.73 613.72 599.82 606.92 610.97 Absolute Error 13.00 4.63 12.70 17.73 4.69 13.33 33.84 20.27 39.72 7.82 7.92 MAD 15.97 Abspolute Percentage Error 2.79% 0.99% 2.58% 3.66% 0.95% 2.60% 6.12% 3.45% 6.92% 1.32% 1.32% MAPE 2.97% Recall that MAD and MAPE for single exponential smoothing method are 18.65 and 3.47% respectively. Therefore double exponential with α=0.75 and β=0.5 performs better since it has a lower MAD and MAPE. Because the underlying trend in the data, double exponential smoothing captures the trend better than single exponential smoothing method. 6 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 d. Use simple linear regression model with month being the independent variable to find a sales forecast for month 13. Compare this new forecast to the forecast for month 13 that you calculated in part (a). Using the regression analysis in Excel, we obtain: Intercept=425.939 Slope=14.842 Regression forecast for moth 13 =425.93+ 14.842 * 13 = 618.89 Month 1 2 3 4 5 6 7 8 9 10 11 12 13 Sales 447 466 469 492 485 492 512 553 588 574 592 599 Forecast 0.00 453.00 473.63 479.30 502.73 496.69 498.67 519.16 567.73 613.72 599.82 606.92 610.97 Regression 440.78 455.62 470.47 485.31 500.15 515.00 529.84 544.68 559.52 574.37 589.21 604.05 618.89 Comment (Not required for credit): The difference between 2 forecast methods can be explained by the fact that regression use a single estimate of trend while exponential smoothing adjusts the trend based on the change of data from period to period. 7 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 Problem 4 a. Determine the slope and the intercept of the least square regression line starting with the 1st quarter of 2004 as period 1 and ending with the 4th quarter of 2007 as period 16. (For example, 2nd quarter of 2005 will be period 6). Quarter Period Sales 2004 Q1 1 379.9 2004 Q2 2 352.3 2004 Q3 3 306.3 2004 Q4 4 353.2 2005 Q1 5 363.5 2005 Q2 6 369.5 2005 Q3 7 333.4 2005 Q4 8 339.7 2006 Q1 9 323.2 2006 Q2 10 300.7 2006 Q3 11 329.9 2006 Q4 12 308.2 2007 Q1 13 451.3 2007 Q2 14 345.4 2007 Q3 15 369.5 2007 Q4 16 330.4 Using the regression analysis in Excel, we obtain the regression line with intercept=346.10 and slope=0.14 8 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 b. Use the regression results to calculate the seasonal index for each quarter of each year. Calculate the indices for all sixteen quarters and then the average seasonal index for each quarter. Regression Forecast = Intercept + Slope * Period Period 2004 Q1 2004 Q2 2004 Q3 2004 Q4 2005 Q1 2005 Q2 2005 Q3 2005 Q4 2006 Q1 2006 Q2 2006 Q3 2006 Q4 2007 Q1 2007 Q2 2007 Q3 2007 Q4 Actual 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 379.9 352.3 306.3 353.2 363.5 369.5 333.4 339.7 323.2 300.7 329.9 308.2 451.3 345.4 369.5 330.4 Regression Forecast 346.23 346.37 346.51 346.65 346.79 346.93 347.07 347.21 347.34 347.48 347.62 347.76 347.90 348.04 348.18 348.32 Index=Actual/Forecast 1.10 1.02 0.88 1.02 1.05 1.07 0.96 0.98 0.93 0.87 0.95 0.89 1.30 0.99 1.06 0.95 2004 Q1 Q2 Q3 Q4 2005 2006 2007 Average 1.10 1.02 0.88 1.02 1.05 1.07 0.96 0.98 0.93 0.87 0.95 0.89 1.30 0.99 1.06 0.95 1.09 0.98 0.96 0.96 9 BUSI 403 Problem Set A Fall 2008 Due: 09/02/08 c. Using the regression line from part (a) and the seasonal indices from part (b), derive a forecast for each quarter of the year 2008. Final Forecast = Regression Forecast * Average Seasonal Index = (Intercept + Slope * Period) * Average Seasonal Index Period 2008 Q1 2008 Q2 2008 Q3 2008 Q4 17 18 19 20 Regression Forecast 348.455 348.59382 348.73265 348.87147 10 Final Forecast 380.96 343.36 336.08 334.23 ...
View Full Document

This document was uploaded on 11/04/2011 for the course BUSI 403 at UNC.

Ask a homework question - tutors are online