E-commerce Sellers

Tracking COGS and supplier receipts for online sellers

The supplier invoice is the smaller half of what a unit costs you. Freight, duty, and inspection arrive on separate documents weeks apart, and they belong to the same stock.

Covers landed cost allocation and the paperwork that arrives weeks after the stock.

A seller imports 500 units at $4.20 each. The supplier invoice says $2,100, so the unit cost is $4.20 and the margin on a $14.99 retail price looks excellent.

Then the rest of the paperwork arrives, spread across six weeks: $680 of sea freight, $340 of duty and import processing, $95 for a pre-shipment inspection, $120 for inland delivery from the port, and a $63 bank fee on the international transfer. Total landed cost is $3,398, which is $6.80 a unit — 62% higher than the invoice price.

Every one of those documents is a separate receipt from a separate vendor, arriving at a different time, and the only thing connecting them is a purchase order number that appears on perhaps half of them.

That connection is what a seller's expense tracking has to preserve. Miss it and you have accurate bookkeeping with a materially wrong view of profitability.

Tie everything to a PO, not a date

Give every inbound stock purchase a reference the moment you place it — PO-2026-014 or similar — and put that reference on every associated expense row regardless of which vendor issued the document or when it arrived.

DateVendorDocumentAmountPOCost type
2026-05-02Ningbo HualingProforma invoice2,100.00PO-2026-014Goods
2026-05-04WiseTransfer fee63.00PO-2026-014Finance
2026-05-19AsiaInspectPre-shipment QC95.00PO-2026-014Inspection
2026-06-11Seaway LogisticsOcean freight680.00PO-2026-014Freight
2026-06-28CBP / brokerDuty and MPF340.00PO-2026-014Duty
2026-07-02Kestrel HaulagePort to 3PL120.00PO-2026-014Freight

Two months from first payment to final cost. A monthly expense report in May would have shown this purchase as $2,163; the true figure was not known until July.

The cost type column matters because not everything on a PO necessarily becomes part of inventory cost — treatment of some charges varies, and your accountant may handle finance fees differently from freight. Typing each cost lets them apply a consistent rule rather than deciding line by line.

Allocating across a mixed shipment

A single container holding one SKU is easy: divide by units. A container holding four SKUs is the realistic case, and the allocation basis changes the answer significantly.

Say PO-2026-021 brings in:

SKUUnitsGoods costWeight (kg)Volume (m³)
A-1005002,1001801.2
B-2202003,400900.4
C-3101,0001,5004103.1
D-4501502,000600.3

Freight of $1,840 allocated three different ways:

SKUBy unit countBy goods valueBy volume
A-100$0.99/unit$0.87/unit$0.84/unit
B-220$0.99/unit$3.51/unit$0.70/unit
C-310$0.99/unit$0.31/unit$1.08/unit
D-450$0.99/unit$4.39/unit$0.70/unit

Look at B-220. Depending on the basis, its freight cost per unit varies between $0.70 and $3.51 — a five-fold spread that lands directly on the margin of a product you are making pricing decisions about.

For ocean freight, volume is usually the most defensible basis because volume is what you are actually buying. For duty, value is typically right because duty is assessed on value. For inspection, unit count often makes sense.

The important part is not picking the theoretically perfect basis. It is picking one per cost type, writing it down, and using it every time, so that margins are comparable across POs.

Cost of goods sold is not the same as what you paid

The distinction that trips up new sellers: money spent on stock is not an expense when you spend it. It becomes cost of goods sold when the unit sells.

Buy 500 units in June and sell 180 by 31 December, and this year's COGS reflects 180 units. The remaining 320 sit as inventory — an asset, not a deduction. A seller who expenses the whole purchase in the year of payment is reporting a loss that is not real and will owe the difference back later.

This has a practical consequence for the sheet: you need a year-end unit count per SKU, and nothing generates it for you. Put a reminder on the last business day of the year, count what is in each location including 3PL and amounts in transit, and record it dated. It is the single most reconstructable-looking record that actually cannot be reconstructed.

The platform fee problem

Marketplace fees are not COGS, but they behave like a cost of sale and they are easy to get wrong because of how the money arrives.

A platform paying out $8,420 for a month in which gross sales were $11,300 has already netted off around $2,880 of referral fees, fulfillment charges, storage, advertising, and refunds. If you record $8,420 as revenue, you have understated both income and expenses by $2,880 and lost all visibility of what the platform actually charged you.

Download the monthly settlement report and record gross sales and each fee category separately. The reconciliation target is simple: gross sales minus total fees minus refunds should equal the deposit that hit your bank. When it does not, the gap is usually a reserve or a chargeback worth knowing about.

Returns, damages, and the units that never sell

Landed cost assumes the units arrive saleable and then sell. A meaningful fraction do neither, and ignoring that inflates every margin figure you produce.

Three leakages worth tracking separately:

Short shipments. The PO said 500 units; 487 arrived. Your per-unit landed cost is calculated on what arrived, not what you ordered, so dividing $3,398 by 500 understates unit cost by about 3%. Count on arrival and record the received quantity, not the ordered quantity.

Damaged or defective on arrival. If 14 units of a 500-unit shipment are unsaleable, the landed cost of the saleable 473 has to absorb the whole shipment's cost. The arithmetic is unforgiving: $3,398 across 473 units is $7.18, not the $6.80 you calculated across 500.

Customer returns. A returned unit that goes back into saleable stock is an inventory adjustment. A returned unit that is destroyed or liquidated is a loss, and it carries its full landed cost with it plus the outbound and return shipping you have already paid.

Add a disposition column to your inventory tab — saleable, damaged, returned-saleable, written off — and the true cost per sold unit emerges rather than the theoretical one.

Multiple sales channels, one inventory

Selling the same SKU on two marketplaces plus your own storefront creates a reconciliation problem that spreadsheets handle well and that catches people out when they do not plan for it.

The stock is physically one pool, possibly split across locations: units at a marketplace fulfilment centre, units at a third-party logistics provider, units in your garage. Each location reports separately, each on its own schedule, and none of them knows about the others.

A workable approach is one row per SKU per location in the inventory tab, with a total that sums across them. Then the year-end count is not a single number but a set of them, each with its own source:

SKUMarketplace FC3PLOwn storageIn transitTotal
A-10018024000420
B-220446012200316
C-3106100900700

The in-transit column is the one people forget. Stock that has shipped from the supplier but not yet arrived is still yours and still has to be counted, and whether it counts depends on the shipping terms — who bears the risk while it is on the water is determined by the incoterms on the invoice, which is worth reading once per supplier rather than assuming.

When the spreadsheet stops being the right answer

This is worth saying plainly, because the structures above scale further than people expect and then stop.

A spreadsheet handles landed cost allocation well up to roughly a few dozen POs a year and a few dozen SKUs. The formulas do not care about volume, and the per-PO structure stays legible.

What breaks first is not size, it is movement. Once stock moves between locations frequently, once you are manufacturing or bundling rather than reselling, or once you need perpetual inventory rather than a periodic count, a spreadsheet starts requiring manual adjustments that are easy to get wrong and hard to audit. At that point inventory software earns its cost.

The useful thing is that the data structure above migrates cleanly. A purchases tab with PO references, cost types, and per-unit allocations is exactly what an inventory system wants on import. Building it properly now is not throwaway work.

A sheet structure that holds all of this

Four tabs, each with one job:

  1. Purchases — one row per expense document, tagged with PO and cost type. This is where scanned receipts land.
  2. PO summary — one row per PO, pulling totals by cost type with SUMIFS, calculating landed cost per unit.
  3. Inventory — units per SKU, with the year-end count.
  4. Settlements — monthly platform data, gross to net.

The landed cost formula on the PO summary tab is just =SUMIFS(Purchases!Amount, Purchases!PO, $A2) / UnitsReceived, which means the number updates itself as the late-arriving documents get captured. That self-updating property is the reason this works in a spreadsheet: the duty invoice in late June recalculates the unit cost without anyone remembering to go back and revise it.

Capturing those documents as they arrive is the only manual discipline required. Scan2Sheet handles the extraction — vendor, date, amount — and files the row with the document image linked, leaving you to pick the PO and cost type from dropdowns. Ten seconds per document, which is about what a habit can sustain when the documents arrive one at a time over two months.

What to check before pricing anything

  1. Every PO has a freight row, a duty row where applicable, and a finance fee row
  2. No PO is still showing only the supplier invoice sixty days after arrival
  3. The allocation basis per cost type is documented somewhere in the sheet
  4. Year-end unit counts exist per SKU and per location
  5. Each month's platform settlement reconciles to the bank deposit

Run that check against your last completed PO. If the landed cost differs from what you assumed when you set the price, that gap has been in every margin calculation you have made since.