Excel Level 1: Population Growth
High School
Concepts
Lesson Abstract
This lesson will introduce students to some of the basics of Excel and how to use formulas, slider bars, and graphs to create a model of population growth. This model will take into account factors such as birthrate and carrying capacity.
The students will guided to first build a simple linear growth model and they will then be taught how to change that into an exponential growth model, finally adding a carrying capacity to simulate an environment with limited resources.
Objectives
- Familiarize students with both basic and more advanced elements of Excel
- Introduce the concepts of a basic Population Growth model.
Key Terms
- Population Growth
- Linear Growth
- Exponential Growth
- Carrying Capacity
Prerequisite Knowledge
- Basic knowledge of Algebra (especially variables and functions)
- Some familiarity with Excel would be helpful, but not necessary
Teacher Preparation
The teacher will need to be familiar with some extra features of Excel beyond the most widely known. The Excel Tutorial can familiarize the teacher with these features:
- Naming Cells
- Formulas
- Slider Bars
- Graphs
You can see the Excel Tutorial here:
Download Document
You can also see the finished model here:
Download File
Materials
| Required Materials | Media | Equipment |
- The Excel tutorial could be helpful for students but is not required
| |
- Whiteboard and markers
- An overhead projector is recommended
- A computer for the teacher
- One computer for each student or pair of students)
- Each computer should be equipped with Microsoft Excel (preferably Excel 2004)
|
Safety
No safety issues in this lesson
Presentation Outline
| Introduction to the Population Growth Model (5 min) - Rabbit Cage - If want to find out how many rabbits will be in a cage after a few days, how can we figure it out? What can affect the size of the rabbit population?
- Increase in rabbits
- More rabbits are born
- Rabbits are added to the cage
- Decrease in Rabbits (We will not be dealing with a decrease in rabbits in this model)
- Rabbits die.
- Rabbits are taken from the cage
- If we know the number of rabbits today, how do we find out how many will be there tomorrow? (What information do we need?)
- How many rabbits are there?
- INITIAL = (Students provide)
- How many rabbits are added each day?
- Added Rabbits = (Students provide)
- HAVE = HAD + CHANGE
- Rabbits now = Rabbits yesterday + Added Rabbits
|
| Linear Growth (10 min)
- Create 2 columns
- In Column D, enter the names of the constants. In column E put the actual constants next to their labels and name the constants so you can refer to them by their names instead of their cell numbers.
- Initial Rabbits
- Added Rabbits
- Explain to students that constants must be named because the reference will stay the same... and it's much easier to remember names than rows and columns! Make sure to name the values rather than their labels.
- Enter values for Initial Rabbits and Added Rabbits
- Enter values in the first field in each column
- Day = 1
- Rabbits = Initial Rabbits (Click on the constant value!)
- Enter values in the next field of each column
- Day = 2
- Rabbits = Previous Rabbits (Click on the cell for day 1) + Added Rabbits (Constant value)
- Point out how the variable names show in the formulas
- Drag down values
- Select Day 1 and 2, drag down to 50
- Select Rabbits on Day 2, drag down to Day 50
- What happens if we change the constant for Initial Rabbits?
- This can be compared to a y-intercept
- What happens if we change the constant for Added Rabbits?
- This can be compared to the slope
- Add scroll bars to Initial Rabbits and Added Rabbits.
- Add a graph
|
Physical Modeling and Measurement
| This lesson does not include Physical Modeling. |
| Exponential Growth (15 minutes)
- How do we find out how many rabbits will be born each day?
- A certain percent (birthrate) of the current number of rabbits
- Add birthrate to the constants (Name it!)
- Give birthrate a value (.1 or .2 are good values)
- Edit the Rabbits equation for day 2
- Rabbits = Previous Rabbits + Added Rabbits + (Prev Rabbits * Birthrate)
- This means that we are both adding a constant number of rabbits to the cage each day and some rabbits are being born each day.
- Drag Day 2 of Rabbits down
- Notice changes to the graph
- Should have an exponential curve going up
Carrying Capacity (15 minutes)
- Introduce Carrying Capacity
- What if there was a limit to the food supply?
- Any given environment can only support a certain number of animals.
- Equation for carrying capacity:
- (Prev Rabbits * Birthrate) * (Prev Rabbits / Carrying Capacity)
- Some of the baby rabbits can't survive.
- As Prev Rabbits gets closer to Carrying Capacity, less babies will survive because Prev Rab / Capacity approaches 1
- New equation:
- Prev R + Added_R + Prev R * Birthrate - (Prev R*Birthrate)*(Prev R/Capacity)
- Change equation for Rabbits in Day 2
- Drag down
|
| The students' finished model should look something like this:
Download File
Allow students to experiment with their models and make observations about the shape of the graph lines. Here are some example questions:
- Why does the graph line level off at the end?
- (carrying capacity, population can't spread beyond the limit)
- What could be added to this model to make it more realistic?
- How could scientists use this model?
- (Study the affects of different factors on a population)
|
| Use Excel II to teach the students about disease spread |
| |