Computer Lab 3
QBUS2820 Predictive Analytics Semester 2, 2014Computer Lab 3The purpose of this lab is to give you practice at smoothing and fitting decomposition models using Excel and SAS. We will continue to distinguish what tasks we can do in SAS and what is easier to do in Excel.PLEASE BRING ALL DATASETS AND PROGRAMS FOR LAB 3 ON BB TO YOUR LAB SESSION. YOU SHOULD TRY THESE TASKS BEFORE YOU ATTEND THE LAB SESSION.Task 1.The file hotel.xls contains monthly averages of the number of occupied rooms at Traveller’s Rest Hotel in the US from Jan, 1991 for the next 14 years. Open this file in Excel. You may do this in pairs if you like:A. Model choice1. Plot the data over time. [pic 1]I created an observation number in column A and a month indicator in row B. See lab 1 and 2 for how to do this.2. Smooth the data with an 2×4 MA smoother and plot the smoothed data.Use the formula in column D, row 4=1/8*(C2+C6)+1/4*SUM(C3:C5) then fill down until row 167. An MA4 leaves 2 observations blank at the start and end of the series. 3. Smooth the data with an MA(12). Use the formula=1/12*SUM(C3:C13)+1/24*(C2+C14)In column E, row 8 then fill down to row 162. An MA12 smoother leaves 6 blanks at start and end of the series.
[pic 2]4. Comment on the major features of the data in terms of the 4 components of time series.The MA12 shows a clear increasing trend that seems close to linear. The MA4 shows a clear repeating seasonal pattern of low occupancy numbers around Christmas and highest numbers in the middle of the year, especially July and August. Cycle and error components are hard to spot as yet.5. Which of the multiplicative or additive decomposition model seems the most appropriate? Explain your answer. The seasonal swing seems to increase as the trend increases. A multiplicative model seems the first choice for this data. B. ForecastingWe will now use the 1st 144 data points (12 years) to forecast the occupancy numbers in the final 2 years using:the Naïve modelthe multiplicative decomposition modelAny other model/method of YOUR choice. Follow the steps given in Lecture 3 to estimate each component for model B individually for the first 12 years. Assume that the seasonal indices are the same each year and that the trend is linear. Provide plots of each component separately.Copy the MA12 series into column F, up to row 145 only. In column G divide the data (C) by the MA12 series (F) to produce the Sε estimated series. In column H average each month’s Sε series to obtain an estimate of S, the seasonal factors. Use a formula like e.g. in row 2 (month 1) of column H =1/11*(G14+G26+G38+G50+G62+G74+G86+G98+G110+G122+G134) be careful with this formula for months 7 to 12. It will need changing and NOT simple filling down.