Stock control in Excel: when the spreadsheet stops coping

Keeping stock records in Excel is a perfectly sensible starting point for many small businesses. A spreadsheet costs nothing extra, it is already on every computer, and stock levels can be written down the same day. Excel does have a limit, though, and beyond it the spreadsheet starts costing more working time and money than it saves. This article looks at where that limit falls, which signs show you have crossed it, and what stock software does differently.

When Excel still works

Excel is fine for stock records as long as one person edits the file and there are only a few movements a day. If you take in the goods yourself, enter the sales yourself and run the stocktake yourself, the file is under your control and every error is yours to find. In that situation there is no point paying a monthly fee for software, because the spreadsheet does the same job for free.

It is important to understand that the limit is not set by the number of items. Two hundred products can be perfectly manageable, and so can two thousand, as long as there are only a handful of movements a day. What matters is the number of movements per day and the number of people opening the file. Excel is built as a single-user tool. Two people cannot safely edit the same file at the same time. In the cloud version you get conflicts and overwrites, where one person's entry disappears under someone else's save. The second weak point is the absence of a transaction log. The file shows the current position, but not who changed a quantity, when, or why. If the stock figures do not add up, there is no way to trace afterwards where the error started.

Four signs you have crossed the line

The surest way to recognise the limit is to look at the daily work rather than the size of the file. The following four signs show that stock control in Excel has started to cost more than decent stock software for a small business would.

• The stock figure in the file does not match what is actually on the shelf. Every stocktake brings a surprise, and the corrections can take a whole day.
• A salesperson promises a customer goods that are not in stock, because the copy of the file on their computer was out of date.
• Formulas break. Deleting, copying or sorting a row can break references without warning, and the error may only surface weeks later.
• Reconciling the file with the actual stock position can take several hours every month, and that work creates no value at all.

If at least two of these sound familiar, the spreadsheet has probably passed its limit. There is one further problem that often goes unnoticed. Anyone who opens the file can also see purchase prices and margins, because Excel cannot grant rights by item or by warehouse. In stock software the picker sees quantities and the purchasing manager sees prices, and neither has to see more than the job requires.

Several warehouses and production

There are two situations in which a spreadsheet nearly always breaks down: the arrival of a second warehouse, and production, where components become a finished product. Both require a single event to create several stock entries at once, and that is exactly what is hardest to do consistently by hand.

Two warehouses in a spreadsheet

Running two warehouses means every movement has to be recorded twice. Goods go out of one warehouse and into the stock sheet of the other. Done by hand, one half is often left undone, and then one warehouse shows goods that have already reached the other site or are in a van on the way. The more movements there are between the two warehouses, the faster the gap grows between the spreadsheet and the shelf. Stock software makes both entries in one action, so the movement is never left half finished.

Production records for a small business

Production records for a small business require a recipe, in other words a list of the components and quantities that make up the finished product. Every unit produced has to write the components off stock automatically and add the finished product to stock. In Excel that means a web of formulas that can break when a row is deleted or sorted, without any warning. If you have ten recipes and products are finished every day, writing off by hand quickly becomes the place where errors appear and the component stock figures lose their credibility.

What stock software does differently

The point of stock software is not a prettier table. The point is that the data sits in a database where every entry leaves a trace and where several people can work at the same time. That is where the practical advantages come from, and a spreadsheet cannot offer them.

• The transaction log shows who changed each quantity and when, so the reason for a stocktake difference can be traced.
• Barcode scanner support reduces manual entry errors on goods-in and picking.
• Rights can be granted by role, so purchase prices and margins are not visible to everyone.
• Stock levels can be linked to a web shop or a till, because an interface needs a database, not a file on a server drive.

Stock software in Estonia is usually priced as a monthly fee, either per user or per company. On top of that, allow for the one-off cost of setup and importing opening balances. An exact figure cannot be given without knowing the number of users and the interfaces required. A useful comparison is the real cost of staying in Excel: the working time spent on manual entry, stocktake corrections and dealing with surplus goods caused by errors. Add that time up and the monthly fee is often lower than the cost of reconciling by hand every month.

How the move from Excel works

The move happens step by step, and the most time-consuming part is not configuring the software. What takes longest is tidying up the item records: consistent codes, consistent units and no duplicates. If the same product appears in the spreadsheet under three different names, that confusion travels into the new system, because software does not fix untidy data. Once the item records are in order, the rest of the move goes surprisingly quickly, as most systems can import an Excel file directly.

A sensible order looks like this. The first step is cleaning up the item records in Excel itself, because that is the quickest place to do it. The second step is choosing and configuring the software, setting up warehouses, bin locations and user rights. The third step is importing the opening balances, which pairs well with a scheduled stocktake, so the new system starts with the real stock position rather than the numbers from the old file. The fourth step is a short parallel period, running both systems side by side and reviewing the differences before the spreadsheet is finally set aside. The fifth step is archiving the file: the old spreadsheet stays available for looking up history, but nobody enters anything into it any more.

If you would like a second opinion on whether your spreadsheet is still coping, you can find us at contact and free audit. We will go through your current stock records with you and give an assessment of whether Excel is still doing its job or whether it is time to move on. For an overview of what our services cost, see the prices page.