Question

The Bayside Fountain Hotel is adjacent to County Coliseum, a 24,000?seat arena that is home to the city’s professional basketball and ice hockey teams and that hosts a variety of concerts, trade shows, and conventions throughout the year. The hotel has experienced the following occupancy rates for the past 9 years, since the coliseum opened:

1..............83

2..............78

3..............75

4..............81

5..............86

6..............85

7..............89
8...............90
9...............86

a.Use Excel and POM?QM to compute an exponential smoothing forecast with ? = .20, an adjusted exponential smoothing forecast with ? = .20 and ? = .20. Print your results.                                                  b.Use POM?QM to compute a linear trend line forecast. Print your results. Use the linear function obtained from POM?QM to manually compute forecast for year 11 through 13.                                          c.Use MAD to compare three forecasts and indicate which seems to be most accurate.

Answer 1

1) Exponential smoothing forecast and MAD

F_t = F_t-1 + ?*(A_t-1 - F_t-1), where Ft is the forecast for year t and At is the actual occupancy rate for year t.

MADt = |At - Ft|

MAD = SUM(|At - Ft|)/n , where n is the number of forecasts.

Formula:

C3 =C2+(B2-C2)*0.2   copy to C3:C10

D3 =ABS(B3-C3)   copy to D3:D10

D13 =AVERAGE(D3:D10)

2) Adjusted Exponential smoothing forecast.

Formulas:

C8 =E7+$F$3*(B7-E7)

D8 =$F$4*(C8-C7)+(1-$F$4)*D7

E8 =C8+D8

F9 =ABS(B9-E9)

Copy these formulas upto year 9 (row 15) of excel sheet

F17 =AVERAGE(F9:F15)  

3) Linear trend line forecast.

4)

Forecast for year 11 = 77.02+1.33*11 = 91.65

Forecast for year 12 = 77.02+1.33*11 = 92.98

Forecast for year 13 = 77.02+1.33*13 = 94.31

c) Using MAD to compare,

MAD of Exponential smoothing forecast model = 4.36

MAD of Adjusted Exponential smoothing forecast model = 4.71

MAD of Linear trend line forecast model = 2.67

MAD of linear trend line model is the lowest. Therefore, it is the most accurate model.