SOLVED

Calculate a percentage increase that doesn't exceed 100%

Copper Contributor

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
best response confirmed by AKP_1 (Copper Contributor)
Solution

@AKP_1 , perhaps

=MIN(<EOM formula>, 0.95)

 

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

@Twifoo 

 

Thank you for your reply. Much appreciated!

 

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?

@Sergei Baklan 

 

Thank you for your response. This looks okay

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%.

@Twifoo 

 

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

1 best response

Accepted Solutions
best response confirmed by AKP_1 (Copper Contributor)
Solution

@AKP_1 , perhaps

=MIN(<EOM formula>, 0.95)

 

View solution in original post