Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Question 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%

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    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!


    Quote Originally Posted by RetiredGeek View Post
    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. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 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=""),"",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
    Attached Files Attached Files

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

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

  6. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    As always Maud, you rock!

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 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

  8. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    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; 2016-08-23 at 21:26.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 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%
    desilva,

    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

  11. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    of course. Thanks!

  12. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    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!

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 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
    Attached Files Attached Files

  14. #13
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Thanks. I'd missed one of the places it was hard coded. It's all working now!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •