Small Order Cost Tracker Spreadsheet
This spreadsheet works out what it costs you to serve one order, and the order value below which the work stops paying for itself.
Taking an order, picking it, driving it out and invoicing it costs about the same whether the order is forty dollars or four thousand. The margin on the goods is in every system you own; the cost of the order itself is in none of them. This file writes that cost down and finds the point where an order stops being worth writing.
What is in it
- Nine linked tabs, from Start Here through to a one-screen Overview.
- Orders — one row per order: seven things typed, nine worked out on the row.
- By Order Size — the same year sorted into eight value bands you name yourself.
- Cost To Serve — where the money actually goes, in seven lines.
- Where It Breaks Even — the smallest order worth writing, by how many lines are on it and whether you deliver.
- Setup — eight numbers and two short lists; change one rate and the whole year reprices.
Who it is for
Anywhere an order has a fixed cost of simply existing: a trade counter, a distributor, a print shop, a parts supplier, a bakery running delivery rounds, a maker selling both wholesale and retail, anyone who packs and posts.
The worked example
The worked year holds 360 orders across six channels. It invoiced $217,091 and made $51,982 of gross profit — 23.9 cents in every dollar. Serving those orders cost $12,181, so what was actually left was $39,801, or 18.3 cents. The average order was $603 and cost $33.83 to serve.
The ladder underneath is the part people do not expect. Orders under $50 lost $14.84 each; orders over $2,500 left $1,244.43 each. A hundred and twenty of the 360 orders cost more to serve than they made — together 3.4% of the money and 29% of the hours. On this business’s own numbers the smallest order worth writing is $111 delivered, or $80 if the customer collects.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
A look inside the file
Questions people actually ask
How do I work out the minimum order value my business should set?
You price what it costs to serve one order, then find the order value at which that cost is covered. In the worked year inside the file it costs $33.83 to serve an order, and the smallest order worth writing is $111 delivered or $80 if the customer collects, on four lines. The Where It Breaks Even tab does that arithmetic from the eight numbers in Setup, so it works before you have typed a single order.
We are not a wholesaler. Does this fit our business?
Yes, it works anywhere an order has a fixed cost of existing: a trade counter, a distributor, a print shop, a parts supplier, a bakery with delivery rounds, a maker selling both wholesale and retail, anyone who packs and posts. All the arithmetic needs is orders that vary in size. It prices the order, not the customer.
We have never timed how long an order takes us. Where do I get that data?
Time a dozen orders with a watch: it takes an afternoon and it is the single most valuable number in the file. Nobody has these minutes recorded at first, so an honest figure you produce this week beats a precise one that does not exist. For the van, put in the marginal cost of one more drop, meaning fuel, wear and the driver's hour, not a share of the whole fleet.
Does it work in Google Sheets, or do I need Excel?
Both, and LibreOffice too. It is built as .xlsx for Excel 2016 or newer on Windows or Mac, and loads into Google Sheets through File then Import then Upload. No macros, no add-ons, no account to create, nothing to install and no subscription.
Does the file hold customer names or any personal data?
No. It holds orders, channels and amounts only, and there are no customer names anywhere in it. It is not an order system, a stock system or an accounts package, and it connects to nothing. It will not tell you what your minimum should be; it tells you what every order below it is costing you today.
Need it built around your business?
This file assumes you sell goods off a shelf, deliver in your own van and invoice on account. If your orders work differently — a made-to-order run with a setup cost per job, pallet freight priced by a carrier rather than your own driver, a trade counter where half the orders are collected and never touch a vehicle — I can build the same arithmetic around the way you actually take and fill an order.
All other templates are listed on the Excel & Google Sheets Templates page.