 • 547K Members
• 2,850 Online
• 652K Conversations
SOLVED

Highlighted

# Calculate a percentage increase that doesn't exceed 100%

Hello everyone,

I am currently working on an occupancy rate up model. Here is the scenario

I have a hotel with occupancy rate of 70% and ramp up 6 months

I need to calculate the EOM occupancy rate and average occupancy rate

EOM occupancy rate = 70%/6 = 0..7/6 = 0.1167 = 11.67%

while the average rate = (previous EOM occupancy rate + current EOM occupancy rate)/2

For a 10 year plan, with the above function, the values will exceed 100% and in reality an hotel won't have 150% occupancy rate.

I need to modify the function so that when EOM occupancy rate reaches 95%, it remains there. It won't exceed 100% even the plan is 100 years. Thank you

7 Replies
Highlighted
Solution

# Re: Calculate a percentage increase that doesn't exceed 100%

@AKP_1 , perhaps

`=MIN(<EOM formula>, 0.95)`

Highlighted

# Re: Calculate a percentage increase that doesn't exceed 100%

Based on your formula, the EOM occupancy rate will be limited to a maximum of 16.67%, which is 100%/6.
Highlighted

# Re: Calculate a percentage increase that doesn't exceed 100%

Yes however there is an provision for business analysts to change the occupancy rate as well as occupancy ramp values...

Do you have any suggestion regarding that?

Highlighted

# Re: Calculate a percentage increase that doesn't exceed 100%

Thank you for your response. This looks okay

Highlighted

# Re: Calculate a percentage increase that doesn't exceed 100%

What are the maximum values for each of those 2 assumptions? Regardless of what they are, the EOM occupancy rate will always be less than 100%.
Highlighted

# Re: Calculate a percentage increase that doesn't exceed 100%

Thank you for your response. I design a model for hotel occupancy.

The minimum value of occupancy rate is usually 50-70% while the ramp up is usually 6 months.

I have a challenge with this formula

If I put the value as occupancy rate = 50%, the forecast rate stops at 50% ... The proposed design is to take 100% then =MIN(D13, 1) keeps it at 100%. I attached a file for more clarity.

Regards

Highlighted