Key takeaways
- Structure your amazon seller accounting spreadsheet around six tabs: orders and fees, COGS, SKU economics, payout reconciliation, VAT, and a dashboard.
- Track fees per order, not per month, so a loss-making SKU cannot hide inside a profitable month.
- Use landed COGS (unit cost plus inbound freight plus duty) rather than supplier unit cost alone to avoid overstating margin.
- Reconcile against Amazon's settlement report every payout with a three-line check and investigate variances above roughly 1%.
- Move to dedicated software once reconciliation exceeds an hour per payout, SKU count passes about 50, or a second marketplace or VAT entity appears.
You track true net profit per unit by building an amazon seller accounting spreadsheet around six tabs: orders, fees, COGS, PPC, VAT, and payouts, then reconciling it against Amazon's settlement report every two weeks. This guide gives you the exact tab structure, column definitions, and formula logic, plus the honest cutoff point where a spreadsheet stops working and software takes over.
Why do Amazon settlement reports make spreadsheet accounting so hard?
The settlement report is the right raw material for an amazon seller accounting spreadsheet, but it fights you on three fronts before a single formula works.
Settlement reports are payout-level, not SKU-level
Amazon's settlement report is built for cash reconciliation, not product economics. It lists amounts Amazon paid you and charged you per payout period, with fee types identified by settlement report field codes documented in Amazon's SP-API reference. To get net profit per unit, you must allocate every line back to the SKU and order that caused it. That allocation is the actual work.
Fees arrive weeks after the sale
A sale in January can carry FBA fees, refunds and storage charges settled in February. If you book revenue at sale date but fees at settlement date, your per-unit profit is wrong until you lag-match them.
One payout mixes multiple marketplaces and fee types
A single settlement can combine US and UK sales, referral fees, FBA fees and currency conversion. Split by marketplace first, then by fee type.
This mismatch is exactly what tools like Sellerboard and SellerMatrix automate, and why the manual version needs a deliberate structure, which the next section defines.
Which tabs should an Amazon seller accounting spreadsheet contain?
Six tabs. Anything less merges numbers you need separate, anything more becomes a part-time job.
Tab 1: Sales & Fees (transaction log)
One row per SKU per day: units sold, gross revenue, referral fee, FBA fulfilment fee, storage, refunds. Pull this from Seller Central's Business Reports and the settlement report, not from memory.
Tab 2: COGS & Inbound
Unit cost, inbound shipping, prep. Update when supplier prices change, and date the change so historical profit stays accurate.
Tab 3: SKU Economics (the profit engine)
The lookup tab. Net profit per unit and breakeven ACOS per SKU, calculated from Tabs 1 and 2. This is where "true profit" lives.
Tab 4: Payout Reconciliation
Settlement totals vs your spreadsheet, per payout, with a variance column.
Tab 5: VAT & Multi-Market (UK/EU sellers)
Marketplace, VAT rate, net-of-VAT revenue per SKU.
Tab 6: Dashboard
Weekly summary: true profit, reorder cash, worst three SKUs. SellerMatrix does this as a Monday review instead of a tab; the DIY version works too, it just costs you the Sunday. If you'd rather build than subscribe, our affiliates page covers reseller terms.
| Tab | Source | Refresh |
|---|---|---|
| Sales & Fees | Settlement report | Per payout |
| COGS & Inbound | Supplier invoices | On change |
| SKU Economics | Formulas | Auto |
| Payout Reconciliation | Settlement report | Per payout |
| VAT & Multi-Market | VAT transaction report | Monthly |
| Dashboard | Formulas | Weekly |
What columns do you need to capture FBA fees, referral fees, and COGS accurately?
Sales tab columns: order ID, SKU, marketplace, gross, VAT, referral, FBA fulfilment fee, storage, refund flags
Your Sales tab is the transaction log. One row per order line: Order ID, date, SKU, marketplace, gross sales, VAT collected, referral fee, FBA fulfilment fee, storage fee allocated, and two refund flags (refunded yes/no, refund date). Amazon publishes its current referral percentages and FBA fulfilment fee schedule on Seller Central's FBA fees help pages, so pull the rates for your marketplace and size tier rather than guessing. Keep storage as a monthly allocation column even though it arrives on the settlement report, not the order.
COGS tab columns: unit cost, inbound freight per unit, landed cost
The COGS tab holds one row per SKU: unit cost from your supplier, inbound freight per unit, and landed cost, which is the sum of the two. Update landed cost every time you place a PO, because last quarter's number silently overstates this quarter's margin.
Why you track fees per order, not per month
Per-order fee capture is the whole point of the spreadsheet. Monthly totals tell you that fees happened; per-order rows tell you which SKU caused them. When you compute true net profit per unit, you need the referral fee, FBA fee, and VAT attached to the specific sale that generated them, otherwise a single loss-making SKU hides inside a profitable month and you keep reordering it. Your Sales tab joined to the COGS landed cost is what makes that join possible, because the lookup only works when both sides carry the same SKU key, and the terms of any tool you later plug into this data, including ours at SellerMatrix, matter less than getting these rows right first.
What formulas calculate true per-unit profit after all Amazon fees?
Net revenue formula (gross minus VAT minus referral minus FBA fees)
Start with gross sales price, then subtract VAT (UK: price ÷ 6 for 20%), the referral fee (usually 15% of price), and FBA fulfilment fee per unit:
Net revenue = Price − VAT − Referral − FBA fee − Storage per unit
Landed COGS formula (unit cost + inbound freight + duty)
Unit cost alone understates your costs. Use:
Landed COGS = (Unit cost + inbound freight per unit + duty per unit)
If a carton of 100 units costs £800 to make, £200 to ship, and £150 in duty, landed COGS is £11.50, not £8.00.
True net profit per unit and margin %
True net per unit = Net revenue − Landed COGS − refunds allowance − PPC cost per unit
Express it as a margin % against gross price so you can compare SKUs. Anything under ~15% deserves a price or cost review before you reorder.
Breakeven ACOS and why it protects reorder cash
Breakeven ACOS = True net margin before ads ÷ gross price
A SKU with a 30% pre-ad margin breaks even at 30% ACOS. Anything you spend below that is reorder cash; above it, you're funding ads from stock money. SellerMatrix computes this per SKU every Monday (ACOS calculator). Honest caveat: the spreadsheet version only updates when you update it, so it lags a fast-moving ad account.
How do you reconcile your spreadsheet against Amazon's settlement report each payout?
Three-line check: settlement total vs spreadsheet fees vs ad spend
Each payout, run three numbers: the settlement total from the Payments dashboard, your spreadsheet's total fees for the period, and your ad spend from the Ads console. If spreadsheet fees equal settlement total minus deposits plus ad spend, you are clean. If they drift more than about 1%, something is missing.
Common reconciliation failures (storage fees, reimbursements, chargebacks)
Storage fees post monthly, not per order, so they vanish if you only reconcile per-sale rows. Reimbursements for lost inventory appear as credits with no matching order row. Chargebacks and A-to-z refunds can post weeks after the sale, which is why refunds need their own column rather than an adjustment buried in the order row.
A weekly 15-minute routine
Monday morning: download the latest settlement, paste it into your Settlements tab, run the three-line check, and log any variance with a one-line reason. Keep ad spend consistent by using one metric, either ACOS or TACOS, so your totals stay comparable; Amazon defines both in its Ads documentation. Once the routine is boring, a tool like SellerMatrix can replace it by flagging the highest-impact variance each Monday, and its FBA profit calculator covers the per-unit side.
How do you handle VAT and multi-marketplace sales in one spreadsheet?
Net-of-VAT revenue as your only true revenue figure
VAT is not your money. Every revenue column in the spreadsheet should be net of VAT, or every per-unit profit figure downstream will be inflated by 20% on UK sales. Store the gross amount and the VAT charged in separate columns, then compute net revenue with a simple subtraction. Your profit formulas reference only the net figure.
Marketplace column as the key, not separate tabs
Add one Marketplace column (UK, DE, FR, US) and use it in SUMIFS and VLOOKUP ranges. Separate tabs per marketplace mean duplicating formulas and forgetting one when you change COGS. One table, filtered by marketplace, gives you per-country profit with a pivot.
Currency conversion without a paid add-in
Add a Rate column and pull monthly rates from Amazon's own settlement report, which already converts at the payout rate. Apply that rate to the gross column before VAT math. Illustrative example: a €100 DE sale at 0.85 becomes £85 gross, then subtract German VAT and fees in euros first, converting after, so fee timing matches Amazon's.
When does a spreadsheet stop working and you need dedicated accounting software?
Four signals you have outgrown the spreadsheet
You are spending more than an hour per payout on reconciliation. Your SKU count has passed roughly 50 and column-copying errors are appearing. You have opened a second marketplace or a VAT-registered UK entity and the tab structure is buckling. Or you are making reorder decisions on last month's data because manual updates lag.
What tools like Sellerboard, Helium 10, tool4seller and My Real Profit automate
Sellerboard, Helium 10, tool4seller and My Real Profit pull settlement, ads and inventory data via API, so per-unit profit updates without manual entry. That kills the reconciliation hour and the copy-paste errors.
Where SellerMatrix falls short: it does not replace your bookkeeping or VAT returns
SellerMatrix tells you what to fix each Monday and protects reorder cash, but it is not bookkeeping software. It will not file VAT returns or handle HMRC's rules on marketplace sales, which treat certain sales as made by the marketplace itself. You still need an accountant.
How do you keep the spreadsheet alive once it is built?
Weekly import routine from Seller Central reports
Block 30 minutes, same day every week, and pull three reports: the settlement report for the latest payout, the Business Reports sales export, and the Sponsored Products campaign export. Paste each into its raw tab, run your fee formulas down the new rows, and check that the settlement total matches your reconciliation column before you close the laptop. A mismatch you catch in week one is a formula error; one you catch in week eight is a month of wrong reorder cash.
When to refresh COGS and inbound costs
Update COGS the moment you place a purchase order, not when stock lands, and split inbound freight across units at the same time. Also refresh whenever Amazon changes FBA or storage fees, which it does at least once a year. Stale COGS is the single most common way a "profitable" SKU quietly goes negative.
Turning the dashboard into one Monday decision
At 7am each Monday, look at one number: true net profit per unit for your worst three SKUs. If any are negative after PPC, FBA fees, and returns, your Monday action is one fix: raise price, cut bids, or stop reordering. That single decision replaces a Sunday of dashboard interpretation, and it is the same logic SellerMatrix automates with a weekly plain-English review, though a disciplined spreadsheet gets you there too.
FAQ
Can I use one spreadsheet for both Amazon FBA and my own website sales?
Yes, but keep them as separate marketplace values in one table, not separate tabs. Add a Marketplace column (Amazon UK, Shopify, WooCommerce) and use it in SUMIFS and VLOOKUP ranges. Website sales carry payment processing charges instead of referral and FBA fees, so add a payment fee column. Your COGS tab stays shared, since landed cost is identical per SKU.
How do I account for Amazon reimbursements and lost inventory in my spreadsheet?
Reimbursements appear on the settlement report as credits with no matching order row, so give them their own column rather than burying them in an order line. When Amazon reimburses lost inventory, reduce your COGS tab's inventory value and log the credit separately. Netting it against revenue inflates per-unit profit for that SKU by an amount no sale caused.
Is a free amazon seller spreadsheet template good enough, or should I build my own?
Free templates usually stop at monthly revenue and fee totals, which answers whether you made money but not which SKU is eating your reorder cash. Building the six-tab structure takes a weekend and gives you per-order fee rows, per-SKU landed COGS, and breakeven ACOS. A template suits a rough monthly view; for reorder decisions, build it.
How do I calculate TACOS in a spreadsheet and what target should I aim for?
TACOS equals total ad spend divided by gross revenue, using period totals rather than per-order rows. Pull ad spend from the Sponsored Products campaign export and revenue from your Sales tab. Aim for TACOS below your pre-ad net margin: if a SKU carries a 30% margin before ads, TACOS above 30% means ads are funded from stock money.
Do I still need an accountant if I track everything in a spreadsheet?
Yes. The spreadsheet handles product economics, not compliance. It will not file VAT returns, apply HMRC's rules on marketplace sales, or produce year-end accounts. Treat it as management accounting covering true net profit per unit, reorder cash, and breakeven ACOS. Your accountant needs clean totals from the reconciliation tab, but the filing stays theirs.