Modeling and Design Optimization of Looped Water Distribution Networks using MS Excel: Developing the Open-Source X-WHAT Model

Abstract

Cost-effective water distribution network (WDN) design is crucial for reliable urban water supply. This paper introduces the open-source Excel for Water system Hydraulic Analysis Tool (X-WHAT) for WDN simulation and preliminary design. Unlike traditional spreadsheet approaches based on Hardy-Cross iteration, X-WHAT uses Excel’s built-in Generalised Reduced Gradient (GRG) solver with a direct matrix formulation, requiring no mass-conservative initial estimates or iterative flow corrections. The mathematical framework covers network topology, mass and energy conservation, and a multi-component cost function including tank material, structural foundation, and pumping energy costs. X-WHAT supports educational and preliminary engineering applications without coding expertise, while enabling sensitivity analysis for demand, configuration, cost, and hydraulic-performance changes. Three case studies, validated against EPANET and published literature, demonstrate the tool’s effectiveness. A tentative class organisation and unit design following the Understanding by Design (UbD) approach are also proposed. Through optimization, X-WHAT achieved a cost reduction of 151,790 USD (9.8% of total network cost) compared to standard design in a network serving 44,416 people. Supplementary material provides a step-by-step guide.