Solving Optimal Groundwater Problems with Excel

Kimberly Burnett, Water Resources, James Roumasset, Christopher Wada, Education, Environment, Publications

Abstract: Welfare maximizing management of coastal groundwater requires a sequence of pumping targets, typically terminating with a constant withdrawal rate in the long run. In contrast, managing according to sustainable yield at best identifies the constant rate of pumping in the long run. We illustrate an accessible solution method, using Excel Solver to find the optimal transition paths of groundwater pumping, price, and head level and the corresponding solutions in the long run. The developed framework is applied to the Pearl Harbor Aquifer (PHA) in Hawaii using data from previous studies. Each step of the solution process is described, including setting parameter values and defining objective, variable, and constraint cells in Excel to facilitate successful replication of the results. Possible extensions are also discussed such as watershed conservation, protection of groundwater dependent ecosystems, and management of multiple aquifers.


Wada, Christopher A., et al. “Solving Optimal Groundwater Problems with Excel.” Applied Economics Teaching Resources (AETR) 5.2 (2023).