Welcome to this self-paced training for the Principles of Modeling with Spreadsheets. You will be introduced to a variety of modeling techniques applicable to resource management and conservation issues. Modeling topics include population models, decision analysis, and ecosystem models. Additional examples of models for conservation are available in the menu drop-down.
The instructors are world-renowned modeler and professor emeritus, Dr. Anthony (Tony) M. Starfield, and spreadsheet modeler Dr. Therese (Terri) Donovan. Tony’s modules introduce modeling concepts and techniques, while Terri’s instruction covers how to build models with spreadsheets.
Each module consists of lectures, spreadsheet exercises, and a scenario to check your modeling skills. The topics are patterned after a full 14-week semester course syllabus.
This course was developed in collaboration between the U.S. Department of the Interior, Bureau of Land Management, National Training Center, the U.S. Fish and Wildlife Service, National Conservation Training Center, and the Vermont Cooperative Fish and Wildlife Research Unit
Website last edited: 8-11-2020 JW
Each chapter contains multiple videos and documents!
Chapter 0: Introductions to R and Spreadsheets:
Real World, Model World, and Spreadsheets:
While you are watching this video, please note at least one “insight.” An insight is an “aha moment” where something struck you as pertinent or new, or when you make a connection between the lesson and something else in your life, etc. In this course, you are required to post insights weekly! It’s good academic reflection and helps in learning. Make sure to read other’s insights too!
Introduction to R:
Introduction to Spreadsheets:
Introduction to Spreadsheets: Part 1
In this course, we’ll be modeling with Microsoft Excel. There a many reasons for this, but the most important are that: 1) it is easy to use, 2) it is widely available, and 3) it has a lot more capability for modeling than most people realize. These videos use Excel 2007; a few use Excel 2010, and Excel 2016. For our class purposes, there are few differences between the versions so you should be able to follow along with either version. (If you use 2003 or before, however, the interface for Excel is quite different! It is definitely time to upgrade!). We will also be picking up Excel tips and tricks throughout the course.
Even if you know Excel well, please review the following video (this task and the next), which was filmed in Excel 2016. Just ignore the heading “Conservation Biology and Modeling”!! (We pilfered this video from another course).
File download: worldpopulationannual12000years.xls
Introduction to Spreadsheets: Part 2
Almost finished! Here is part 2 of the Excel tutorial:
Chapter 2: Rapid Prototyping and Heuristics
1. Rapid Prototyping Applied to Modeling Lecture #1:
Welcome to Module 2! This first video features Tony explaining the concept of “rapid prototyping.” We will do a lot of rapid prototypes in this class via spreadsheet key-alongs. In addition, the Intermediate (modeler’s) group will come up with one rapid prototype of a modeling challenge each week where you will be asked to apply concepts you learned in lecture. We’ve found that rapid prototyping is an excellent practice that goes beyond modeling.
2. How many ping pong balls would fill the room you are in?:
OK, here is a classic Tony challenge to get you “rapid prototyping.” You’re hoping to surprise a co-worker by filling up their office with ping pong balls. Wouldn’t that be a hoot when they open the door in morning! The question is, how many ping pong balls do you need to purchase?
Take a moment, and assuming you are in their office right now, estimate how many balls it would take to fill the room. Be sure to think about the real world and model world. How will you represent the room? Do you need to think about the clock on the wall? How about tables and chairs? How will you represent ping-pong balls?
Jot your ideas down, and after you think you have an answer, post your ideas on the Module 2 forum.
3. Rapid Prototyping Ping Pong Ball Problem Spreadsheet #1:
In this video, Terri will walk you through one possible model whose objective is to estimate the number of ping pong balls in your co-worker’s office. This video covers the essential topics of sensitivity analysis and assumption analysis.
You should build this spreadsheet along with the video (a practice we call “key-along”).
Video Lesson: https://fws.rev.vbrick.com/#/videos/680ec5e9-09f4-4c0c-93ff-716de9f4cdb4
4. Modeling Heuristics Lecture #2:
This is a great lecture on modeling heuristics; short but important. It covers general modeling rules of thumb. This lecture is best viewed after you have your first spreadsheet modeling experience, so make sure to finish the ping pong ball exercise prior to viewing this lecture.
Note that Tony’s 17 key modeling heuristics are posted in this module. It’s a good idea to print this out and post it prominently near your desk to remind you of the simple rules of thumb that keep you on track when building a model.
Chapter 3: Deterministic Population Models
1. Deterministic Population Modeling Lecture #1
Hi! Welcome to Module 3 of our course. This is the first video lecture you should watch. This video features Tony talking about the mighty Arabian Roc, where he challenges you to develop a population model of Roc population dynamics.
2. Roc Population Model Spreadsheet #1
Bon jour! This video should be viewed AFTER Tony’s lecture about basic population models.
3. Deterministic and Sensitivity Analysis Lecture #2
This very short video should be viewed after you have completed the first portion of the Roc model on a spreadsheet.
In this video, Tony talks about the all-important concept of sensitivity analysis as it pertains to the Roc population. The Caliph is interested in funding more research on Rocs. Should he fund a study to get a better estimate of Roc population size? How about a thorough investigation of clutch size? Or maybe find out exactly how many silver bullets are out there? At the end of this video, Tony challenges you to figure out which proposal the Caliph should fund. Try this on your own! Then, we’re sending you back to your Roc spreadsheet to actually do a sensitivity analysis as your fourth task.
4. Roc Model and Sensitivity Analysis Spreadsheet #2
Welcome! In this video you’ll see how easy it is to do sensitivity analysis in a spreadsheet while completing your Roc population model. Don’t forget to save your spreadsheet! After this is finished, Tony will share some final thoughts about population modeling in Task 5.
5. Deterministic Wrap Up Lecture #3
This video features Tony showing you yet another way to graph a sensitivity analysis that is very useful for reporting a lot of results in one image. Tony concludes with some final thoughts about deterministic population modeling.
Chapter 4: Environmental Stochasticity
1. Environmental Stochasticity Lecture #1
Hello, and welcome to week 4 of the course! This week’s topic is environmental stochasticity. In this lecture, Tony introduces Roc flu, a devastating disease that occurs on average once every 400 years.
2. Roc Flu Spreadsheet #1
Now it’s time to build the Roc population model that takes into account the probability that the Roc flu will strike. It guides you through the very beginning of a new spreadsheet model.
3. Environmental Stochasticity Lecture #2
Now that you have the first part of your spreadsheet model working, this video features Tony discussing the logic of the formulae needed to include in your Roc flu model.
4. Roc Flu Spreadsheet #2
In this spreadsheet demonstration, we will start to build our Roc model equations to project the population size through time. We’d like you to carefully consider the equations you use for projecting the Roc population through time. Of particular interest is WHEN do you subtract off flu deaths and deaths by silver bullets. The issue is centered around the notion of WHEN counting occurs in a time step: Does it occur at the very beginning of the time step, or at the end? We’ll visit this topic at our llive session as well.
5. Environmental Stochasticity Lecture #3
In this short video, Tony asks, “What can we do with our model, now that it is working?” It turns out that one replicate is not useful, at all, in a stochastic model.
6. Roc Flu Spreadsheet #3
OK, hang on to your hat! In this last video task, you’ll learn how to use macros in a spreadsheet and will finish your Roc flu model. After your macro is complete, you’ll be in a position to give a report back to the Caliph. Post your completed spreadsheet in the Shared Documents folder under Week 4 Spreadsheet Videos homework assignments.
7. Optional (Nice to Know) – Binomial Distribution
Meet Lew Coggins! Lew is a Fisheries Biologist Research Scientist, and was formally with FWS-NCTC. He was the lead for the Principles of Modeling online course in 2011, and developed this video that explains how the binomial distribution is related to the IF function we used in the Roc Flu spreadsheet to determine whether a flu outbreak occurred in a century or not.
Hint: the IF function, in this case, is a Bernoulli trial (a single draw from the Binomial Distribution) in which p (the probability of success) is the probability of a flu occurring.
This is an optional (nice to know) task, but very informative video.
Chapter 5: Demographic Stochasticity
1. Demographic Stochasticity Lecture #1
Welcome to Week 5! This week’s topic: demographic stochasticity. In this first video, Tony brings us up to the 20th century, where an exciting discovery has been made: ROCS have been rediscovered! But there are only two of them. How do we handle modeling such small population sizes? Task 1 will introduce you to a solution.
2. Demographic Stochasticity Spreadsheet #2
In this video, you’ll build a spreadsheet model that features demographic stochasticity in action – we’ll model the reproductive output of INDIVIDUAL rocs; that is, a roc can have 0, 1, or 2 eggs. We’ll approach this modeling problem by modeling individuals, rather than treating reproduction as a population rate.
Video Lesson: https://fws.rev.vbrick.com/#/videos/0578f1ce-4ffb-4234-8def-56eb71dd76e3
3. Deterministic vs Stochastic Model Lecture #2
Now that you have a working model of demographic stochasticity, Tony brings us back to reality by asking “When should you use a deterministic model versus a stochastic model?”
Video Lesson: https://fws.rev.vbrick.com/#/videos/f1386953-ba2e-4b36-b1f0-25b1da3c5106
Chapter 6: Age-Structured Population Models
1. Age Structured Models Lecture #1
Hi, and welcome to Week 7. You’ll be happy to know that the Roc population is flourishing, so much so that we can turn our attention elsewhere. How does Africa sound? Elephants, in fact. This week’s topic covers age structured models in four videos. The first video introduces age structured modeling.
2. Age-Structured Elephant Model Spreadsheet #1
In this exercise, we’ll build a spreadsheet model of the elephant population. This particular model is an age-structured population with a post-breeding census (so you count newborns).
3. Age Structure with Sensitivity Analysis Lecture #2
Now that you have your elephant population model spreadsheet running, it’s time to hear Tony discuss the concept of calibration.
4. Age Structure Elephant Model with SA Spreadsheet #2
In this spreadsheet wrap up video, we’ll re-enforce the concept of long-term lambda for structured models. You will be able to use a spreadsheet tool called Goal Seek to calibrate our model.
Chapter 7: Managing Age Structured Populations
1. Managing Age Structured Populations Lecture # 1
Now that you have your elephant population model working, we’re going to now add management activities to it. Tony goes over the basic set up in this video.
Video Lesson: https://fws.rev.vbrick.com/#/videos/4b893fb3-845e-4463-8801-f287d1116116
2. Elephant Darting Spreadsheet #1
This video builds on the elephant age-structured model you built in week 7. So dust off your old model and go to the link.
3. Managing Age Structured Populations Lecture # 2
Tony is back, discussing short-term versus long-term lambda in terms of management objectives. This video breaks all speed records — it’s 3 minutes long!
4. Elephant Darting Spreadsheet #2
Back to the spreadsheet… in this video, we’ll use Goal Seek to determine what proportion of animals need to be darted to stabilize the population at a growth rate of 1.
5. Managing Age Structured Populations Lecture #3
Now that you know how many animals should be darted to stabilize the elephant population, Tony discusses sensitivity analysis of this result.
6. Elephant Darting Spreadsheet #3
Time to wrap up this spreadsheet model. In this video, we focus on stabilizing the population size at 3500 animals.
7. Managing Age Structured Populations Lecture #4
In this last wrap up video, Tony provides some final thoughts about modeling heuristics as related to the elephant darting model.
8. (Optional Lecture) Five Questions for Population Models
Hi all. Task 8 is optional but encouraged…it’s a short lecture video with Tony summarizing the five questions you need to ask when developing a population model.
Chapter 8: Decision Trees
1. Decision Trees
Welcome! We are now starting the section of the course that deals with decision analysis. Most students find these modules very useful in terms of linking modeling with management decisions. The first task is a video where Tony introduces decision trees.
2. Booths Model Spreadsheet #1
In this exercise, we’ll set up a spreadsheet model to evaluate which booth you should play in: red or blue. We’ll visit the topic of Monte Carlo simulations as a way of describing potential outcomes.
3. Decision Trees Lecture #2
In this video, Tony introduces the concept of a bribe for playing in the blue booth. How much would you be willing to pay to play blue?
4. Booths Model with Bribe Spreadsheet #2
This short video adds bribes to your spreadsheet model.
5. Decision Trees Lecture #3
In this video, Tony discusses decisions in which the decision is not based on statistical laws of average, but rather on your risk tolerance.
Video Lesson: https://fws.rev.vbrick.com/#/videos/94f49613-c160-42ce-a9a9-1981241c7496
6. Booths Model with Risk Spreadsheet #3
What will you do with one ticket! In this last video, we’ll discuss decision making in the context of risk, not expected values.
7. Nice to Know
Hi everyone. Attached are two chapters on basic probability (prob1) and conditional, joint, and marginal probability (prob2). Each has a spreadsheet to play around with.
These are the first two chapters of a book that I am writing with some colleagues on Bayesian Inference (they are drafts only). You should be able to open them in your browser, but you should look to see if your browser is blocking scripts and then allow the blocked content (or you won’t see the equations).
Chapter 9: Linear Programming Decision Analysis
1. Linear Programming Lecture #1
Welcome! We will be continuing our lessons on decision analysis, and this week our topic is linear programming, which is a useful analysis framework for many conservation and management problems. The modeling problem for this week’s lecture: figure out how many hectares of two habitats should be purchased to maximize species conservation.
Before watching the video, please download the attached file – Saving Species of Erehwon (Plants at Last), Part 1 – this outlines the problem in detail.
Look for in files folder: ErehwonPlants1_exercise.pdf
2. Erehwon Model Spreadsheet #1
Now that you understand the modeling challenge at hand, it’s time to see how it would be set up on a spreadsheet.
3. Linear Programming Lecture #2
Tony discusses linear programming models in a general sense, including variables, objective function, and constraints.
4. Erehwon Model Spreadsheet #2
Now that you have your spreadsheet working, we’ll spend some time exploring what the optimal solution would be if you changed any of the model inputs.
5. Linear Programming Lecture #3
In this short video, Tony discusses what happens if your constraints are non-linear, as could be a factor in many natural resource management problems.
6. Erehwon Model Spreadsheet #3
In this video, we’ll use Solver to find the optimal solution to Conserving the Species of Erehwon. (Have you noticed that Erehwon is “nowhere” spelled backwards yet?). The Solver tool is a great way of solving complex linear modeling problems.
7. Linear Programming Using Solver
Tony wraps up the topic of linear programming in this video by talking about the Solver Answer Report — in particular, the concept of binding constraints and slack.
Chapter 10: Integer Programming Decision Analysis
1. Integer Programming Lecture #1
Welcome! This week we’ll continue with our discussion of decision analysis, and will focus on integer programming. The challenge this week: buying parcels (sites) of land to protect a large number of species, given a limited budget. This a very realistic problem (though the data are hypothetical).
Before you view this video, you need to read a handout called, “The Endangered Species of Int.”
Needed from files folder: SpeciesInt_exercise.pdf
2. Species of Int Model Spreadsheet #1
In this video, we’ll start building a spreadsheet model to tackle the Species of Int problem.
3. Integer Programming Lecture #2
Tony’s back with a presentation that takes from the model world result to the real world problem. In other words, even though you have a solution, it might not be the “best” solution from a number of different perspectives. For instance, what if you need to conserve species at two sites each? What if a stakeholder indicates that one particular site must be purchased? We’ll explore these in video because it’s critical to keep in mind that decision analysis is a multi-faceted endeavor. Tony introduces ‘hard’ versus ‘soft’ approaches in this video.
4. Species of Int Model Spreadsheet #2
In this final spreadsheet video, we’ll head back to the spreadsheet and discuss how you, as a modeler, can start to add some ‘soft’ touches to your model.
Chapter 11: SMART Decision Analysis
1. SMART Lecture #1
Welcome to week 12, our last session on decision analysis. This week we focus on decision problems that have multiple objectives, and Tony introduces a technique called SMART (Simple Multi-Attribute Ranking System).
2. Buy a Car Model Spreadsheet #1
In this video, we’ll set up a spreadsheet that uses the SMART approach for guiding decisions about the car purchase problem.
3. SMART Lecture #2
Tony is back and discusses a sensitivity analysis in the SMART framework — i.e., how does changing the weights affect the decision.
4. Buy a Car Model with Sensitivity Analysis Spreadsheet #2
In this very short video, we’ll head back to our spreadsheet and do a sensitivity analysis on the weights for each objective and determine how that affects the decision.
Chapter 12: Mobbing Monk Seal Simulation Modeling
Three videos (Monk Seals, Parts I, II, and III):
Monk Seals – Part 1: https://fws.rev.vbrick.com/#/videos/1a77d114-bfc8-4f43-b55b-f5c55a47576a
Monk Seals – Part 2: https://fws.rev.vbrick.com/#/videos/64818d80-fb23-4cee-b9c9-e1ddf5b569b9
Monk Seals – Part 3: https://fws.rev.vbrick.com/#/videos/bbfe262f-dbad-4d46-80ee-03d6180785d4
Chapter 13: Markov Matrix and Frame-Based Models
1. Ecosystem Modeling, Markov Matrix Lecture
Welcome! This week launches our investigation of ecosystem modeling. In this introductory lecture, Tony introduces ecosystem Markov modeling.
2. Markov Modeling on a Spreadsheet
Now that you understand the jack pine-white pine-spruce transition matrix, we’ll show you how to run this model on a spreadsheet.
3. State and Transition Conceptual Models Lecture
Now that you have a sense of what Markov Matrix Models are, Tony debunks them and highlights their soft-underbelly (challenges) from a management perspective. In this video, Tony introduces state-and-transition conceptual models as an alternative.
4. Frame-based Models Lecture #1
Frame based modeling comes out of computer sciences, and Tony brilliantly applies this method to the jack pine-white pine-spruce ecosystem model.
5. Frame-Based Models Spreadsheet Part 1
Now that you have a sense of what a frame-based model is, we’re going to build the jack pine-white pine-spruce frame model on a spreadsheet. This is a pretty lengthy exercise (sorry!), so it is broken down into two parts.
6. Frame-Based Models Spreadsheet Part 2
Time to finish up the jack pine-white pine-spruce frame model on a spreadsheet. By the way, if you can think of a more elegant and simpler spreadsheet model that what we have displayed, we’re all ears!
7. Frame-Based Models Lecture #2
Now that you have a spreadsheet frame model, let’s take a peek at how Tony developed this model as a stand-alone computer application.
8. Frame-Based Models Wrap Up Lecture #3
Time to wrap up this first section of ecosystem modeling.
Chapter 14: Ecosystem Spatial Models
1. Spatial Modeling Lecture
Spatial models are a lot of fun, and are highly relevant. In this video, Tony introduces spatial models by talking about the spread of fire and how you would conceptually model this process.
Video Lesson: https://fws.rev.vbrick.com/#/videos/c33aac10-6e95-44dc-bfa2-d39acf59a2fd
2. Spatial Model Spreadsheet Exercise
Fire Model Spreadsheet. Spatial models are a lot of fun, and in this video you’ll develop a simple model that lets a fire rip across your spreadsheet.
3. Cellular Automata Models
Now you have a spreadsheet model working, Tony talks about what kind of model you actually built.
4. Spatial Non-cellular Automata
In this video, Tony gives an example of a non-cellular automata model.
5. Spatial Wrap-Up
In this short video, Tony wraps up spatial and frame modeling.
Nice To Know (Bonus Lectures)
1. Ecosystem Models (frames, spatial models, and spatial plus frames)
Rapid Prototyping: Deschutes National Forest – Part 1:
Rapid Prototyping: Deschutes National Forest – Part 2
Starfield and Chapin (1996) Model of transient changes in arctic and boreal vegetation in response to climate and land use change. Ecological Applications 6:842-864
Starfield et al. (1989) A rule-based ecological model for the management of an estuarine lake. Ecological Modeling 46:107-119
Rupp et al. (2000) A frame-based spatially explicit model of subarctic vegetation response to climatic change: comparison with a point model. Landscape Ecology 15:383-400.
Ralston et al. (2014) State-and-transition prototype model of riparian vegetation downstream of Glen Canyon Dam, Arizona: U.S. Geological Survey Open-File Report 2014-1095, 26 p.
Starfield, A. M., & Bleloch, A. L. (1986). Building models for conservation and wildlife management. Macmillan. (Chapters 3, 4, and 6)
2. Hybrid Models
Three videos (Lake St. Lucia, Parts I, II, III)
Lake St. Lucia – Part I: https://fws.rev.vbrick.com/#/videos/1cafa5f6-5822-4e84-969c-17fa53592c49
Lake St. Lucia – Part II: https://fws.rev.vbrick.com/#/videos/34a172a5-1c6e-4202-9578-228b130366a0
Lake St. Lucia – Part III: https://fws.rev.vbrick.com/#/videos/edf01b1b-496e-4432-a89b-10e17e0fe512
Starfield et al. (1989) A rule-based ecological model for the management of an estuarine lake. Ecological Modeling 46:107-119
3. Validation and Verification of Models
Grimm et al. (2014) Towards better modelling an decision support: documenting model development, testing and analysis using TRACE. Ecological Modeling 280:129-139.
Oreskes et al. (1994) Verification, validation, and confirmation of numerical models in the earth sciences. Science 263:461-464.
4. Using other Peoples Models
Kettenring et al. (2006) Good practice for sharing ecological models. BioScience 56:59-64
5. Interdisciplinary Projects
Nicolson et al. (2001) Ten heuristics for interdisciplinary modeling projects. Ecosystems 5:376-384.
Starfield, A. M., & Jarre, A. (2011). Interdisciplinary modeling for an ecosystem approach to management in marine social-ecological systems. World fisheries: a social-ecological analysis. Blackwell, Oxford, UK. http://dx. doi. org/10.1002/9781444392241. ch6, 105-119.
6. Expert Systems
Three videos (Expert Systems Parts I and II, Demo)
Expert Systems – Part I: https://fws.rev.vbrick.com/#/videos/4bac83c7-4267-4ed1-a09e-e2476f10c462
Expert Systems – Part II: https://fws.rev.vbrick.com/#/videos/b0f737b8-0bbf-4364-9493-1f5e3bc53b56
Expert Systems – Demo: https://fws.rev.vbrick.com/#/videos/f6cb049f-66c0-485b-9063-b278dae39189
Starfield, A. M., & Bleloch, A. L. (1986). Building models for conservation and wildlife management. Macmillan. (Chapters 10)
7. Bayesian Belief Networks
Two videos (Bayesian Belief Networks, Parts I & II):
Bayesian Belief Networks – Part 1: https://fws.rev.vbrick.com/#/videos/8eaab7ff-5041-4864-9248-fe998902eb90
Bayesian Belief Networks – Part 2: https://fws.rev.vbrick.com/#/videos/6d3c7ac0-e893-4835-a724-f7bde8cf4da7
Amstrup, S. C et al. (2008) A Bayesian network modeling approach to forecasting the 21st century worldwide status of polar bears, in Arctic Sea Ice Decline: Observations, Projections, Mechanisms, and Implications, Geophys. Monogr. Ser., vol. 180, edited by E. T. DeWeaver, C. M. Bitz, and L.-B. Tremblay, pp. 213–268, AGU, Washington, D. C. Mechanisms, and Implications. Geophysical Monograph 180. American Geophysical Union, Washington DC. McCann et al. (2006)
Marcot et al. (2006) Guidelines for developing and updating Bayesian belief networks applied to ecological modeling and conservation. Canadian Journal of Forest Research 36:3063-3074
8. Working with Contractors
Two Videos (Five Questions for Population Modeling, Purpose-Oriented Models):
Five Questions for Population Modeling: https://fws.rev.vbrick.com/#/videos/44264047-a3df-456f-9cec-fd3c4171fc9a
Purpose-Oriented Models: https://fws.rev.vbrick.com/#/videos/e2cfdb8b-f2bd-4aae-a4dc-acb94790cceb