One stock for store and online: how not to oversell

A customer buys the last two units of a product on the website at 11:40. At 11:45, a salesperson in the store sells those same two units over the counter, because web and store stock are reconciled once a day. The next day someone calls the customer to cancel, refund the payment and apologize. The problem is not having two channels: it is having two different numbers for the same box of goods.

Selling in store and online without overselling depends on three things: separating stock types properly, deciding how much each channel sees and how often it is updated, and handling returns carefully. All of it can be run in a spreadsheet while volume is low.

Physical, committed, reserved and available

Not everything on the shelf can be sold. Keep these apart:

Stock type	What it is	Example
Physical	What exists in the warehouse or store	What a count would give you
Committed	Sold and confirmed, pending delivery	Paid web order not yet shipped
Reserved	Set aside temporarily, can be released	Order awaiting payment, item held for a customer
Unsellable	Exists but cannot be offered	Damaged, under review, expired
Available	What can really be sold right now	The number each channel should see

The base formula:

Available = physical − committed − reserved − unsellable

If the website shows physical stock instead of available, it will sell units that already have an owner.

Reservations: hold without blocking forever

A reservation holds stock while a purchase is completed. To keep it from becoming phantom stock:

1. Set an expiry. An order awaiting payment that is not paid within a defined window releases its reservation. The window depends on your payment methods and is your company’s decision.
2. Turn the reservation into a commitment when payment is confirmed. The available number does not change, but the status does.
3. Release on cancellation. A cancelled order returns its units to available immediately.
4. Review old reservations daily. If a reservation is older than a reasonable window, something went wrong.

How often to sync

If store and website share a single stock record, every sale deducts instantly and there is nothing to sync. If they are separate systems, the interval between updates defines your risk:

Units at risk = hourly sales of the other channel × sync interval (in hours)

With one sync a day, the risk is everything the store sells in a day. With one every 15 minutes, it is what it sells in a quarter of an hour. That number is what your buffer stock must cover.

Buffer stock per channel

Buffer stock is a quantity hidden from a channel to absorb sync lag and inventory errors:

Stock shown on the website = available − buffer stock

Buffer stock ≈ units at risk from sync + typical inventory error

The typical inventory error comes from your counts: if you usually find 2 units fewer than the system when counting a product, add them. For slow movers the buffer can be zero; for best sellers it is worth calculating one by one.

Worked example

Suppose a product with these hypothetical figures:

• Physical: 40 units.
• Paid web orders waiting to ship: 6.
• Orders awaiting payment: 3.
• Damaged units under review: 1.
• Store sales at peak hour: 12 units per hour.
• Sync with the website: every 30 minutes.
• Typical count error: 2 units.

Step 1. Available: 40 − 6 − 3 − 1 = 30 units.

Step 2. Units at risk: 12 × 0.5 = 6 units.

Step 3. Buffer stock: 6 + 2 = 8 units.

Step 4. Shown on the website: 30 − 8 = 22 units.

If you improve sync to every 10 minutes, units at risk drop to 12 × 0.17 ≈ 2 and the buffer to 4: the website could show 26. Better frequency frees stock to sell; raising the buffer only hides the problem.

When web available stock reaches zero, the product should show as out of stock or with a restock notice, never accept the order “in case something turns up”.

Returns and putting them back

A return does not go back to available the moment it comes through the door:

1. Receipt: the return is recorded and the item goes to “under review” (unsellable).
2. Inspection: someone checks condition, packaging and, where relevant, lot and expiry.
3. Decision:
   • Sellable: back to available in the warehouse or store where it physically sits.
   • Unsellable: written off, sent back to the supplier or sold as clearance, with its own record.
4. Deadline: set a maximum time to inspect. An uninspected return is stock invisible to every channel.

If the return has an associated invoice, the paperwork (credit note or other) follows the current electronic invoicing rules; validate it with your accountant.

Indicators

Indicator	How to calculate it	How to read it
Orders cancelled for lack of stock	Web orders cancelled for stock ÷ web orders × 100	Suppose 18 of 600: 3%. If it rises, review sync and buffer
Stock-out rate	Product-days with zero available ÷ total product-days × 100	High on A items: buffer or replenishment too low
Inventory accuracy	Products counted with no variance ÷ products counted × 100	Low accuracy forces larger buffers
Expired reservations not released	Reservations older than the defined window	Should be zero
Return processing time	Average days between receipt and decision	Every extra day is stock that does not sell

Common mistakes

• Showing physical stock instead of available.
• Syncing once a day a product that sells every hour.
• Reservations without expiry that pile up phantom stock.
• Putting returns back into available without inspection.
• Using the same buffer for every product.
• Fixing overselling by cancelling orders instead of improving the stock record.

Taking it into a system

With few products and few orders, a spreadsheet with these formulas prevents most overselling. As volume grows, an ERP such as MKA can run the online store on the same stock as your branch tills, with stock reservations in inventory (see the solution for stores and chains).