From Excel to warehouse software without losing a week

Clean up the product list before you import it

A spreadsheet kept for a few years almost always hides the same product under several names. "Bolt M8x40", "bolt m8 40mm" and "B M8x40 galv." are one thing to you, but the program will treat them as three products with three separate stock levels. So the first job is the list, not the installation. Sort the sheet by name and by barcode and merge the repeated rows. Do not carry over products that have been neither sold nor delivered for a year or two. If you need them again, you can create them then.

Every product needs one code that never changes and is never handed to another product later. Settle the base unit too: if you buy cable in 100 m reels and sell it by the metre, the base unit is the metre and the reel is a pack. Check the barcodes — one barcode cannot sit on two products. Where a barcode is missing, leave the field empty rather than making up a number. Before the import, the sheet should have at least these columns:

• Code — one per product, and permanent.
• Name — the way your people search for it.
• Unit, and the pack if there is one.
• Barcode, if the product has one.
• Group or category.
• Purchase price and selling price.

Pick a go-live day: count first, then start

Opening stock is the number the program starts counting from. Take it straight from the spreadsheet and you carry over all of its errors. It is safer to choose a quiet day — a Friday afternoon or a Saturday, say — stop receiving and dispatching for a few hours and count the goods. The counts go in as opening stock, and everything after that is recorded in the program only.

Decide separately what to do with goods that are on the way or already sold. A delivery that has left the supplier but not arrived is not counted — it goes into the program when you receive it. Goods that are sold but not yet shipped are on the shelf, so they are counted and then dispatched or reserved for the customer straight away. In a large warehouse you can count the slow-moving products a day or two early, as long as you record everything that leaves them before go-live.

Customers and suppliers: bring across the ones you work with

The customer list in Excel builds up over the years too: companies that stopped buying long ago, the same customer on two rows spelled two ways, a phone number in the address field. Bring across the customers who have bought in the last year or two, and check them by company registration number — that is the quickest way to catch duplicates. For suppliers, keep their own code for each product if the new program has a field for it. Your next order to them can then go out without digging through their catalogues.

Do not enter unpaid amounts as one lump sum per customer. Bring them across document by document — which document, what date, how much is left — and reconcile them with your accounts before go-live. Otherwise, when the first payment arrives, nobody will know exactly what it pays. In SellM8 Warehouse, for example, customers are imported from a CSV file, and we plan the transfer of products and opening stock together with the client before go-live.

Prices: decide which ones stay before you import them

In a spreadsheet, prices often sit in several columns — "price", "new price", "old wholesale" — and only one person knows which one applies. Before the import, decide which prices the program will keep: retail, wholesale, purchase. Record discounts for individual customers as a rule, for example "5% on the whole fasteners group", not as a price corrected by hand on each row. If you are unsure of a purchase price, leave it empty and fill it in at the next delivery rather than entering a guess. The program will calculate profit from it.

Running the spreadsheet and the program side by side: best avoided

Keeping the spreadsheet and the program going in parallel feels safe, but in practice it doubles the work. By day three someone forgets to record a sale in one of them, and nobody knows which count is right. So after go-live, stock is kept in one place — the new program. Rename the old spreadsheet, for example "Stock — OLD, do not edit", and keep it for reference only. The same goes for an old desktop program: it can stay open for looking up past documents, but with no new entries.

A trial run belongs before go-live, not after. A day or two early, enter real documents from the previous day — one delivery, a few sales, one return — and check that the stock comes out as it does in the spreadsheet. Then delete the trial data or start on a clean database. That way, mistakes in the setup show up while nothing depends on them yet.

Train the people who will use it every day

Training the manager alone is not enough. The program will be used by the warehouse staff, the sales reps and the person in the office who issues documents, and each of them needs different things. Run short sessions by role, with your own products and with the phones and scanners people will actually hold. A warehouse worker should be able to receive a delivery and dispatch an order without asking. Do this before go-live day, not on it.

A single sheet pinned up by the warehouse computer helps too: how to receive a delivery, how to dispatch, and what to do after a mistake. And choose one person who knows the program best, for the others to go to with questions in the first few weeks.

Who may correct what

In Excel, anyone who opens the file can change any cell, and afterwards nobody knows who did it. In the program, settle this in advance. The warehouse worker receives and dispatches but does not correct stock. Corrections are made by the warehouse manager, always with a reason — "broken during unloading", "error in the opening count". One person changes prices. Decide, too, who sees purchase prices. Everyone signs in as themselves, not under a shared "warehouse" user — otherwise the change log is of no use.

The first week: what to check every day

The first days show where the transfer was inaccurate and where people still work the old way. Set aside half an hour at the end of each day for a few checks. If the same mistake keeps coming back, the cause is usually a habit or a setting, not the person — fix that.

• Count five to ten products that moved during the day and compare them with the program.
• Check that every delivery of the day has been received in the program, not just unloaded.
• Review the stock corrections: who, when and for what reason.
• Look for products with stock below zero — usually something was dispatched before it was received.
• Write down people's questions and go through them together at the end of the week.