Anyone pricing in a spreadsheet
Margin formula sheet
What this file is
A margin formula sheet works one product's price down to what it keeps: VAT out, then gross margin, markup, the payment fee and the contribution per order. This one writes every formula three ways, for an English Excel, a German Excel and Google Sheets set to Germany, and fills in a €64,90 product.
What is inside
One header row, fifteen filled rows.
One product worked from the price customers pay down to what the order keeps, with each formula written for an English Excel, a German Excel and Google Sheets, so the sheet works whichever one you open it in.
Rows 1 to 16 are the file, and the sheet scrolls to show them all. Click any cell to read it; the foot of the sheet says what its column is for.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | item | value | excel | excel_german | google_sheets_german | in_words | |
| 2 | Price incl. VAT | 64.90 | input | input | input | What the customer pays for the product, VAT included | |
| 3 | VAT rate | 0.19 | input | input | input | As a fraction: 0.19 for 19% | |
| 4 | Unit cost | 22.00 | input | input | input | What one unit costs you, freight and duty included | |
| 5 | Net price | 54.54 | B2/(1+B3) | B2/(1+B3) | B2/(1+B3) | The price without VAT: divide by 1 plus the rate, never take the rate off | |
| 6 | Gross profit | 32.54 | B5-B4 | B5-B4 | B5-B4 | Net price minus unit cost | |
| 7 | Gross margin | 0.5966 | IFERROR(B6/B5,0) | WENNFEHLER(B6/B5;0) | IFERROR(B6/B5;0) | Gross profit as a share of the net price; format the cell as a percentage | |
| 8 | Markup | 1.4790 | IFERROR(B6/B4,0) | WENNFEHLER(B6/B4;0) | IFERROR(B6/B4;0) | The same gross profit as a share of the unit cost | |
| 9 | Shipping and packing | 6.40 | input | input | input | The carrier, pick and pack and the box for this order | |
| 10 | Payment fee rate | 0.015 | input | input | input | The payment provider's percentage, as a fraction | |
| 11 | Payment fee per payment | 0.25 | input | input | input | The payment provider's fixed fee per payment | |
| 12 | Payment fee | 1.22 | ROUND(B2*B10+B11,2) | RUNDEN(B2*B10+B11;2) | ROUND(B2*B10+B11;2) | Charged on the price with VAT, rounded to the cent | |
| 13 | Contribution per order | 24.92 | B5-B4-B9-B12 | B5-B4-B9-B12 | B5-B4-B9-B12 | What the order leaves after the goods, the parcel and the fee | |
| 14 | Contribution margin | 0.4569 | IFERROR(B13/B5,0) | WENNFEHLER(B13/B5;0) | IFERROR(B13/B5;0) | Contribution per order as a share of the net price | |
| 15 | Target gross margin | 0.65 | input | input | input | The gross margin you want, as a fraction | |
| 16 | Price for that margin, incl. VAT | 74.80 | ROUND(B4/(1-B15)*(1+B3),2) | RUNDEN(B4/(1-B15)*(1+B3);2) | ROUND(B4/(1-B15)*(1+B3);2) | The price incl. VAT that earns the target gross margin on this unit cost | |
| 17 | |||||||
| 18 |
A · itemWhat the row is. Column A of the sheet; the header is row 1.
Every column, explained.
In the order they sit in the file. Point at one and it is outlined in the sheet.
Behind the file
Why it is built this way.
The first rows are the product. A €64,90 price with 19% VAT is a net price of €54,54, and against a €22,00 unit cost that leaves €32,54 of gross profit: a gross margin of 59,7% of the net price, and a markup of 147,9% on the cost. Same euros, two shares, and mixing them up is the most expensive mistake a price list can make. Every step is the profit margin formula in Excel written out cell by cell, with the reasoning behind each one.
Why three spellings: a German Excel writes its decimals with a comma, so it separates the arguments of a function with a semicolon and names its functions in German. The gross margin row is =IFERROR(B6/B5,0) in an English Excel, =WENNFEHLER(B6/B5;0) in a German one and =IFERROR(B6/B5;0) in Google Sheets set to Germany, which keeps English function names unless you change them in its settings. Rows with only signs, like =B5-B4, read the same in all three.
The order then pays for more than the goods. A €6,40 parcel and a payment fee of €1,22 on the price with VAT leave €24,92 of contribution per order, a contribution margin of 45,7%: the contribution margin formula at the size of one order. The last row turns the question round. For a gross margin of 65% on the same unit cost, the price with VAT has to be €74,80.
For many products at once, the SKU margin sheet puts one product on each row and sorts the catalogue by what it keeps. For the whole store, the monthly P&L template takes the same subtraction down every line of a month, from GMV to EBITDA.
More templates
More free files, same format.
Monthly P&L template
All 34 lines of an online store's P&L, one month, formulas included.
Your daily P&L, without the spreadsheet.
nouz works out your real profit every night, after product costs, shipping, payment fees, ad spend and running costs, and sends it to your inbox each morning.