How to track your small shop's profit in a spreadsheet
Sales are easy to see. Profit is harder, because it is what is left after the platform, the postage, the materials and every other cost have taken their share. A spreadsheet is enough to track it properly, as long as you record the right things on every order. This guide shows a layout that works in Excel and Google Sheets.
What counts as profit
For one order, profit is what the buyer paid, minus everything that order cost you:
Profit = item price + shipping charged, minus platform fees, minus product cost, minus the postage you paid
Two numbers get mixed up a lot. Revenue is the money the buyer paid. Profit is what you keep. A shop can have growing revenue and shrinking profit if fees or postage creep up, which is why it is worth tracking both.
Use three sheets
Keep the workbook simple. Three tabs cover most small shops:
- Orders: one row for each product sold.
- Expenses: costs that do not belong to a single order, such as packaging bought in bulk, software, equipment and advertising.
- Summary: totals for each month, worked out by formulas so you never type them.
The Orders sheet
Give each order row these columns:
| Column | What goes in it |
|---|---|
| Date | The day the order was placed |
| Order number | The reference from your platform, so you can find it again |
| Product and quantity | What was sold and how many |
| Price each | The item price the buyer paid |
| Shipping charged | What the buyer paid for delivery |
| Postage paid | What delivery really cost you |
| Platform fees | Worked out by a formula (see below) |
| Product cost | Materials or wholesale cost for the items in this row |
| Profit and margin | Worked out by formulas |
If one order contains two different products, use two rows with the same order number and put the shipping on the first row only. That way the shipping is not counted twice.
Working out the fees
Most marketplaces and payment providers charge a percentage of the sale, a fixed amount per order, or both. Some apply the percentage to the shipping the buyer pays as well as the item price. The pattern is:
Fees = (order total × fee percentage) + fixed fee
Put the fee percentage and the fixed fee in their own cells on a small settings area, then point the formula at those cells. When a fee changes you update one cell and every row follows.
Fees differ between platforms and they change. This guide does not quote any platform's rates. Look up the current fee page on the platform you sell on and enter those numbers yourself.
A worked example
Say you sell a candle for 20.00 and charge 3.50 for shipping. The candle cost you 6.00 to make and the postage was 3.20. For the example, assume a fee of 10% of the order total plus 0.30 per order. Those fee numbers are made up for the sum and are not any platform's real rates.
- Order total: 20.00 + 3.50 = 23.50
- Fees: 23.50 × 10% = 2.35, plus 0.30 = 2.65
- Profit: 23.50 minus 2.65, minus 6.00, minus 3.20 = 11.65
- Margin: 11.65 divided by 23.50 = 49.6%
In the spreadsheet, with quantity in D, price in E, shipping charged in F, postage paid in G and product cost in I, the formulas for row 2 look like this:
- Order total:
=D2*E2+F2 - Fees:
=H2*Settings!$B$1+Settings!$B$2where H2 is the order total - Profit:
=H2-J2-I2-G2where J2 is the fees - Margin:
=IF(H2=0,0,K2/H2)where K2 is the profit
You can try the same sum with your own numbers in the free marketplace fee and profit calculator.
The Expenses sheet
Some costs cannot be pinned to one order: a box of mailers, a monthly software subscription, a new printer, an advert. Record each one on the Expenses sheet with a date, a category and an amount. Keep the categories few and fixed (for example Packaging, Software, Equipment, Advertising, Other) so the totals stay meaningful.
Be careful not to count a cost twice. If the cost of packaging is already inside your product cost on the Orders sheet, do not also enter the bulk purchase as an expense.
The monthly Summary
On the Summary sheet, list the months down the side and let SUMIFS add up the rows that fall inside each month. With the first day of the month in A2, the revenue for that month is:
=SUMIFS(Orders!H:H,Orders!A:A,">="&A2,Orders!A:A,"<"&EDATE(A2,1))
Use the same pattern for fees, product costs, postage and order profit, and again on the Expenses sheet. Then:
Net profit for the month = profit from orders, minus expenses
Both SUMIFS and EDATE work in Excel and in Google Sheets.
Common mistakes
- Forgetting your own postage. Shipping charged and postage paid are different numbers. Track both.
- Typing fees by hand. It is slow and easy to get wrong. Use a formula and a settings cell.
- Ignoring refunds and discounts. Add a discount column, and record a refund as its own negative row so the history stays honest.
- Leaving it for months. Ten minutes a week is far easier than a weekend of catching up.
This guide is about record keeping only. It is not tax or accounting advice, and the rules for what you must record depend on where you live.
If you would rather not build this yourself, the Shop Profit Tracker is a ready made version: orders with automatic platform fees, expenses, a dashboard and a price calculator, for Excel and Google Sheets.