Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| رقم التشغيلة | المنتج | تاريخ التصنيع | الكمية المنتجة فعليًا | العائد الفعلي | تاريخ الانتهاء | حالة الجودة | الكمية المفرج عنها |
|---|---|---|---|---|---|---|---|
| B-2609-01 | منتج تجريبي (أ) أقراص | 46279 | 99,100 | 99.1% | 47026 | مفرج عنه | 99,100 |
| B-2610-01 | منتج تجريبي (أ) أقراص | 46300 | 98,200 | 98.2% | 47057 | مفرج عنه | 98,000 |
| B-2610-02 | منتج تجريبي (ب) شراب | 46307 | 19,300 | 96.5% | 46873 | مفرج عنه | 19,300 |
| B-2610-03 | منتج تجريبي (ج) كريم | 46315 | 13,900 | 92.7% | 47057 | مفرج عنه | 13,800 |
| B-2611-01 | منتج تجريبي (أ) أقراص | 46328 | 103,000 | 103.0% | 47087 | قيد الفحص | |
| B-2611-02 | منتج تجريبي (ب) شراب | 46332 | 18,200 | 91.0% | 46904 | مرفوض | 0 |
| B-2611-03 | منتج تجريبي (ج) كريم | 46336 | 47087 | قيد الفحص |
Example from the “سجل التشغيلات” tab (Arabic), with the numbers and formulas that are in the file.
What is inside
- الإعدادات: Settings: today's date, your minimum and maximum yield limits, the allowed tolerance on material issues, the deviation closing period and the warning days before expiry, plus the lists of material types and units. These are numbers you set and are not official limits.
- الخامات: Materials: the catalogue of raw and packaging materials with type, unit, cost and minimum stock. It shows received released stock, quarantined and rejected quantities, quantity issued, released balance and its value, and status. Holds up to 60 materials.
- استلام الخامات: Material receipts: the lot register with lot number, material, supplier, receipt date, quantity, test status and retest or expiry date. It shows days remaining, quantity issued, balance and a warning (near expiry, expired, rejected or in quarantine). Holds up to 200 lots.
- المنتجات: Products: each product with its batch size and shelf life in months. It shows the batch's material cost and unit cost, number of batches, quantity actually produced, average yield, quantity released, quantity dispatched and stock available. Holds up to 20 products.
- قائمة المواد: Bill of materials: one row per material in a product, with its quantity per batch (figures you enter from your approved documents; the example numbers are made up), the row cost and a warning for an unknown code or a duplicate row. Holds up to 100 rows.
- سجل التشغيلات: Batch record log: each batch with its product, manufacturing date, actual quantity, in-process test result, QC status, decision date and quantity released. The sheet works out actual and theoretical yield, expiry date, yield rating, quantity dispatched, quantity available, open deviations, material cost and a warning. Holds up to 150 batches.
- صرف الخامات: Material issues: what was issued to each batch by lot number, compared with the bill of materials. It shows the difference in quantity and in percent and warns if it exceeds your tolerance or if material was issued from a lot that is not released. Holds up to 400 rows.
- الانحرافات والشكاوى: Deviations and complaints: a log of deviations, complaints and non-conformances with date, batch, severity, owner and status. It shows days open and warns if they pass the period you set. Holds up to 100 rows.
- صرف المنتج التام: Finished-goods dispatch: what left each batch to warehouses and customers, with the batch's QC status and quantity available, and a warning if a batch that is not released was dispatched. Holds up to 300 rows.
- الملخص: Summary: figures for batches, yield, finished goods, materials, lots and deviations, tables of yield by batch, by month and for finished goods, and five charts.
The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.
What makes it different
The sheet links lot, batch, deviation and finished product. It catches a material issued from a lot still in quarantine, a batch whose yield fell outside your two limits, a batch released with a deviation still open, and a batch dispatched before release.
How to use it
- In "الإعدادات" (Settings) enter today's date and your own limits: warning days before expiry, minimum and maximum yield, the allowed tolerance on material issues and the deviation closing period.
- In "الخامات" (Materials) enter the catalogue of raw and packaging materials with code, type, unit, unit cost and minimum released balance.
- In "المنتجات" (Products) enter each product with its batch size and shelf life in months. In "قائمة المواد" (Bill of materials) enter each material's quantity per batch (figures you take from your approved documents).
- Record each material delivery in "استلام الخامات" (Material receipts) with a lot number, test status (quarantine, released or rejected) and the retest or expiry date.
- Record each batch in "سجل التشغيلات" (Batch record log) with its product, date, actual quantity, in-process test result, QC decision, decision date and quantity released. Then enter the materials issued in "صرف الخامات" (Material issues) by lot number.
- Record deviations and complaints in "الانحرافات والشكاوى" (Deviations and complaints) and finished goods leaving in "صرف المنتج التام" (Finished-goods dispatch). Then review the warnings in "سجل التشغيلات" and "الملخص" (Summary).
Before you start:
- The sheet is for records and calculations only and does not replace a validated, approved system. Requirements, limits and required documents follow the Egyptian Drug Authority and the competent authority in your country.
- The sheet has no doses, no therapeutic claims, no real formulations and no official limits. The product names, material names and bill-of-materials quantities in the example are made up to show how it works, and the yield and tolerance limits are numbers you set.
- Expiry date = the end of the month reached by adding the shelf life (in months) to the manufacturing date. Actual yield = quantity produced / batch size (the theoretical yield). A warning appears if it falls outside your two limits.
- Materials are reconciled by comparing what was issued to a batch with the quantity in the bill of materials: the difference in quantity and in percent. A warning appears if it exceeds the tolerance you entered or if material was issued from a lot that is not released. If a material is issued from more than one lot, write one row per lot; the comparison shows on the first row.
- The sheet holds 60 materials, 200 lots, 20 products, 100 bill-of-materials rows, 150 batches, 400 material-issue rows, 100 deviations or complaints and 300 finished-goods dispatch rows.
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.
Still running this on Excel?
This sheet calculates requirements for the orders you type in yourself. In MESwiro, material requirements planning (MRP) breaks planned production orders down into their components and works out shortages against stock.
| In this sheet | In MESwiro |
|---|---|
| You type in every order and every balance by hand. | Material requirements planning (MRP) breaks planned production orders down into their components and calculates shortages against stock. |
| You ask suppliers for quotes by phone. | It requests quotes from several suppliers and compares price, lead time and minimum order side by side. |
| Line scheduling in a sheet hides conflicts. | It assigns work orders to lines and shifts by drag and drop, with a Gantt chart and a panel for unscheduled orders. |
| Efficiency and order cost are guesswork. | It calculates OEE for each line automatically and shows the material, labor and machine cost of each order. |
Frequently asked questions
Does the sheet set doses or official limits?
No. It is for records and calculations only. It has no doses, no therapeutic claims, no real formulations and no official limits. The yield limits, tolerance and closing period are numbers you enter, and requirements follow the Egyptian Drug Authority and the competent authority in your country.
Can it replace a validated, approved system?
No. It is an Excel file that anyone who opens it can edit, so it does not replace a validated, approved system where the authorities require one. It is suited to organising records, follow-up and trials.
How are yield and expiry date calculated?
Actual yield = quantity produced / batch size (the theoretical yield). Expiry date = the end of the month reached by adding the shelf life in months to the manufacturing date, so a batch made on 2026-10-05 with a 24-month shelf life expires on 2028-10-31.
How are issued materials reconciled?
The sheet totals what was issued of each material to the batch and compares it with the quantity in the bill of materials. It shows the difference and the percentage, and warns if it exceeds the tolerance you entered. It also warns if the material came from a lot in quarantine or a rejected lot.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
All the templates in this group: Factory management sheets: production, cost and quality
More free templates for: Factories and production
More free templates
- Electronics factory sheet: BOM, production and quality
- Food factory sheet in Excel: recipes, batches, cost and shelf life
- Biscuit factory sheet: dough, lines, packing and scrap
- Detergent factory sheet: formulas, batches, cost and filling
- Detergent ingredients and their names: function, chemical name and use
- Industrial waste management sheet: quantities, classification, recycling and cost
- Algebra rules summary sheet: laws, identities and solving equations with examples
- Solid geometry formulas sheet: areas and volumes with examples and a calculator
- Livestock and dairy farm sheet
- Complete farm management sheet in Excel: plots, workers, crops and sales
- Agricultural waste management sheet: rice straw, residues, compost and recycling
- Trip organizer sheet: participants, instalments, costs and itinerary
- Building maintenance sheet: flat fees, expenses, elevator and pump
- Canteen management sheet





