You run a small drug plant or a contract manufacturing line and want to see in one place: which raw-material lot is still in quarantine, which batch came out of your yield limits, whether a material was issued from a lot that is not released, and how much released finished product is left. The free pharma factory sheet keeps these records in one file you fill in yourself. It is for records and calculations only: no doses, no therapeutic claims, no real formulas and no official limits. Requirements are taken from the drug authority and the competent body in your country, and the sheet does not replace a validated, approved system.
The problem it solves
In a drug plant the thread is split across notebooks: material lots with the storekeeper, batch records with production, release decisions with quality, complaints with customer service. So a material is issued from a lot still in quarantine because nobody linked the two numbers, or a batch is released while a deviation is open, or nobody notices a lot nearing its retest date. The sheet links lot to batch to deviation to finished goods and warns you in the same row.
How the sheet works, step by step
- In الإعدادات (Settings) write today's date, your lower and upper yield limits, the allowed tolerance in material issue and the deviation closing period. These are your own numbers, not official limits.
- In الخامات (Materials) write the master of materials and packaging with type, unit cost and the minimum released stock.
- In المنتجات (Products) write batch size and shelf life in months, and in قائمة المواد (Bill of materials) the quantity of each material per batch from your approved documents.
- In استلام الخامات (Receipts) log each lot with its QC status and its retest or expiry date.
- In سجل التشغيلات (Batch log) log the batch with product, date, actual quantity, in-process checks, QC status, decision date and released quantity, then in صرف الخامات (Material issues) what was issued to it by lot number.
- Log deviations and complaints in الانحرافات والشكاوى, and what left finished stock in صرف المنتج التام.
- Review the alerts in the batch log and in الملخص (Summary).
What each sheet holds
- Materials and receipts: released, quarantined, rejected and issued quantities, the released balance, days left on each lot and a near-expiry or expired alert.
- Products and bill of materials: material cost per batch and per unit, average yield, released, dispatched and available.
- Batch log: actual versus theoretical yield, expiry date, yield assessment, available stock and one alert per batch.
- Material issues: the gap from the bill of materials in quantity and percent, and an alert for a lot that is not released.
- Deviations, dispatch and summary: follow-up, availability, the numbers and the charts.
Walk-through on the sample numbers
These figures are fictional sample data in the sheet, not a real plant. It has 10 materials, 15 lots, 3 products and 7 batches. The yield limits in the sample are 95% and 102%, tolerance 2%, deviation closing period 30 days and today is 2026-11-15:
- Lots: 12 released, 2 in quarantine and 1 rejected. The sheet flags two lots near retest (L-2606 in 77 days, L-2614 in 15 days) and one expired lot that still holds 500 litres (L-2501). Sample excipient 2 is under its minimum: released balance 110 kg against a minimum of 200.
- Batch B-2610-01: 98,200 made against a batch size of 100,000, a yield of 98.2%, inside the limits, expiring 2028-10-31 with a 24-month shelf life. But excipient 1 was issued at 124 kg against 120 in the bill of materials, +3.3%, above the 2% tolerance, so the alert shows.
- Batch B-2610-03: 13,900 of 15,000, 92.7%, under the limit, with a deviation under investigation for 24 days although the batch is released, so "release with an open deviation or complaint" appears.
- Batch B-2611-01: 103,000 of 100,000, 103%, above the limit (check the count first). A material was issued from a lot in quarantine and 1,000 units were shipped before release, so both the batch and the issue line are flagged.
- Totals: 351,700 made against 355,000 theoretical for the batches run, a yield of 99.1%. Released 230,200, dispatched 151,500, available 78,700. Five batches carry an alert and three are outside the yield limits.
- Deviations: six records, four of them open, a complaint open for 38 days (past the 30), and one critical item open.
- Cost: the materials of one sample product A batch cost 48,900, that is 0.489 a unit, and the released materials in stock are worth 322,745.
Common mistakes
- Leaving the QC decision date empty on release or rejection, which loses the timeline.
- Issuing a material without a lot number: its QC status and quantity can then not be checked.
- Typing the bill of materials from memory: write it only from your approved documents, or the reconciliation means nothing.
- Treating your yield limits as official: they are your numbers; the competent authority is the reference.
- Leaving a deviation open past its period with no clear owner.
When to move to a system
The sheet suits a small plant where one or two people write, with up to 150 batches and 200 lots. If you have several lines and shifts, or need planned orders broken down into components automatically, supplier quotations and line scheduling, MESwiro does material requirements planning (MRP) and works out shortages against stock. For dated stock in general see the batch and expiry inventory sheet, and for stage tracking the production tracking sheet.
Next step
Open the sheet page and download the blank copy. Enter your first two materials and their lots, then your first batch and what was issued to it, and read the "تنبيه" (alert) column in the batch log.





