Net Margin Formula Using SUM= To Calculate Gross Margin Cells

Originally Posted by jezrp22

I have attached a sample from the worksheet.

I have formulas set up to calculate the Net Margin (cells M3 > M7) for products i sell if i set the Gross Margin (Cells D3 > D7) using a set % in D1

This is great but what i really need is to understand what Gross Margin amount i shoudl add in order to achieve a set Net Margin.

I have doubts about some of your formulas. See "Notes" below. But I am not familiar with selling products on EBay.

Nevertheless, see column D in the worksheet "Gross Margin" in the file "ebay gross margin.xls" (click here), downloaded from https://app.box.com/s/jl9nj9hl38yoa5mrpt3xko8vi12kzz5a

For example, the gross margin in D3 is calculated by:

Please Login or Register  to view this content.

where:
1. A3 is the "purchase price" (your cost?).
2. E3 is the "P&P" (post and packaging).
3. M1 is the required %Net Margin.
4. B1 is the %VAT, and I1 is the EBay %fee.

Hindsight.... Change $M$1 to N3 if you want a different %Net Margin for each item. (Move the existing column N, %Gross Margin, to column O.)

The derivation and explanation of the formula is beyond the scope of this forum. Suffice it to say: it was derived algebraically by solving the formula in N3 for D3 (see G3), followed by mechanical algebraic manipulations to gather terms and simplify. You can see some of the work in worksheet "Derivation".

I did make a couple design changes, outlined below.

Notes....

1. I assume that your use of 1.111 in column H is intended to be 1/(1-10%), where 10% is the EBay %fee, which also appears in column I. Thus, column H is the EBay price that yields the required sales revenue in column G after subtracting the EBay %fee. I put the EBay %fee into I1 and changed the formulas in column H and I accordingly.

2. In that case, column I should be and is identical to column G. So I changed the formula in column L (L3, for example) to =G3-K3-E3-C3. And we no longer need column I. (Move I1, EBay %fee, to another column.)

Aside.... Alternatively, it might be useful to repurpose column I so that it shows the EBay fee. For example, the formula in I3 might be =H3*$I$1.

3. I assume your use of 1.2 in columns F and J is intended to be (1+20%), where 20% is the %VAT, which also appears in column B. I put the %VAT into B1 and changed the formulas in columns B, F and J accordingly.

4. I changed 111.1215 to 111.12 in A4 and A5. Currency amounts are usually entered accurately to the cent, since that is the amount we must actually pay/charge.

Hindsight.... Perhaps 111.1215 is intended to be a unit cost of an item that you sell only in bulk. In that case, you could restore the original unit cost; it makes no difference in the design. On the other hand, if my assumption is right, I'm surprised you do not enter the bulk cost instead. Oh well, I don't know anything about EBay.

5. To that end, all of the calculated currency amounts should be rounded to the cent. I did not make that change. But if you do, you might discover that the calculated %Net Margin in column M does not always match the required %Net Margin in M1 due to rounding anomalies. That's life!

6. All of your uses of the SUM function are unnecessary, and they serve no useful purpose. I removed them all.

Aside.... In column G (G3, for example), you might write =SUM(C3:E3) instead of =E3+D3+C3, which was originally =SUM(E3+D3+C3).

And in columns F, J and K (F3, for example), your use of SUM(F3) serves a useful purpose if F3 might contain text, such as the null string (""). That did not seem likely in the example provided; and I would think other design changes are needed. But perhaps you simplified things (good idea!), and I'm not seeing the "big picture".

7. I am suspicious of the VAT calculations in columns F and J. I did not change them. However, they do complicate the derivation of the formula for column D above. For my edification, I'd appreciate it if you can explain those calculations, preferrably by providing a pointer to an authoritative explanation, for example an EBay document.