Assignment 9: A spreadsheet groundwater model

In this assignment, you will work with an Excel spreadsheet that uses a finite-difference approximation
to simulate steady-state groundwater flow in an aquifer. The model will be calibrated using data from
monitoring wells. It will then be used to predict the maximum rate at which a new pumping well can
extract water without causing stream water to enter the aquifer.
Please submit answers in a separate document.

Study Area
The 50 ft thick unconfined aquifer consists of mostly sand with some thin clay layers, and receives 18
inches a year of recharge. Slug tests and pumping tests have been conducted and found that the
hydraulic conductivity is between 1 and 20 ft/day.
For the modeling, it is assumed that the aquifer is at a steady-state over the long term and that it is
homogeneous and isotropic.
The stream is on the north end of the aquifer and has a
water level of 110 ft above sea level on the west side
and 101 feet on the east side.
There is a groundwater divide to the south (so no flow
crosses the boundary). The east and west boundaries
represent the physical limits of the aquifer, so these
are also zero flux (no flow) boundaries. The aquifer
dimensions are 900 ft (N-S) x 1800 ft (W-E).
The location of the proposed new pumping well is
shown on the map.

The Spreadsheet Model

The spreadsheet model discretizes the aquifer (breaks the aquifer into grid cells) and solves for
hydraulic head in the center of every cell simultaneously. Each Excel cell represents a 50 ft x 50 ft area
of the aquifer.
Because the hydraulic head in each cell is affected by the neighboring cells, the model will need to
“iterate”, or repeatedly calculate hydraulic head at every one of the cells until the hydraulic heads satisfy
both Darcy's law and conservation of volume (the amount coming into each cell has to equal the amount
going out).
Download and open the Excel file. When you first open the excel spreadsheet, it will probably give you
a warning about a circular reference. That is expected, because the solution requires iteration (as
described above). To enable iterations in Excel 2013 for PC: Go to the File pulldown menu and choose
options, then Formulas. Click on
the checkbox to Enable Iterative
Calculations. Change the
maximum number of iterations to
5000 and maximum change to
0.0001 then say OK. If you are
using a different version of
Excel, you can use the Excel help menu and search for “iterations”.

Calibrating the Model

Before you can use the model for prediction, the hydraulic conductivity must be calibrated by
comparison to observations. Right now, the model is calculating the hydraulic heads for a hydraulic
conductivity of 1 ft/day. The pumping well rate is zero ft3/day because the well has not yet started
pumping.

There are five observation wells where water levels have been measured over several years and you will
use the average values to calibrate the steady-state model. The measured hydraulic head values are
shown in the spreadsheet just to the right of the shaded region. The spreadsheet also shows the simulated
values. If the model is well calibrated, the simulated values should be similar to the observed values.

Enter the known recharge rate of 18

inches/yr if it is not already entered.
Excel should recalculate the
hydraulic heads throughout the
aquifer. You should observe that the
simulated hydraulic gradient is too
high, causing the hydraulic heads at
the observation wells to be too high.
NOTE: Some students have found
that they need to click on
“Calculate” (located in the lower left
hand corner) several times to ensure
that Excel completes enough iterations to reach a stable solution. So try clicking on “Calculate” after
each change. When your solution is stable, the calculated hydraulic heads will stop changing.

Question 1:

A) To lower the observed hydraulic heads in the aquifer, should you increase or decrease the
hydraulic conductivity in the model? Explain your answer using Darcy's Law.
 B) Change the hydraulic conductivity
to 20 ft/day by replacing the "1"
with a "20" and clicking “enter”.
What happens to the hydraulic head
values in the aquifer? Does this
agree with your expectations?

Vary hydraulic conductivity between 1 and 20 ft/day to find the K (to the nearest ft/day) that provides
the best match between the simulated and observed hydraulic heads. If you are strategic, you do not
have to try every possible K value.
2. Which hydraulic conductivity value yields the best match to the observed hydraulic heads? This is
your “calibrated” hydraulic conductivity.

Now that the model is calibrated, simulate the effect of a new pumping well with an extraction rate of
30,000 ft3/day per day. Change the pumping rate in the spreadsheet and hit enter.
The pumping well is located in
the white cell. You should see
the hydraulic head decrease.
Note: Observed and simulated
values will no longer be similar.
You have introduced a major
change to the hydraulic heads by
adding pumping.
Effects of Pumping
3. A) Based on the model results, would a pumping rate of 30,000 ft3/day cause stream water to
flow toward the well?

B) How much drawdown occurs at the pumping well? (You might have to change the pumping rate back
to zero to determine the hydraulic head before pumping).

C) Given that the aquifer thickness is 50 feet, why might that amount of drawdown be unrealistic?
(What would happen to your pump?)

D) Even if this much drawdown were possible, what negative impacts could this much drawdown
have on the aquifer? (Hint: Consider the description of the aquifer material provided on the first
page).

Use trial and error to find the highest pumping rate (to the nearest 1,000 cubic feet per day) that can be
pumped without water flowing from the stream to the well. There should be a “groundwater divide” in
between the stream and the well. On the north side of the divide, groundwater flows to the stream. South
of the divide, groundwater flows to the well.
5. Highest pumping rate: _______________

6. This simulation was for steady-state conditions (e.g., long-term averages of recharge and pre-
pumping hydraulic heads). In real life, streams have water levels that vary through time and
recharge changes through time. How might those changes affect your conclusions?

7. GLY 5827 (1 pt XC GLY 4822) The spreadsheet model can help you get a general feel for the
relationship between hydraulic conductivity and the shape of the cone of depression. For your
optimal pumping rate, increase and decrease the hydraulic conductivity and describe how the
cone of depression is affected. Does this result agree with your expectations based on what you
learned in Module 8?