Excel Level 2: Susceptible Infected Recovered Model
Excel Level 2: Susceptible Infected Recovered (SIR) Model
High School
Concepts
Lesson Abstract
This lesson will guide the students to build a Susceptible, Infected, Recovered (SIR) Model of the spread of a disease, by finding and graphing the number of susceptible, infected, and recovered people in the model over time.
Objectives
 To build on the basic Excel skills from Lesson 1
 Introduce concepts of epidemiology
Key Terms
 Susceptible
 Infected
 Recovered
 Epidemiology
Prerequisite Knowledge
 Some familiarity with Excel.
 Basic knowledge of algebra (use of variables and functions)
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
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 SIR Model (5 min)
 At your School  if one person is sick, how long will it take for the disease to spread through the whole school?
 What do we need to know in order to find that out? (write on the board)
 How many people are at the school? (Total Number of People)
 TOTAL = (Students provide)
 A good number might be around 1000.
 How many people are sick at first? (Infected)
 INITIAL INFECTED = (Students provide)
 A good number would probably be 1.
 How many aren't sick right now, but could get sick? (Susceptible)
 SUSCEPTIBLE = TOTAL  INFECTED
 In other words, out of the total population of the school, whoever isn't sick must be well and susceptible to getting sick.

 Start building your model!
 Create 3 columns
 A = "Time"
 B = "Susceptible"
 C = "Infected"
 In Column G enter the names of the constants. In column H, write the constants themselves.(Don't forget to name them!)
 Enter values for Total and Initial Infected
 A2 = 0 (Initial Value of Time)
 B2 = TOTAL  INFECTED
 C2 = INITIAL INFECTED
 What happens if we change the Initial Infected?
 More people are initially sick.
 What happens if we change the Total?
 More people in the school who could possibly get sick.
 Add scroll bars to Total and Initial Infected.

Physical Modeling and Measurement
 The standard 50minute lesson does not include physical modeling. If there is extra time, students can play the SIR game during this time (see attached material)
Download Document

 Build the full model (30 min)
 How do we find out how many people will get sick each day?
 To figure this out, we are first going to have to figure out how many possible interactions between infected and susceptible people there are (we don't care about interactions between infected people and other infected people or susceptible people and other susceptible people, because those won't change the number of infected or susceptible people). How do we find out how many possible interactions there are? Use this example to help students: Hold up two fingers on one hand and three on another. If you want to touch each finger on one hand (infected) to each finger on the other hand (well) once, how many times would you touch your fingers together? Answer: 3*2 = 6 times. In our model, it works the same way.
 How do we calculate the number of possible interactions between infected and susceptible people?
 Interactions between susceptible and infected
 Possible Interactions = S * I
 Actual Interactions = Possible * Interaction Rate
 Infectious Interactions = Actual * Infection Rate
 delta Infected = S * I * Interaction rate * Infection Rate
 Interaction rate = % people you interact with
 Infection rate = % interactions result in infection
 Equations are the same as before!
 Infected = Infected + delta Infected * delta Time
 Susceptible = Susceptible  delta Infected * delta Time
 Columns:
 Day
 Susceptible
 Infected
 Recovered
 Constants (name these!):
 Total
 Initial Infected (slider bar)
 Delta Time (slider bar)
 Interaction Rate (slider bar)
 Infection Rate (slider bar)
 Enter Time
 Use formulas in Susceptible and Infected Columns
 Add slider bars
 Demonstrate how they need to be converted to get a fraction
 Build a graph

 Allow students to explore their models and make observations about the shape of the graph lines. Here are some example questions:
 Why do the graph lines level off at the end?
 (constant population, disease can't spread beyond the limit)
 What could be added to this model to make it more realistic?
 (vaccination, recovered become susceptible again, randomness)
 How could scientists use this model?
 (Study the affects of different diseases on a population, according to contagiousness)

 With extra time, you can also build a recovered column:
 Adding Recovered
 delta Recovered = I Before * Recovery Rate
 Infected = I Before + (delta Infected  delta Recovered) * delta Time
 Recovered = R Before + delta Recovered * delta Time
