Would you please provide me with a step by step solution to solving this problem? Can you also show me how to do it step by step in Excel? I am not good at Excel. Thank you so much in advance for all your help!

MARRIOTT ROOMS FORECASTING

Linda Snow Snows responsibility to prepare a forecast on Tuesday afternoon of the number of rooms that would be occupied each day of the next week (Saturday through Friday).

Overbooking

Snow needed her forecast to know how to treat an opportunity that was developing for next Saturday. It was Tuesday, August 18, 1987, and Snows forecasts were due by midafternoon for Saturday, August 22 through Friday, August 28. Although 1,839 rooms were already reserved for Saturday, Snow had just received a request from a tour company for as many as 60 more rooms for that night. The tour company would take any number of rooms less than 60 that Snow would provide, but no more than 60. The request, in its entirety, put reservations above the capacity of the hotel. True, a reservation on the books Tuesday was not the same as a head in the bed on Saturday, especially when weekend nights produce a lot of no-show reservations. Chances are good we still wouldnt have a full house on Saturday.

Forecasting

Snow focused on getting a good forecast for Saturday, August 22, and making a decision on whether to accept the additional reservations for that day. She had historical data on demand for rooms in the hotel. Exhibit 1 shows demand for dates starting with Saturday, May 23, 1987. (Saturday, August 22, was the beginning of week 14 in this database.) Demand figures included the number of turned-down requests for a reservation on a night when the hotel had stopped taking reservations because of capacity, plus the number of rooms actually occupied that night. Also included in Exhibit 1 is the number of rooms booked as of the Tuesday morning of the week prior to each date. (Note that this Tuesday precedes a date by a number of days that depends on the dates day of week. It is four days ahead of a Saturday date; seven days ahead of a Tuesday; ten days ahead of a Friday. Also, note that on a Tuesday morning, actual demand is known for Monday night, but not for Tuesday night.)

Snow had calculated pickup ratios for each date where actual demand was known in Exhibit 1. Between a Tuesday one week ahead and any date, new reservations were added, reservations were cancelled, some reservations were extended to more nights, some were shortened, and some resulted in no-shows. The net effect was a final demand that might be larger than Tuesday bookings (a pickup ratio greater than 1.0) or smaller than Tuesday bookings (a pickup ratio less than 1.0). Snow looked at her forecasting task as one of predicting the pickup ratio. With a good forecast of pickup ratio, she could simply multiply by Tuesday bookings to obtain a forecast of demand.

Snow was aware that the day of the week (DOW) made a lot of difference in the demand for rooms, it was key in forecasting pickup ratios. Downtown business hotels like hers tended to be busiest in the middle of the workweek (Tuesday, Wednesday, and Thursday) and light on the weekends. Using the data in Exhibit 1, she had calculated a DOW index for the pickup ratio during each day of the week, which is shown in the last column of Exhibit 1. Thus, for example, the average pickup ratio for Saturday is about 86.5% of the average pickup ratio for all days of the week. Her plan was to adjust the data for this DOW effect by dividing each pickup ratio by that factor. This adjustment would take out the DOW effect, and put the pickup ratios on the same footing. Then she could use the stream of adjusted pickup ratios to forecast Saturdays adjusted pickup ratio. Once she had the forecast of adjusted pickup ratio, then she could multiply it by the Saturday DOW index to get back to an unadjusted pickup ratio.

1. How should Linda Snow develop a forecast on Tuesday for each days demand from the coming Saturday through Friday? What procedure / method should she use?
2. Using the procedure and guidelines you recommended in (2) develop a forecast using data in Exhibit 1 (see Excel file given below) for the coming Saturday. How accurate is your forecasting procedure (in other words how much can the actual demand deviate from your forecast)? Hint: Though not strictly required, advanced students should consider trying more than one procedure and selecting the best one based on some performance metric.
3. For your forecasting procedure, described in (1) and used in (2), please comment on:
   • Should we use the pick-up ratio in developing our forecast or should we just use the actual demand?
   • Is there seasonality in the data? If yes, how does your procedure handle seasonality?