Hello,
I need help figuring out the formula to calculate my estimated net commissions.
My commission plan is as follows.
50/50 split until I make $17,798 (35,596 Gross)
95/5 split until I make $211,106 (Not quite sure the gross)
100 Anything after $211,106.
This would go into the estimated net commissions row.
Thank you!
In which cell will your formula go?
It would go in Cell Z70
Maybe like that?
=IF(Z68<=17798,Z68*0.5,IF(AND(Z68>17798,Z68<=211106),Z68*0.95,Z68))
That didn't work correctly.
The correct Net income should be $14,450 with the current numbers in the sheet.
If you add another 500,000 sale at 3%, then the net commission should be $25,686.80
I calculated these by hand like this:
28900 Gross
28900*.5=14450 Net
Add another 500,000 Sale at 3%
43900 Gross
35596*.5=17798 Net
43900-35596(limit at 50/50 split)=8304 leftover at a 95% commission rate.
7888.80 Net on the leftover
Total Net= 25,686.80
I feel that it is easier to split the calculations into parts
Cell AD70 calculates the 50/50 split commission using: =MIN(Z68*0.5,17798)
Cell AE70 calculates the 95/5 split commission using: =IF(AD70< 17798,0,MIN(211106-17798,(Z68-SUM(AD68:AD70))*0.95))
Cell AF70 calculates the remaining commission using: =IF(SUM(AD70:AE70)< 211106,0,Z68-SUM(AD68:AE70))
Z70 is populated using: =SUM(AD70:AF70)
Note that cells AD68:AE68 are used to help check the result. When AD68:AF70 are selected the SUM shown in the bottom right of the spreadsheet should equal the value in cell Z68.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks