How to calculate my minimum price (in Excel) when percentages are involved
You have a product, and you want to know the minimum price you can sell it for such that at least you don't lose money. This market price will be your variable, lets call it $MP$. Now we have to look at the restrictions of your problem.
First, you have 2 fixed costs, the shipping cost $SC = \$5,$ and the product cost $PC= \$50$.
Now, you have 2 variable costs, which depend on the price you are going to sell the product. The VAT, which is $20\%$ of $MP$ and the market share, which is another $20\%$ of $MP$.
So, your total cost, $TC$, will be
$$TC = 0.2MP + 0.2MP + SC + PC = .4MP + \$5 + \$50.$$
In order to break even ($\$0$ profit) you need that your market price $MP$ minus your total cost $TC$ equals $0$, which means
$$MP  TC = 0 \iff 0.6MP = \$55 \iff MP = 91.666\ldots$$
To confirm this, lets check. Set your price to $\$91.666\ldots$.
Now, you have to subtract $20\%$ for VAT and an equal amount for market place share...
$$(.2)\cdot\$91.666\ldots = \$18.333\ldots$$
So, we have $\$91.666\ldots  \$18.333\ldots  \$18.333\ldots = \$55,$
Subtract the $\$50$ of the price and the $\$5$ of the shipping cost and you get $\$0.$
Related videos on Youtube
Aerendir
Pensiero Classico, formazione giuridica, anima innovaTTiva. Sono un blogger, amo Internet e l'#ecommerce è la mia perfetta sintesi tra piacere e lavoro (futuro)
Updated on November 28, 2022Comments

Aerendir 11 months
Say I have a good that costs $100.
I've paid it $50.
To the endprice I've to add, say, 20% for VAT.
I want also to offer free shipping and this costs me, say, $5,00.
Then I sell it on a marketplace, and the market place takes another 20%.
How can I know which is the minimum price at which I can sell the good to not lose money?
In fact, the problem is this.
I sell the good at
$100 + 20% VAT = $120
: this is the end price.To calculate my earnings I have to do:
$120 (end price)  ($100  20% VAT) = $100 (price without VAT) = =  $5.00 (Shipping) = $95 = =  $50 (good cost to me) = $45 = =  $120 (end price)  (($120 + $5.00) * 20% = $25, marketplace fee) = $20 (My profit)
Now, if I calculate my minimum price from these calculations, I get that I can sell the good at $100 to be in pair. But this is not correct, as, changing the sell price, change also the results of the calculations with percentage. There is some sort of "circular reference" (a term coined in development, but that well describes the phenomenon I'm seeing in this scenario).
So, which is the right formula to calculate my minimum price to be in pair when percentages are involved?
I'd like to put this formula in a spreadsheet where I set all required parameters (buy cost, shipping cost, VAT percentage, fee percentage, ecc.) and in the end get my minimum price.

Aerendir about 7 yearsOk, thanks for the downvotes, but it would be better if you tell my why... Thank you.

Luis Vera about 7 yearsDoes the VAT applies to the sell price? For example, if I sell it at $100$, do I have to take $20$ off? and, the $20%$ of the market place share is also calculated over those $100$? or after the VAT is subtracted?

Aerendir about 7 yearsIs this relevant? Anyway, 100 + 20% VAT + 20% marketplace's fee...


Luis Vera about 7 yearsHere, I'm assuming that the VAT and market place share are subtracted over the market price, which was my question in the comments...

Aerendir about 7 yearsYes, you are right, you have put the question in right mathematical terms. But my problem is not the calculation in itself, but the "dynamic" part. Because it is some sort of "until MP  TC == 0 then continue to lower the MP". Is this part that is complex for me... I'm asking if this "condition", this "for..." is representable in a formula.

Aerendir about 7 yearsOk, I need some time to understand... I don't use algebra since a lot of time :)

Luis Vera about 7 years@Aerendir If my assumptions are correct, then $MP = \frac{SC + PC}{1 VAT(=.2)  MarketShare(=.2)},$ but if they are not, then the other answer is correct.