Excel is flexible, affordable and easy to start with. Once warehouse, sales, purchasing and finance teams depend on the same stock figures, unrestricted editing becomes a risk.
Several people editing one spreadsheet is an early warning
Shared spreadsheets introduce version conflicts, inconsistent permissions, accidental deletions and overwritten data.
Batch tracking makes spreadsheets complex
Quantity tracking may be manageable. Batches, expiry dates, suppliers, customer orders and traceability call for a more structured system.
Recurring stock discrepancies point to missing records
An inventory system should record every receipt, issue, adjustment and stocktake, rather than simply replace the spreadsheet interface.
Start small before building a full WMS
Start with items, balances, receipts, issues, stocktakes and search. Add scanning, storage locations, approvals and reporting as needed.
Engineering judgment · context matters
Stock balances should come from an auditable movement ledger
The problem is not spreadsheet size. People can change a balance without recording why. Separate purchases, sales, transfers, stocktake adjustments, returns and reservations. Trace every change to its document, operator and time.
Do not copy every old spreadsheet into the new system. Migrate batches with stock and unfinished transactions; archive completed records for reference. During parallel operation, reconcile daily and classify differences as missing entries, duplicates, unit errors or cut-off issues. Do not simply adjust totals until they match.
Illustrative scenario: from Excel stock sheets to traceable warehouse operations
This scenario explains an engineering approach. It is not a verified client delivery or evidence of business results. Actual project scope depends on the information reviewed.
Using Excel is not itself a problem. Trouble starts when several people edit the same data, batch rules rely on memory and stocktake differences cannot be traced. Consider a warehouse with separate sheets for receipts, production issues and finished-goods dispatch: managers see month-end totals while staff manage individual physical movements each day.
Business workflow and product scope
An initial release might cover item records, batches and locations, receipts, issues, stocktakes and stock search. A scanning workspace reduces typing; an admin interface handles master data and exceptions. Managers see low stock, aging stock, pending counts and discrepancies. Keep spreadsheet import and export, but use the system ledger as the source of truth.
Technical approach
Use an immutable movement ledger recording the document, item, batch, location, quantity, unit, operator and timestamp. Update the balance cache in the same transaction and support rebuilding it from the ledger. Give scans terminal event IDs for idempotency. Stage imports for field mapping, unit conversion and duplicate checks before posting them.
Difficult design decisions
Unit conversions, batch splitting, negative stock, incoming goods and movements during stocktakes need explicit rules. Define physical, available, reserved and incoming stock first. Use a stocktake snapshot and adjustment documents instead of overwriting balances. Have business owners approve unresolved item names, missing batches and unusual units before migration.
User experience
After scanning, show the item, default location, recent batch, operation quantity, available stock and resulting balance. Preserve context for repeated work. Provide recovery paths for partial picking, incorrect scans and disconnections. Keep frequent tasks quick, while requiring reasons and impact review for risky adjustments.
Validation and acceptance
Run the system and previous records in parallel for a stocktake cycle, tracing differences document by document. Agree measures such as discrepancies, item lookup time, missed entries, counting effort, batch traceability and adjustments. The system is ready to replace spreadsheets when key items can be traced from receipt to use through their original documents.
The challenge is more than building a stock table
Quantity should be the result of recorded transactions. A traceable model includes items, warehouses, locations, batches, balances, documents and movements. Receipts, issues, transfers, counts and returns create ledger entries linked to source documents, then update balances. A discrepancy can then be traced back to a transaction and operator.
Separate available, physical and reserved stock
Orders may reserve goods before picking or dispatch, while items under inspection may be unusable. A single quantity field can lead to double allocation or stock that appears available but cannot ship. Define on-hand, available, reserved, blocked and incoming quantities, then specify how each business action affects them.
Enforce stock deductions in the database
A browser stock check cannot prevent two people issuing the same batch. Use transactional conditional updates, row locks or version checks to prevent over-allocation. Business references and idempotency keys prevent repeated scans and submissions. Write the movement and balance together so neither can exist without its matching record.
Establish master data before migrating from Excel
Old sheets often contain duplicate item names, inconsistent units, missing batches and varied location names. Agree item codes, conversions, warehouses and location dictionaries before mapping records. Initially import verified opening balances and essential batches, retaining old movements as an archive. Schedule a write freeze, physical count and discrepancy approval for cutover.
Test reconciliation and traceability
Acceptance should include finding an item's location and batch, tracing any balance to movements and documents, recording stocktake adjustments and preventing duplicate deductions. Run representative receipt, transfer, issue, return and dispatch documents through the complete workflow before deciding whether it is ready.
Changing the data model comes before changing the interface
A balance is a calculated result, not a number to overwrite
Separate items, warehouses, locations, batches, movements and balances. Each receipt, issue, transfer, freeze and count creates a ledger entry with a business reference. Update the balance transactionally or rebuild it from the ledger. Corrections need adjustment documents with reasons and approval, so a missing quantity can be explained.
Database constraints must protect concurrent deductions
Reserve stock when an order requires it, convert the reservation into a deduction on dispatch, and release it on cancellation or expiry. Lock rows or check versions before updating balances. If affected rows differ from expectations, recalculate. Unique constraints on business references and action types prevent scanner retries from creating duplicate issues.
Clean historical data before importing it
Standardize item codes, units, warehouse names and batch formats. Identify duplicate SKUs, negative balances and missing dates. Freeze old writes, import opening balances and reconcile item counts, batches, totals and sampled details. Put unmapped records into an exception list for business approval instead of guessing defaults.
Plan for disconnections, incorrect scans and reversals
Show the item, location and running quantity immediately after a scan, with clear duplicate warnings. An offline queue needs client_event_id values for safe replay. Reverse approved documents rather than deleting them. Retain operator, device, time and before-and-after values for traceability.
Start with these questions
- Do several people maintain the inventory spreadsheet?
- Do you need batch tracking or stock movement records?
- Are stocktake differences frequent?
- Do staff need barcode scanning or mobile access?