Spreadsheet Exponential Population Growth Model

Earlier I covered applying spreadsheets to the old BSCS sparrow lab-Part One.

Now for Part Two:

After the students build their own spreadsheet models of the hypothetical sparrow population, as a class we discuss the parameters that taken together determine population growth or decline.  I guide the discussion with questions until the students are able to articulate the four factors that determine population growth:  birth rate, death rate and migration (emmigration and immigration).  I am careful to make sure the discussion includes reviewing a working definition of a population and that the factors identified are rates and therefore have a time element to consider.  At this point we revisit the sparrow spreadsheet model and identify how these four parameters are taken into account in the actual cells of the spreadsheet.  Students quickly identify that there is no migration terms and that the birth rate is taken care of when each pair of sparrows produce 10 offspring (column D).  They have a more difficult time with the death rate.  There is no explicit cell with a death rate parameter but only the offspring in column D move to the next year–death is taken care of by omission.

The discussion then moves to asking the students to consolidate these four factors into one term–a per cent of increase or r“.  We also establish a variable for the population size at any particular time interval: “Nt“.  The students are now challenged to represent the exponential population growth in a single equation with the variables “r” and “Nt“.

Eventually the class arrives at the following:

The new population is equal to the previous population + rate of increase times the previous population


Nt = N(t-1) + r*N(t-1)

At this point the students are directed back to their spreadsheets and challenged to develop a new model based on this equation.  This time their instructions include assigning initial values to the variables.  On the board, again I help them set up a possible structure:

r = 0.1
N = 10
Time Interval Population
0 10
1 11

It’s now up to them to create the spreadsheet using the formula.  Interpreting normal algebraic notation into spreadsheet notation is a bit of a challenge but they usually figure it out.  By iterating the formula (using the results from one time interval as the basis for the next) the students can explore and create models that without computers would require a familiarity with calculus.  Once the students have created their simple model I have them expand it to 300 generations or time intervals and graph the results.  When trying this with your students make sure that you don’t get too explicit with your help—students have to work at building this model but it is doable for most.  You should try it as well before checking on the spreadsheet embedded here:

If the spreadsheet is not loading you can find it at: Exponential Model

There is one spreadsheet technique that you need to be aware of to make this model–the difference between relative reference and fixed reference in a cell’s formula. Since the reference to “r” always points to the same cell, it should not change. The default in a spreadsheet formula reference is “relative”, which changes. You can make a cell reference fixed by adding a dollar sign in front of both components of a cell’s address. For example referring to cell: B1 is a relative reference but referring to $B$1 is a fixed reference.

Normally when I explore this topic in my class, we can pretty easily get through the sparrow population model and the exponential model in one hour. Modeling is an additive process and this is only the start. Note that the procedure thus far has only added a bit of complexity at each step–with only rudimentary math operations. The next step will be to explore the logistic model. I try and reserve at least a day for it along with a homework assignment. I’ll cover the logistic in the next post.




1 Comment »

RSS feed for comments on this post. TrackBack URL

Leave a Reply

You must be logged in to post a comment.