Good Evening,
I am a begginer and am having difficulty creating a formula for my sales department to compute their commission. I have a current formula that works for the basic sell commission, however, we offer a 40/60 split for those sales above our listed price. We use an 18% markup on our manufacturing cost to compute commission. A sale at manufacturing cost earns the salesperson 5%. The 18% is worth an additional 5% of the overall sale in commission. Making a full sell worth 10% of the total sell. Should the sales person sell above the 18%, then he/she receives 40% of each dollar above the 18%. I use the 18% as a divisor to compute the commision with no problems. It is when someone sells above the 18% that I have a problem with a formula. I have to manually enter numbers after computing differences on a calculator. Thus making the spreadsheet ineffective. Here's how I figure commisson.
Cell A1 Mfg Cost = $10,000.
Cell A2 18% = $1,800.
Cell A3 Full sell = $11,800
Cell A4 Job Sell = $11,300.
Cell A5 Markup Sold = $1,300
Cell A6 Markup Fact = 0.722
Cell A7 Add'l Comm. = 0.036
Cell A8 Total Comm. = 0.086
Cell A9 Comm Earn = $971.80
Job sells for $11,300 (Cell A4), I then deduct Cell A1 from Cell A4 to get the total markup sold (Cell A5). Then I divide Markup sold (Cell A5) by markup (Cell A2) to get a markup factor (cell A6). Then I multiply Cell A6 by 5% (total available commission on 18% markup) to get additional commission earned (Cell A7). Then I add the base 5% for selling the mfg cost to get total commission % earned (Cell A8)and total commission (Cell A9). I have no problem with the above. However, I can't figure out how to create a formula when a salesperson puts a "bump" on the job.
Cell A1 Mfg Cost = $10,000.
Cell A2 18% = $1,800.
Cell A3 Full sell = $11,800
Cell A4 Job Sell = $12,800.
Cell A5 Markup Sold = $1,800
Cell A6 Markup Fact = 1.0
Cell A7 Add'l Comm. = 0.05
Cell A8 Total Comm. = 0.10
Cell A9 Comm. Earn = $1180.
Cell A10 Bump = $1,000
Cell A11 Bump Money = $400.
Cell A12 Total Earn = $1580.
I am trying to limit imput to two cells (A1 & A4), then have the formula work everything out. I want cell A5 to put no more than cell A2 in as a value, and if the value is greater than cell A2, put the excess in cell A10 to figure the bump money. if there is no bump then cells A10 & A11 will be zero. Is ther a way to do this?
Thanks in advance and sorry about the long post,
John
I am a begginer and am having difficulty creating a formula for my sales department to compute their commission. I have a current formula that works for the basic sell commission, however, we offer a 40/60 split for those sales above our listed price. We use an 18% markup on our manufacturing cost to compute commission. A sale at manufacturing cost earns the salesperson 5%. The 18% is worth an additional 5% of the overall sale in commission. Making a full sell worth 10% of the total sell. Should the sales person sell above the 18%, then he/she receives 40% of each dollar above the 18%. I use the 18% as a divisor to compute the commision with no problems. It is when someone sells above the 18% that I have a problem with a formula. I have to manually enter numbers after computing differences on a calculator. Thus making the spreadsheet ineffective. Here's how I figure commisson.
Cell A1 Mfg Cost = $10,000.
Cell A2 18% = $1,800.
Cell A3 Full sell = $11,800
Cell A4 Job Sell = $11,300.
Cell A5 Markup Sold = $1,300
Cell A6 Markup Fact = 0.722
Cell A7 Add'l Comm. = 0.036
Cell A8 Total Comm. = 0.086
Cell A9 Comm Earn = $971.80
Job sells for $11,300 (Cell A4), I then deduct Cell A1 from Cell A4 to get the total markup sold (Cell A5). Then I divide Markup sold (Cell A5) by markup (Cell A2) to get a markup factor (cell A6). Then I multiply Cell A6 by 5% (total available commission on 18% markup) to get additional commission earned (Cell A7). Then I add the base 5% for selling the mfg cost to get total commission % earned (Cell A8)and total commission (Cell A9). I have no problem with the above. However, I can't figure out how to create a formula when a salesperson puts a "bump" on the job.
Cell A1 Mfg Cost = $10,000.
Cell A2 18% = $1,800.
Cell A3 Full sell = $11,800
Cell A4 Job Sell = $12,800.
Cell A5 Markup Sold = $1,800
Cell A6 Markup Fact = 1.0
Cell A7 Add'l Comm. = 0.05
Cell A8 Total Comm. = 0.10
Cell A9 Comm. Earn = $1180.
Cell A10 Bump = $1,000
Cell A11 Bump Money = $400.
Cell A12 Total Earn = $1580.
I am trying to limit imput to two cells (A1 & A4), then have the formula work everything out. I want cell A5 to put no more than cell A2 in as a value, and if the value is greater than cell A2, put the excess in cell A10 to figure the bump money. if there is no bump then cells A10 & A11 will be zero. Is ther a way to do this?
Thanks in advance and sorry about the long post,
John
