# Pharma factory Excel (free): batch records & release

> Free Excel sheet for a small drug plant: material lots, batch records, yield, expiry, material reconciliation, deviations and released stock. Records only.

Source: https://softwiro.com/blog/pharma-factory-batches-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Pharma factory in Excel (free): batch records, materials and release

See which lots are in quarantine, which batch left your yield limits, what was issued against the bill of materials, and how much released stock is left.

15 November 2026 6 min read

## In short

- Every raw-material lot has a QC status (quarantine, released, rejected) and a retest or expiry date with a near-expiry alert.
- Each batch shows actual versus theoretical yield, its expiry date, and a flag when the yield leaves your own limits.
- Issued materials are reconciled with the bill of materials per batch, and a lot that is not released is flagged.
- Deviations, complaints and dispatch are tied to the batch, so a release with an open deviation shows at once.
- Records and calculations only: no doses, no real formulas, no official limits.

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](https://softwiro.com/templates/pharma-factory-batches) 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

1. 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.
2. In الخامات (Materials) write the master of materials and packaging with type, unit cost and the minimum released stock.
3. 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.
4. In استلام الخامات (Receipts) log each lot with its QC status and its retest or expiry date.
5. 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.
6. Log deviations and complaints in الانحرافات والشكاوى, and what left finished stock in صرف المنتج التام.
7. 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](https://softwiro.com/systems/meswiro.html) does material requirements planning (MRP) and works out shortages against stock. For dated stock in general see the [batch and expiry inventory sheet](https://softwiro.com/templates/batch-expiry-inventory), and for stage tracking the [production tracking sheet](https://softwiro.com/templates/production-tracking).

## Next step

Open the [sheet page](https://softwiro.com/templates/pharma-factory-batches) 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.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: pharma factory batch records (1/3)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-1.jpg)[![Cheat sheet: pharma factory batch records (2/3)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-2.jpg)[![Cheat sheet: pharma factory batch records (3/3)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/pharma-factory-batches/en-3.jpg)

0 downloads · 3 pages

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Questions this raises

### Does the sheet set doses or official limits?

No. It is for records and calculations only: no doses, no therapeutic claims, no real formulas and no official limits. The yield limits, tolerance and closing period are numbers you type, and requirements come from the drug authority and the competent body in your country.

### Does it replace a validated, approved system?

No. It is an Excel file anyone who opens it can change, so it is no substitute for a validated system where the authorities require one. It suits organising records, follow-up and trials.

### How are yield and expiry date calculated?

Actual yield = quantity made ÷ batch size. Expiry = the end of the month after the shelf life in months from the manufacturing date, so a batch made on 2026-10-05 with 24 months expires on 2028-10-31.

### How are issued materials reconciled?

The sheet adds what was issued of each material to the batch, compares it with the bill of materials, shows the gap and percentage and warns if it exceeds your tolerance or the material came from a lot in quarantine or rejected.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
