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

TL;DR

The paper develops X-WHAT, an open-source Excel-based tool for modeling looped water distribution networks under steady-state, incompressible flow. It replaces the Hardy-Cross iterative approach with the Excel solver, enabling hydraulic simulation (WFP) and design optimization (DOM) without coding, and provides a rich cost framework including pipeline, tank material, foundation, and pumping energy. Three numerical case studies validate the solver against EPANET and literature, showing MAEs at or below 0.1% for flows in larger networks, and demonstrate notable cost reductions (up to about 10-12%) when applying the optimization to tank depths/heights under realistic constraints. The tool emphasizes educational utility, rapid sensitivity analyses, and open accessibility, while acknowledging limitations in network size and full pipe-diameter optimization, and outlines future enhancements for valves, minor losses, and broader applicability.

Abstract

Cost-effective water distribution network (WDN) design with acceptable pressure performance is crucial for the management of drinking water in cities. This paper presents a Microsoft Excel tool to model, simulate, and optimize WDNs with looped pipelines under steady-state incompressible flow simulations. Typically, the hardy-cross method is applied using spreadsheet calculations to estimate discharges. This method requires mass-conservative initial estimates and requires successive iterations to converge. In this paper, however, we develop an alternative method that uses the built-in solver capabilities of Excel, does not require initial mass-conservative estimation, and is free of flow corrections. The main objective of this paper is to develop an open-source accessible tool for simulating hydraulic networks also adapted for teaching and learning purposes. The governing equations and the mathematical basis for the hydraulic modeling of the system are mathematically described, considering the topology of the network, mass and energy conservation, cost of tank material, foundation, and cost of pumping energy to fill the tank. The use of this tool is encouraged at the undergraduate and graduate engineering levels, as it offers the opportunity to address complex concepts in a comprehensive way using a spreadsheet that does not require coding expertise. Hence, users can debug all cells and understand all equations used in the hydraulic model, as well as modify them. To demonstrate the model capabilities, three practical examples are presented, with the first one solved step by step, and the results are compared with the EPANET and with the results reported in the literature. Using the optimization method presented in this paper, it was possible to achieve a cost reduction of 151,790 USD (9.8% of the total cost) in a network that supplies a 44,416 population.

Figures (9)

• Figure 1: Notation used in the paper for describing the WDNs Components. Nodes and links are continuous numbered. Loops follow the clockwise convection. Nodes, tanks, and pumps are defined by their topographic elevation. Tanks have their dynamic pressure defined by $P_{\mathrm{r}}/\gamma$.
• Figure 2: Pump to tank scheme, where the manometric head gain $H_{\mathrm{m}}$ is calculated accounting for the head losses in the pipe and the geometric differences within the pump level ($z_{\mathrm{p}}$) and the bottom of the tank $z_{\mathrm{b}}$.
• Figure 3: Static representation of the lateral forces acting in the tank surface, where (a) shows the lateral forces acting in the tank. The hydrodynamic force is modeled through $k_{\mathrm{w}} (h_b + h)^p$, with $k_{\mathrm{w}}$ being wind drag coefficient [$\mathrm{F}\cdot \mathrm{L}^{-1(2+p)}$], resulting in a force per unit elevation [$\mathrm{F}\cdot \mathrm{L}^{-1}$]. This force is integrated to produce $F_{\mathrm{w}}$ that represents the total force per unit of tank width that if multiplied by the diameter of the tank, gives the total lateral force at the foundation. This lateral force can be used with the lever arm $r_c$ to calculate the bending moment at the foundation. Alternatively, the total bending moment using static moment formulation is presented in (b), where $M_{\mathrm{r}}$ is the total bending moment projected at the tank lateral surface as a function of the tank diameter and pressures. At the foundation level, the normal force is the tank weight, and the horizontal force equals $F_{\mathrm{k}}$. The variable $r_c$ is the lever arm and $\gamma_{\mathrm{H_2O}}$ is the specific weight of the water.
• Figure 4: Foundation cost model in comparison with a regression model, where a structural foundation model was run for combinations of 10, 100, and 1000 $\mathrm{m^3}$, $\mathrm{kN.m}$, or $\mathrm{kN}$ of tank volume, lateral bending moment, and horizontal wind force. The regression parameters of Eq. \ref{['equ:foundation_costs']} are presented in the figure.
• Figure 5: General data entry flowchart. No macros or VBA coding are required to run the model. Boundary conditions can be specified for nodes where the pressure is known.