• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF-Then Help

SheilaAnn

New Member
Hi, long time lurker, first time poster in need of guidance. I need to calculate bonuses based on percentage of occupancy.

If occupancy is 30% - 50%, a 10% bonus is earned.
If occupancy is 50% - 65%, a 12% bonus is earned.
If occupancy is 65% - 80%, a 15% bonus is earned.
If occupancy is 80% - 90%, an 18% bonus is earned.
If occupancy is 90% - 100%, a 20% bonus is earned.

And at each level, the bonus is compounded. Clear as mud, huh? Any help would be immeasurably appreciated!!
 
First, can you elaborate what "bonus is compounded" means? Also, should we assume each level given follows pattern of ">=30% and <50%"?
 
here is the long version:

Incubator kitchens are to be rented/licensed on an hourly basis and are available 24 hours per day, 7 days per week (except for hours of closure of 4th Street Market for special events, holidays and the like). To the extent aggregate paid usage revenues of the ten (10) incubator kitchen facilities calculated for a particular month exceed the revenues that would be generated from 30% usage of aggregate available usage hours, calculated based on a 30-day month (but excluding hours not available due to closure) and utilizing the average hourly rate for peak and non-peak hours that month (“Threshold Revenues”), Manager shall be entitled to receive the following percentages of gross revenues received in excess of Threshold Revenues:


Excess Revenues Range Percentage


(i) In excess of 30% usage to 50% usage 10.0%

(ii) In excess of 50% usage to 65% usage 12.0%

(iii) In excess of 65% usage to 80% usage 15.0%

(iv) In excess of 80% usage to 90% usage 18.0%

(v) In excess of 90% usage to 100% usage 20.0%


By way of illustration, if the average hourly rate for usage hours was $26, such that 30% usage at such rate would produce Threshold Revenues of $56,160 per month [10 kitchens x $26/hr. x 24 hrs. x 30 days = $187,200 x 30% = $56,160], and aggregate actual monthly revenue totaled $145,000 (representing 77.46% usage), Manager’s total monthly incentive compensation (in addition to base compensation of $6,000) would equal $10,611.60, calculated as follows:


x. Under clause (i) above, Manager would receive $3,744 [50% usage revenues of $93,600 less 30% Threshold Revenues of $56,160 = $37,440 excess x 10% incentive rate = $3,744];

y. Under clause (ii) above, Manager would receive an additional $3,628 [65% usage revenues of $121,680 in excess of 50% usage revenues of $93,600 as to which incentive paid under subparagraph (x) = $28,080 additional excess revenues x 12% incentive rate = $3,369.60]; and

z. Under clause (iii) above, Manager would receive an additional $3,498 [77.46% usage revenues of $145,000 in excess of 65% usage revenues of $121,680 as to which incentive paid under subparagraphs (x) and (y) = $23,320 additional excess revenues x 15% incentive rate = $3,498]


Additionally, if monthly usage reached 90% under the above illustration, for example, Manager’s incentive compensation would total $14,695.20. For purposes of calculating incentive compensation, gross revenues are determined prior to any deduction for credit card processing fees.
 
Ah, gotcha! It's a lot like the U.S. tax system, with different rates on different levels, but only for that portion (as if that makes any sense :rolleyes:)
Anyway, the good news is that a variable rate problem occurs in several things, so we've got a solution. Check out the answer here:
http://www.mcgimpsey.com/excel/variablerate.html
Note the table setup, including the differential rate.
 
Back
Top