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-101 | P01 | 2,000 | سائل غسيل أطباق | ناجح | 1,946.7 | 2.7% | 24,608.00 |
| B-102 | P02 | 3,000 | منظف أرضيات | ناجح | 2,929.0 | 2.4% | 23,134.20 |
| B-103 | P03 | 1,500 | صابون سائل لليدين | ناجح | 1,456.0 | 2.9% | 15,564.75 |
| B-104 | P01 | 2,500 | سائل غسيل أطباق | مرفوض | 2,430.8 | 2.8% | 30,760.00 |
| B-105 | P02 | 2,500 | منظف أرضيات | ناجح | 2,393.7 | 4.3% | 19,278.50 |
| B-106 | P01 | 2,000 | سائل غسيل أطباق | ناجح | 1,987.9 | 0.6% | 24,608.00 |
| B-107 | P03 | 1,200 | صابون سائل لليدين | مرفوض | 1,144.0 | 4.7% | 12,451.80 |
| B-108 | P02 | 2,000 | منظف أرضيات | ناجح | 15,422.80 |
Example from the “التشغيلات” tab (Arabic), with the numbers and formulas that are in the file.
What is inside
- الإعدادات: Overheads and labour per kilogram of product, the acceptable loss limit and the first month of the summary.
- المواد الخام: Each raw material with its code, cost per kg, opening stock, minimum level and supplier, with incoming, used in formulas, stock and its value, and status (in stock, order or negative stock). Holds 40 materials.
- مواد التعبئة: Bottles, caps, labels and cartons in the same way; their use is deducted from the number of packs filled. Holds 25 materials.
- المنتجات: Each product with its density (kg per litre) and the pH and viscosity limits you enter, with the total of its formula and its status, the cost of materials and overheads per kg, and the kg made, filled and lost. Holds 12 products.
- التركيبات: One row per material in the product with its weight percent, showing the cost of that percentage and the kg used of each material, with a warning for a code that does not exist or a duplicate row. Holds 120 rows.
- العبوات: Each pack with its product, size in litres, bottle, cap, label, carton and selling price, with pack weight in kg, cost of contents and filling, cost per pack, margin and packs filled. Holds 24 packs.
- الوارد: A purchase log for raw and packaging materials together, with date, quantity, unit price and supplier; each material's stock rises from it automatically. Holds 150 rows.
- التشغيلات: The batch with its date, product, weight and pH and viscosity readings, next to the product limits and the result (pass or fail), kg filled, loss, batch cost and a warning if the loss exceeds the acceptable limit. Holds 120 batches.
- التعبئة: One row per filling: batch, pack and number of packs. The sheet calculates the pack weight from density and the kg filled, and warns if the pack is not for the batch's product. Holds 300 rows.
- الملخص: Stock value and materials below minimum, production, loss and quality results, a table of products, another of packs with cost and margin and a monthly one, with five charts.
The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.
What makes it different
A batch does not end at its weight. The sheet links litres to kilograms with the product density, works out the loss between what you made and what you filled, deducts raw and packaging materials automatically, and arrives at the full cost of a pack and its margin.
How to use it
- In the "الإعدادات" (Settings) sheet, enter overheads and labour per kilogram of product, the acceptable loss limit for a batch and the first month of the summary.
- In the "المواد الخام" (Raw materials) sheet, enter each material with its code, cost per kg, opening stock and minimum level. In "مواد التعبئة" (Packaging materials), enter bottles, caps, labels and cartons the same way.
- In the "المنتجات" (Products) sheet, enter each product with its density (kg per litre) and the pH and viscosity limits you set for your product. The sheet shows the total of its formula and the cost per kg.
- In the "التركيبات" (Formulas) sheet, enter a row for each material in the product with its weight percent. A product must total 100%, otherwise the sheet shows a warning in the "المنتجات" (Products) sheet.
- In the "العبوات" (Packs) sheet, define each pack: its product, size in litres, bottle, cap, label, carton, packs per carton and selling price.
- Record purchases in "الوارد" (Incoming), raw and packaging materials together, and each material's stock rises automatically.
- Log each batch in "التشغيلات" (Batches): its number, date, product, weight in kilograms and the pH and viscosity readings. Raw materials are deducted automatically and the pass or fail result against the specification appears.
- Record what was filled from it in "التعبئة" (Filling): batch, pack and number of packs. The sheet converts the count to kilograms by density, works out the batch loss and deducts packaging materials.
- Open "الملخص" (Summary) to see production, loss, quality, cost per kilogram and the margin of each pack.
Before you start:
- The formula percentages, specifications and material prices in this sheet are illustrative examples only and are not a recipe suitable for manufacturing. Your formulas and quality limits are set by you and your lab.
- Safety, registration, licensing and the label follow the requirements of the competent authority. The sheet is for records and calculations only and does not set doses or legal limits.
- Material use = percent ÷ 100 × batch weight in kilograms (this includes what was lost in filling, because it used up its materials). Stock = opening stock + incoming - used.
- Filling conversion: pack kilograms = pack size in litres × product density. Batch loss = batch weight - kilograms filled, and it appears after the first filling of the batch is recorded.
- Cost per kg = sum of (percent × cost per kg of each material) ÷ 100 + overheads and labour per kg. Cost per pack = pack kilograms × cost per kg + bottle + cap + label + share of the carton.
- Conformity: the sheet compares the pH and viscosity readings with the product limits you entered. A blank limit is not checked. The sheet comes with no ready-made limits, so enter them from your product's specification.
- The product "سائل متعدد الأغراض (تجريبي)" (multi-purpose liquid, trial) has a formula of 99.5% on purpose, so you can see the formula warning.
- Capacity: 40 raw materials, 25 packaging materials, 12 products, 120 formula rows, 24 packs, 150 incoming rows, 120 batches and 300 filling 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
Are the formulas in the sheet suitable for manufacturing?
No. The percentages, prices and specifications in the example are illustrative only, to try out the calculations. Your formulas and quality limits are set by you and your lab, and safety and registration follow the requirements of the competent authority.
How is a pack converted from litres to kilograms?
Pack kg = pack size in litres × product density (kg per litre), which you enter in the "المنتجات" (Products) sheet. So a 3-litre pack of a product with density 1.03 weighs 3.09 kg.
How are raw materials deducted from stock?
Material use = percent ÷ 100 × the weight of the product's batches in kilograms, and the sheet totals it for each material in the "المستهلك" (Used) column. Packaging materials are deducted from the number of packs filled in "التعبئة" (Filling).
What happens if a formula does not total 100%?
The status "لا يساوي 100%" (does not equal 100%) shows in red in "المنتجات" (Products) and the product is counted in "الملخص" (Summary). Cost per kg is calculated on the percentages as you wrote them, so correct it before relying on it.
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 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
- 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





