May 4, 2009 at 04:31:42
Specs: Windows Vista
 I am trying to create a real time bonus calculator that I can use that builds bonus per sale. Here are the rules:I'm selling 2 products, call them Product A and Product BI have a target of 100 units of Product A and 50 units of Product B. Both products cost £10 each.My regular bonus for selling Product A is 4% of the total sales and for Product B it is 8%. I must reach at 60% sales of my targets for BOTH Product A and Product B to release any bonus.When I achieve 60% in both products, my bonus up to 80% for each will be 0.5 of my regular bonus, i.e 2% and 4%When I achieve 80% upwards in either, my bonus is 1.O of my regular bonus for the Product I am over 80% or both. i.eProduct A is 70% Product B is 90% - Product A paid at 0.5, Product B at 1.0. Product A is at 80% Product B is at 110%, both are paid at 1.0If both Product A and Product B achieve 100% of target, then they will be paid as follows:Up to 110% 1.5 times regualr bonus (6%/12%)Up to 120% 2 timesUp to 150% 2.5 timesOver 150% 3 timesSo Product A could be at 102% and Product B at 143% - Product A paid at 6% (1.5 times) and Product B at 20% (2.5 times).Is there a single formula that can capture these 'What If scenarios? If not how do I do it?Many Thanks