# Detergent factory Excel (free): formulas & costs

> Free Excel sheet for a small detergent plant: formulas totalling 100%, batches, pH and viscosity checks, filling by density, loss and pack cost. No sign-up.

Source: https://softwiro.com/blog/detergent-factory-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Detergent factory in Excel (free): formulas, batches and pack cost

Know the real cost of each pack, how much each batch loses before filling, and whether your formulas total 100%.

15 November 2026 6 min read

## In short

- Each product's formula is typed in weight percent, and the sheet flags any product that does not total 100%.
- Filling is entered as pack counts and converted to kilograms with the product density: kg = litres × density.
- Batch loss = batch weight minus kilograms filled, with an alert when it passes your accepted limit.
- pH and viscosity are compared with limits you type per product; pack cost includes bottle, cap, label and carton share.

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](https://softwiro.com/templates/detergent-factory) 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

1. In Settings type the overhead and labour per kilogram and the accepted loss limit (3% in the sample).
2. In Raw materials and Packaging type each material with its cost, stock and minimum.
3. In Products type the density (kg per litre) and the pH and viscosity limits of each product.
4. In Formulas type one line per material with its weight percent; each product's total and status appear.
5. In Packs define the size, bottle, cap, label, carton and selling price.
6. Log purchases in Receipts, the batch in Batches (weight, pH, viscosity) and what was filled in Filling.
7. 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](https://softwiro.com/templates/production-tracking) for tracking stages, and the [recipe costing sheet](https://softwiro.com/templates/restaurant-recipe-costing) for the idea of costing a recipe. If you need planned orders broken into materials automatically, quotes from several suppliers and line scheduling, [MESwiro](https://softwiro.com/systems/meswiro.html) runs material requirements planning (MRP) and works out shortages against stock.

## Next step

Open the [sheet page](https://softwiro.com/templates/detergent-factory) 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.

## 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)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-1.jpg)[![Cheat sheet: detergent factory (2/3)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-2.jpg)[![Cheat sheet: detergent factory (3/3)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/detergent-factory/en-3.jpg)

0 downloads · 3 pages

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Questions this raises

### Are the sheet's formulas usable for manufacturing?

No. The percentages, prices and specifications are illustrations for trying the calculations. Your formulas and quality limits are set by you and your lab, and safety and registration follow the competent authority.

### How is a pack converted from litres to kilograms?

Pack kilograms = pack size in litres × product density (kg per litre) typed in Products. A 3 L pack of a product with density 1.03 weighs 3.09 kg.

### How are raw materials deducted from stock?

Material used = percent ÷ 100 × the weight of the product's batches in kilograms, totalled per material in the Consumed column. Packaging is deducted from the pack counts logged in Filling.

### What if a formula does not total 100%?

The status "not 100%" shows red in Products and the Summary counts the product. Cost per kilogram uses the percentages as typed, so correct them before relying on it.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
