Forum Discussion
Help with excel formula please
mathetes So, let me make myself more clearer to prevent any further confusion or ambiguity.
Following a detailed discussion between myself and a colleague we both had interpreted this differently, we have now mutually agreed what the end goal should look like, see below:
Cell E9 is 5% of c9
Cell F9, G9 and H9 are all worth 5% of D9 if 100% is achieved - 95% till 99.9 will result in 2.5% being removed from L9 (Total Bonus), 90% to 94.9% will result in 5% being removed from L9, Below 90% will result in 10% being removed from L9
Cell I9 is worth 5% of D9 if 100% is achieved - Anything less than 100% will result in 50% of total bonus being removed.
Hope this helps provide some clarity for all the confusion...
I have attached a sample of the workbook
We're getting to greater clarity, although it still would help if you worked through (as if on a white board or black board) a few examples. As you'll see in the following, there are still ambiguities, and having you work through a few examples of different scenarios (with explanation) would help even further. It's clear in your minds now, but greater clarity still could be achieved with examples, not just the 'rules' of how the bonus is to be calculated.
That said, let me ask a few questions to make sure I'm getting each of these other points you've made.
Cell E9 is 5% of c9
Clear enough.
Cell F9, G9 and H9 are all [each] worth [an additional] 5% of D9 [Don't you mean E9??] if 100% is achieved
And presumably applied to L9 (that's a question)
- 95% till 99.9 will result in 2.5% being removed from L9 (Total Bonus), 90% to 94.9% will result in 5% being removed from L9, Below 90% will result in 10% being removed from L9
Are these removals to be calculated based on the value of L9 after whatever increases might have been earned for 100% achievements in the prior step? Or are these percentages calculations based on the value in E9?
Cell I9 is worth 5% of D9 if 100% is achieved - Anything less than 100% will result in 50% of total bonus being removed.
Now this statement really needs clarification. Again, I'm sure it's clear in your own mind, but notice, D9 in your spreadsheet shows a value of 1,150, 5% of which would be 52.50, which is fine, but very different from 5% of E9. And is the "Anything less than 100% will result in 50% of total bonus being removed" to be applied to the bonus in L9? And does all that get applied to L9 after all of the foregoing calculations have been completed?
Please: provide several examples of different ratings and how they influence the calculations of bonus. Feel free to use your spreadsheet as the white board, but add a few comments to explain how you're getting from raw data to final conclusion. Note: I'm not asking you to use Excel formulas or function to explain, just use the cells as repositories for numbers, with comments explaining how you calculated them. HansVogelaar or I can translate the comments into formulae.
In case this seems like nit-picking, it's because we're not able to sit down face-to-face, so questions like the ones I've asked above , or interpretations like I've interposed above, are what we'd be doing IF we were able to meet face-to-face. I'm quite confident the end result will be relatively simple, but we need to get the system systematized.
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!!!
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.
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!