An order tracking spreadsheet is one row per order, from the moment it comes in to the moment it's delivered, with a status that moves along as the order does. The columns that matter: the order number and date, the customer, how it came in, what they ordered, the date you promised, the status, the date it actually shipped or was picked up, a tracking number for anything shipped, and whether it's been paid. Two formulas do the work. One flags any order past its promised date that hasn't gone out. The other counts how many orders went out on time, which is the number to watch in a busy month. Keep the statuses few and plain, like new, in production, ready, shipped and delivered, so anyone can update the sheet without asking what a word means. In the example below, a cookie bakery tracks its first week of holiday preorders.
The short version
- One row per order, from new to delivered.
- Columns: order number, date, customer, channel, items, promised date, status, shipped date, tracking, paid.
- Keep five or so plain statuses.
- Flag any order past its promised date that hasn't shipped.
- Count on-time orders, and look at the late ones each week.
- Sort by promised date each morning: that's the day's work list.
- TaroStack keeps orders with their status and what's shipped. More below.
The columns
| Column | What to put |
|---|---|
| Order number | Your own, in sequence |
| Order date | When it came in |
| Customer and channel | Who, and how: preorder, café, online, market |
| Items | What and how many |
| Promised date | The date you told the customer |
| Status | New, in production, ready, shipped, delivered |
| Shipped or picked up | The real date |
| Tracking number | For anything sent by a carrier |
| Paid | Yes or no |
| Days late | Worked out: past the promised date and not out, or out after it |
What happens when part of an order can't go is in backorder template, and the order document itself in sales order template.
An example: a week of holiday preorders
Granite Peak Cookie Co., a made-up cookie bakery in Sandy, Utah, opened holiday preorders on December 1. By the 8th its tracker had twelve orders:
| Order | Customer | Items | Promised | Status | Out | Days late |
|---|---|---|---|---|---|---|
| 1201 | Preorder, pickup | 2 dozen chocolate chip | Dec 5 | Delivered | Dec 5 | 0 |
| 1202 | Café, Draper | 60 chocolate chip | Dec 4 | Delivered | Dec 4 | 0 |
| 1203 | Online, shipped | 1 dozen, gift box | Dec 6 | Delivered | Dec 7 | 1 |
| 1204 | Preorder, pickup | 1 dozen decorated | Dec 6 | Delivered | Dec 6 | 0 |
| 1205 | Office party | 6 dozen mixed | Dec 8 | Delivered | Dec 8 | 0 |
| 1206 | Online, shipped | 2 dozen, gift box | Dec 8 | Shipped | Dec 9 | 1 |
| 1207 | Preorder, pickup | 1 dozen chocolate chip | Dec 9 | Ready | ||
| 1208 | Café, Sandy | 48 chocolate chip | Dec 9 | In production | ||
| 1209 | Preorder, pickup | 2 dozen decorated | Dec 10 | New | ||
| 1210 | Online, shipped | 1 dozen, gift box | Dec 10 | New | ||
| 1211 | Office party | 4 dozen decorated | Dec 12 | New | ||
| 1212 | Preorder, pickup | 1 dozen mixed | Dec 12 | New |
Of the six orders out by the 9th, four went on time and two shipped orders went a day late, both on boxes waiting for gift tags. The owner moved the tags into the packing kit and started promising shipped orders two days after baking instead of one. Sorting by promised date each morning put 1207 and 1208 at the top of the list. What the cookies cost to make is in cookie business profit margin.
Get the template
Download the order tracking spreadsheet. One sheet: type each order's number, date, customer, channel, items, promised date, status, the date it went out, tracking and paid; days late fills in, late orders turn red, and a summary counts open orders, late orders and the on-time rate. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
The tracker says what's promised. It doesn't say whether there's butter for Tuesday's six dozen, or which orders the same batch of dough has to cover, and in December that's the question that makes orders late.
How TaroStack does it
TaroStack keeps each order with its status and what's shipped, beside the stock that has to cover it.
Sales orders. Each order records the customer, the lines and the dates, and shows as open, partly shipped or shipped.
One picking list. The day's open orders pick from one list, and each delivery note records what went.
Stock behind the orders. "What could I make right now" from your recipes and what's on hand shows whether the butter and chocolate cover the week, and the reorder list shows what to buy in days of cover.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| Which orders are open | Filter the status column | Open sales orders |
| What to pack today | Sort by promised date | One picking list |
| Whether there's butter for them | Walk to the cooler | What could I make right now |
| What to buy | A list on the fridge | Reorder list in days of cover |
Sales orders, recipes and the reorder list are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.