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.

nouz-margin-formula-sheet.csv6 columns
A2Price incl. VAT
nouz-margin-formula-sheet.csv: 6 columns, the header in row 1 and 15 filled rows in rows 2 to 16
RowABCDEFG
1itemvalueexcelexcel_germangoogle_sheets_germanin_words
2Price incl. VAT64.90inputinputinputWhat the customer pays for the product, VAT included
3VAT rate0.19inputinputinputAs a fraction: 0.19 for 19%
4Unit cost22.00inputinputinputWhat one unit costs you, freight and duty included
5Net price54.54B2/(1+B3)B2/(1+B3)B2/(1+B3)The price without VAT: divide by 1 plus the rate, never take the rate off
6Gross profit32.54B5-B4B5-B4B5-B4Net price minus unit cost
7Gross margin0.5966IFERROR(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
8Markup1.4790IFERROR(B6/B4,0)WENNFEHLER(B6/B4;0)IFERROR(B6/B4;0)The same gross profit as a share of the unit cost
9Shipping and packing6.40inputinputinputThe carrier, pick and pack and the box for this order
10Payment fee rate0.015inputinputinputThe payment provider's percentage, as a fraction
11Payment fee per payment0.25inputinputinputThe payment provider's fixed fee per payment
12Payment fee1.22ROUND(B2*B10+B11,2)RUNDEN(B2*B10+B11;2)ROUND(B2*B10+B11;2)Charged on the price with VAT, rounded to the cent
13Contribution per order24.92B5-B4-B9-B12B5-B4-B9-B12B5-B4-B9-B12What the order leaves after the goods, the parcel and the fee
14Contribution margin0.4569IFERROR(B13/B5,0)WENNFEHLER(B13/B5;0)IFERROR(B13/B5;0)Contribution per order as a share of the net price
15Target gross margin0.65inputinputinputThe gross margin you want, as a fraction
16Price for that margin, incl. VAT74.80ROUND(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
nouz-margin-formula-sheet

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.

All 17 templates

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.

Your trialToday
14 days of nouz, every feature$0
Card needed to startNone
Access to your storeRead-only
Due today$0