Spawner-recruit models

file: d:\b173-2010\spawner_recruit_curves.wpd

date: October 24, 2010

Introduction

Previously we learned the basics of graphing points, lines and functions using Excel. We also learned the beginnings of "graphical analysis", i.e. that you can use graphs to derive the values of equations.

Today, you are going to use the spreadsheet to explore some functions. With simple functions, like lines, it is easy to see how changing one parameter will change the model. But it gets more difficult to visualize these effects with more complex models. Excel can make this job easy. In essence, you create a "function drawing machine".

Plotting functions is actually no different than what you have previously done plotting regression lines. Basically, you produce a list of x values and calculate the y value for each value of x. Instead of plotting a regression line, you plot the function you are interested in. You do this by entering the formula for the function in the first "y" cell, and copying the formula to other cells.

To make the spreadsheet more useful and to make it easier to explore alternate values for the constants used in the function, you should enter these constants not as values in the formula, but rather put each constant in its own cell and refer to that cell using the absolute location syntax, i.e., putting a '$' in front of the cell reference in the formula, e.g. $C$14. That way, when the formula is copied, the same constant will always be used.

To make comparison of functions easier, it is best to plot two (or more) functions side by side so that you can see the effects of changing parameters.

The Fill function

Note: In some instances, some of the Fill options may not be "turned on" in your version of Excel, or someone may have turned them off. To turn them on, go to Excel Options by clicking on the round button in the extreme upper left of the screen, then select Excel Options on the bottom of that menu. Next choose "Advanced" and look at the "Cut, Copy and Paste" options to be sure that everything is ticked.

When creating a function drawing machine, you need to decide an appropriate range over which to plot the function, i.e., the range of the "x" values. Let's say we decide to use 1 to 100. You certainly do not want to have to type in all those numbers. Excel has a "Fill" function to make your life easier.

Put a '1' in the first cell where you want it, e.g., cell A14. In the lower right corner of cell A14, there is a little box. As you move your mouse over that box, it will change to a little, dark '+' sign. Left click that '+' sign and drag down as many cells as you want to fill, e.g., to cell A113. Release the button.

Notice that the range is still highlighted and Excel has put a '1' in each of the cells. But also notice a small floating menu to the right of cell A113. Pull down on the little menu and notice an option entitled "Fill Series". Click on that. Voila! The numbers from 1 through 100 should now fill the range.

More on using Fill (not needed for this lab, but useful to know...)

The Fill function also works horizontally, i.e., across columns, not just vertically, across rows. Try it.

The Fill function is actually quite "intelligent". Just for kicks, try this: Excel understands dates in a rather sophisticated way. Type 'Jan 1'. Then grab the little '+' and pull it down. You should see the rest of the month appear. By the way, it knows how many days there are in the month! If you just type "January" and use the fill function, it will dutifully fill in with February, March, etc below that.

Finally, if you want to do more things than what the little floating menu provides, there is a more advanced Fill function. Click on the "Home" tab. Look to the far right in the ribbon menu. You should see "Fill" in the last group. Pull down the menu and look at the options. The "Series" option is particularly useful.

For instance, if you want to make a series of odd numbers, i.e., starting at 2, then 4, 6, 8, etc, do the following. Type the number 2 in the first cell. Pull down the Fill | Series menu. Click on "Columns" so that your series appears in a vertical column. Enter "2" for "Step Value". Enter "100" for "Stop Value" and hit "Okay".

The "linear" option adds the step value. The "growth" option multiplies the current cell by the step value. So for instance, to get the series 1,2,4,8,16,32,64, 128, 256, 512 you would select "columns", "growth", step of "2", stop value of "1000".

The date option allows you to use Days, Weekdays, Months or Years. This is very handy if you need to set up some sort of data or task list based on dates.

Bottom line: The fill function can save you an enormous amount of work, plus once you have the hang of it, you are much less likely to make little typing errors. Now take a moment and bask in the glow of your new-found power.

Exercise 1:

We will create a function drawing machine for exploring spawner-recruit models (see your text, page 75). These models can take a variety of forms, one of which is called the Beverton-Holt spawner-recruit model. It tells you the number of recruits, given some constants and the number of spawners.

R = aS / (1 + bS) where a and b are constants and S is the number of spawners.

Construct a spreadsheet for plotting functions. For initial purposes, your x values should go from 1 to 100.

The spreadsheet should look something like this:

A	B	C	D	E

1	Spawner Recruit Curves

2	Ron Coleman

3	File:	d:\B173_2010\spawner_recruit_curves.xls

4	Date:	October 18, 2010

5

6	F1	F2	F3

7	a=	6	6	a*=	0.167

8	b=	0.2	0.3	b*=	0.0333

9

10	Beverton-Holt

11	F1	F2	F3

11	Spawners Recruit Recruit	Recruit

12

13	1	5.00	4.62	4.99

14	2	8.57	7.50	8.56

15	3	...	...	...

16	4	...	...	...

..	...	...	...	...

..	100	...	...	...

For function 1 and 2, plot the Beverton-Holt spawner-recruit function

aS

R =	------

1 + bS

For the first, use a = 6, b=0.2. For the second, use a=6, b=0.3

Plot a graph of function 1 and 2 (draw only the line, not the points, and get rid of any unsightly clutter on the graph such as gridlines. Be sure to label the axes and label the curves).

Exercise 2.

There are alternate formulations of the Beverton-Holt model plotted in Exercise 1. One of these is

S

R = -----------

a* + b*S

where a* = 1/a and b* = b/a where a and b are from the previous equation.

To convince yourself that this is actually the same thing, add a third column to your spreadsheet, calculate R this new way and plot this new function (Function 3) on the same graph. So for example, if a=6 and b=0.2, then a*=0.167 and b*=0.0333. There may be very slight differences in the values in F1 and F3 due to rounding errors, but basically this line should be on top of the other line..

Exercise 3.

The Ricker model of spawner-recruitment looks quite different than the Beverton-Holt model (it incorporates strong density dependent mortality as spawner abundance increases).

R = a S e-bS

On a new graph, plot Ricker curves for (a=5, b=0.07) and (a=5, b=0.10) [Note that your text incorrectly has a=0.05 in Figure 4.6, page 76; it should be a=5]

Also note that to enter e-bs, you will need to type EXP(-b*S) substituting appropriate cells for the b and s.

Exercise 4.

Construct graphs showing the Shepherd model

R = a S / (1 + (b S)c)

If c = 1, the Shepherd model looks like the Beverton-Holt model.

If c > 1, it looks like a Ricker curve

If c < 1, then there is no asymptote (what does this mean about density dependence?)

Create a graph showing these three outcomes.

Note that to enter (b S)c you will need to type (b*S)^c Be sure you understand why.

Print and turn in the graphs from Exercises 1, 3 and 4.

-- end