OPTIMIZATION OF WATER DISTRIBUTION SYSTEM USING SIMPLEX ALGORITHM ON MICROSOFT EXCEL
Article Sidebar
-
Simplex Algorithm, Microsoft Excel Solver, Linear Programming in Hydraulic Modeling, Water Distribution System, Cost Optimization
Abstract
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.
Full text article
References
Al-Najjar, B., Lee, S., & Jung, D. (2023). Advances in assessing the reliability of water distribution networks: A bibliometric analysis and scoping review. Water, 15(5), 986. https://doi.org/10.3390/w15050986
American Water Works Association (AWWA). (2017). Manual M32: Distribution network analysis for water utilities (3rd ed.). AWWA Press.
Awe, O., Adie, D. B., & Adesoye, P. O. (2020). Hydraulic performance analysis of Kurudu PSHS water distribution system. Journal of Water Resource and Protection, 12(4), 312–327. https://doi.org/10.4236/jwarp.2020.124019
British Standards Institution (BSI). (2000). BS EN 805: Water supply — Requirements for systems and components outside buildings. BSI Standards Publication.
Chandrakantha, L. (2011). Using Excel Solver in Optimization Problems. Proceedings of the 23rd International Conference on Technology in Collegiate Mathematics. Retrieved from https://www.math.odu.edu/~bogacki/epictcm/i/23/C006.html
Dai, P. D. (2021). Optimal Pressure Management In Water Distribution Systems Using An Accurate Pressure Reducing Valve Model Based Complementarity Constraints. Water, 13(6), 825. https://doi.org/10.3390/w13060825
Dini, M., Asadi-Aghbolaghi, M., & Shokoohi, A. (2022). Performances of pressure reducing valves in variable demand conditions: Experimental analysis and new performance parameters. Water Resources Management, 36(10), 2639–2652. https://doi.org/10.1007/s11269-022-03166-5
Ezeokwelume, O. V. (2016). Solving Linear Programming Problems and Transportation Problems using Excel Solver. International Journal of Scientific & Engineering Research, 7(9), 2220–2228. Retrieved from https://www.ijser.org/researchpaper/Solving-Linear-Programming-Problems-and-Transportation-Problems-using-Excel-Solver.pdf
Gu, Y., & Sioshansi, R. (2025). Coupling water–energy nexus operational models: Implications for optimization and planning. Applied Energy, 361, 122924.https://doi.org/10.1016/j.apenergy.2024.122924
Hazen, A., & Williams, G. S. (1902). Discussion of “An equation for the flow of water in pipes”. Transactions of the American Society of Civil Engineers, 47, 539–546.
Janus, T., Ulanicki, B., & Diao, K. (2023). Optimal scheduling of variable speed pumps with mixed integer linear programming. CCWI 2023, 24(7). https://doi.org/10.48550/arXiv.2309.04715
Jenks, N., Wu, W., & Mitrovic, D. (2024). Review of metaheuristic methodologies for leakage reduction and energy saving in water distribution networks. Water Resources Management, 38, 3973–4001. https://doi.org/10.1007/s11269-024-03822-y
Luan, H., Wu, W., & Hou, J. (2023). A GPU-accelerated and LTS-based 2D hydrodynamic model for the simulation of rainfall-runoff processes. Journal of Hydrology, 623, 129735. https://doi.org/10.1016/j.jhydrol.2023.129735
Microsoft. (2023). Use the Solver add-in to define and solve a problem. Microsoft Support. https://support.microsoft.com/office/solver
Moeini, R., Sela, L., Taha, A. F., & Abokifa, A. A. (2023). Bayesian optimization of booster disinfection scheduling in water distribution networks. Water Research, 242, 120117. https://doi.org/10.1016/j.watres.2023.120117
Rossman, L. A. (2000). EPANET 2: Users manual. U.S. Environmental Protection Agency.
Shafaei, M. (2024). Impact of Pipe Age on Hazen-Williams Coefficient Accuracy in Water Distribution Systems. Journal of Hydraulic Engineering, 150(8), 123-130.
Shao, Y., Zhou, X., Yu, T., Zhang, T., & Chu, S. (2024). Pump scheduling optimization in water distribution system based on mixed integer linear programming. European Journal of Operational Research, 313(3), 1140–1151. https://doi.org/10.1016/j.ejor.2023.08.055
Sharma, J. K. (2017). Operations research: Theory and applications (6th ed.). New Delhi: Macmillan.
Smith, A., Perelman, G., & Housh, M. (2023). Adjustable robust optimization for water distribution system operation under uncertainty. Water Resources Research, 59(12), e2023WR035508. https://doi.org/10.1029/2023WR035508
Tello, J., Bartz, J., Khosravi, M., Eliades, D., & Kyriakou, M. (2024). Large-scale multipurpose benchmark datasets for assessing data-driven deep learning approaches for water distribution networks. Engineering Proceedings, 69(1), 17.https://doi.org/10.3390/ecws-7-11242
Thomas, M., & Sela, L. (2024). A mixed-integer linear programming framework for optimization of water network operations problems. Water Resources Research, 60(2), e2023WR034526. https://doi.org/10.1029/2023WR034526
Walski, T. M., Chase, D. V., Savic, D. A., Grayman, W. M., Beckwith, S., & Koelle, E. (2017). Advanced water distribution modeling and management (3rd ed.). Bentley Institute Press.
Zhang, H., Wang, Y., & Liu, X. (2024). Multi-objective optimization in water distribution systems: A review of methods and applications. Journal of Hydroinformatics, 26(2), 245–264. https://doi.org/10.2166/hydro.2024.130
Authors
Copyright (c) 2026 Stephen E. Iheagwara, Ndubisi D. Ayebamieprete, Eniola P. Apalowo
This work is licensed under a Creative Commons Attribution 4.0 International License.
Authors who publish with this journal agree to the following terms:
- Authors retain copyright and grant the journal right of first publication with the work simultaneously licensed under a Creative Commons Attribution License [CC BY-NC-SA 4.0] that allows others to share the work with an acknowledgment of the work's authorship and initial publication in this journal.
- Authors are able to enter into separate, additional contractual arrangements for the non-exclusive distribution of the journal's published version of the work, with an acknowledgment of its initial publication in this journal.
- Authors are permitted and encouraged to post their work online.