A Management Report ?There are three things that I wish I had to make my decision easier. First, I?d like to have a summary report that shows what the composition of the next weeks occupancy is ? how full we are by unit type and where those reservations came from ? and what the present composition of the following week?s occupancy is. If I had that I could at least understand what the big picture is, rather than having to work that out from the reservation system report. To be clear, next week is February 13th and the following week is February 20th.? ?The other thing I?d like is a management decision tool that would show me three scenarios, based on the available options I have to sell the remaining inventory for the week in question. What would my revenue be for each scenario and based on cost what is my forecast profitability.? ?Building on that, what I would really like is a spreadsheet application that could take the available capacity I have to work with, solve for me the best solution that maximizes my profitability and present to me a recommended solution based on what we could do with the available rooms. While a computer won?t know the different variables that a given week might have, the combination of the three views I?ve described would sure help me feel more confident about the decisions I make on any give Tuesday.? ?If I had a spreadsheet application like that, at least I wouldn?t feel this decision was gambling rather than business. Can you help me with something like that??

02-20-2010 1A 1A 1A 1A 1A 1A 1A 1A 1A 1A 1A 1A 1A 1A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 2A 1B 1B 1B 1B 1B 1B 1B 1B 1B 1B 1B 1B 1B 1B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2B 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 1N 2N 3N 4N 5Y 6N 7Y 8Y 9N 10 N 11 N 12 Y 13 Y 14 N 15 Y 16 N 17 N 18 N 19 Y 20 N 21 N 22 N 23 N 24 Y 25 Y 26 N 27 N 28 Y 29 N 30 N 31 N 32 Y 33 N 34 N 35 Y 36 N 37 N 38 N 39 N 40 N 41 N 42 N 43 N 44 N 45 N 46 N 47 N 48 Y 49 Y 50 N 51 Y 52 N 53 N 54 N 55 N 56 N 57 Y 58 N 59 Y 60 N 61 Y 62 N 63 Y 64 N 65 N 66 N 67 Y 68 N 69 Y 70 N 71 N 72 Y 73 N 74 N 75 N 76 N 77 N 78 N 79 Y 80 N 81 N 82 N 83 Y 84 N 85 N 86 N 87 Y 88 N 89 N 90 Y 91 N 92 N 93 N 94 N 95 Y 96 N 97 Y 98 N 0 0 0 0 2 0 1 2 0 0 0 2 2 0 2 0 0 0 2 0 0 0 0 2 2 0 0 2 0 0 0 2 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 2 2 0 1 0 0 0 0 0 2 0 2 0 2 0 2 0 0 0 2 0 2 0 0 2 0 0 0 0 0 0 2 0 0 0 2 0 0 0 2 0 0 1 0 0 0 0 2 0 1 0 0 0 0 0 2 0 0 1 0 0 0 1 0 0 1 0 0 0 2 0 0 0 0 2 0 0 0 2 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 2 0 0 0 0 0 2 0 1 0 0 0 2 0 0 0 0 0 1 0 0 2 0 0 0 0 0 0 2 0 0 0 0 0 0 0 2 0 0 2 0 0 0 0 1 0 1 0 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 1 02-13-2010 Room Type Room Number Reserved Number of Adults of Childer Number Source 1A 1N 0 0 1A 2N 0 0 1A 3Y 2 1 1A 4Y 2 0 1A 5Y 2 0 1A 6Y 2 0 1A 7Y 2 2 1A 8Y 1 2 1A 9Y 2 0 1A 10 N 0 0 1A 11 N 0 0 1A 12 Y 2 2 1A 13 Y 2 0 1A 14 Y 2 2 2A 15 Y 2 0 2A 16 N 0 0 2A 17 Y 2 1 2A 18 Y 2 2 2A 19 Y 1 2 2A 20 Y 2 1 2A 21 Y 2 1 2A 22 Y 2 1 2A 23 N 0 0 2A 24 N 0 0 2A 25 N 0 0 2A 26 Y 2 0 2A 27 Y 2 2 2A 28 N 0 0 2A 29 Y 1 0 1B 30 N 0 0 1B 31 N 0 0 1B 32 Y 2 1 1B 33 Y 1 1 1B 34 Y 2 1 1B 35 Y 2 0 1B 36 N 0 0 1B 37 Y 2 0 1B 38 Y 2 0 1B 39 Y 2 1 1B 40 Y 2 2 1B 41 Y 2 2 1B 42 Y 2 0 1B 43 N 0 0 2B 44 N 0 0 2B 45 N 0 0 2B 46 N 0 0 2B 47 Y 2 2 2B 48 Y 2 1 2B 49 Y 2 2 2B 50 N 0 0 2B 51 Y 2 2 2B 52 Y 2 0 2B 53 Y 2 0 2B 54 N 0 0 2B 55 N 0 0 2B 56 Y 2 0 2B 57 Y 2 0 2B 58 N 0 0 2B 59 N 0 0 2B 60 Y 1 0 2B 61 Y 2 1 2B 62 Y 2 0 2B 63 N 0 0 2C 64 Y 2 0 2C 65 N 0 0 2C 66 Y 2 1 2C 67 N 0 0 2C 68 Y 2 1 2C 69 Y 2 0 2C 70 N 0 0 2C 71 Y 2 2 2C 72 N 0 0 2C 73 Y 2 2 2C 74 N 0 0 2C 75 Y 2 0 2C 76 N 0 0 2C 77 Y 2 1 2C 78 Y 2 1 2C 79 Y 2 1 2C 80 Y 2 2 2C 81 Y 1 2 2C 82 Y 1 2 2C 83 Y 2 2 2C 84 Y 2 2 2C 85 Y 2 0 2C 86 N 0 0 2C 87 N 0 0 2C 88 Y 2 0 2C 89 N 0 0 2C 90 Y 2 2 2C 91 Y 2 0 2C 92 Y 2 2 2C 93 Y 2 1 2C 94 Y 2 2 2C 95 Y 2 2 2C 96 N 0 0 2C 97 N 0 0 2C 98 N 0 0 1 1 1 2 1 1 1 1 1 1 1 1 2 2 1 1 1 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 2 2 1 1 1 1 1 1 2 2 1 1 1 1 2 2 1 1 1 2 1 1 1 1 1 1 1 1 1 2 1 1 1 1 1 1 2 1 1 1 1 1 1 1 2 1 2 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1=call center 2=Web site 3=wholesaler 4=package 5=discounted web Sunset Vacations Inc.1 Howard Johnston, founder and CEO of Sunset Vacations Inc., looked across his desk and let out a sigh. He looked exhausted and not a little bit frustrated as he removed his glasses and started to speak. \"It's Tuesday, the worst management day of my week\" he said. \"Today is the day I have to decide what t o do with our remaining occupancy for a week Saturday to ensure we operate with a full property. When I first came into this business, you made your profit from the last twenty percent of rentals, now you try not to lose your shirt to keep that last twenty percent filled. I blame the internet!\" Sunset Vacations Howard Johnston, then a 31 year old businessman, founded Sunset Vacations Inc. in 2001 with the purchase of a resort property in the Mexican Riviera. His business plan called for the creation of a company that would deliver an all-inclusive beach vacation to residents of the North East United States and Central and Eastern Canada, with a heavy focus on the 25 - 50 year old family demographic looking f or a 1 week holiday in the winter season. Howard felt he had several strategic differentiators that would support his business plan: Through a business associate, Howard was able to negotiate a fixed fee deal with a major air carrier to transport passengers from any of his target markets to his resort for a per person cost of $450 for adults and $250 per child; Howard had studied marketing in college and was a strong believer in the internet as a marketing channel. The internet was now the source of most of his reservations; Howard brought a good accounting background as well as an analytical mindset to his daily operations; and The last few winters had been particularly cold throughout his target market and this had allowed him to raise prices and build up a bit of a cash balance that he felt could sustain him t hrough a period of slower occupancy. The property itself was composed of a combination of 26 family villas on the beach and a tower complex t hat contained 70 suites and also housed the restaurants, bars, the spa and other entertainment f acilities. Howard knew from his analysis that the type of clientele each of his suites and villas attracted would spend different amounts of money on the premium services his resort offered. He also understood his fixed and variable costs - fixed costs were incurred whether the suite or villa was occupied or not, while variable costs were incurred only when the room was occupied. 1 This case has been prepared to provide material for class discussion; it is not intended to illustrate effective or ineffective handling of a managerial situation. Certain names and events may have been altered to protect confidentiality or to aid in illustration. _ x000C_ Howard priced his vacation packages in a straight forward fashion. Each type of unit had a cost for a week, which always ran Saturday to the following Saturday, and this cost was the same no matter how many people were staying in the unit (up to the maximum occupancy of the unit). Howard included in his room rate air fare for two adults and two children, such that the price quoted was the price of the vacation unless extra services were consumed by the family. Extra services included alcohol, spa services and meals in the specialized restaurants. Howard felt that extra services generated a lot a supplemental income and the bulk of the profitability for Sunset. The fixed costs / unit, was a standard $1,000 regardless of the type of unit. Variable costs, which included resort staff, housekeeping, consumables such as soap and towels, were broken out as follows: Reservation System Sunset Vacations had a fairly simple reservation system with two methods of inputting weekly reservation. Agents, who worked in a call center answering phones from customers and travel agents, could access t he reservation system through computer screens. They could look up available rooms for a given week and see a grid of filled and empty rooms. If a customer was interested in booking they could select an available room and input the appropriate information for the customer. Howard had invested in a web site that allowed customers to browse vacation packages and purchase f rom the site. The web site engine accessed the same screens as the call center agents, and to the reservation system it looked like a web reservation was created by a call center agent. _ x000C_ Each night a program ran on the reservation system to identify new reservation and book the appropriate airfares with the airline. The reservation system had limited reporting options and a single data extract routine that wrote out a comma separated file for a given week, showing the status of reservations and vacancies for that week. The file format was as follows: Line 1: 02-13-2010 Line 2 - 97: room reservation details The first line was the date of arrival for this weekly extract, while the remainder of the file had one line f or each of the units in the resort, made up of the following values: Room Type One of 1A, 1B, 2A, 2B or 2C Room Number All rooms are sequentially numbered starting at 1 and going through to 96 Reserved Y or N, for Yes or No Number of Adults Up to 2, no value if the room isn't reserved Number of Children 0 - 2. At present Sunset can't facilitate more than 2 children in a suite or villa Source The source of the reservation. 1 = call center, 2 = web site, 3 = wholesaler (see below), 4 = package (see below), 5 = discounted web (see below) Selling Excess Capacity Howard would often extract the data from his reservation system and ponder what he should do about occupancy in future weeks. In particular on Tuesday's he had to notify other parties that could sell units f or him of available capacity and release to those channels any rooms he wanted to sell to ensure his property was as full as possible at all times. While his call center and website generated the bulk of his sales, there were times he needed extra help to sell a given week, and he needed to release content a minimum of 10 days from arrival Saturday to these other channels so they could sell on his behalf. There were three main channels Howard sold excess capacity through, and they had different costs and probability of success associated with each of them. The most predictable channel for selling capacity was what was called a \"wholesaler\". Howard could approach a wholesaler with a set of rooms, of any type, and they would buy the available rooms at a steep discount and sell them (or not) at whatever price they could. Sunset received a fixed price per room, didn't have to arrange airfare for the guests and were paid if a guest occupied the room or not. In _ x000C_ analyzing the vacation packages sold through wholesalers, Sunset had found that 75% of what was purchased by the wholesalers resulted in families in the rooms on average and that guests who booked t hrough the wholesaler purchased fewer supplemental services. The wholesale values are: Sunset had experimented with specialty tour operators in the past, where they assemble a package to a particular clientele and sell from a member list. What was best about this type of sale was the premium attached to the package. By selling a specialized program, say \"food and fine wine club\