Environmental Engineering Reference
In-Depth Information
analyses, particularly when the model needs to be run for a number of years. When
this happens, you need to use a proper programming language.
Tips for effective spreadsheet use:
●
Declare all your variables at the top of the spreadsheet, so that you can change
the numbers easily. No formula should include a number, only cell references.
This is a useful discipline for when you start to programme.
●
Use relative cell referencing: In Excel, = $A$4 means that cell A1 will be refer-
enced always, to wherever you copy the formula. = $A1 means that as the
formula is copied, the column will stay constant but the row will change, = A$1
keeps the row constant but lets the column change, = A1 means the formula is
completely relative. The key F4 lets you switch between these.
●
Plot up relationships between variables (for example, between the population
size and the mean hunter cost, or between total deer population size and the
number of offspring produced), and check that they are as you expect.
Deer model—spreadsheet version
We do the model with constant survival and hunting mortality rates, and density-
dependent fecundity as in the equations above. The initial parameter values are:
Survival rates:
Fecundity rate:
S
1
0.6
a
0.6
S
2
0.9
b
0.02
S
A
0.8
Harvest rate:
H
0.05
The spreadsheet should comprise a series of columns, one for each year, in which
the rows are:
Row 1 (juveniles): Adults last year multiplied by the fecundity function above
Row 2 (sub-adults): Juveniles last year multiplied by
S
1
Row 3 (adults): Sub-adults last year multiplied by
S
2
plus adults last year multi-
plied by
S
A
minus adults last year multiplied by H
.
This set of fecundity values gives a fecundity rate that declines fairly linearly
from about 0.65 offspring per adult at very low population sizes to about 0.2 off-
spring per adult at a population size of 100. Starting from any population size and
structure, the model is run until the stable population size and structure is reached,
which in this case is a population of 68 individuals of which the majority are adults:
Stable age structure:
Age 1: 18
Age 2: 11
Adult: 39
