Moving from spreadsheets into a production system can feel like a clean start. It is not a clean start if duplicate items, uncertain units, old formulas, and unverified balances make the trip with you.
Production data cleanup means deciding what each record means, whether it can be trusted, who owns it, and whether it belongs in the new system. Done before a spreadsheet migration, it gives the team a more credible opening position.
Choose the first migration scope before cleaning everything
Do not begin by fixing every file the business has ever created. Choose one active product family that includes typical materials, packaging, a current formula, recent production, and sellable inventory. This creates a manageable first scope and reveals the relationships the new system must preserve.
List the files that support that flow, including private trackers, shared sheets, paper records, and ecommerce exports that influence production decisions. Identify one source of truth for each record type.
If you are still deciding whether the workbook is safe enough to repair, start with this [seven-sign production spreadsheet risk check](https://resources.buildwithkerno.com/production-spreadsheet-risk-seven-warning-signs/).
Clean these eight production data sets
1. Item names, codes, and status
Give each material, package, work-in-process item, and finished product one unique name and code. Merge true duplicates, but do not combine items that look similar while differing in grade, size, supplier specification, color, or approved use.
Record whether each item is active, seasonal, on hold, or discontinued. Keep obsolete identities only when they are needed to understand old batches.
2. Units and conversions
A material may be purchased by the case, stored by the bottle, and consumed by weight. Define each unit and test every conversion with a real supplier pack and production example.
Do not guess at conversions to complete a field. Mark uncertain values for correction. A small error can distort available quantity, reorder needs, formula usage, and cost at once.
3. Supplier and material identity
Standardize supplier names, item numbers, lead times, pack sizes, and approved alternatives. One internal material may have several approved sources; two similarly named supplier products may not be interchangeable.
Inventory data cleanup should show which supplier item maps to each internal material and when it may be used.
4. Formula and bill-of-material versions
Identify the current approved formula or bill of materials for each product. Confirm quantities, units, yield basis, packaging, effective date, and approval status. Preserve prior versions when old batches depend on them.
Do not create one “clean” formula by overwriting evidence of past changes. The new system needs a current standard and enough version history to explain what earlier production actually used.
5. Lot, batch, and production identifiers
Review how incoming lots, production batches, packaging runs, and finished goods are identified. Remove accidental formatting differences and document the future pattern.
Check whether recent production records connect batches to actual material lots, formula versions, yields, and release status. List missing relationships as exceptions; do not invent them during import.
6. Locations and inventory states
Standardize locations such as receiving, storage, production, quarantine, and finished-goods shelves. Define the states that change availability: reserved, issued, work in process, on hold, released, rejected, or damaged.
A quantity can be physically present without being available. Make that distinction explicit so the imported record does not promise materials or finished goods that cannot be used or shipped.
7. Quantities, costs, and opening balances
Decide which date will establish opening inventory. Reconcile spreadsheet quantities with a dated physical count as close to cutover as practical. Investigate important differences rather than forcing the file to match the shelf without a reason.
Review cost fields separately. Confirm what each cost includes, its date, currency, and unit basis. Ask an accountant to review opening values when they affect financial reporting.
8. Ownership, corrections, and retained history
Name an owner for item creation, unit changes, formulas, adjustments, and migration approvals. Define how corrections are recorded. Clean data will drift if everyone can create a new spelling or unit.
Preserve original spreadsheets as read-only records under the business’s retention policy. Limit access to sensitive formulas, supplier pricing, costs, and customer information. The FTC’s [Start with Security guide](https://www.ftc.gov/business-guidance/resources/start-security-guide-business) offers practical principles.
Use a keep, correct, or archive decision
For every record in scope, choose one action:
- **Keep:** active, clearly defined, and supported by a trustworthy source.
- **Correct:** needed in the new system, but a name, unit, status, balance, or relationship must be verified first.
- **Archive:** obsolete, duplicative, historical, or too uncertain for daily use, while still retained when history or policy requires it.
Do not use “delete” as a shortcut for uncertainty. Archive source evidence, document unresolved gaps, and avoid importing questionable records as if they were approved facts.
Build a migration map before the first import
Create a table with the source field, destination field, format, transformation rule, owner, validation check, and exception decision.
“Qty” is not enough. Is it physical on hand, available after reservations, or an old manual total? “Cost” might mean last purchase price, average material cost, or finished-product standard cost. Define it before mapping.
The broader [guide to moving beyond spreadsheets](https://resources.buildwithkerno.com/moving-beyond-spreadsheets-digital-tools-for-production/) explains how workflow mapping, access, piloting, and downtime planning fit around this data work.
Test one product family from end to end
Load a small sample first. Trace one product through supplier receipt, material availability, formula use, production, quality status, finished inventory, and a count adjustment. Compare source values with imported values at every step.
Include a common exception: a split lot, unit conversion, formula revision, lower yield, or quality hold. Use this [12-question workflow test for small-batch manufacturing software](https://resources.buildwithkerno.com/small-batch-manufacturing-software-workflow-test/) to check the connections.
Set a cutoff for old-file transactions. Reconcile any temporary parallel entry daily and end it on a defined date, or the business creates two competing sources of truth.
Kerno is being built to connect materials, formulas, production, costing, quality, and finished inventory. Whether you move into Kerno or another dedicated system, clean definitions and verified opening data are what make the new records useful.
Practical takeaway
A manufacturing software migration should not begin with “import everything.” Begin with one active product family and eight clean data sets. Decide what to keep, correct, or archive; map every field; verify opening quantities; and prove the workflow before expanding.
For more implementation context, review how to [choose an inventory system around real material and production movements](https://resources.buildwithkerno.com/choosing-the-right-inventory-system-for-small-business/).
Frequently asked questions
Should we migrate every old production record?
No. Import active records and the history needed for operations, traceability, service, or policy. Preserve other source files as controlled archives instead of crowding daily work.
How do we handle inventory quantities we do not trust?
Perform a dated physical count, investigate material differences, record approved adjustments, and use the reconciled quantity as the opening balance. Do not label a guess as verified inventory.
What should we do with duplicate material names?
Confirm they describe the same specification, unit, and approved use before merging. Keep aliases in the cleanup map so old records can still be understood.
When should we perform the final physical count?
As close to cutover as practical, during a controlled period with limited movement. Record receipts, production usage, completions, sales, and adjustments that occur after the count.
Can we clean data after the new system goes live?
Some improvement will continue, but critical names, units, formulas, statuses, and opening balances should be verified first. Cleaning them later can force corrections across connected transactions.
