Inventory Systems, and the Point Where a Spreadsheet Stops Working

Inventory is the largest number on most small trading businesses’ balance sheets and the least accurate one. The gap between what your records say you have and what is physically on the shelf is where margin quietly disappears — through goods that expired, goods that were never counted, goods sold at a price based on the wrong cost, and goods reordered because nobody could see the ones already in the back.

An inventory system is not a list of products. It is a record of movements — what arrived, what left, what it cost, and when. That distinction is the whole subject, and it is why a spreadsheet works until suddenly it does not.

The short answer

• Buy an inventory system when you have sold something you did not have, or when reordering has become guesswork. Not before.
• The number that matters is not quantity. It is landed cost — and in Oman that is not the supplier’s invoice price.
• A perpetual inventory system that is never physically counted is worse than a spreadsheet, because people trust it.
• The system’s job is to answer three questions: what do I have, what did it cost, and what should I reorder.

The Omani arithmetic nobody puts in the spreadsheet

Suppose you import goods invoiced at OMR 10,000 CIF. Two charges land on top, and they behave completely differently.

Customs duty at the GCC common external tariff — 5% on the CIF value for most goods — adds OMR 500. That money is gone. It is a cost of the goods, and it belongs in the cost of every unit in the shipment.

Import VAT at 5% is calculated on the duty-inclusive value, so on OMR 10,500, which is OMR 525. But if you are VAT-registered, that is recoverable input tax. It is not a cost of the goods at all; it is a cashflow event.

So the combined charge on import is about 10.25% of CIF — 1.05 × 1.05 — but only half of it is cost. The half that is cost has to be spread across the units in the shipment, along with freight, insurance, clearance and inland transport. That figure is your landed cost, and it is the number your margin is calculated from.

This is the specific reason spreadsheets under-report cost in importing businesses. The spreadsheet almost always holds the supplier’s invoice price, because that is the number on the document. Every gross-margin calculation downstream is then optimistic by whatever duty and freight actually were — frequently ten to twenty per cent of the goods value, and never evenly distributed, because freight on a container does not divide neatly across mixed cartons.

The first question to ask any inventory system: can it apportion a landed cost across a shipment, and how? If the answer is that you enter a unit cost manually, you have bought a more expensive spreadsheet.

The four ways a stock spreadsheet fails

Spreadsheets handle inventory longer than software vendors admit. They fail in four specific ways, and none of them is about the number of rows.

Two people need it at once. The moment stock is being received in the warehouse while an order is being picked at the front, one file cannot be the truth. Emailing versions around produces several truths and no way to identify the current one.

It records state, not events. A spreadsheet cell says the stock level is 40. It cannot tell you it was 55 on Tuesday, that 12 went out on an order and 3 were written off as damaged — which is exactly what you need when the number is wrong. Every real inventory system is a movement ledger with the balance derived from it, not the other way round.

No validation. Nothing stops someone typing 400 instead of 40, and once typed, the wrong number is indistinguishable from a right one.

No reorder logic. A spreadsheet will not tell you today that an item needs ordering. Someone has to look, and someone eventually will not.

If none of those four is happening to you, keep the spreadsheet. It is free, everyone can use it, and it will tell you honestly when it stops working.

What the system actually has to do

Four capabilities, in order of how much money they save.

1. Cost the stock correctly

Every system uses a costing method, and you should know which one before you buy. The two you will meet are weighted average cost — recalculated each time you receive stock at a new price — and FIFO, which assumes the oldest units sell first. Both are legitimate. What matters is that the system applies one consistently and can show its working, because that number becomes your cost of goods sold, which becomes your taxable profit.

The one to avoid is a system that lets a user type a cost price directly over the calculated one. It will be done, once, in a hurry, and after that the number is fiction with no audit trail.

2. Tell you what to reorder, before you run out

The mechanism is a reorder point: the stock level at which you must order to avoid running out before the new stock arrives. It has two inputs, and both are knowable.

Reorder point = (average daily sales × supplier lead time in days) + safety stock

If you sell 8 units a day and your supplier takes 21 days, you consume 168 units while waiting. Ordering at 100 guarantees a stockout. Safety stock is the buffer for the weeks when demand or the supplier misbehaves — and for goods coming through customs clearance, the variability in lead time is often larger than the variability in demand.

This one calculation, applied to your top twenty items, recovers more money than any other feature in an inventory system. It is also entirely doable in a spreadsheet, which is why the reorder point is the honest test of whether you need software: if you would calculate it and you do not, the software is buying you the discipline, not the arithmetic.

3. Distinguish items that must be tracked individually

Most stock is fungible — one tin is identical to the next. Some is not, and mixing the two in a system that only handles the first is a recurring problem.

• Batch or lot tracking. Necessary if you need to know which shipment a unit came from — for a recall, a supplier quality dispute, or a warranty claim against your supplier.
• Expiry dates. Non-negotiable for food, cosmetics, and anything with a shelf life. A system that cannot warn you at 60 days is a system that will show you the write-off at 0 days.
• Serial numbers. For high-value items where the individual unit matters — electronics, equipment, anything under warranty.

Ask about these specifically. They are frequently present in the demo and absent from the tier you were quoted.

4. Handle more than one location

The moment stock sits in two places — a shop and a store, two branches, a warehouse and a van — you need per-location quantities and a recorded transfer between them. A single total across locations is the same as no information: you cannot fulfil from a total.

Counting: the part everyone skips

Every perpetual inventory system drifts. Goods break, get miscounted at receiving, walk out, or get sold without being scanned. The system does not know about any of it. Within a year the difference between the record and the shelf is large enough that people stop trusting the record, and once nobody trusts it, the system is decoration you pay a subscription for.

The fix is counting, and the practical version is not the annual shutdown.

Cycle counting counts a small slice of items continuously rather than everything at once. Combined with ABC classification — where roughly the top 20% of items by value account for most of your stock value — a workable rhythm is to count A items monthly, B items quarterly, and C items once a year. Nothing closes, and no single count is big enough to be postponed.

Two rules make the difference between counting and pretending to count:

• The counter does not see the system quantity. If the expected number is on the sheet, it is what gets written down.
• Every variance is investigated, not just adjusted. An adjustment hides the cause. A pattern of shortages in one category is theft, or a receiving error, or a scanning problem — and each has a different fix.

Where inventory has to connect

Inventory is rarely the system where a movement originates. It is the system where movements are recorded, which makes its connections more important than its features.

From the point of sale. Every sale must decrement stock. If it does not, your inventory is accurate only until the shop opens. This is the single most important integration and it is covered from the other side in POS systems for retail and restaurants.

To accounting. Closing stock value and cost of goods sold are accounting figures produced by the inventory system. If someone is retyping them quarterly, they are wrong quarterly. See accounting software for a small Omani business.

To and from e-commerce. An online store selling stock it does not have generates refunds and a review problem. The connection has to run both ways and it has to be quick.

Purchasing. The purchase order is what turns a reorder point into an actual order, and it is where landed cost is captured. A system that tracks stock but not purchase orders leaves the most expensive part of the process outside the system.

When several of these are true at once, the question stops being which inventory system and becomes how the systems connect — a different discipline, described in how system integration actually works.

What goes wrong

The data is dirty on day one. Migration is where these projects die. Starting from an inaccurate stock count means starting from a system that is already wrong, and it never recovers. Do a full physical count immediately before going live, and treat that count as the opening balance regardless of what the old records said.

Receiving is not disciplined. If goods reach the shelf before they are booked in, the system is behind reality every single day, and nobody knows by how much. Receiving is the control point. Everything downstream inherits its accuracy.

Nobody owns it. Inventory accuracy is a job. If it is everybody’s job, it is not being done. One named person should be accountable for the count, the variances and the reorder points.

Too many items. A catalogue with 4,000 lines, half of which have not sold in a year, is a maintenance burden that guarantees the data will not be maintained. Reducing the range is often more valuable than any software.

Six questions before you buy

1. How is landed cost apportioned across a shipment? Duty, freight, clearance. If it cannot, it does not fit an importing business.
2. Which costing method, and can I see the calculation?
3. Does it support batch, expiry and serial tracking — on the tier I was quoted?
4. How are stock counts entered, and does it support cycle counting?
5. What is the integration with my POS and accounting, and is it live or a file export?
6. Can I export the full movement history? Not the current balance — every movement. VAT records must be retained for ten years under Article 70 of the VAT Law, and a system you cannot extract history from makes that your vendor’s problem instead of yours.

Questions people ask

When is a spreadsheet no longer enough? When two people need it simultaneously, when you need history rather than current state, when a mistyped number cannot be detected, or when nobody is checking reorder points. Number of products is not the test — a business with 60 items and three locations outgrows a spreadsheet faster than one with 600 items in one room.

What is landed cost, and why does it matter more here? It is the total cost of getting a unit onto your shelf: supplier price plus freight, insurance, customs duty, clearance and inland transport. It matters because margins are calculated from it, and because in Oman the 5% GCC customs duty is a real cost while the 5% import VAT is recoverable input tax if you are registered. A system that treats both as cost overstates your cost; one that treats neither understates it.

Do I need barcode scanning? Not to start, but it is the cheapest accuracy improvement available. Manual entry has a keying error rate that scanning effectively eliminates, and the errors happen at receiving, which is the worst place for them. If your items already carry manufacturer barcodes, you are most of the way there.

How often should stock be counted? Continuously, in slices, rather than annually in one shutdown. Highest-value items monthly, mid-value quarterly, the long tail once a year. The annual full count is mostly a ritual — it finds the discrepancy nine months after the cause, when nobody can explain it.

Should the inventory system or the accounting system hold the stock value? The inventory system calculates it; the accounting system records it. They must agree, and one of them must be the source. If both are maintained independently they will diverge, and reconciling them becomes a permanent monthly task.

Is a free or open-source inventory system good enough? Often, for a single location with straightforward stock. The costs to check before committing are support when something breaks mid-week, whether it handles landed cost, and the export path. Those three, not the licence, are what determine the real price.