Excel: bonus disqualifiers for excess vacation days


hope all is well everyone. may i seek your help for coming up with a template for computing for disqualifiers for a certain bonus given the following conditions (that sadly i cannot convert to formula): folks are given bonus for completing work days in a month; however, due to being understaffed, taking vacation leaves that go beyond 10% of work days in a month is discouraged. base bonus amount = 2,903.336 (subject to change monthly but is fixed for this specific example) # of worked days in a month = 21 (other possible values of days worked: 19, 20, 22, 23) 10% of days worked 19 = 1.9 20 = 2 21 = 2.1 22 = 2.2 23 = 2.3 # days in excess of 10% of work days will incur increasing deductions 1 day = less 25% of base amount 2 days = less 50% of base amount 3 days = less 75% of base amount 4 days = less 100% of base amount for example, month of june has 21 work days. an employee with exactly 2 (to at most 2.1) days approved & plotted vacation, will be rewarded 100% of base bonus amount whereas an employee with who filed for 4 days of leaves (4/21 = 19% has .9 excess days is definitely more than 10% of 21 work days of the month), will still be rewarded bonus but 50% less of base bonus amount compared to the other employee in prior example. kindly see sample table below. for results shown in the attachment, these were just manually computed & referenced to tables. any approach or presentation to make it even leaner/better, will be greatly appreciated.

A B C D E F G
A1 base bonus amount 2,903.336
A2
A3 # of work days 10% excess days deduction amount total
A4 19 1.9 1 -25% -725.834 2,177.502
A5 20 2 2 -50% -1451.668 1,451.668
A6 21 2.1 3 -75% -2177.502 725.834
A7 22 2.2 4 -100% -2903.336 0.000
A8 23 2.3
A9
A10
A11 Agent Leave(s) bonus
A12 Agent 01 0 2,903.336
A13 Agent 02 0 2,903.336
A14 Agent 03 2 2,903.336
A15 Agent 04 6 0
A16 Agent 05 1 2,903.336
A17 Agent 06 4 1451.668
A18 Agent 07 4 1451.668
A19 Agent 08 3 2177.502
A20 Agent 09 3 2177.502
A21 Agent 10 5 725.834
looking forward to your prompt help. cheers.


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of June 15, 2014.


For more resources for Microsoft Excel