OPTIMIZATION OF WATER DISTRIBUTION SYSTEM USING SIMPLEX ALGORITHM ON MICROSOFT EXCEL

This study applies the Simplex linear programming (LP) algorithm—implemented using Microsoft Excel Solver to optimize a simulated water distribution system (WDS) through an accessible and fully reproducible spreadsheet workflow. The model represents a hypothetical urban network of 3,650 buildings arranged in a 10 × 10 grid (100 junctions, 180 pipes), and seeks to minimize installation and operational costs while satisfying hydraulic and design constraints. Hydraulic behavior was computed using the Hazen–Williams equation (C = 150), with optimization performed in Excel Solver’s Simplex LP engine and independently cross-validated using the HiGHS optimizer in Python. The optimized configuration, consisting of a 15 m reservoir elevation and 150 mm pipe diameter, reduced the total system cost from USD 375,000 in the baseline design to USD 195,000, achieving a 48% improvement while maintaining acceptable head-loss (5.59 m ≤ 20 m) and velocity (0.85 m/s within the 0.3–2.5 m/s recommended range). Although the model is limited to steady-state hydraulics, uniform pipe diameters, and simplified friction assumptions, its transparency, low computational requirements, and ease of implementation make it well suited for academic instruction, rapid preliminary design, and resource-constrained municipal environments. Sensitivity analysis (±10–15% demand; ±10% Hazen–Williams roughness coefficient C) indicates that the optimal design is robust under moderate parameter uncertainty. Future research will integrate EPANET for nonlinear hydraulic verification and extend the approach to larger networks and multi-objective optimization.