What is the difference between yield and reconciliation?

Yield is the good output as a share of the theoretical yield: 743,787 capsules from 800,000 is 92.97%. Reconciliation adds everything else that is documented (samples, rejects, other recorded losses) and asks whether the batch adds up: 743,787 + 340 + 49,600 + 5,297 = 799,024 of 800,000 is 99.88%, so the low yield is explained by rejects but must still be investigated against the yield limit.

Where do the yield and reconciliation limits come from?

From your own process: the master batch record states the theoretical yield and the percentages beyond which an investigation is needed, set from validation and batch history. 21 CFR 211.186 and 211.103 require them for drug products in the US, and the EU GMP Guide Part I Chapter 5 asks for yields to be checked and discrepancies investigated. The example limits are invented; type yours on Setup.

Why flag a yield above the upper limit?

Because a batch cannot make more than its formula allows. A yield over 100% (or over your upper limit) usually means a counting, weighing or recording error, or theoretical yield calculated wrongly, and needs the same investigation as a low yield. The workbook marks both as Out of limits.

How is the unaccounted quantity calculated?

Theoretical yield minus actual yield, QC samples, rejects and other accounted losses. In the example, syrup batch CS26-103 had 24,600 bottles theoretical, 23,833 good, 40 samples, 198 rejects and 135 other losses: 394 bottles are not accounted for, a reconciliation of 98.40% against a 99.0% lower limit. It has no deviation reference yet, so its Check column says Deviation reference missing.

Why does the by-product table not show one overall yield for the plant?

Products are counted in different units (tablets, capsules, bottles, kg), so adding them together is meaningless. Each product's yield and reconciliation are formed from the sums of its own batches (sum of actual / sum of theoretical), never as an average of batch percentages. Plant-wide, the Dashboard counts batches instead: in the example 30 of 32, or 93.8%, are within all limits.

Can I use this workbook as a GMP record?

It is a calculation and review aid. If you keep GMP records in it, validate it under your own procedures (for example EU GMP Annex 11 or 21 CFR Part 11 for electronic records) and control the file like any other GMP document. The Verify sheet checks the formulas as shipped; it cannot see later edits.

Industry Cost Kits

Batch Yield Reconciliation Excel Template

Log every batch's theoretical and actual yield, QC samples, rejects and other documented losses; get the yield and the material reconciliation against each product's own limits, and see which out-of-limits batches still have no deviation reference.

Overview

This batch yield reconciliation template is for QA and production managers, batch record reviewers and process engineers in pharmaceutical, biotech, nutraceutical and food plants. It ships with 32 example batches of four over-the-counter products from a fictional manufacturer over eight weeks, so every number moves before you type your own.

Each batch is a row on Batch Log, in the unit its product is counted in. Yield is the actual yield divided by the theoretical yield from the batch formula. Reconciliation adds everything else that is documented (QC samples, rejects and other recorded losses) to the actual yield and divides by the theoretical yield; what is left is the unaccounted quantity. Both are tested against that product's limits on Setup, which you take from your own validated process: the template invents none. A yield above the upper limit is flagged too, because it usually means a counting or weighing error.

When a batch is out of limits, the Check column asks for its deviation reference until one is entered. The Dashboard shows how many batches are within all limits, which are out and which still have no reference, the yield and reconciliation of the latest 20 batches against each batch's own limits, and by product the yield and reconciliation formed from the sums, with the share of the theoretical yield lost to samples, rejects, other documented losses and the unaccounted remainder.

What this template includes

Batch Log table: batch number, date, product, theoretical yield, actual yield, QC samples, rejects, other accounted losses, deviation reference and notes for each batch
Setup sheet with a Products list: the unit each product is counted in and its yield and reconciliation limits, typed from your own validated process
Per batch: yield, reconciliation and the unaccounted quantity, with Within limits or Out of limits for yield and for reconciliation against that product's limits
Check column that names an out-of-limits batch without a deviation reference, a product not on Setup, a duplicate batch number or a missing input in words
Dashboard: batches reconciled, share within all limits, batches out of limits and those without a deviation reference, with yield and reconciliation charts of the latest 20 batches against their limits
By-product table with yield and reconciliation formed from the sums in each product's own unit, and a chart of where each product's losses go
Method sheet with every formula and the regulations it supports, and a Verify sheet that rechecks the formulas, including the live Pharma Batch Yield calculator's example
CSV with live formulas of the Batch Log; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it during batch record review to compute yield and reconciliation the same way every time, to flag batches outside their limits that need an investigation, and to trend yield and losses by product.

How to use it

On Setup, list your products with the unit they are counted in and the yield and reconciliation limits from your master batch record.
On Batch Log, add one row per batch: batch number, date, product, theoretical yield and actual yield, keeping the log in date order.
Add QC samples, rejects and other documented losses, then read yield, reconciliation, the unaccounted quantity and the two status words.
For every batch out of limits, enter the deviation or investigation reference; the Check column names any that are still missing.
Open the Dashboard to see the latest batches against their limits, the batches that need attention and where each product's losses go.

Frequently Asked Questions

What is the difference between yield and reconciliation?: Yield is the good output as a share of the theoretical yield: 743,787 capsules from 800,000 is 92.97%. Reconciliation adds everything else that is documented (samples, rejects, other recorded losses) and asks whether the batch adds up: 743,787 + 340 + 49,600 + 5,297 = 799,024 of 800,000 is 99.88%, so the low yield is explained by rejects but must still be investigated against the yield limit.
Where do the yield and reconciliation limits come from?: From your own process: the master batch record states the theoretical yield and the percentages beyond which an investigation is needed, set from validation and batch history. 21 CFR 211.186 and 211.103 require them for drug products in the US, and the EU GMP Guide Part I Chapter 5 asks for yields to be checked and discrepancies investigated. The example limits are invented; type yours on Setup.
Why flag a yield above the upper limit?: Because a batch cannot make more than its formula allows. A yield over 100% (or over your upper limit) usually means a counting, weighing or recording error, or theoretical yield calculated wrongly, and needs the same investigation as a low yield. The workbook marks both as Out of limits.
How is the unaccounted quantity calculated?: Theoretical yield minus actual yield, QC samples, rejects and other accounted losses. In the example, syrup batch CS26-103 had 24,600 bottles theoretical, 23,833 good, 40 samples, 198 rejects and 135 other losses: 394 bottles are not accounted for, a reconciliation of 98.40% against a 99.0% lower limit. It has no deviation reference yet, so its Check column says Deviation reference missing.
Why does the by-product table not show one overall yield for the plant?: Products are counted in different units (tablets, capsules, bottles, kg), so adding them together is meaningless. Each product's yield and reconciliation are formed from the sums of its own batches (sum of actual / sum of theoretical), never as an average of batch percentages. Plant-wide, the Dashboard counts batches instead: in the example 30 of 32, or 93.8%, are within all limits.
Can I use this workbook as a GMP record?: It is a calculation and review aid. If you keep GMP records in it, validate it under your own procedures (for example EU GMP Annex 11 or 21 CFR Part 11 for electronic records) and control the file like any other GMP document. The Verify sheet checks the formulas as shipped; it cannot see later edits.