Formula to calculate desired profit margin, Acurately without Solver

[jamobe]

Hi

So I have seen various threads with this question and varying answers. Chatgpt also gives me different answers.

I have ended up with the formula below

=(B2+B3+B4+B6)/((1-SUM(B7,B8,(B9))))

B2, B3, B4 and B6 are costs
B7 = Marketplace Fee varies between 10 and 15%
B8 = VAT @ 20%
B9 is the profit margin.

I have B9 in a cell which I can change myself so If want a 10% profit margin I set this and it increases the sell price to give me the desired result after costs. Except after calculating the costs, it yields a 13.33% profit instead. By tinkering it a bit I found by adding the bold part (0.0333) =(B2+B3+B4+B6)/((1-SUM(B7,B8,(B9-0.0333)))), when I now enter 10% the calculated results are 10%. Am I right in thinking this is correct or have I got it all backwards? Also when I enter 80% in B9, the calculation doesn't even work as intended and gives incorrect results.

I have spent days even weeks trying to find how I should be doing this correctly, and sat staring at different formulas, my brain is fried, so would love it if someone could have a fresh set of eyes on this and shed some light on how you can set a desired profit margin and for it to calculate correctly.

Many Thanks

 

[6StringJazzer]

Throwing in a random 0.0333 indicates that your calculation is not correct, and that won't fix it.

How do you want to calculate profit margin? Just on your net cost (B2:B6) or after all taxes and fees? VAT is a passthrough that is actually paid by the customer so I wouldn't include that, but it sounds like your marketplace fee is a commission so that is part of your cost. Do you want to apply profit to your marketplace fee?

Here is a scenario where you disregard VAT for purposes of calculating profit. Your use of VAT indicates you are probably in the EU but since I am in the U.S. my currency shows as USD.

I would use

=D6*(1+B7)/(1-B9)+B8*D6

to calculate sales price. See B11 below. This excludes VAT from your profit calculation but includes marketplace fee.

$scratch.xlsm
	A	B	C	D
1
2	Cost	$ 20.00
3		$ 30.00
4		$ 30.00
5
6		$ 20.00	Total net cost	$ 100.00
7	Marketplace fee	10%		$ 10.00
8	VAT	20%		$ 20.00
9	Profit margin	10%		$ 14.22
10
11	Sale Price (calculated based on desired margin) incl. VAT	$ 142.22
12
13	Sale price less VAT	$ 122.22
14	Gross cost before VAT	$ 110.00
15	Error check: Profit before VAT	$ 12.22	10.00%
Costs

Cell Formulas
Range	Formula
D6	D6	=SUM(B2:B6)
D7	D7	=B7*D6
D8	D8	=B8*D6
D9	D9	=B9*B11
B11	B11	=D6*(1+B7)/(1-B9)+B8*D6
B13	B13	=B11-D8
B14	B14	=SUM(D6,D7)
B15	B15	=B13-B14
C15	C15	=B15/B13
D15	D15	=IF(C15=B9,1,-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D15	Other Type	Icon set		NO

 

[jamobe]

Thank you very much for this, it works exactly how I would like it to. Yes I am in UK, so will just use the marketplace fee as you have rightly mentioned. Really appreciate this, thanks!

 

[jamobe]

1 thing I left unnoticed is that the marketplace fee is based on the total selling price. not from the total net cost. So in your example the market place fee would be $14.22 and not $10.00. I changed cell D7 to =B7*B11 but then rather than 10% in the error check, I am now getting 6.55%. Do you think there is a way round that at all?

Thanks

 

[6StringJazzer]

Oh, that makes perfect sense, I should have realized that. I'm sure there is a way to do this analytically (without Solver), I will have to do a little algebra and get back to you.

 

[jamobe]

Thanks so much. I have been trying out different formulas but still going round in circles, the closest I've gotten so far is this formula and using your sheet cell references = (D6+ (D6 * B8)) / (1 - B7 - B9), but in the error checking part, it is always 2% lower than the target margin and am unsure why and would be ideal to at least find a way it can do it, even if it involves using additional cells to work it out. Also to note that 20% VAT is removed from the total sale price also, not added onto the net price. ie in the example Sale price $142.22, the VAT for this will be $23.70. So in cell D8 ive used =(B11*B8)/(1+B8).

 

[jamobe]

actually scrap that formula I just did, when the Market place fee changes, it just gives a different value

 

[6StringJazzer]

Try this. You should be able to copy/paste this right into a worksheet to play with it. I can link to an Excel file if that would help.

$scratch.xlsm
	A	B	C	D
1
2	Cost	$ 20.00
3		$ 30.00
4		$ 30.00
5
6		$ 20.00	Total net cost	$ 100.00
7	Marketplace fee	10%		$ 10.00
8	VAT	20%		$ 25.00
9	Profit margin	10%		$ 12.50
10
11	Sale Price (calculated based on desired margin) before VAT	$ 125.00
12
13	Sale price with VAT	$ 150.00
14
15	Error check: Profit before VAT	$ 15.00	10.00%
jamobe

Cell Formulas
Range	Formula
D6	D6	=SUM(B2:B6)
D7	D7	=B7*D6
D8	D8	=B8*B11
D9	D9	=B9*B11
B11	B11	=D6/(1-B7-B9)
B13	B13	=(1+B8)*B11
B15	B15	=B11-D6-D7
C15	C15	=B15/B13
D15	D15	=IF(C15=B9,1,-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D15	Other Type	Icon set		NO