An online store has one inventory problem a shop with a counter doesn't: the customer can't see the shelf. The site says "in stock" because a number in a spreadsheet or a store admin says so, and if that number is wrong you find out after you've taken the money. So an ecommerce inventory spreadsheet tracks two quantities for every product. On hand is what you counted on the shelf. Available to sell is on hand minus the orders already paid for and not yet shipped. The second number is the one the site should show, less a small buffer for miscounts. Below is a sheet for a made-up online tea shop that works both out, what to do about bundles and a second sales channel, and the file.
The short version
- A row for each SKU: every size, flavor and bundle a customer can order.
- Available to sell = on hand − unshipped orders.
- Show the site a little less than that. The buffer absorbs a miscount.
- A bundle packed ahead is its own row. One packed to order is limited by its scarcest part.
- Two channels selling one shelf each get a share, not the whole number.
- Reorder from what's available, not from what's on hand.
- TaroStack keeps the stock side current, by lot. More below.
An example sheet
| SKU | Product | On hand | Unshipped | Available | Buffer | Show online | Reorder at | Status |
|---|---|---|---|---|---|---|---|---|
| TEA-EG-100 | Earl Grey, 100 g tin | 64 | 9 | 55 | 5 | 50 | 30 | OK |
| TEA-JA-100 | Jasmine green, 100 g tin | 28 | 6 | 22 | 5 | 17 | 30 | REORDER |
| TEA-CH-100 | Chai blend, 100 g tin | 41 | 3 | 38 | 5 | 33 | 25 | OK |
| TEA-SMP-6 | Sampler box of 6 | 12 | 7 | 5 | 3 | 2 | 10 | REORDER |
| INF-SS-01 | Steel infuser | 90 | 4 | 86 | 5 | 81 | 40 | OK |
| TIN-EMP-100 | Empty tin, 100 g | 300 | 0 | 300 | 0 | 300 | 200 | OK |
Six rows, $1,126.20 at cost, with two to reorder. The sampler shows why the second column of numbers matters. Twelve boxes are on the shelf. Seven are already someone's. Five can be sold, and with a buffer of three the site should offer two.
On hand comes from a count, not from memory. Unshipped orders comes from your store's order list: paid, not yet packed. Type both, and the sheet does the rest.
The buffer is a judgment. This shop holds back five tins of each tea because its counts are sometimes off by a few, and nothing on packaging, which no customer orders. Too large a buffer hides stock you could have sold. None at all means one miscount is one apology.
Reorder on what's available. Jasmine has 28 tins on the shelf, close to its reorder level of 30. Six of them are sold already, so the real figure is 22. How to set the level is in reorder point example, and the cushion in safety stock formula.
SKUs
Every row needs a code that your store, your labels and this sheet all share. TEA-EG-100 says what it is to anyone packing an order. One product in three sizes is three SKUs. How to build them is in how to create SKU numbers.
Bundles
A bundle is either stock or a recipe, and it has to be one or the other.
Packed ahead. The sampler in the sheet is boxed in advance, so it's a row with its own count. Packing twelve boxes took tins off the shelf, and those tins have to come off their own rows on the day you pack.
Packed to order. If you assemble a sampler only when one sells, it has no stock of its own. What you can sell is set by its scarcest part.
| A sampler of two tins each | Available | Samplers it allows |
|---|---|---|
| Earl Grey | 55 | 27 |
| Jasmine green | 22 | 11 |
| Chai blend | 38 | 19 |
| Samplers you can promise | 11 |
Eleven, not nineteen or twenty-seven, and every sampler sold takes two tins from each row. More on this is in kitting for gift boxes.
Two places to sell
Say the Earl Grey is listed on your own site and on a marketplace. The sheet says 50 can be shown. If both places show 50, you can sell 100 tins you don't have. Two ways round it:
- Split it. Give your own site 30 and the marketplace 20, and move stock between them when one runs low.
- Update both after every order. Workable at ten orders a day, by hand. Not at a hundred.
Either way, one sheet holds the truth and each channel is told a number from it.
Get the spreadsheet
Download the online store inventory spreadsheet. Two sheets and a read-me: inventory that works out available to sell, what to show online, REORDER and value, and a sheet for deliveries received. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
The sheet is right when you type the count and the unshipped orders. Ten minutes later an order arrives and it's wrong by one. At a few orders a day you update it each evening and the buffer covers the gap. At fifty, the evening update is an hour, the buffer is always too small or too large, and the bundle sums are done in your head while you pack. And none of it says which batch of tea went into which order.
How TaroStack does it
TaroStack keeps the stock side: what's on the shelf, which lot, and what it cost.
Connect your Shopify store: we set it up with you. Orders arrive by import and a daily check, not to the second, and that check tells you when the store and TaroStack disagree.
A bundle is a recipe. A sampler that uses two tins of each tea takes them from stock when you make it, and "What could I make right now" says how many you can pack.
Receiving adds each delivery as a lot, with its date and cost.
Counting works on a phone with no signal. A count shows what each difference is worth.
A reorder list in days of cover, with each supplier's lead time and minimum.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| How many samplers can I pack | A sum in your head | What could I make right now |
| Which lot of tea is on the shelf | Not recorded | Stock by lot |
| When to reorder tins | A level set once | Days of cover, from real use |
| Whether the store's number is right | Found by a customer | A daily check |
TaroStack doesn't connect to Etsy, WooCommerce or Amazon, and it doesn't update a store's stock to the second. Stock, lots, counts, recipes and reordering are on every plan, from $9.99 a month (50 items, 5 recipes) or $39 a month (3,000 items, 50 recipes). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
