• To learn how to put a delta-based model in Excel form.
• To see an example of creating a model from biological data.
• To adjust the model to look like an actual set of data.
• To test the original purpose of the Lotka-Volterra model.

We will be spending the next two weeks in lab putting together a predator-prey model for snowshoe hares and Canada lynxes. We have over one hundred years of trapping data that have been used to estimate the populations of these two species in the Canadian Northwest. This data has been used to validate the kind of predator-prey model we’ve studied in class because that model predicts the kind of repeated peaks-and-valleys you see in the actual hare and lynx data.

We will proceed in three steps:

Step 1: This will focus on Excel mechanics for this kind of model. Our goal here is to reproduce the graphs in Exercise 8 in Chapter 4 (logistic rabbits and non-logistic foxes) and, in the process, show you how to set up a model like this on Excel.

Step 2: This will actually be part of your homework assignment for next week. We will use biological data about hares and lynxes, as well as some hints and help, to find reasonable starting values for the parameters a, b, c, d, and e in a logistic hare and non-logistic lynx model:

Step 3: You will use these estimates to start a model for hares and lynxes in lab. Your goal will be to modify your values from Step 2 to try to reproduce the actual historical data, as shown in the graph below: Finally, you’ll study the original predator-prey question that inspired Lotka and Volterra to construct the first such model: what is the effect of hunting?

We’re assuming you’re getting enough experience with Excel that you won’t need Practice Tables, and we’re trusting you to read through lab before you go. Don’t let us down . . .

Step 1:

Unlike the last few labs, we’ll build this spreadsheet from scratch, so there’s nothing to download at the beginning. The model we’re trying to put into Excel is

We’re going to be computing our model just once a year, so if we measure time t in years, then Dt = 1. If we substitute that into the equations above, well, it just disappears, since dividing by 1 leaves things unchanged:

Now Dr is just the change in r: that is, the difference between this year’s value for r and last year’s. So we can substitute words in for Dr and Df:

Solving each of these for this year’s values, we get the following equations, which are what we are actually going to enter into Excel:

1. Start by putting your name and date and stuff in the upper left hand corner of your spreadsheet. Now enter the letters a, b, c, d, and e in cells A5 through A9 (a = 0.4, b = 0.0015, etc.). Next to each of these letters, in column B, put in the number from model we’re using: that is, in B5 put 0.4, the value for a; in B6 put 0.0015, the value for b, etc. Don’t use any minuses, we’ll stick them in formulas later. Also, the book uses different letters in Exercise 8 than we are here; don’t be confused, and stick to these letters.

2. Now we’ll start the table. In A11 put "Year"; in B11 put "Rabbits"; and in C11 put "Foxes". In line 12 we’ll put the starting values of each variable, so A12 is 0, B12 is 100, and C12 is 8.

3. Finally, we need to enter the formulas for each variable. We’ll add a year on each line, so the formula for cell A13 is =A12+1. Referring to the equation above for "This year’s rabbits", we need to enter a formula for "Last year’s rabbits + 0.4r - 0.0015r² - 0.05fr". So that formula for cell B13 would be

Similarly, the formula for C13 would correspond to "Last year's foxes + 0.001rf - 0.1f" Figure out what the appropriate formula should be, and type it into C13. Now copy these formulas, and paste them down 79 more rows, so that the last year’s row is year 80.

4. Make two graphs for this table. For one, put years on the x-axis, and rabbits and foxes on the y-axis. You’ll need to clean up the graph a little. First, double click on any rabbit dot (that’s dot, not pellet) and, under where it says "Line", click on "Automatic". Do the same for the foxes. Also, you will need to graph the foxes on the second axis to get them to show up better. Click (just once) on any fox dot, and choose the menu item Format-Selected Data Series.... Click on the Axis tab, then choose "Secondary Axis". Finally, to label each axis, use menu item Chart-Chart Options... and enter the word "Rabbits" for the y-axis and the word "Foxes" for the secondary y-axis.

The second graph is the phase plane. So, start a second graph with rabbits on the x-axis and foxes on the y-axis. Again, click on any dot and choose "Automatic" under "Line".

Print the table and each graph to turn in. Be sure to save your work because you’ll need it for Step 3.

As mentioned in the introduction, this will be your homework next week. We are going to be using various quotes from the two websites mentioned in the footnotes to help us guess what the coefficients a, b, c, d, and e. Throughout we assume h = 1000 hares and l = 4 lynxes.

Rabbit birthrate a:

"Studies of snowshoe hare reproduction in Alberta over a 12-year period disclosed that litter size varied . . . with [average] numbers of young per female ranging from 8 to 18. The survival rate among first year hares varies annually from 3 to 40 per cent."

5. Answer the following questions to figure out a value for a:

• We have 1000 hares and half of them are female. So, how many females?
• Based on the quote above, an average number of young per female is 13 per year. How many young is that for all the hares in all?
• Again based on the above, survival rate of these young is about 15%. How many young survive their first year?
• ah is the number of new rabbits. Set ah equal to your answer to the last question, recall that h was assumed to be 1000 to start with, and solve for a.

Rabbit death rate due to lynxes c:

"When lynxes are abundant [in winter], a lynx may kill [a hare] ever one or two days. In summer the lynx’s diet is more varied."

6. Answer the following questions to figure out a value for c:

• Assume overall a lynx kills a hare every 3 days. How many hares per year is that for each lynx?
• Set ch equal to your answer, use h = 1000, and solve for c.

Rabbit death rate due to overcrowding and disease b:

"Annual survival of adult hares is also highly variable, ranging from 12% to 50% per year."

7. Answer the following questions to figure out a value for b:

• Assume the normal survival rate is 30% per year. So, what’s the overall death rate per year?
• The overall death rate for rabbits is bh -cl. Set this equal to your answer to the last question, use the value for c you got above, h = 1000 and l = 4, and solve for b.

Lynx birthrate d:

"Mating occurs during February or March each year, and the young (usually four) are born in April and May." (That means four births total, not four in each of April and May.)

8. Answer the following questions to figure out a value for d:

• So again, 4 lynxes, half are female: how many young per year?
• Assume they all survive the first year: then dhl equals the answer to the last question. Solve for d.

Lynx deathrate e:

"About 40% of the total lynx population may starve to death following a crash in the snowshoe hare population."

• So e = 0.4.

9. As a final homework problem and part of Step 2, solve the equations you get with your values of a, b, c, d, and e above for the equilibrium points for this model.

10. Back to the spreadsheet in lab. Make a copy of everything you did in Step 1, and in the copy replace the values of a, b, c, d, and e with the values you found in Step 2. Change your starting values to 1000 hares and 4 lynxes.

11. Our next-to-last goal is to see how close we can get our graph to look like the graph of the historical data at the top of the printout. You’ll never get your graph to look exactly like the historical graph: in particular, your graph will be much more regular than the historical graph, and the values on your y-axis will be much smaller. You're just shooting for the right shape. Try some of the following in different combinations and see what you can come up with.

• Increase the hare birthrate.
• Increase the hare overpopulation/disease death rate.
• Decrease the numbers of hares eaten by lynxs.
• Increase the lynx birthrate.

Print the table and graphs from this result to turn in.

12. Our final goal is to reproduce the results obtained by Lotka and Volterra, the original mathematicians who studied this kind of model. What they wanted to know was, what is the effect on each species if hunters take a percentage of both predator and prey? We'll test this by modifying the above model to include an additional 20% death rate for each species each year from hunting.

First, return all your values for a, b, etc. to the values you started with in #10 above (the ones you found from homework). Now to introduce hunting into the model, note that subtracting 20% of the hares is like subtracting 0.2h from that equation, and that, in turn is like reducing the birth rate by 0.2. So, reduce the birth rate for hares by 0.2 in your spreadsheet. In the same way, subtracting 20% of the lynxes is like subtracting 0.2l from that equation, and that, in turn is like increasing the death rate for lynxes by 0.2. So, increase the death rate of lynxes by 0.2 in your spreadsheet. Print just your graphs with these new values.

1. Take the phase plane graphs you made for the models in Step 3 and identify, approximately, the central equilibrium point around which each model is spiraling. Based on these central points, did the hunters help the prey species, or the predator species? Explain how you reach your conclusion (math, not biology).

2. There are a variety of ways in which the model we’ve built in this lab is simpler than reality (remember the goal of modeling: a model is simpler than reality, but still conveys useful information). Below are some additional quotes from the webpages we used as our source. For each quote, identify a biological feature from reality that we did not include in the model, or that our model doesn’t seem to mimic.

"When lynxes are abundant [in winter], a lynx may kill [a hare] ever one or two days. In summer the lynx’s diet is more varied."

"The spectacular cyclic fluctuations of snowshoe hare populations are well known in Canada . . the actual interval between successive peaks varies from 8 to 11 years and averages 9.6 years."

"There is high mortality among [only] young male [snowshoe hares], for example, when cool wet weather occurs during the first three weeks of life."

"In some regions, the snowshoe hare is hunted for sport, but elsewhere, as in western Canada, it is not."

Footnotes:

All information about hares and lynxes in this lab come from two Canadian Wildlife Service webpages:

http://www.cws-scf.ec.gc.ca//hww-fap/snowshoe/snowshoe.html and

http://www.cws-scf.ec.gc.ca/hww-fap/lynx/lynx.html.

The source for the graph on historical trends in lynxes and hares is:

Elton, C. and M. Nicholson. 1942. The ten-year cycle in numbers of the lynx in Canada. Journal of Animal Ecology 11:215-244.

who, in turn credit:

MacLulich, D.A. 1937. Fluctuations in the numbers of the varying hare (Lepus americanus). University of Toronto Studies Biological Series 43. University of Toronto Press. Toronto.