Results 1 to 13 of 13

20160823, 14:56 #1
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
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.
Please advise.
Bryan
SALES  COGS  COMMISSION = GROSS MARGIN
COMMISSION = GROSS MARGIN * 5%

20160823, 15:42 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,865
 Thanks
 417
 Thanked 1,577 Times in 1,428 Posts
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 .
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160823, 17:10 #3
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post

20160823, 17:10 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,884
 Thanks
 147
 Thanked 733 Times in 665 Posts
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=""),"",A3B3C3) 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

The Following 2 Users Say Thank You to Maudibe For This Useful Post:
bdesilva (20160823),RetiredGeek (20160823)

20160823, 18:17 #5
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
As always Maud, you rock!

20160823, 19:38 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,884
 Thanks
 147
 Thanked 733 Times in 665 Posts
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

20160823, 20:23 #7
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
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!
I do have a question about your screenshot though.
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.Last edited by bdesilva; 20160823 at 20:26.

20160823, 20:30 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,865
 Thanks
 417
 Thanked 1,577 Times in 1,428 Posts
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!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160823, 22:58 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,884
 Thanks
 147
 Thanked 733 Times in 665 Posts
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%
Using your formula:
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

20160824, 10:39 #10
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
of course. Thanks!

20160825, 18:01 #11
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
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!

20160825, 21:29 #12
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,884
 Thanks
 147
 Thanked 733 Times in 665 Posts
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

20160826, 12:40 #13
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 190
 Thanks
 9
 Thanked 1 Time in 1 Post
Thanks. I'd missed one of the places it was hard coded. It's all working now!