# Thread: Calculate commission problem when commission is COGS and based on gross profit

1. ## Calculate commission problem when commission is COGS and based on gross profit

It's common to base commission on gross margin and that's what I want to do. Commissions are commonly part of COGS, which makes perfect sense to me. However since gross margin is what the commission is based on and COGS determines gross margin, I don't see how to calculate commission as each side of the equation depends upon the other! I have a spreadsheet with all the numbers and know there must be a way to solve this! Formula below in CAPS.
Bryan

SALES - COGS - COMMISSION = GROSS MARGIN
COMMISSION = GROSS MARGIN * 5%

2. Not to be flip but I'd go to the Accounting department and get the components of COGS numbers (materials, direct labor, etc.) leaving out commissions (they definitively should have these numbers) then just add them together and subtract from sales to get gross margin Before Commissions and the rest is easy peasy. Of course if you're taking a math class and have to back into the numbers well that's not what we're here for .

3. The name is Bryan, not Flip...
I have all that. The problem is commissions are part of GM and that to calculate commissions I need GM. Recursive algebra stuff needed in an excel formula!

Originally Posted by RetiredGeek
Not to be flip but I'd go to the Accounting department and get the components of COGS numbers (materials, direct labor, etc.) leaving out commissions (they definitively should have these numbers) then just add them together and subtract from sales to get gross margin Before Commissions and the rest is easy peasy. Of course if you're taking a math class and have to back into the numbers well that's not what we're here for .

4. Although I agree with RG, calculating Commission based on Gross Margin while using Commission to calculate Gross Margin is possible.

Use the following formulas:

Assuming a header in row 2 where A2 is SALES, B2 is COGS, C2 is COMMISSION, and D2 is GROSS MARGIN

In Cell C3, caclulate Commission using the formula =IF(OR(A3="",B3=""),"",((0.05*A3)-(0.05*B3))/1.05) then copy down

In Cell D3, calculate Gross Margin using the formula =IF(OR(A3="",B3=""),"",A3-B3-C3) then copy do thn copy down

As a check to see if the commission is correct, in cell F3 place the formula =IF(OR(A3="",B3=""),"",D3*0.05)

The Calculated commission in C3 equals the check of Gross Margin x 5% in F3

HTH,
Maud

desilva1.png

5. ## The Following 2 Users Say Thank You to Maudibe For This Useful Post:

bdesilva (2016-08-23),RetiredGeek (2016-08-23)

6. As always Maud, you rock!

7. This is basically goal seeking with formulas: Looking for a commission amt that when subtracted from the difference between SALE PRICE and COGS to calculate GROSS MASRGIN equals GROSS MARGIN times 0.05

If interested, here is the algebra that shows how I arrived at the formula for commission:

desilva2.png

The formula could be even more versatile by replacing the .05 constant by a cell reference that the user enters a commission percentage

HTH,
Maud

8. Thanks again. Of course it gets more complex as the commission rate varies with amount sold across the year. For now I'm just going to implement this part of it and see how it goes. Thanks!

The second section saying two formulas equal GM is confusing as I don't see how the second line is true GM # commission/.5 as far as I would understand.

9. As they say on the "Big Bang Theory" ... "do you want to do the math?"...
BBTMath2.JPG
Me not so much. I'm fine with your basic math and I can fill in the blanks on a formula I find on the internet and even turn it into an excel formula or program it into a programming language. But figuring out what the formula should be I'll leave that to the geniuses like Maud. Very Nicely Done!

10. ## Systems of Equations

Thanks RG but I doubt that formula will ever put anyone on the moon!

SALES - COGS - COMMISSION = GROSS MARGIN
COMMISSION = GROSS MARGIN * 5%
desilva,

COMMISSION = GROSS MARGIN * 5%

If you divide both sides by 5% you end up with Gross Margin = Commission/5% which is saying, "if the sales person received a 5% commission of \$x.xx, what was the sale price?"

If a sales person receives a 5% commission of \$250.00 then the sales price was \$5000.00 or \$250.00/.05

Using Systems of Equations, two formulas that equal the same thing, equal each other enabling you to solve for a solution that satisfies both equations

HTH,
Maud

11. of course. Thanks!

12. Thanks again Maud,
I've integrated it into my spreadsheet and it works perfectly!
I did fail getting it to use a cell with the commission in it rather than embedded though. It calculated it differently. But I'll figure that out later!

13. bdesilva,

Here is the modifications needed to change the commission rate. In Cell I1, enter the new commission rate and the calculated commissions will be adjusted.

Formula in Cell C3 then copy down
=IF(OR(A3="",B3=""),"",((\$I\$1*A3)-(\$I\$1*B3))/(1+\$I\$1))

HTH,
Maud

14. Thanks. I'd missed one of the places it was hard coded. It's all working now!