Hi,
I am by no means an Excel expert. I am working with some salesperson commission spreadsheets that I've cleaned up, but whose formulas were written by someone no longer with the company.
I have found an error in a calculation. I am attaching to this a blank copy of the spreadsheet to show the problem.
1. Each salesperson is giving a sales goal. (C12)
2. They are paid a flat bonus depending on their achievement to that goal (L16) That lookup works fine.
3. We also keep track of the cash receipts from their accounts each month and they are paid a percentage of cash receipts as part of their bonus, HOWEVER, the percentage paid is based on #2, their achievement percentage to goal.
4. SO, when I key their sales into C12 and it calculates what percentage of goal they reached in H16 it SHOULD then drop the cash receipts number in C13 down into the table (A) titled "Incentive Payment" (bottom left) in the proper spot based on percentage to goal in the table just above where it reads "Based on Sales".
5. However, as you can see by this example spreadsheet, this person had a goal of $21,000 they sold $21,000 so that is 100% of goal. They had $18,000 in cash receipts. Therefore, that $18,000 should drop into the table below in the "$20,000 - $25,000" line (D32) and be paid at 3.5% and that number ends up in L12. If they had sold $25,000 the $18,000 would be paid at 4% and should drop down to D33.
Clearly, the cash receipts number is not being dropped into the right spot on the Incentive Payment table. But, to be honest, I'm not seeing anything in the formula that would lookup what percentage it should be paid at and tell it where to land in the table. End result, you end up paying a salesperson the wrong percentage of manually typing their cash receipts number into the table in the right spot based on their sales.
I hope I made this clear. I try to explain without going overboard. I am so lost as to how to fix this and, yes, we have caught errors luckily before anyone was paid incorrectly.
Does anyone know how to set this up correctly so it does what it should?
My deepest thanks! (In advance)
Bookmarks