Solver Help...

[broncojared]

I have hit a block in progress and am hoping someone out there might have some good ideas on how to make my idea work.

Basically I am attempting to do a sensitivity analysis on my model to see the differing effects of the room counts of a hotel. The room sizes differ but are all priced based off a price per square foot model. So, excel believes the larger per sq ft model is the most advantageous to build. However, the goal of the hotel is to be a small space hotel. So that is where I run into problems. I want to look at differing room counts of the different sq footages and what mix would be most profitable. I can make restraints, and I have, but I still can't seem to get this to work...

Key criteria:

Room sq ft, room nightly rate per sq ft, room count, and room revenues.

For example,

350 sq ft room, nightly rental of $0.50/sq ft, with min of 10 of these available max of 20
400 sq ft room, nightly rental of $0.50/sq ft, with min of 5 of these available max of 15
450 sq ft room, nightly rental of $0.50/sq ft, with 1 of these min and 5 max

What would be a good method to test the best mix etc? I am at a loss.

 

[Eric W]

I could enter you data into a Solver model, but based on the information you provided, it would come back saying build the maximum number of rooms per size. You need a few more constraints.

Is there a total number of square feet available that must be split up amongst all the rooms? Also, do you have some kind of occupancy numbers? For example, on an average basis, you can sell 75% of the 350 sq.ft. rooms, and 45% of the 450 sq.ft. rooms. How about price sensitivity? For example, at $175 per room, you can sell 25 rooms. At $200 per room, you can sell 22 rooms, and so on. At the higher end, you might find that you can sell 4 rooms at $225 per room, or 4 rooms at $240 per room. (Possibly higher income people are slightly less sensitive to a $15 per room change.) Which would lead you to charge a bit more per sq. ft. for the larger rooms.

There are many, many possible parameters that can feed into this. Hotel location, number of nearby events, local economy, cost of labor (does it cost the same to clean the different sized rooms?), etc. Try to figure out your parameters, then you can build a model, and Excel can possibly be used to analyze it. However, that might be beyond the scope of this forum. Good luck.