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 DocumentYou 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 recommendedA computer for the teacherOne computer for each student or pair of studentsEach 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 - INFECTEDC2 = 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 50-minute 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 infectedPossible Interactions = S * IActual Interactions = Possible * Interaction RateInfectious Interactions = Actual * Infection Ratedelta Infected = S * I * Interaction rate * Infection Rate
Interaction rate = % people you interact withInfection rate = % interactions result in infectionEquations are the same as before!
			Infected = Infected + delta Infected * delta TimeSusceptible = Susceptible - delta Infected * delta Time
Columns:
			DaySusceptibleInfectedRecovered
Constants (name these!):
			TotalInitial Infected (slider bar)Delta Time (slider bar)Interaction Rate (slider bar)Infection Rate (slider bar)
Enter TimeUse formulas in Susceptible and Infected ColumnsAdd 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 RateInfected = I Before + (delta Infected - delta Recovered) * delta TimeRecovered = R Before + delta Recovered * delta Time
 |