485 Math Spreadsheet Lab Activity 2
Lab description: In this lab activity, we will use Excel to simulate a Ricker model, and a Lotka-Volterra discrete dynamical system. We will follow similar instructions as before but each part will also incorporate dierent modeling aspects. Please refer to Lab 1 whenever you need to refresh on how to manipulate functions and how to insert appropriate graphs (charts) along with changing scales and adding data, etc.. If you dont know how to do something, I recommend that you play around with Excel and see if you can gure out a solution on your own. We will also use Excels solver function to help nd unknown parameters. For this lab activity, you will need to Download the spreadsheet SpreadsheetLab2.xls Part 1: In part 1 of this lab activity we will investigate Rickers population model. If you consider a simple logistic growth model, then it is possible that large uctuations in the data may lead to negative populations in subsequent generations, which is clearly unrealistic. Several alternative models have been proposed, where the updating function remains positive. One such model is Rickers model, which it is claimed was originally formulated using studies of salmon populations. This model is very frequently used in sheries management problems. We will apply this model to the sockeye salmon data given on sheet 1 of SpreadsheetLab2.xls. Rickers model is given by the equation P (n + 1) = aP (n)ebP (n) where a and b are positive constants that are t to the data. According to some experts, the salmon population in the Pacic Northwest has become suciently endangered that many salmon spawning runs could become extinct. (This already happened years ago in California.) Salmon are unique in that they breed in very specic fresh water lakes and die. Their ospring migrate a tortuous path to the ocean, where they mature for about 4-5 years. Then an unknown urge causes the mature salmon to migrate at the same time to return to the exact same lake or river bed where they hatched 4-5 years earlier. The adult salmon breed and die with their bodies providing many essential nutrients that nourish the stream where their young will repeat this process. Human activity from damming rivers, forestry, which allows the water to become too warm, and agriculture, which results in runo pollution, all adversely aect this complex life cycle of the salmon. Many of the ancient salmon runs have now gone extinct.
This life cycle of the salmon is a clear example of a complex discrete dynamical system. Because of the importance of this sh to many people in the Pacic Northwest, there have been many studies of the salmon populations. Below is a table listing four year averages of the sockeye salmon (Oncorhynchus nerka) in the Skeena river system in British Columbia from 1908 to 1952. (The Canadian river systems have not been as severely aected by other human activities as the ones in the U.S., but rapid development in their forests are likely to have similar eects.) The table lists the four year averages from the starting year of the data being averaged. Since it is 4 and 5 year old salmon that spawn, each grouping of 4 years is a rough approximation of the ospring of the previous 4 year average of salmon. (It is complicated because the salmon have adapted to have either 4 or 5 year old mature adults spawn, but this will be ignored in our modeling eorts.) To investigate Rickers model follow these instructions: 1. Starting with Sheet 1, the actual recorded populations of Skeena Sockeye Salmon are given in the cells B9 through B19. Create a graph of P(n+1) vs. P(n). First enter P(n+1) in cell C8. Then copy cells B10 through B19 and insert the values in cells C8 through C18. Your sheet should now look like this:
2. Plot P(n+1) vs. P(n) by highlighting cells B9:B18 and cells C9:C18. Insert a chart either by selecting the Insert menu and selecting Chart, or by clicking on Chart Wizard. Choose XY- (Scatter) and select the data points option which looks like the following:
2
3. Make sure that you clearly label your graph. Change the x-axis scale to be from x=0 to x=1200. 4. Notice that the data is somewhat scattered. Unfortunately, Excels trendlines are not well suited to t this type of data very well. Instead, using a nonlinear least squares t which we have not discussed yet, one can nd parameters a and b that approximate the data fairly well. These numbers have been included in the sheet. We will see that for a Ricker model with these parameters, the function f (x) = axebx gives a reasonable t throught the data. 5. Highlight cells A21 throught A34. Select Insert, Name, Create and press OK. Recall this gives the name x to the entries in cells A22:A34. Enter the funtion f(x) above using the parameter values given. 6. Plot the function f(x) (as a continuous curve) on the graph of P(n+1) vs. P(n). It should like like this:
3
7. In cell A38, write the Ricker model equation using the initial condition P(0) and the two parameters a and b given in the worksheet. 8. Complete the model simulation by dragging your formula from cell A38 to A47. 9. On a separate chart, graph the Ricker model data as a continuous curve along with (on the same graph) the Sockey Salmon Data given. You should have something which looks like:
10. Change the parameters a and b to see how the model changes. Does it seem to t better or worst with these new parameters? For the homework, print a page with the given best t parameters (without changing a and b) and one with other choices of parameters (you decide).
4
Part 2: In this part of the lab, we will investigate a Lotka-Volterra predator-prey model. A classical example shown in many textbooks is the population dynamics of a simple predator-prey system. Most mammalian predators rely on a variety of prey, which complicates mathematical modeling; however, a few predators have become highly specialized and seek almost exclusively a single prey species. An example of this simplied predator-prey interaction is seen in Canadian northern forests, where the populations of the lynx and the snowshoe hare are intertwined in a life and death struggle. One reason that this particular system has been so extensively studied is that the Hudson Bay company kept careful records of all furs from the early 1800s into the 1900s. It is often assumed that the furs are representative of the populations in the wild because of the trapping intensity and trapping techniques, so these data can be used to represent the relative populations of the lynx and hares in the wild. The records for the furs collected the by Hudson Bay company showed distinct oscillations (approximately 12 year periods), suggesting that these species caused almost periodic uctuations of each others populations. Included in sheet 2 are the collected fur data. Use sheet 2, and follow these instructions: 1. Plot the Hare and Lynx data on the same graph. To do this highlight cells A3 through A23 along with B3:B23 and C3:C23. Select Chart Wizard and add an XY- (Scatter) plot of the form shown in the following gure (continuous with data points):
5
Make sure that you clearly identify and label your graph. This becomes especially important when multiple data sources are plotted together. Insert this chart in the upper right hand portion of the page (without covering up the parameters). You should have something which looks like:
2. Next we need to name the parameters. First, highlight cells F2:F7 along with G2:G7. Select Insert, Name, and Create. Here, the names are in the left column so select Left column and press OK. This gives the values in column G the corresponding names in column F. Repeat this to name the variable h in column I (and J). 3. The Lotka-Volterra model is given by the following system of dierential equations: dH(t) = a1 H(t) a2 H(t)L(t) dt dL(t) = b1 L(t) + b2 H(t)L(t) dt In this model,the deaths of hares only occur from predation(from the Lynx population) and is given by the term a2 H(t)L(t). The growth of the Lynx is similar to the death rate of the hares with a dierent constant of proportionality. Mathematically, the growth term of the Lynx population can be written as +b2 H(t)L(t) In the absence of hares, the lynx population declines in proportion to their own population while in the absence of lynxs the hare population grows exponentially. 6
Using Eulers method to nd the solution to this system we have the discrete Lotka-Volterra system which has the form: H(t + 1) = H(t) + h(a1 H(t) a2 H(t)L(t)) L(t + 1) = L(t) + h(b1 L(t) + b2 H(t)L(t)) We will use this form for our simulations. Part 3: In this section we will use Excel to simulate the system and use Excels solver to estimate the parameters of best t. 1. First notice that the value h = 0.1 was used in the simulation over twenty years. Notice that the respective populations are only shown for each year. If you would like to see the full simulation you can unhide the rows from row 28 to row 228 (but I dont recommend it!). This h values gives a small enough time interval so that Excel gives an accurate simulation (without spitting out crap). 2. Graph the simulation by entering the appropriate equations in cells B38 and C38. Respectively, you should enter = B37 + h (a 1 B37 a 2 B37 C37) and = C37 + h (b 1 C37 + b 2 B37 C37) . Now use the skinny cross to drag the formula down to row 228. 3. Graph the simulation...Highlight columns A28 through A228 along with B28:B228 and C28:C228. Use Chart Wizard, XY- (Scatter), with a continuous plot:
7
Change the x-axis so that it plots from 1900 to 1920. You should get a graph which looks like the following:
This should not match the data very well. This means that the choice of parameters may not be good. We would like to see how well the model simulates the data by adding the data to the Model graph. To do this make sure the chart is highlighted and click on the Chart menu at the top of the page followed by Add Data.. click and drag so that cells B3:B23 and C3:C23 are highlighted. Press OK (twice). This will put two new curves on the graph (of dierent colors, i.e.,yellow and green). To help us distinguish between data and model, click on the yellow curve and select the menu Chart followed by Chart Type and select to display just data points. Do the same for the green curve. Make sure to name the two new series. You should have a chart which now looks like the following:
8
Notice that the blue curve is suppose to match the yellow arrows and the red curve is suppose to match the green xs. Now we will nd the ...