You run a small plant making liquid detergents (dishwashing liquid, floor cleaner, liquid hand soap, say) and you want to know what one pack really costs, how much you lose between the mixer and the filling line in each batch, whether your formulas are written in percentages that total 100%, whether a batch left within the pH and viscosity limits you set for your product, and what raw and packaging material will run short. The free detergent factory sheet answers this from tables you type yourself. (The template page is in Arabic only.)
Note: the percentages, prices and specifications in the sheet are illustrations for trying the calculations, not a manufacturing recipe. Your formulas and quality limits are set by you and your lab, and safety, registration and labelling follow the competent authority.
The problem it solves
In a detergent plant profit leaks in three quiet places. The first is the formula: you add a material here and trim another there and the total is no longer 100%, so the cost per kilogram is wrong. The second is density: you make by the kilogram and sell by the litre, so a 1 L pack is slightly heavier than one kilogram. The third is loss: you make 2,000 kg and fill less, and the gap only shows when the stock runs short. On top of that sits the pack cost itself, the bottle, cap, label and the carton share, which is often forgotten and makes the margin look bigger than it is.
How the sheet works, step by step
- In Settings type the overhead and labour per kilogram and the accepted loss limit (3% in the sample).
- In Raw materials and Packaging type each material with its cost, stock and minimum.
- In Products type the density (kg per litre) and the pH and viscosity limits of each product.
- In Formulas type one line per material with its weight percent; each product's total and status appear.
- In Packs define the size, bottle, cap, label, carton and selling price.
- Log purchases in Receipts, the batch in Batches (weight, pH, viscosity) and what was filled in Filling.
- Open Summary for output, loss, quality, cost per kilogram and the margin of each pack.
What each sheet holds
- Products: formula total (valid or "not 100%"), cost per kilogram, kilograms made, filled and lost.
- Raw materials and Packaging: receipts, consumption, balance, its value and status (available, order, negative).
- Packs: pack kilograms (size × density), contents cost, packaging cost, pack cost and margin.
- Batches: spec result, kilograms filled, loss %, batch cost and the loss alert.
- Filling: pack counts converted to kilograms, with an alert if the pack belongs to another product.
Walk-through on the sample numbers
The numbers below are sample data in the sheet, not a real plant. It holds 10 raw materials, 9 packaging items, 4 products, 5 packs and 8 batches, with overhead of 1.20 EGP per kilogram:
- Cost per kilogram: dishwashing liquid 12.304 (materials 11.104 + overhead 1.20), floor cleaner 7.711, liquid soap 10.377 (rounded).
- 1 L dishwashing pack: weighs 1.03 kg (1 × 1.03), so its contents cost 12.67. Packaging is 5.42 (bottle 3.20 + cap 0.60 + label 0.45 + carton share 14 ÷ 12 = 1.17), so the pack costs 18.09 against a price of 28: a margin of 9.91 EGP, 35.4%. The 3 L pack costs 50.07, sells at 75, margin 33.2%.
- Best margin: the 1 L liquid soap, cost 16.26, price 35, margin 53.5%.
- Batches: 8 batches weighing 16,700 kg, of which 14,288.1 kg were filled into 12,070 packs; loss on filled batches is 411.9 kg (2.80%). Production cost is 165,828.05.
- Loss: batch B-105 lost 4.25% and B-107 lost 4.67%, both above the 3% limit, so the alert shows. B-108 has no filling logged yet, so it shows no loss.
- Spec check: 6 batches pass and 2 show "fail": B-104 because its pH 8.6 is above its product's 8.0 limit, and B-107 because its viscosity 1,800 is under the 2,000 limit.
- Formulas: the sample used 820 kg of LABSA (8% of 6,500 kg of dishwashing liquid + 4% of 7,500 kg of floor cleaner). The trial product totals 99.5% on purpose, so it shows red and the summary counts it.
- Stock: perfume has 2.2 kg against a minimum of 15, so its status is "order". Among packaging, the 1 L bottle and cap need ordering, and the 3 L jerrycan and the 4-jerrycan carton show "negative balance" because their purchases were never logged, which shows the sheet exposing a missing receipt.
Why these features matter
- Density conversion: it turns litres into kilograms, so you do not misjudge your loss.
- Percentages checked for you: the "not 100%" status stops you relying on a wrong cost.
- Full pack cost: bottle, cap, label and carton share are included, so the margin is real.
- A spec check on your limits: you type the pH and viscosity limits, and an empty limit is not checked.
- Works in Excel 2016 and later, Google Sheets and LibreOffice, and holds 120 batches and 300 filling lines.
Common mistakes
- Logging filling in litres without density: use the density column so volume becomes weight.
- Forgetting to update a material cost: LABSA and perfume prices move, so update the cost column in Raw materials.
- Not logging receipts: the balance turns negative, as in the sample.
- A pack linked to another product in Filling: the sheet warns "pack is not for the batch's product".
- Leaving quality limits blank: the result shows "no spec" and nothing is checked.
Who it suits and when it is not enough
It suits a small plant with a few dozen materials and a few products, where one person types. If you have more than one filling line or shift, see also the production tracking sheet for tracking stages, and the recipe costing sheet for the idea of costing a recipe. If you need planned orders broken into materials automatically, quotes from several suppliers and line scheduling, MESwiro runs material requirements planning (MRP) and works out shortages against stock.
Next step
Open the sheet page and download the blank copy. First type one product's formula and check it totals 100%, then define one pack, log the first batch and its filling, and review the loss and pack cost in Summary.





