How to Track Inventory for a Small Online Shop Without Losing Sales

Last December, a candle maker in Leeds sold 40 gift sets she did not have. Her online shop showed the sets in stock, her spreadsheet said something different, and her spare room shelf told a third story. By the time she found the truth, she had taken payment for orders she could not fill, refunded half of them, and lost three regular customers who never came back. Nothing about her candles was wrong. Her stock records were.

This is the quiet problem behind many small online shops. Sales feel good, parcels go out, money comes in, and nobody notices the numbers drifting until a customer asks where an order is. Learning how to track inventory for a small online shop is not about buying expensive software. It is about building a simple system you actually use, every week, without fail. This guide shows you how to do it with a spreadsheet, a clear product code for every item, and a short weekly routine that keeps your records honest.

Why stock records drift out of step

Most small shops start with stock in one place and sales in several. You list a product on your own website, then add it to Etsy, then take a few units to a weekend market. Each channel keeps its own count, and none of them talks to the others. A sale on Etsy does not update your website. A market sale never reaches any record at all unless you write it down the same evening.

The drift grows slowly. A return goes back on the shelf but never back into the count. A damaged jar gets thrown away and forgotten. A supplier delivery arrives short by two units and nobody checks the packing note. After a few months the numbers in your spreadsheet are a guess, and guesses are what cause overselling, emergency orders, and money tied up in stock that never moves.

The fix is not complicated. You need one master list that every sale, delivery, and return flows through, and a habit of checking it against the real shelves. Everything in this guide builds toward that one list.

Give every product a code you can read at a glance

Before you build a spreadsheet, give every product a short code, known as a SKU. A good code tells you what the item is without opening a second file. Take a candle shop as an example. A large lavender candle in a glass jar might be CAN LAV LG. A small rose candle in a tin might be CAN ROS SM. The pattern is simple: product type, scent or colour, then size. Anyone helping you pack orders can read it and pick the right item.

Keep every code unique. Two products that look alike but have different scents, sizes, or colours need different codes, because your records cannot tell them apart if the code is the same. Never reuse a code from a product you stopped selling. Old codes in new rows are one of the fastest ways to corrupt a stock list.

Write the code on the shelf label, on the storage box, and in your spreadsheet. When the same code appears in all three places, counting becomes fast and mistakes become obvious. A box with no code is a box that will be counted wrong.

Shopkeeper weighing products on a scale in front of stocked shelves

How to track inventory for a small online shop with a spreadsheet

You do not need paid software to track inventory for a small online shop with fewer than a few hundred products. A spreadsheet in Excel or Google Sheets does the job well, provided you set it up the right way. The key rule is simple: never type over a stock number. Log what happened instead, and let formulas work out the current total.

Start with three tabs. The first tab is your product list, with one row per product. Give it these columns: product code, product name, supplier, unit cost, selling price, opening stock, and reorder point. The second tab is your stock log, with one row per movement: date, product code, type of movement, quantity, and channel. A movement is either stock in, when a delivery arrives, or stock out, when a sale, return, or damaged item changes the count. The third tab is your summary, which adds up the log for each product and shows the current stock, the stock value, and whether a reorder is needed.

The formula that ties it together is SUMIFS. In the summary tab, stock in minus stock out for each product code gives you the live count. Once the formula is in place, you never edit the summary by hand. You only add rows to the log, and the numbers follow. This is the single most valuable habit in stock control, because a number you typed by hand can be wrong in ways nobody can trace, while a log shows exactly what happened and when.

Keep the log honest. Record every sale, including market stall sales and sales to friends. Record returns as stock in, and damaged or lost items as stock out with a note. If you skip the small movements, the drift returns, and you are back to guessing.

Set a reorder point for every product

A reorder point is the stock level that tells you to place a new order. Without one, you either order too early and tie up cash, or order too late and run out during your busiest week. Setting it takes five minutes per product, and it saves you from the most common stock mistake a small shop makes.

Work it out like this. Take your average daily sales for the product, multiply by the number of days your supplier takes to deliver, then add a small safety buffer. If you sell two units a day and your supplier takes seven days, you need 14 units to cover the wait. Add four units of safety stock and your reorder point is 18. When the count falls to 18, you order. The formula is simple, and it works for almost every product a small shop sells.

Check your supplier lead times honestly. A supplier who usually delivers in five days but took twelve days at Christmas needs a bigger buffer in November and December. Build your reorder points around the slow delivery, not the fast one, because the slow one is what causes a stockout.

Review reorder points every three months. A product that sold slowly in spring may sell fast in the run up to Christmas. A product that once sold two units a day may now sell five. Your spreadsheet already holds the sales history, so the review is a quick look at the numbers, not a fresh piece of work.

Count your stock every week

No spreadsheet, however well built, replaces a physical count. Paper numbers and shelf numbers drift apart, and the only way to catch the drift is to count what is actually there. The good news is that a small shop does not need a full stocktake every week. A rolling count works better and takes far less time.

Pick a quiet time, such as Monday morning before orders start, and count one section of your stock. Count your ten best sellers every week, and rotate through the rest of the range across the month. Count slowly, out loud, and write the number down before you look at what the spreadsheet says. If the two numbers differ, find the reason before you correct the record. Was there a sale you forgot to log? A return that went back on the wrong shelf? A delivery that arrived short? Fix the cause, not just the number, or the same gap will appear again next week.

Photograph the shelves as you count. A quick photo of each shelf, taken from the same spot each week, gives you a visual record that helps when a number looks wrong later. It takes seconds and it settles arguments with yourself about whether a box was really there.

A full stocktake still matters, and most small shops should do one four times a year, at the end of each quarter. Close the shop to new orders for a morning, count everything, and correct the spreadsheet in one go. The quarterly count is also the right moment to check expiry dates, look for damaged packaging, and spot stock that has sat untouched for months.

Glass jars of jam and honey arranged neatly on wooden shop shelves

Keep every sales channel in step

Selling on more than one channel is where most small shops lose control of their stock. Your website, your Etsy shop, and your market stall all draw from the same shelves, but each one shows its own version of the truth. The customer sees the version that is wrong, and you find out when the order arrives.

The simplest fix is a weekly channel check. Once a week, write down what each channel shows for your ten best sellers, then compare it with your master spreadsheet. Where a channel shows more than you have, lower it at once. An oversell costs you a refund, a bad review, and a customer. An undersell costs you one sale. Overselling is always the worse mistake, so when in doubt, show less stock than you hold.

If you sell on Etsy, export your listing data regularly. Etsy lets you download your listings with quantities included, and that export makes the weekly check fast. Shopify has a similar export from its inventory page. Use the exports instead of typing numbers from a screen, because exports do not make typing mistakes.

When your sales grow, this weekly check is the first job worth automating. Low cost tools can sync stock across channels in real time, and they pay for themselves the first time they stop an oversell. Until then, the weekly manual check is enough, provided you never skip it. Skipped weeks are how two channels quietly drift hundreds of pounds apart.

Spot dead stock and free your cash

Dead stock is any product that has not sold for two or three months. It sits on the shelf, takes up space, and holds money you could use for products that sell. Every small shop accumulates some, usually after a seasonal range ends or a new product fails to find buyers. Finding it is easy once your spreadsheet is working, because the log shows exactly when each product last sold.

Run a dead stock check once a month. Sort your products by date of last sale and look at everything with no sale in 60 days. Then decide, product by product, what to do. A small price cut often moves slow stock quickly. Bundling a slow product with a best seller can clear it without a deep discount. A seasonal product may simply need to wait for its season, in which case move it to the back of the shelf and stop reordering it.

Be honest with yourself about products that will never sell. Keeping them on the shelf because you paid for them does not recover the money. The money is already spent. Clearing the shelf at a discount, or even giving the item away with a larger order, recovers some value and frees space for stock that earns its keep. Good stock control means knowing what to stop buying as well as what to buy more of.

Dead stock also warns you about your buying habits. If the same type of product keeps ending up unsold, the problem is in the ordering, not the selling. Use the dead stock list to shape your next supplier order, and order less of what does not move.

Keep records your accountant will love

Stock is not just an operational detail. It is part of your accounts, and your year end stock value affects your profit figure and your tax bill. Under UK rules, stock is valued at the lower of cost and net realisable value, which means what you paid for it or what you could sell it for, whichever is lower. A spreadsheet that tracks unit cost and quantity on hand gives your accountant the numbers they need without a last minute scramble.

Keep supplier invoices filed by date, and match each delivery to the stock log entry for that day. When the numbers agree, your accounts are easy. When they do not, the mismatch is usually a delivery that arrived short or a price that changed without notice, and catching it in the month it happened is far easier than finding it a year later.

Good admin habits support good stock control, and the two save each other time. Batching your paperwork into one weekly session, for example, keeps invoices, orders, and stock counts in the same routine. If you want a simple way to organise that weekly session, our guide on how to batch admin tasks for a small business owner shows a routine that many small shops use. Cash flow matters here too, because stock you cannot pay for on time becomes a supplier problem, so knowing how to chase unpaid invoices in the UK keeps money coming in to fund your next order. And when you agree terms with a new supplier, putting the agreement in writing protects both sides, as our guide to how to write a freelance contract in the UK explains for written agreements in general.

Shop worker arranging fresh fruit on display shelves in a small grocery shop

A simple weekly routine that takes 30 minutes

All of this comes together in a short weekly routine. Do it at the same time every week, and it becomes a habit rather than a chore. Thirty minutes is enough for most small shops.

First, log every sale, delivery, and return from the past week into the stock log, including market sales and any items you gave away or damaged. Second, check your ten best sellers against the shelves and correct any gaps, finding the cause before you change the number. Third, look at the summary tab and order anything that has hit its reorder point, allowing for the supplier lead time. Fourth, run the channel check on your best sellers and lower any listing that shows more stock than you hold. Fifth, glance at the dead stock list and mark anything that needs a price cut or a bundle.

That is the whole routine. Five steps, thirty minutes, once a week. Shops that do it rarely oversell, rarely run out of best sellers, and always know how much money is sitting on their shelves. Shops that skip it drift back to guessing within a month.

Conclusion

Learning how to track inventory for a small online shop comes down to three things: one master list that every movement flows through, a reorder point for every product, and a weekly count that keeps the list honest. You do not need expensive software to do any of it. A spreadsheet, a clear product code, and thirty minutes a week will carry most small shops a long way.

Start today, even if your records are messy. List every product with a code, count what is on the shelves, and enter the real numbers as your opening stock. Set a reorder point for your ten best sellers first, then add the rest over the next few weeks. Within a month you will know exactly what you hold, what you need to order, and what is quietly eating your cash. That knowledge is what turns a busy little shop into a business that runs on facts instead of guesses.

Frequently asked questions

Do I need inventory software for a small online shop?
Not at first. A spreadsheet with a product list, a stock log, and a summary tab handles a few hundred products well. Move to software when you sell across many channels, hold more than a few hundred products, or spend over an hour a day on stock updates.

How often should I count my stock?
Count your ten best sellers every week and rotate through the rest of your range across the month. Do a full stocktake four times a year, at the end of each quarter, to catch drift, damage, and expired goods.

What is a reorder point and how do I work it out?
A reorder point is the stock level that triggers a new order. Multiply average daily sales by supplier lead time in days, then add a small safety buffer. When the count falls to that level, place the order.

How do I track stock across Etsy and my own website?
Keep one master spreadsheet and run a weekly channel check. Compare what each channel shows for your best sellers with the master list, and lower any listing that shows more than you hold. Export listing data from each channel instead of typing numbers from a screen.

What should I do with stock that never sells?
Check it monthly. Cut the price, bundle it with a best seller, or give it away with a larger order. Stop reordering it, and use the dead stock list to guide your next supplier order so the same mistake does not repeat.

Leave a Reply

Your email address will not be published. Required fields are marked *