Practical guide / Batch Production & Materials 1.0
Close a batch with its actual materials and waste.
Connect a bill of materials to each batch, actual material issues, good output and scrap without double-counting inputs.
Your first useful result: Define the product recipe Follow the first task
Define the product recipe
Name products and materials; put each material once in the Bill of Materials with the quantity for one started unit.
The bill of materials supplies the standard use per started unit.
Review: Review the labelled results and correct source warnings before acting.
Close the batch count
Record started, good and scrap units on the production date. Good plus scrap must equal started.
Started, good and scrapped units stay distinct for each batch.
Review: Review the labelled results and correct source warnings before acting.
Post material issues
Enter each material quantity and its actual unit cost once for the batch. Review missing material lines and the cost difference.
Issued material quantities and costs reconcile against the standard batch requirement.
Review: Review the labelled results and correct source warnings before acting.
Check the assumptions.
- Save a working copy. Manual entry, with no automatic sync or external connections.
- Supplied tables have fixed capacities. Extend formulas, tables and validation together before using extra rows.
- Use one currency. Blank means unknown; enter 0 when the amount is known to be zero.
- Resolve the highlighted input rows before relying on the summary.
- Uses exact-mode XMATCH. Requires Excel 2021, Excel 2024 or Microsoft 365; older Excel editions are not supported. Native Excel, Google Sheets and Numbers have not been verified.
- One Bill of Materials row is one material per product. The per-unit standard is applied to every started unit, including scrap.
- Enter one Material Issues row per batch/material, including an explicit zero where none was issued. The cost is a dated price snapshot, not the current Materials reference price.
- Actual material cost divided by good output is a material-only rate. It excludes labour, overhead, tax, work in progress and selling price.
Keep a copy you can return to.
Keep the supplied clean and example files unchanged. Save your own working file with a useful name, such as batch-production-2027-home.xlsx, and keep a dated backup before a large edit. A purchased ZIP is the blank source, not a backup of your entries.
If an input or formula is replaced
Undo the edit while it is still available, or return to a known good backup. If you need a fresh clean file, transfer only your records into its marked input cells and follow the setup guide again. Do not paste whole sheets over the supplied formulas, validations or reference lists. Check IDs, units, dates and linked results against the example before relying on the copy.
When the supplied rows are full
80 products · 120 materials · 220 bill of materials · 100 batches · 300 material issues. Calculation ranges and validations have fixed capacities. A new row below a table does not prove that every linked result includes it.
For a separate period, project or volume, preserve the old workbook and start a named clean copy. If records must carry forward, record exactly which inputs you transfer, preserve their references and compare the opening and closing figures. Avoid copying historical activity as both an opening amount and a new movement. There is no automatic migration between files.
Inspect the workbook before you choose.
View its real sheets and a fictional calculation example.
Explore Batch Production & MaterialsWritten for version 1.0. Examples describe the supplied workflow; they are not customer results.