BA540 -- Problem Set 1 Problem 1.2 (Chapter 5): PART A: The following table gives restaurant sales data for the Timber Ridge ski lodge over a three year period. For the first part of this exercise, assume that the revenues are randomly distributed, with no trend, seasonal, or cyclical component. Year 2012 Quarter Month 1st Qtr Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sales Revenue (in 1,000s) 438 420 414 318 306 240 240 216 198 225 270 315 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 480 468 464 368 361 284 294 261 254 273 319 365 2nd Qtr 3rd Qtr 4th Qtr 2013 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Page 3 of 4 459 440 438 346 333 260 270 238 225 248 293 337 BA540 -- Problem Set 1 Note that you may want your spreadsheet to be laid out such that your columns of forecasted data are just to the right of the given data, with separate columns for each forecasting method and error measurement, such as: Year 2012 Quarter Month 1st Qtr Jan Feb Sales Revenue 438 420 3 QTR Moving Average Weighted Exponential Moving Smoothing Average MAD 3 QTR MA MAD WMA MAD Expo. Smoothing a. Use the formulas in Chapter 5 to develop a three-period moving average forecast and use it to forecast each month of the existing three year period. Use the model to predict revenues for January 2015. b. Use the formulas in Chapter 5 to develop a weighted three-period moving average forecast using weights of 0.60, 0.25, and 0.15 for the most recent, middle, and most distant periods, respectively. Use the model to calculate forecasts for each month of the existing three year period and then to predict revenues for January 2015. c. Use the formulas in Chapter 5 to develop an exponential smoothing model for the revenue data with = 0.25. Assume the starting forecast for January 2012 to be 438. Use the model to forecast each month of the existing three year period and then to predict revenues for January 2015. d. Compute MAD for each forecast method, and use it to compare the accuracy of each model (use Excel's ABS function to compute absolute values). To be fair, begin the analysis with the April 2012 forecasts. Which model produces the best overall results? Explain your reasoning. e. How would you extend the forecast through the entirety of 2015? Could you use any of the time series forecast methods to do this? Could you forecast 2016? Explain. PART B: For this part, you will need to develop a linear trend line model using the revenue data given in the table, above. In developing the trend line equation (aka: regression equation), it is preferred that you use Excel's SLOPE function to find b1 and the INTERCEPT function to find b0. You can find more on these function using Excel's Help feature, or simply type =SLOPE( or =INTERCEPT( into a cell, and then click on the fx button on the formula bar to bring up the Function Arguments dialog box. NOTE: In order for the functions to work, you will have to convert the text-based periods (Jan, Feb, Mar....Oct, Nov, Dec) into numeric values (1, 2, 3, ....34, 35, 36). It is also recommended that you use a second spreadsheet, beginning with the layout introduced in presenting the revenue data for Part A, above. f. Graph the demand data. Describe any trends, cycles, and/or seasonal patterns that you see in the plot. g. Develop a linear trend line equation using the revenue data for the three years. Use the equation to calculate forecasts for each month of the existing three year period and then to predict revenues for January 2015. h. Calculate a seasonal index with no trend using the given revenue data. It is recommended that you develop a spreadsheet that resembles Table 5.8 on page 168 in the Quantitative Analysis textbook to do this. You can place it in the same workbook as the spreadsheet for part e. i. j. Use the Average Seasonal Indices you calculated in part h to adjust the linear trend line forecast done in part g , above. Calculate mean squared error (MSE) for both forecasts, using the formula from Chapter 5. Interpret the results. k. Are any of the methods used for Problem 1-2, Parts A & B viable? Discuss how you might improve forecast accuracy, including comments on developing a trend adjustment for this data. You are on the right track if MSE for the LTL model is 6808.8 and the Average Seasonal Index for January is 1.42. Page 4 of 4