You run a small bottled-water plant with small bottles, a 1.5 litre bottle and a 19-litre jug, and at the end of the week you want to know: how many cartons and jugs came off the lines and how many were rejected, whether bottles, caps and cartons will last, what a unit really costs, and where the jugs are and who owes money. The free bottled water plant sheet answers that from tables you type. It is one file for packing, distribution and cost, the answer to a search for a water plant Excel sheet.
The problem it solves
In a small water plant the numbers are scattered: empty bottles are counted in the store, output goes into the shift notebook, jugs at customers are tracked from memory and customer accounts sit in an exercise book. At month end you do not know what a carton costs once rejects, energy and labour are counted, nor how many jugs each customer holds and whether the deposit covers them. This sheet links materials to products to the production log to dispatch, so a water filling cost calculation and customer balances come from the same numbers.
How the sheet works, step by step
- In Settings type today's date, the target reject rate, the overhead share, the jug deposit, the number of jugs you own and the first month of the summary.
- In Materials type each packaging material with its type, cost, stock and minimum.
- In Products type each size you make, its containers per sale unit, energy and labour per unit and price, and in Components what one unit of the product uses of each material.
- In Material moves log deliveries and damaged stock.
- In Production log add a row per run: planned, actual, rejects and the check result yes or no.
- In Dispatch type each delivery, in Collections each later payment, and in Jugs what went out, what came back and the deposit.
- Read Customers, Unit cost and Summary.
What is in each sheet
- Materials: received, used, balance, status (available, order or out) and the suggested order with its cost.
- Products: unit cost and margin, produced, rejected and good units, and finished stock after dispatch.
- Production log: reject rate, achievement, run cost and an alert on every row.
- Customers: balance due, credit limit, jugs held and the deposit difference.
- Unit cost: materials, energy, labour and overhead share, then the actual cost of a good unit.
- Summary: the indicators and six charts for products, cost, months and materials.
Walk-through on the numbers in the sheet
The numbers below are sample data in the sheet, not a real plant. The example has 14 materials, 4 products, 28 runs, 38 dispatch rows and 9 customers, with a target reject rate of 3%, overhead of 10% and a jug deposit of 120:
- Cost of a 330 ml carton (24 bottles): materials 35.14 (bottles 20.40, caps 4.80, labels 2.88, shrink film 1.86, carton 5.20), energy 1.50, labour 3.00 and overhead share 3.96, so the cost is 43.60 against a price of 52 and a margin of 8.40 (16.2%).
- The first run (3 October, 330 ml): planned 500, actual 480, rejects 12, so rejects are 2.5%, achievement 96% and good units 468. The run costs 20,928, so a good unit costs 44.72.
- Output: 10,506 actual units of 10,850 planned (96.8%), rejects 292 (2.8%), two check results of "no" and alerts on 9 rows, mostly for rejects above 3%. The good output equals 105,871 litres.
- Actual cost: 330 ml carton 44.83 against 43.60 calculated, 600 ml 30.85, 1.5 litre 28.16 and the jug 3.89 against 3.80.
- Materials: stock is worth 90,430.11 and five materials need ordering: the 330 ml bottle at 1,760 against a minimum of 20,000, the 1.5 litre label at 1,004, shrink film at 51.05 kg, the 24-bottle carton at 490 and the jug seal, out of stock at -63. The suggested order costs 44,878.48.
- Dispatch: worth 340,360 with 278,615 collected (236,995 at delivery and 41,620 later), so customers owe 61,745. The largest is Al-Amana supermarket at 24,000 inside its 40,000 limit; the only one over its limit is Al-Reef restaurant at 10,720 against 6,000. The estimated margin on what was sold is 78,729.60.
- Jugs: 78 jugs sit with 5 customers and 422 are in the plant out of 500, with 9,600 of deposits received. Al-Nil Contracting holds 27 jugs and has paid 2,400 of deposit against 3,240 expected, a gap of 840.
- Months: October 6,656 units and 199,565 dispatched; November to date 3,850 units and 140,795 dispatched.
Common mistakes
- Not updating Components: if you change a carton or a label and do not edit Components, cost and usage stay on the old one.
- Typing rejects in a different unit: type rejects in the sale unit (cartons or jugs), not in a count of bottles.
- Forgetting to log deliveries: stock only rises with an "in" move, so balances look lower than reality and order alerts keep repeating.
- Mixing dispatch with jug movements: selling a filled jug goes in Dispatch, while the empty jug and its deposit go in the Jugs sheet.
- Treating the target reject rate as an official standard: it is an internal target for alerts only; standards and requirements follow the competent authority's requirements.
The sheet holds records and calculations only. For a neighbouring template for a plant with packing lines and cartons see the biscuit factory sheet, and for following debts in detail see the customer accounts sheet.
Who it suits and when it is not enough
It suits a small plant with a few lines where one person writes the records. With many lines and shifts, or when you want to schedule orders onto them and read efficiency automatically, MESwiro assigns work orders to lines and shifts and works out OEE per line.
Next step
Open the sheet page and try the example or download the blank copy. Type your best-selling product and what it uses of each material, then log one run and check the cost of its unit.





