Forum Discussion

davidmac1346's avatar
davidmac1346
Copper Contributor
Jun 13, 2020

Excel margin Formula

Trying to figure out profit margins on products. The two formulas I have used come up with different percentages. Looking to make 40% profit.

 

Formula 1: (sale-cost)/sale   example:   (1.40-1.00)/1.40=29%  -  I have to raise sale price to 1.67 to get to 40%

 

Formula 2: Cost*1.40=1.40  example:  1.00*1.40=1.40  (40%) 

 

What am I not seeing here? My goal is a 40% profit margin.

Thanks

David 

 

  • mathetes's avatar
    mathetes
    Silver Contributor

    davidmac1346 

     

    I'm not an expert on this topic, but a quick Google search on the formula for profit margin tells me that it's usually done at a gross level. Here's one definition.

    The profit margin formula is net income divided by net sales. Net sales is gross sales minus discounts, returns, and allowances. Net income is total revenue minus expenses. A 10% margin is considered average.

    So one thing you're missing is that it's not just a single product you're looking at, but a bigger picture accumulating sales, discounts, returns, and income minus expenses, although your first formula does bear some resemblance to the official formula.

     

    The other is simple math. Algebraically they're not equivalent calculations at all. If you recall your ninth (or was it eighth) grade lessons... the second formula can be re-written as 1.40/1.40=1 whereas the first is .4/1.4=.29

Resources