VIEWS: 456 PAGES: 24 CATEGORY: Business POSTED ON: 11/30/2010
Hotel Excel Spreadsheet document sample
Exercise 7.5 (p. 343) Consider the hotel occupancy data in Table 6.4 of Chapter 6 (p. 297) t yt t yt t yt t yt t yt t yt t yt 1 501 25 555 49 585 73 645 97 665 121 723 145 748 2 488 26 523 50 553 74 593 98 626 122 655 146 731 3 504 27 532 51 576 75 617 99 649 123 658 147 748 4 578 28 623 52 665 76 686 100 740 124 761 148 827 5 545 29 598 53 656 77 679 101 729 125 768 149 788 6 632 30 683 54 720 78 773 102 824 126 885 150 937 7 728 31 774 55 826 79 906 103 937 127 1067 151 1076 8 725 32 780 56 838 80 934 104 994 128 1038 152 1125 9 585 33 609 57 652 81 713 105 781 129 812 153 840 10 542 34 604 58 661 82 710 106 759 130 790 154 864 11 480 35 531 59 584 83 600 107 643 131 692 155 717 12 530 36 592 60 644 84 676 108 728 132 782 156 813 13 518 37 578 61 623 85 645 109 691 133 758 157 811 14 489 38 543 62 553 86 602 110 649 134 709 158 732 15 528 39 565 63 599 87 601 111 656 135 715 159 745 16 599 40 648 64 657 88 709 112 735 136 788 160 844 17 572 41 615 65 680 89 706 113 748 137 794 161 833 18 659 42 697 66 759 90 817 114 837 138 893 162 935 19 739 43 785 67 878 91 930 115 995 139 1046 163 1110 20 758 44 830 68 881 92 983 116 1040 140 1075 164 1124 21 602 45 645 69 705 93 745 117 809 141 812 165 868 22 587 46 643 70 684 94 735 118 793 142 822 166 860 23 497 47 551 71 577 95 620 119 692 143 714 167 762 24 558 48 606 72 656 96 698 120 763 144 802 168 877 a. Analyze this data using the multiplicative decomposition method in an Excel spreadsheet. The book contains a CD-ROM with the data sets in different data formats. Note! Data are given consecutively in column 1 (not tabled as in Table 6.4 of the book) Add a column with the time variable (month) In column C, add the formulas for calculating CMAt Monthly data Formula can be entered in cells C8:C163 (Row 1 is reserved for column labels) yt 6 2 yt 5 yt 4 yt yt 5 yt 6 CMAt 2 12 Enter the following formula in cell C8 and copy it to cells C9:C163 “=(B2+2*(B3+B4+B5+B6+B7+B8+B9+B10+B11+B12+B13)+B14)/24” Next step: Divide yt with CMAt for t = 7,…,162 (Spreadsheet formula is entered into cells D8:D163) This gives the rough seasonal components (y / CMA) Now we need to estimate the seasonal component, i.e. the 12 seasonal indices Average the rough seasonal components for each calendar month Cell E8 corresponds with calendar month 7, i.e. July Enter the following formula in E8 “=(D8+D20+D32+D44+D56+D68+D80+D92+D104+D116+D128+D140+ D152)/13” Copy the formula to cells E9:E17 (August-April). For the cells E18:E19 (May and June), remove the last term in the sum of the formula and divide by 12. The contents of cells E8:E19 are now close to the seasonal components. Fine adjust them by dividing with their average. In cell F8 enter the following formula =E8/(AVERAGE($E$8:$E$19)) and copy this to the cells F9:F19 Now, the seasonal components shall be “copied” to the rest of the relevant cells in column F. To make it more dynamic (allowing original observations to be changed) use formulas depending on the values calculated in F8:F19 In Cell F2, enter the formula =F14 (as month 1 is the same calendar month as month 13) In cell F3, enter formula =F15 and so on until cell F25 For the cells F26:F37 enter successively formulas =$F$2, =$F$3, =$F$4, =$F$5, =$F$6, =$F$7, =$F$8, =$F$9, =$F$10, =$F$11, =$F$12, =$F$13 The copy and paste these 12 cells further in the column Now, deseasonalize the original observations by dividing them with the corresponding seasonal component In cell G2 enter =B2/F2 , copy and paste into all relevant cells of column G. From deseasonalized data, the trend function can be estimated. If a linear trend is to be found, we simply use columns A and G, if a quadratic trend is to be found a new column with squares of the values in A must be created. Stick to the linear case here. In Excel, open the menu Tools (open it while a cell in the spreadsheet is active, not e.g. a chart) Select the alternative Data Analysis… If that alternative does not exist, select the alternative Add-Ins… and check the box for the Analysis ToolPak add-in, then Data Analysis will appear on the Tools menu. Scroll down to the alternative Regression and select that one. Enter the cells with the deseasonalized data here, i.e. cells G1:G169 (including column label) Enter the cells with the time variable(s) here, in our case cells A1:A169 (including column label) Check this box as labels are present (otherwise you may enter the ranges G2:G169 and A2:A169 in the Input Y and X Ranges above) Enter a cell here that does not interfere with your previously entered columns or your prospectively entered columns (trend column, cyclical component etc.) Preferably: Enter a cell under your data columns, i.e. G171 Click OK, look at cell G171 and below! Standard regression output. What we need here are the contents of cells H187 (intercept) and H188 (slope parameter). Column H should now contain the estimated trend component. In cell H2 enter the formula =$H$187+$H$188*A2 (tr1 = 554.0933 + 2.003414 t ) Copy and paste into all relevant cells in column H Next step is to filter out the combined cyclical and irregular component. This should be stored in column I. (cl ir)t = yt* / trt Enter the formula =G2/H2 in cell I2. Copy and paste into all relevant cells in column I. To separate between the cyclical and the irregular component, moving averages need to be calculated. The moving averages constitute cl and the ratio between (cl ir) and cl constitutes ir Excel has this function among the Data Analysis methods, but it is not consistent with the description in this course. Only 3-point moving averages are centred, the rest are skewed. To make 3-point centred moving averages, enter the following formula into cell J3: =average(I2:I4) Copy and paste into cells J4:J168 (Cell J169 like cell J2 will not have any value) Then enter the formula =I3/J3 into cell K3 , copy and paste into cells K4:K168 to produce ir. To make 5-point centred moving averages, enter the following formula into cell J4: =average(I2:I6) Copy and paste into cells L5:L167, calculate ir (=I4/L4) in cells M4:M167 Etc. In this case, calculate 3-, 5- 7- and 9-point moving averages. To judge upon which of the moving averages that works best, calculate the standard deviation and the serial correlation coefficient for all estimated irregular components. Use e.g. rows 171 and 172 for these outputs. In cell K171 enter the formula =stdev(K2:K169) It doesn’t matter for this calculation that some cells are empty. Excel only calculates with active cells. This formula can therefore be copied to cells M171, O171 and Q171 In cell K172 enter the formula =correl(K2:K168,K3:K169) This calculates the correlation coefficient between the series of ir terms and the one- step lagged series of ir terms. Note that be benefit of that cell K169 is empty. Empty cells are treated as missing values in the Excel calculations. Copy and paste the formula into the cells M172, O172 and Q172. There are more empty cells in these columns, but that does not affect the result. Now we can see that the 3-point moving average gives the lowest standard deviation (0.042967) of the ir terms and the 5-point moving average gives the lowest serial correlation (in absolute value). To help in the decision process, it could be wise to plot (cl ir) together with cl for the two cases (use the “line” chart type in the Chart Wizard)). 3-point 1.3 1.2 1.1 1 cl x ir 0.9 cl(3) 0.8 0.7 0.6 1 13 25 37 49 61 73 85 97 109 121 133 145 157 5-point 1.3 1.2 1.1 Gives a smoother impression 1 cl x ir 0.9 cl(5) 0.8 0.7 0.6 1 13 25 37 49 61 73 85 97 109 121 133 145 157 b. Use statistical software, such as SAS or MINITAB, to produce point forecasts and 95% prediction intervals for the deseasonalized hotel room averages in each month the 15th year. Copy the column of deseasonalized values (G) together with the time column (A) to the software (here to MINITAB). Perform a regression of deseasonalized values against time and ask for prediction in the 15 added time points. c. Using the values from part (b), compute point and 95% prediction interval forecasts of the hotel room averages in each month of the 15th year. See page 339 in the textbook. The same error bound can be used in the prediction interval for y as those previously used for y* . In the Minitab worksheet, calculate the bounds as the differences between column C6 (PLIM2) and C4 (PFIT1) and the differences between C5 (PLIM1) and C4. Commands that can be used in Session Window if not Calculator is used Now, the forecasts for y (hotel room averages) can be formed by multiplying the forecasted deseasonalized values (in column C4) with the estimated seasonal components from the Excel spreadsheet. Thus copy the first 12 seasonal components to MINITAB and then add the bounds to these forecasts. Forecasts Lower 95% Pred.limits Upper 95% Pred. Limits