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
| رقم الأمر | كود المنتج | الفعلي | السليم | العائد النهائي | تكلفة الأمر |
|---|---|---|---|---|---|
| PO-101 | P01 | 300 | 295 | 98.3% | 94,050.00 |
| PO-102 | P02 | 500 | 485 | 97.0% | 49,750.00 |
| PO-103 | P03 | 400 | 387 | 96.8% | 38,820.00 |
| PO-104 | P01 | 180 | 150 | 83.3% | 56,430.00 |
| PO-105 | P03 | 300 | 265 | 88.3% | 29,115.00 |
| PO-106 | P02 | 0 | 0.00 | ||
| PO-107 | P03 | 0 | 0.00 | ||
| PO-108 | P01 | 0 | 0.00 |
Example from the “أوامر الإنتاج” tab (Arabic), with the numbers and formulas that are in the file.
What is inside
- الإعدادات: The cost of a labour hour, each labour hour's share of overheads, the target yield, the first month of the summary, and the list of defect types.
- المنتجات: Each product with labour hours per unit and selling price, with the BOM material cost, labour, overheads, unit cost and profit margin, then actual output, good units, yield, good-unit cost and the planned quantity remaining. Holds 30 products.
- المكونات: Each component with its unit, cost, opening stock, minimum level and supplier, with incoming, used, stock and its value, what planned orders need, the shortage, the suggested quantity, the cost of ordering and status. Holds 100 components.
- BOM: One row per component that goes into a product with its quantity per unit, showing the line cost, actual use and what the plan needs, with a warning if a code does not exist or a row is repeated. Holds 200 rows.
- الوارد: A log of component purchases with date, quantity, unit price, supplier and invoice number; the component's stock rises from it automatically. Holds 300 rows.
- أوامر الإنتاج: The order with its date, product, planned and actual quantity and status (planned, in progress or finished), with inspection results, good units, first-pass yield, final yield, order cost, good-unit cost and a warning if yield falls below target. Holds 200 orders.
- فحص الجودة: One row per inspection result: passed first time, reworked, rejected, passed after rework and the defect type, from which the production order calculates its yield. Holds 500 rows.
- النواقص: Components that are not enough for the planned orders or whose stock would fall below the minimum after them, sorted by order cost, with the shortage, the suggested quantity and each component's supplier.
- الملخص: Component value and shortages, production, yield and cost, a table of products, a monthly one and one of defects, with four charts.
The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.
What makes it different
A production order does more than record a quantity. It deducts its components with the formula BOM × quantity, loads the cost of rejected units onto the good ones, and shows before you start the planned orders which component will run short and how much to buy.
How to use it
- In the "الإعدادات" (Settings) sheet, enter the cost of a labour hour, each hour's share of overheads, the target yield and the first month of the summary, and review the list of defect types.
- In the "المكونات" (Components) sheet, enter for each component: code, name, unit, unit cost, opening stock, minimum level and supplier.
- In the "المنتجات" (Products) sheet, enter each product with its code, the labour hours needed per unit and its selling price.
- In the "BOM" sheet, enter a row for each component that goes into the product: product code, component code and quantity per unit. The materials cost per unit appears in "المنتجات" (Products).
- Record purchases in "الوارد" (Incoming) and the component's stock rises automatically.
- Record the production order in "أوامر الإنتاج" (Production orders): number, date, product, planned quantity and status, and enter the actual quantity as the work progresses. Components are deducted from stock by BOM × actual quantity.
- Record inspection results in "فحص الجودة" (Quality inspection): passed, reworked, rejected, the defect type, and passed after rework. The production order calculates yield and good-unit cost.
- Open "النواقص" (Shortages) to see the components that are not enough for planned orders, with the suggested quantity and its cost, and "الملخص" (Summary) for the indicators and charts.
Before you start:
- Component use = BOM quantity per unit × actual quantity produced (this includes rejected units, because they used up their components). A "planned" order deducts nothing until you enter its actual quantity.
- Component stock = opening stock + incoming - used. Needed for planned orders = BOM × (planned - actual) for orders that are not "finished".
- Shortage = needed - stock, if positive. Suggested quantity = needed + minimum level - stock, that is, what is required to cover the planned orders while keeping the minimum level.
- Final yield = good units ÷ units produced, and good units = passed at first inspection + passed after rework. First-pass yield = passed at first inspection ÷ inspected.
- Unit cost = BOM materials + labour hours × cost per hour + labour hours × the hour's share of overheads. "Good-unit cost" spreads the whole order cost over the good units only, so the loss from rejects shows.
- A unit that failed rework and was not repaired: do not record a pass after rework for it, and it appears in the "تحت الإعادة / لم تُصلَّح" (under rework / not repaired) column.
- The sheet is for records and calculations only, and specifications and quality requirements are taken "according to the requirements of the competent authority" and your product's specifications. It holds 100 components, 30 products, 200 BOM rows, 200 production orders, 500 inspection rows and 300 incoming rows, and "النواقص" (Shortages) shows the first 30 components.
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
How are components deducted from stock?
The sheet multiplies the component quantity per product unit (from the BOM) by the actual quantity in "أوامر الإنتاج" (Production orders), and totals it for each component in the "المستهلك" (Used) column. Nothing is deducted for a "planned" order until you enter its actual quantity.
How is yield calculated?
Final yield = good units ÷ units produced, and good units = passed at first inspection + passed after rework. First-pass yield is passed first time ÷ inspected.
How is unit cost calculated?
Unit cost = BOM materials + labour hours × cost per hour + labour hours × the hour's share of overheads. The good-unit cost spreads the whole order cost over the good units only.
Does the sheet set quality requirements?
No. The sheet is for records and calculations, and specifications and quality requirements are taken from the competent authority and from your own product's specifications.
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
- 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
- Pharma factory sheet: batch records, materials, QC and release
- Industrial waste management sheet: quantities, classification, recycling and cost
- Wholesale clothing trade sheet: traders, stock and collection
- Food wholesale and distribution sheet in Excel: routes, expiry and collection
- Periodic table in Excel (Arabic names): lookup, molar mass calculator and quiz
- Science formulas cheat sheet: physics, chemistry and biology with examples
- Complete nursery management sheet
- Private school management sheet
- Complete clinic management sheet
- Medical lab management sheet





