Simulation using a Spreadsheet (a PDF version is here)

If you are unfamiliar with using a spreadsheet, a tutorial can be found here. A good general resource on modeling and simulation can be found here: http://www.csi.uoregon.edu/nacse/ecosim/

Modeling processes in the sciences have become useful research tools in many areas of the sciences and the availability of computers extends these techniques to any that wish to use these tools. Mathematical representations of biological systems have long been used by ecologists to model various processes. The models are useful to access how much is understood about a real system. If the model is a good representation of what goes on in the "real world", then the author is fairly confident that they understand the phenomena. If, however, the model doesn't follow the "real world", then it's back to the computer. Some models are so good they have predictive value. Models that predict locust outbreaks in Africa are routinely used to predict when control measures are needed in a rejoin. At home, farmers routinely use models to predict when herbicides, fungicides, and insecticides should be applied to their fields. On a grander scale, climate models are used to shape laws and international treaties. One of the first models written was one to mimic population growth. A model of the simulation process can be seen in the following diagram.

The first model we'll explore is the geometric model. According to these equations, a population grows uninhibited over time. The important variables in this simulation are the starting population size and the rate of increase. The equation for this simulation is: dn/dt = r*N where dn/dt is the change in the population size (n) over time (t). The variable "r" is the rate at which the population increases per generation (between 0.0 and 1.0). The "d" part of dn/dt just stands for "change in" (delta) so it is read as "change in number over change in time". The N_{(t +1)} = N_t + dn/dt  is read as the "population size at time +1 is equal to the population size at time 0 plus the change in size over the change in time". This scenario will result in an exponential increase in the population size.

• Open an excel spreadsheet and type "Exponential Growth Model" in cell A1.

• In cell A3 type "Start N:" and in cell B3 type "10". We will start with a population of 10.

• In cell A4 type "r:" and in cell B4 type "0.2". The growth rate is "0.2"

• In cell A9 type "t" (our time column) and in cell B9 type "N" (population size at time t

• In cell A10 type "1", the start of time.

• In cell A11 type "=A10+1", the formula for advancing our time unit by one. When you hit return, the cell should show "2".

• Copy the contents of A11 to the clipboard. Select and highlight cells A12 through A39 and paste the clipboard data into these cells. Those columns should now show the numbers 1-30.

• In cell B10 type "=B3". We're just copying the starting population size from cell B3. It should say "10"

• In cell B11 type "=B10+($B$4*B10). The dollar signs are a trick t0 anchor that part of the formula so that it always points to our rate of increase in cell B4. Cell B11 should say 12.

• Copy the formula in B11 to the clipboard. Highlight cells B12 through B30 and paste the formula into those cells.

• To see the results (which are probably wider than the column, go to the top of the page with the column headings (A,B,C...) and then position your cursor over the line between B and C. It will switch to a cursor that looks like this: . Then click and drag the column so it's larger and you can see all the numbers

• Let's make a graph. First, select rows B10 through B39 and click the chart wizard button ().Change to a line chart, probably the first one. Click Finish and your chart will be displayed.

• Try different starting population sizes and rates of increase (vary one or the other, not both at the same time).

• Save your file to your W drive as "Exponential Model".

Figure 1. Human Population Growth

The geometric model is not very satisfying since it doesn't seem to mimic the way real populations behave. Real populations don't expand indefinitely (except, perhaps humans; Figure 1.  Instead, they tend to increase for a short time, then level off. Figure 2 depicts the growth of bighorn sheep populations in the Rockies from the early 1800's to about 1940. Note the rapid expansion of the population following it's initial introduction, followed by a leveling-off of the population at about 1.75 million sheep. This is a typical population response seen in most natural populations. It's as if the population has filled to environment. This "filling" of the environment with a particular species is the "carrying capacity" for that species. For birds, the limiting factor in the environment may be the availability of next sites. Other species may be limited by the availability of food or water. Whatever the cause, the environment is capable of supporting a limited number of a particular species. That number is the carrying capacity, or it represents the environmental resistance to further population growth. The carrying capacity of a system may change over time. Droughts, pestilence, and other climatic changes may temporally increase or depress the carrying capacity of the environment. These factors may be responsible for the "bumps" in the above data (more on that later).

Figure 2. Bighorn sheep populations

To be realistic, our model needs to reflect the carrying capacity of an ecological system. Ecologists have settled on the variable "K" to represent the carrying capacity of the environment for a given population of organisms. For our simple model, it doesn't matter if the limiting factor is nest sites, or the availability of food (although different limiting factors could be easily accommodated. This equation is essentially the same as our first try, with the exception of the (K-N)/K addition (K is the carrying capacity, N is the current population density). The (K-N)/K term in the equation "puts the breaks" on population growth as the population reaches carrying capacity. Let's fix our first try to reflect this hopefully improved model. The equations are shown below.

• Open your Exponential Model on your W drive if it isn't already open.

• Replace cell A1 with "Logistic Growth". Then use the "File.. Save as" excel option to save the file as "Logistic" on your W drive. This insures that you won't overwrite your original model.

• In cell A5 type "K:". In cell B5 enter "400". The carrying capacity for our population will be 400.

• In cell B11 you put the new equation. Type "=B10+($B$4*B10)*(($B$5-B10)/$B$5)" Compare this to the above equation to see how it works.

• Copy the contents of B11 to cells B12 through B39. Your graph probably changed.

• Begin with a starting population of 10, a rate of increase of 0.5 and a carrying capacity of 400. Then try other combinations (changing only one at a time) to see how your model works.

Still, these models are not completely satisfying (they don't have the jiggles seen in Figure 2), so some more tweaking is needed. The current model assumes that a population responds instantaneously to changes in population size. This assumption is unreasonable. If the population has reached it's carrying capacity (K), and 50% or 60% of the females are pregnant, they'll still give birth, causing the population to overshoot the carrying capacity. In addition, some stresses don't assert themselves immediately. For example, as population density increases, it would take some time for individuals to become stressed. Hormones kick in and  eventually the individuals are not as capable of reproduction. An example of a change in reproductive rate with increasing population density is shown in Figure 3. Under low density conditions the crustacean Daphnia (water flea) produces 4 offspring per day while under high density conditions no offspring are produced. Note also that survivorship is adversely affected by increasing population density.

Figure 3. Changes in reproduction of Daphnia under different population pressures

This reproductive time lag is represented by variable c in the model; see model below. Our population doesn't respond instantaneously, but is lagged by one generation.

• Open the Logistic model if it's not on your screen. Replace cell A1 with "Reproductive Lag Model" and use the "File.. Save as..." menu function to save your file as "Reproductive"

• In cell A6 type "c:" and in B6 type "1". It turns out that it's difficult to put together a formula that will read this number. It's much easier to change the formula in the right places. These cell entries are just used as reminders.

• Change the contents of cell B12 to =B11+($B$4*B10)*(($B$5-B10)/$B$5). The red highlights show where the formula is changed. The easiest way to do this is to click on B12 and then edit the formula in the formula box (fx box) at the top of the form. You can place your cursor on the formula, use the left and right keyboard arrows to move around and delete and change the formula. Press return when your finished. What have we just done (be able to explain how this fits the above equation for a time lag of 1)

• Copy the contents of cell B12 to B13 through B39.

• Try the following:

  • N=10, r=.2, K=400

  • N=10, r=.5, K=400

  • N=10, r=.9, K=400

  • Then hold r at a low or high value and change K, then N. What's going on?

• How could you simulate a reproductive lag of 2? Hint: B12 was changed for a lag of 1, so B13 needs to be changed to force a lag of 2. Don't forget to copy the contents of B13 to B14 through B39.

With a reproductive lag we find that populations with a low intrinsic rate of increase (r) survive longer than those with a high reproductive rate. In addition, the stability of those populations with low reproductive rates is greater (less oscillations in their growth curves). Finally, populations with high reproductive rates also are prone to outbreaks (population increases far in excess of the carrying capacity).

In the real world conditions affecting the reproductive rates of organisms (such as overcrowding, drought, disease, etc) are not likely to last over an extended period of time. In the short-term, populations with low reproductive rates can wait out the environmental problems while those with higher reproductive rates are more likely to go extinct locally. We also find that conditions that adversely affect the carrying capacity can also make a species more prone to local extinction (especially those with a high r). Activities such as deforestation, water polluting, and overuse of pesticides can all decrease the carrying capacity (increase the environmental resistance) and make populations more susceptible to local extinction.

Recognizing the differences between species with high rates of reproduction and those with low intrinsic rates of increase, ecologists often describe a species as being "r-selected" or "K-selected" (Table 1). Those species whose populations levels are controlled by their reproductive rates are defined as r-selected while those whose reproduction is controlled more by environmental resistance are termed K-selected.

Table 1

We're getting close. Real populations show fluctuations in density in response to randomly changing environmental challenges (as well as intrinsic rhythms). In some cases the fluctuations are clearly due to changes in the environment, while at other times the variations are not as clear. We can mimic these random effects by randomly adding or subtracting individuals from the current population size:

In the above formula the size of N is decreased by a random amount and then increased by a random amount (in Excel the RAND() function generates a random number between 0 and 1, so if we multiply that random number by N, N will be decreased. We then add on another random amount less than N. Therefore, N will sometimes be larger or sometimes smaller. This affects the ability of our model organisms to track where they are in relation to the carrying capacity (K). Sometimes they "think" they are below the carrying capacity, when actually they may be above it (and visa versa). One could imagine an r-selected beast, such as a bug, wouldn't have a good idea of their relation to the environment and could make such a "mistake". Alternately, if the environment is rapidly changing the model population might have trouble tracking it accurately. The above model would also fit that scenario.

• Open the Logistic model and change cell A1 to "Stochastic Model". Use the "File..Save As" menu function and save the new file as "Stochastic" on the W drive.

• Change the formula in cell B11 to "=B10+(($B$4*B10)*($B$5-(B10-(RAND()*B10)+(RAND()*B10)))/$B$5)" Remember that you can edit the existing formula rather than type in the whole new formula. Correlate the formula you just entered with the population model formula shown above. Watch out for the parentheses. If you get an error message, do not let excel fix your formula, it'll probably do it wrong.

• Copy the formula in B11 to the clipboard and then paste it in cells B12 through B39.

• Change the starting values to N=100, r=.2, K=1000 and the program will generate a new graph.

• Lets modify the model so that it will run for 100 generations.

  • Select cells A39 and B39 and copy them to the clipboard. Select cells A40 through A109 and paste the formulae in the clipboard into the new range.

  • Now we need to fix the graph so it shows more than 30 generations. Click on the graph and the rows that will be graphed will be surrounded by a blue rectangle that represents the graph range. It should look like this:

  • Next, grab the lower right or left handle (the dot- ) and your cursor will change to a double arrow (). Click and drag the box so it now covers 100 generations (From B10 through B109). When you scroll up the graph will now show 100 generations.

• Up to this point, the models we used were deterministic, meaning that each time you run the model, you'll get exactly the same results as long as you don't change the input. This model is stochastic and will change each time you run it, giving you a a view that is more similar to the real-world situation. Compare the above graph to figure 2, for example. To use these models, you run them over and over using the same input data. Each run will result in a slightly different population projection and graph. To run the model with the same data input, you can force a recalculation by hitting the Ctrl-S key.

• Try ten runs or so by repeatedly striking the Ctrl-S key. More likely than not, each of your population runs will succeed in reaching 100 generations.

• If, by chance, one of your runs fails to reach 100 generations and the population goes extinct, you graph will be similar to the following:

• Now increase r in units of .1 (r=.3, r=.4, etc). What effect does this have on your populations. At what point do the populations begin to regularly fail to reach 100 generations? What type of animal might you be modeling at higher reproductive rates? What type of real-world situation(s) might this represent?

• Try varying the stating population and carrying capacity (one at a time, not together) for low and high reproductive rates. What situations are you modeling here?