Softwiro
Free Excel template

Detergent factory sheet: formulas, batches, cost and filling

A free Excel sheet for a small liquid detergent factory. You enter raw materials and packaging materials with their cost, stock and minimum level, and each product's formula in weight percent; the sheet warns you if a formula does not total 100%. You then log batches in kilograms and raw materials are deducted automatically. You log filling by number of packs (1 litre, 3 litre and others), the sheet converts them to kilograms using the product density and works out the batch loss, compares pH and viscosity readings with your product's limits, and calculates cost per kilogram, cost per pack and selling margin. The percentages and prices in the example are illustrative and are not a manufacturing recipe.

Excel file (.xlsx) · 135 KB · 12 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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

Example from the “التشغيلات” tab (Arabic), with the numbers and formulas that are in the file.
رقم التشغيلةكود المنتجوزن التشغيلة كجمالمنتجنتيجة المطابقةكجم معبأةفاقد %تكلفة التشغيلة
B-101P012,000سائل غسيل أطباقناجح1,946.72.7%24,608.00
B-102P023,000منظف أرضياتناجح2,929.02.4%23,134.20
B-103P031,500صابون سائل لليدينناجح1,456.02.9%15,564.75
B-104P012,500سائل غسيل أطباقمرفوض2,430.82.8%30,760.00
B-105P022,500منظف أرضياتناجح2,393.74.3%19,278.50
B-106P012,000سائل غسيل أطباقناجح1,987.90.6%24,608.00
B-107P031,200صابون سائل لليدينمرفوض1,144.04.7%12,451.80
B-108P022,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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. In the "العبوات" (Packs) sheet, define each pack: its product, size in litres, bottle, cap, label, carton, packs per carton and selling price.
  6. Record purchases in "الوارد" (Incoming), raw and packaging materials together, and each material's stock rises automatically.
  7. 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.
  8. 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.
  9. 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.

Excel file (.xlsx) · 135 KB · 12 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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: detergent factory (1/3)Cheat sheet: detergent factory (2/3)Cheat sheet: detergent factory (3/3)
0 downloads · 3 pages

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 sheetIn 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.

See MESwiro · Ask for a demo

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

All templates · Free tools