Cleaning a production spreadsheet can create a dangerous feeling of relief. Duplicate item names have been merged. Units look consistent. Old formulas have been labeled. Inventory balances have been reviewed.
But clean-looking rows are not the same as trustworthy production data.
A material can have a tidy name and still use the wrong purchasing unit. A formula can be marked “current” without connecting to its packaging. An opening inventory balance can match the spreadsheet while disagreeing with the shelf. These problems often stay hidden until records are connected inside a new system.
Before importing everything, run a small migration rehearsal. Use 25 carefully chosen records from one real product family and require them to pass defined checks. The goal is not to make a perfect demo. It is to find mistakes while they are still small, visible, and inexpensive to correct.
Start after cleanup—not instead of it
A rehearsal does not replace production data cleanup. First, standardize the essential records: items, units, suppliers, formulas, lots, locations, inventory states, costs, and ownership.
If that work is not complete, begin with the guide to the eight production data sets to clean before migration.
The rehearsal begins when the team believes those records are ready. It asks a different question:
Can these clean records survive being connected, calculated, and used in a realistic production flow?
That distinction matters. Reviewing rows one by one may reveal spelling errors. Running a product from material receipt through finished inventory reveals whether the rows agree with one another.
Build a 25-record rehearsal pack
Choose one active product that represents normal work. Avoid the simplest item in the catalog, but do not start with the product that has every possible exception.
Assemble approximately 25 records:
- one finished product;
- one current formula or bill of materials;
- eight to twelve ingredients or materials;
- two to four packaging items;
- two supplier records;
- two recent supplier lots;
- one storage location;
- one recent production batch;
- one quality or release status;
- one opening finished-goods balance.
The pack should include purchasing and production units, a formula version, real lot identities, and quantities that can be checked against physical evidence.
Keep the original source files read-only. Perform the test import in a safe test environment or an import template that cannot change live production records.
Define acceptance tests before importing
Do not decide that the rehearsal “looks good.” Define what passing means before anyone sees the result.
| Test | Passing evidence |
|---|---|
| Identity | Every item has one unique code, name, type, and status |
| Units | Purchasing, storage, and production units convert correctly |
| Formula | The approved version uses valid items, units, and expected yield |
| Inventory | Opening quantities reconcile to a dated count |
| Relationships | Suppliers, lots, locations, batches, and finished goods remain connected |
| Ownership | A named person can approve or reject every correction |
| Retrieval | Another trained person can find and explain the records |
Write the expected result beside each test. If a case of 24 bottles should become 24 individual bottles, record that expectation. If a 10-kilogram material lot should support four planned batches after reservations, calculate the expected remainder before import.
This is the heart of production data validation: the team decides what correct means before a new screen presents an answer.
Trace one product from receipt to finished stock
After loading the sample, follow the physical sequence.
Start with a supplier receipt. Confirm the supplier item, internal material, lot number, quantity, unit, date, and storage location. If the business purchases by the case but consumes by the unit, test the conversion.
Next, open the approved formula. Confirm that every ingredient and package points to the intended item—not merely an item with a similar name. Check the formula’s unit basis and expected yield.
Create a test production run. Confirm that planned requirements use the correct formula version and quantities. Record actual usage, including one modest difference from plan. Complete the run with a realistic yield and place the finished units into the correct status: released, on hold, or awaiting a check.
Finally, trace backward. Can someone start with the finished batch and identify the formula version and material lots used? Can they start with a supplier lot and find the test batch that consumed it?
A manufacturing data migration is not ready if the numbers arrive but the relationships disappear.
Reconcile four totals
At the end of the rehearsal, compare four totals with independent evidence:
- Material received: Does the imported receipt match the supplier document?
- Material remaining: Does receipt minus actual usage equal the expected balance?
- Finished quantity: Does completed output match the batch record?
- Available quantity: Does the result distinguish released stock from held, damaged, or reserved stock?
Record the difference for each total. A zero difference is ideal, but the important result is explainability. If five jars were rejected, the finished total should not quietly shrink. The record should show what happened to them.
Do not force a balance to match by overwriting it. Correct the source definition, conversion, transaction, or status that caused the difference. That discipline makes inventory data validation useful instead of cosmetic.
Keep an exception log
Every failed test should create an exception with five fields:
- the record or relationship affected;
- expected result;
- observed result;
- probable cause;
- owner and next action.
Group causes into useful categories: source-data error, unclear definition, mapping rule, system configuration, workflow gap, or training issue.
That classification helps prevent the wrong fix. A unit conversion error should not be covered with an inventory adjustment. An unclear item-creation rule should not be treated as a one-time typo.
The broader guide to moving beyond production spreadsheets can help place this data work within the larger transition plan. The five-scenario production spreadsheet stress test is also useful when you need to expose relationship failures before migration.
Require sign-off before expanding
The rehearsal is complete when the expected results and imported results agree, every important exception is resolved or explicitly accepted, and another trained person can follow the records without relying on the spreadsheet owner’s memory.
Ask the owners of inventory, formulas, production, and finance-sensitive values to approve their areas. One person should own the final decision to expand the import.
Then repeat the rehearsal with a second product family containing a different complication: another unit conversion, split supplier lots, seasonal packaging, a revised formula, or held finished goods.
Only after both samples pass should the team consider a larger spreadsheet migration. Before selecting or configuring a system, the 12-question small-batch software workflow test can help confirm that the tool follows the same real movements.
Practical takeaway
Clean records are ready for migration only when they remain trustworthy in context.
Choose one product family, assemble a 25-record rehearsal pack, write the expected results, run a connected production flow, reconcile four totals, and log every exception. If the sample cannot explain what was received, used, produced, held, and made available, importing more rows will only make the uncertainty larger.
Frequently asked questions
Should we use our easiest product for the rehearsal?
Use a familiar product with normal complexity. It should include real materials, packaging, units, and production history without introducing every rare exception at once.
What happens if several records fail?
Stop expansion and classify each failure. Correct source data and definitions before reimporting; fix mapping, configuration, or workflow problems in the appropriate place.
Should the rehearsal include old production history?
Include enough recent history to test formula versions, lot relationships, and balances. Historical records needed only for reference may remain in a controlled archive.
Who should approve the final import?
Assign one cutover owner, with separate sign-off from the people responsible for inventory, formulas, production records, and financially important opening values.
