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

2,099

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

Share:
2,099

Related videos on Youtube

Aerendir
Author by

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, 2022

Comments

  • Aerendir
    Aerendir 11 months

    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.

    • Aerendir
      Aerendir about 7 years
      Ok, thanks for the downvotes, but it would be better if you tell my why... Thank you.
    • Luis Vera
      Luis Vera about 7 years
      Does 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
      Aerendir about 7 years
      Is this relevant? Anyway, 100 + 20% VAT + 20% marketplace's fee...
  • Luis Vera
    Luis Vera about 7 years
    Here, I'm assuming that the VAT and market place share are subtracted over the market price, which was my question in the comments...
  • Aerendir
    Aerendir about 7 years
    Yes, 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
    Aerendir about 7 years
    Ok, I need some time to understand... I don't use algebra since a lot of time :)
  • Luis Vera
    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.