A spreadsheet is a good first inventory system. It costs nothing, everyone can read it, and for one location, one person and a few hundred products it does the job.
The trouble is that it keeps appearing to do the job long after it has stopped. Nothing in the spreadsheet tells you it is wrong. Here are the signs that it is, and what moving off it involves.
Eight signs
1. Two people have it open at once. The moment a second person records movements, you have two versions of the truth. Shared spreadsheets help a little, until two people edit the same cell in the same minute.
2. You cannot say who changed a number. A quantity went from 120 to 20 last Tuesday. Nobody knows who typed it or why. A stock record without an author and a reason is a guess with a date on it.
3. Stock goes negative. A cell showing minus 14 is not a quantity. It means a stock-out was recorded for units that were never recorded as arriving, and the error is somewhere upstream, unfound.
4. The year-end value is a formula nobody trusts. Quantity times the last price paid is not a valuation. When purchase prices change, the units on the shelf were bought at different costs, and a spreadsheet rarely knows which.
5. You found out a product was out of stock when a customer asked. Low-stock warnings in a spreadsheet depend on someone opening it and looking at the right column. Reordering by feel is what the spreadsheet was supposed to replace.
6. Purchase orders live somewhere else. Orders are in email, deliveries are in the spreadsheet, and invoices are in the accounting system. Reconciling what was ordered, received and paid means opening three windows.
7. A second location appeared. A shop, a van, a storage unit. The spreadsheet gets a second tab, transfers between them are typed twice, and the two tabs disagree within a month.
8. The file is slow to open. Formulas across thousands of rows, a tab per month, and a copy on everyone’s desktop named final-v3. The spreadsheet has become a database, run by hand.
Two or three of these are an inconvenience. Five or more mean the spreadsheet is costing more in errors and time than a system would.
What to clean before you move
Moving is the best chance you will ever get to clean the list. Do it in the spreadsheet, before the import, where fixes are quick.
- One row per product, one code per row. Merge duplicates. Decide on a product code format and apply it.
- Remove products you no longer stock. They can be archived later, but a shorter list imports and checks faster.
- One number format. Either 1234.56 or 1.234,56 across the whole file, not both.
- Supplier names spelled one way. “Acme”, “ACME Ltd” and “acme ltd” become three suppliers if you let them.
- Count before you move. Opening stock should be what is on the shelf, not what the spreadsheet says. A fresh count at the start is the one count that pays for itself immediately.
- Find the cost of what you hold. Opening stock needs a value as well as a quantity, or the valuation starts wrong on day one. The last purchase price per product is usually a fair approximation.
What moves and what does not
Products, suppliers and opening stock move. Movement history usually does not, and it should not. The spreadsheet’s history is the record it was, with its gaps and its unexplained edits. Keep the file as an archive, start the new system from a counted opening position on a chosen date, and let the clean history begin there.
Open purchase orders are the one thing to enter by hand, so that deliveries can be received against them.
How the move works in StockBold
StockBold imports products and suppliers from a CSV file that follows its template. Before anything is saved, the check shows which rows are ready and which need fixing, row by row. Numbers can be written as 1234.56 or 1.234,56, as long as the file uses one style; ambiguous numbers are flagged. EAN-13 and UPC-A barcodes with a wrong check digit are flagged too.
For each product, the opening stock goes in with its quantity, total value, location and date, so the valuation is right from the first day. Rows that share a product code can be merged into one product, adding up quantities and values. Suppliers, categories and units named in the file but missing in StockBold are created during the import, and you see the list before confirming.
From then on, every change to stock is a recorded movement with a date, a reason and an author, a location can never go below zero, and the stock value on any date is rebuilt from the history.
Read more about CSV import and the API, or the help guide on importing your data.