Forum Discussion
Help with excel formula please
So to clarify further...
Employee X gets a monthly set target (Cell B9)
We will then at month end enter their actual sales figure (cell C9)
This will then autocalculate any variance between target vs achieved in cell D9
All employees who hit their Rev sales target then gets 5% base bonus of their ACTUAL sales achieved
they then can earn even more £££, dependent on achieving 4 key separate KPI's namely: NEW, 45%>unlimited mix, 2nd line attachment, HBB
However we obviously want them to strive to achieve these key company KPI targets, and then penalised financially for failing to achieve,
So in terms of rewarding them for achieving any of the 4 KPI's we do this as below:
-IF they hit any of the KPI's they will then get 5% of the variance over their target vs actual sales, namely cell D9,
-IF they nearly achieve a KPI and only get 95-99.9% of then they will lose 2.5% of their calculated TOTAL bonus (cell L9)
-IF they only achieve 90-94.9% of a KPI, they will lose 5% of their TOTAL bonus (cell L9)
-IF they achieve anything less than < 90%, then this will result in 10% being removed from their TOTAL bonus (Cell L9)
-Notably though whilst their 'HBB' Kpi is worth the same 5% of any variance over target in cell (D9) IF achieved, HOWEVER, for this one anything less than < 100% for this HBB KPI will result in 50% of their TOTAL bonus being removed (cell L9)
Hopefully this is more succinct, thanks!
I've asked several times for you to work through several examples. You have yet to do so. Doing so would help put flesh on the bones of your description of the rules to be applied. Again, as I said the last time, I'm NOT asking that you resolve it in Excel; that's what you're asking us to do. I am asking you to work through several examples as if you were illustrating the process on a white board. Several examples. Showing your work (as our mathematics teachers in high school might have said). That is, show the calculations and how they follow upon one another toward the final result.
You've already acknowledged that you and a colleague had different interpretations of what you're being asked to do. That's why it's important to do all we can to remove any remaining ambiguities. Actually working through the application of these rules, applying several different ratings, would help do that.
My intention here is not to be difficult. I'm not sure what I can do to be more succinct with my request, which I think is entirely reasonable.
Look forget it!!!
If you insist.
I'm disappointed, because it would have been satisfying to work it through with you. I wish you luck in resolving it yourself. I will say, I expect the formulas are pretty simple once it's clear what they're to do. So since it's so clear in your own mind, you should have no trouble. Best wishes.
- Look forget it!!!