Question

Wilson and Sons Corp. has bought a prime parcel of beachfront property and plans to build a luxury hotel. After meeting with the architectural team, the Wilson family has drawn up some information to make preliminary plans for construction. Excluding the suites, which are not part of this decision, the hotel will have four kinds of rooms: beachfront non-smoking, beachfront smoking, lagoon view non-smoking, and lagoon view smoking. In order to decide how many of each of the four kinds of rooms to plan for, the Wilson family will consider the following information.

• After adjusting for expected occupancy, the average nightly revenue for a beachfront non-smoking room is $175. The average nightly revenue for a lagoon view non-smoking room is $130. Smokers will be charged an extra $15.
• Construction costs vary. The cost estimate for a lagoon view room is $12,000 and for a beachfront room is $15,000. Air purifying systems and additional smoke detectors and sprinklers add $3000 to the cost of any smoking room. Wilson and Sons Corp. has raised $6.3 million in construction guarantees for this portion of the building.
• There will be at least 100 but no more than 180 beachfront rooms.
• Design considerations require that the number of lagoon view rooms be at least 50% more than beachfront rooms but no more than 150% of beachfront rooms.
• Industry trends recommend that the number of non-smoking rooms be at least 50% more the number of smoking rooms.

Formulate a linear programming model to maximize revenue and solve in Excel.

Include in your answer the:

1. Model
2. Excel input
3. Answer report
4. Description of the optimal solution

Answer 1