Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Hotel Excel Spreadsheet Exercise 7 5 p 343 Consider the hotel occupancy by nxh20843

VIEWS: 456 PAGES: 24

Hotel Excel Spreadsheet document sample

More Info
									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

								
To top