X-WHAT: Free Excel Tool Optimizes Looped Water Distribution Network Design

Some of a water utility’s most consequential decisions are buried underground: which pipe diameters to lay, how tall to raise a storage tank, how much pumping capacity to purchase. Getting them wrong can lock a city into decades of unnecessary spending. A team of researchers in Brazil and the United States now argues that the software needed to avoid those mistakes has been sitting on most office computers all along. In a study published on 29 August 2026 in the journal Water Resources Management, engineers from the University of São Paulo, the University of Texas at San Antonio, the University of Arizona, Vanderbilt University and Stanford University introduce X-WHAT — the Excel for Water system Hydraulic Analysis Tool — a free, open-source model that simulates looped water distribution networks and optimizes their preliminary design entirely inside Microsoft Excel. When the tool’s optimizer was turned loose on a network serving 44,416 people, it cut 151,790 dollars from the design bill, a 9.8 percent saving achieved by nothing more exotic than rethinking how tall and how deep the system’s storage tanks should be.

Water distribution networks are among the most capital-intensive systems a municipality will ever build, and their design is deceptively hard. The friction losses that govern how much pressure survives a journey through a pipe grow nonlinearly with flow, so the equations balancing every junction and every loop resist the tidy algebra engineers might prefer. Specialized commercial packages can handle the mathematics, but the study’s authors note that most demand paid licenses and substantial training — barriers that weigh heaviest on small utilities, cash-strapped municipalities and university teaching laboratories, precisely the settings where design expertise is scarcest. EPANET, the free simulation engine maintained by the United States Environmental Protection Agency since 2000, is primarily a simulator rather than a design optimizer. Spreadsheet attempts to fill the gap have circulated for two decades, but nearly all of them leaned on the Hardy-Cross technique, a trial-and-error scheme that iteratively corrects assumed pipe flows until the network balances.

The Hardy-Cross method, devised in 1936, asks the analyst to first imagine a plausible set of pipe flows that conserves mass at every node, then repeatedly correct those guesses until the energy balance closes around every loop. For a small classroom network the ritual is instructive; for a system with dozens of links it becomes what the researchers call tedious and time-consuming, because inventing a mass-conservative starting point for every pipe is itself a puzzle. X-WHAT abandons that workflow. Instead of iterating toward equilibrium, the tool reformulates the entire hydraulic problem as a matrix-based optimization and hands it to Excel’s built-in Generalized Reduced Gradient solver, a gradient-based nonlinear engine that has shipped with the program for decades. The solver starts from a deliberately naive guess — every pipe carrying ten percent of the network’s total demand — and still converges on the reference solution, because the conservation laws are enforced as explicit constraints rather than coaxed into existence by hand-tuned iteration. No coding, macros or add-ins beyond Solver itself are required.

The mathematical skeleton of the model is classical hydraulics. At every junction, continuity requires that inflows minus outflows minus the water withdrawn by consumers sum to zero. Around every loop, the head losses accumulated in a complete circuit must cancel, each pipe contributing a term of the form kQ|Q| raised to a power, where k encodes diameter, length and roughness and the exponent depends on the friction law. X-WHAT implements both the empirical Hazen-Williams equation, with an exponent of 1.85, and the physically based Darcy-Weisbach equation, with an exponent of 2, using an explicit friction-factor formula valid across Reynolds numbers from roughly ten thousand to two hundred thousand — the turbulent range the case studies actually occupied. Network topology is captured in two incidence matrices: one recording which pipes enter or leave each node, the other recording which pipes belong to each loop and in which direction. Filling in those matrices is the only genuinely manual step, and a companion browser-based interface now draws the network, previews both matrices and exports a package the workbook imports directly.

Where the tool departs from most of the literature is in what it chooses to price. Optimization studies of water networks routinely minimize pipeline cost alone, treating everything else as fixed overhead. X-WHAT instead assembles a full net-present-value budget spanning a 25-year lifespan at a 12 percent interest rate and 6 percent annual energy-price inflation. Pipeline costs come from a seventh-order polynomial fitted to Brazilian ductile-iron price data across diameters from 40 to 1,000 millimetres, with a coefficient of determination above 0.999. Tank material costs scale with the surface area of a steel cylinder whose volume is fixed, following Brazilian practice, at one-third of the peak day’s demand — a value users can swap for their own regulations. The most unusual ingredient is the foundation: the team modeled lateral wind pressure on an elevated tank, integrated it into a total horizontal force and a bending moment at the base, and regressed the concrete and reinforcement costs of a footing checked against tipping, sliding, shear and bending. Pumping energy, priced per kilowatt-hour over a 12-hour daily schedule at 85 percent efficiency, is converted to present value so that a taller tank’s construction premium can be weighed honestly against decades of cheaper electricity.

Validation proceeded through three networks of escalating complexity. The smallest is a two-loop, five-node system with a single tank serving 160 people, built from 100-metre pipes of 40-millimetre diameter with a Hazen-Williams coefficient of 130; two of its nodes sit ten metres above the rest, deliberately stressing the pressure budget. The second, adapted from a Brazilian hydraulics textbook, serves 12,800 people and uses Darcy-Weisbach friction. The largest, drawn from a 2004 paper in the American Society of Civil Engineers’ Journal of Hydraulic Engineering, contains eight loops, twelve interior junctions, two tanks and 21 links, supplying a city of 44,416. Against EPANET, the spreadsheet reproduced flows with a mean absolute error of 0.074 litres per second and pressures within 0.016 metres in the first case, and errors of 0.004 litres per second and 0.058 metres in the second. For the largest network, benchmarked against the published solution rather than EPANET, the mean absolute flow error was 0.13 litres per second — below 0.1 percent of the maximum value for every metric examined.

The optimization results carry a genuinely counterintuitive lesson. For the small network, the solver rejected a ground-level tank and instead specified one elevated 16.07 metres above the ground, cutting total life-cycle cost by 12 percent — a decision that mirrors what experienced engineers do in topographically awkward terrain but which emerged here purely from the arithmetic. The authors credit the wind-loaded foundation model for making that trade-off visible; without pricing the structural consequences of height, an optimizer has no way to weigh them. For the two larger networks the logic flipped. Serving tens of thousands of people requires tank volumes in the thousands of cubic metres, and the wind forces and foundation costs of hoisting so much water overwhelm any pumping savings, so ground-supported tanks won. Most strikingly, in the largest network the conventional shortcut — choosing the minimum tank depth that merely satisfies the pressure floor — would have cost 151,790 dollars more than the optimized design. The nonlinear interplay between tank material, foundation and energy costs, the researchers conclude, makes trial-and-error guessing unreliable as soon as more than one tank is involved.

The tool’s boundaries are drawn as plainly as its ambitions. Excel’s GRG solver accepts at most 200 decision variables, capping simulation at roughly 198 pipes and reserving two decision variables per tank in design runs; the case studies, the largest with 21 pipes, never approached the limit, and runtimes ranged from about three seconds to 45 seconds with the solver’s Multi-Start option probing 100 random initial points. The workbook itself is under two megabytes, runs on Excel 2013 and later, and contains no macros or code to install. Pipe sizing — selecting diameters from the commercial catalogue — remains out of reach, because it converts the problem into a mixed-integer nonlinear program that gradient solvers cannot reliably digest; the recommended workaround is to run the optimizer repeatedly over candidate diameter sets. The model is also strictly steady-state, treats pumps purely as external tank-filling elements, and neglects minor losses from bends and fittings — a simplification the team stress-tested by inflating every friction coefficient by 15 percent and watching total cost move by less than 2 percent.

Beyond consulting practice, the authors invested heavily in the classroom. The paper lays out a five-hour teaching sequence grounded in the Understanding by Design curriculum framework, moving from conservation fundamentals, to solving the five-node network by hand on a blackboard, to running X-WHAT on larger systems, to optimizing tank design, with assessment rubrics mapped to Bloom’s taxonomy. The same machinery invites exercises no licensed package would tolerate on a student budget: deleting demands to probe network resilience, swapping configurations, or even repurposing the objective function as an error metric to calibrate pipe roughness against observed pressures and flows. The fluid parameters can be changed, the authors note, opening the door to oil and gas pipeline networks. Everything — the workbook, the web interface, step-by-step guides and the supplementary material — is available through the project’s GitHub repository under a Creative Commons open-access license, with publication costs covered by the Brazilian federal agency CAPES.

The broader significance of X-WHAT may lie less in spreadsheets than in who gets to participate in infrastructure design. Hydraulic modeling has spent decades migrating toward ever more powerful specialized platforms, with accessibility often an afterthought; this study runs the other direction, betting that transparency and ubiquity matter as much as raw capability for preliminary design and education. A tool that opens in the same program used to draft budgets, that displays its conservation equations in visible cells, and that a student can debug line by line lowers the entry cost of a discipline whose consequences are measured in public health and public money. The team identifies full pipe-sizing optimization, valve and pump dynamics, and minor-loss handling as priorities for future versions. For now, the message to utilities, instructors and engineers in under-resourced settings is concrete: the gap between a conventional tank design and a near-optimal one — nearly ten percent of an entire network’s cost — can be closed by an office program most people already have open.