How to calculate my minimum price (in Excel) when percentages are involved

Question

Say I have a good that costs $100.

I've paid it $50.

To the end-price 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.

Answer 1

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.$

Answer 2

Let's use some algebra.

Initial cost to you is $c$.

Markup is $m$ so that advertised price is $c+m$

VAT rate is $v$. This will be a decimal, so if VAT is 20%, $v=0.2$

End price is therefore $(c+m)(1+v)$

Shipping cost is $s$

Buyer therefore pays $(c+m)(1+v)+s$

Market charges you a fee $f$. Also a decimal, so if fee is 17%, then $f=0.17$

What you receive from the buyer is therefore $((c+m)(1+v)+s)(1-f)$

You still have to pay the original VAT to the government, you have to pay the shipping cost and you have to pay for the item in the first place.

You therefore receive a profit $p=((c+m)(1+v)+s)(1-f)-(c+m)v-s-c$

This simplifies as $p=(c+m+cv+mv+s)(1-f)-cv-mv-s-c$

$p= c+m+cv+mv+s-fc-fm-fcv-fmv-fs-cv-mv-s-c$

$p= m-fc-fm-fcv-fmv-fs$

This can be rearranged: $p+fc+fcv+fs= m-fm-fmv$

$m(1-f-fv)= p+fc+fcv+fs$

$m= \frac {p+fc+fcv+fs} {1-f-fv}$

This is the mark up required to achieve a particular profit.