[TEMPLATE] Total Lifetime Cost of Ownership Calculator.xlsx

	A	C	E	I	J	K	L	M	N	O	P
1
2
3		This Total Cost of Ownership (TCO) calculator has been developed to help NHS Trusts identify the true financial and environmental cost of using sterile bottled water. It enables you to quantify not just the visible purchase price, but the hidden costs of logistics, storage, handling, disposal, and carbon impact.
4
5		This calculator is best used within Google Sheets; the formulas and dashboards are most congruent with this platform. Should you decide to use alternative spreadsheet programs you may find certain graphs and charts do not render correctly.
6
7
8
9		To use this calculator, follow these steps:			Savings
10		1 Enter your core details in the input fields below (either figures per department OR total known bottle use).
11		2 View the high level graph blow to understand the higher level impact.			Removing sterile bottled water use over 5 years will save:
12		3 Navigate to the specific department type tabs to see the individual department impacts.			Removing sterile bottled water use over 5 years will save:
13
14		Input
15		Organization Name	Example NHS Trust
16		Department 1	Endoscopy
17		Total number of working procedure rooms	16
18		Working days per week	7
19		Working weeks per year	52		447.26		£289,493		204,728
20
21		Department 2	Dental
22		Total number of working procedure rooms	22		Tonnes of CO₂e from entering the atmosphere		cost saving over bottled water		Plastic bottles from incineration or landfill
23		Working days per week	5		Tonnes of CO₂e from entering the atmosphere		cost saving over bottled water		Plastic bottles from incineration or landfill
24		Working weeks per year	52
25
26		Department 3	LDU
27		Total number of working procedure rooms	10
28		Working days per week	7
29		Working weeks per year	52
30
31			OR
32
33		Know annual trust bottle use
34		Estimated number of departments
35
36		MedicalOne Lease Option	Complete 72h £300 /mo
37
38
39		Assumptions
40		Water Volumes
41		Department 1 Litres per day	4
42		Department 2 Litres per day	1
43		Department 2 Litres per day	3
44
45		Costs
46		Current Cost Per Litre	£1.26
47		UK Inflation Rate	4.10%		Totals with inflation
48		Estimated orders per year (e.g. once ever 2 weeks enter 26 for the year)	26			Year 1	Year 2	Year 3	Year 4	Year 5	Total
49		Estimated Ops spend per order (e.g. Cost of Ordering, Storing, Disposal etc)	£150	1	Bottle Cost	£51,591	£53,707	£55,909	£58,201	£60,587	£279,995
50		Total estimated cost per litre (known £/bottle + calculated opperational £/bottle)	£1.65	2	Total Cost	£63,291	£65,886	£68,588	£71,400	£74,327	£343,493
51				3	Number of bottles	40,946	40,946	40,946	40,946	40,946	204728
52		Carbon Impact / Bottle	kg CO₂e.	4	Tonnes of CO2e	89.45	89.45	89.45	89.45	89.45	447.26
53		1. HDPE Production:	0.358	5	Tonnes of Plastic	4.7	4.7	4.7	4.7	4.7	23.7
54		2. Bottle Manufacturing:	0.131
55		3. Transportation:	1.379
56		4. HDPE Recycling:	0.317		This tool is designed to highlight the scale of costs and carbon emissions associated with sterile bottled water use as clearly as possible. While it provides indicative results based on typical NHS data, there may be additional factors within your organisation that affect actual outcomes. If you would like to include additional departments, sites, or data inputs, please let us know — we can adapt the calculator to reflect your specific requirements.
57		Total	2.185
58		Weight of bottle and cap (kg)	0.116
59
60		For details on our carbon calculations and collaboration with the National Green Theatres Programme, see the CO₂e calculations tab below.
61
62
63	© 2025 IF Water. Registered as Clean Water Designs Ltd. All rights reserved. Learn more at if-water.com \| Contact us at business@if-water.com
64
65