Difference Equation Modeling on Excel

Difference Equation Modeling on Excel
This practice assignment will task you with building a spreadsheet-based simulation (using difference equations) to model the spread of the Hong Kong Flu through New York City in the winter of 1968-1969.

The equations below can be used to describe the cycle of a disease. Equation 1 shows that the susceptible population decreases as more people within the susceptible group become infected (more people become infected based on the infection rate and how many people are already infected). Equation 2 shows that the infected group changes based on the number of people who are susceptible to be infected and how many people are recovering, and Equation 3 shows that the number of people in the recovered group changes based on the recovery rate and the number of already infected people. The state variables in this system are St, It, and Rt. The definition (and units) for each of the variables in the three equations are given below the equations themselves.

Eq 1: St+∆t = St-?StIt∆t

Eq 2: It+∆t = It+[?StIt-(dIt)] ∆t

Eq 3: Rt+∆t = Rt+dIt∆t

Where

St = # in susceptible group at time t (people)

It = # in infected group at time t (people)

Rt = # in recovered group at time t (people)

d = recovery rate (time / person)

? = infection rate (time / person)

∆t = the length of time between examinations of the state variables (time)

Use Equations 1, 2, and 3 to build a model of the disease progression over time in Excel. As a hint, you will likely want to use different columns in Excel for: the number of days that have passed, the Susceptible Population (St), Infected Population (It), Recovered Population (Rt). In such a set-up, each row will represent t (the number of days that have passed), and each row will contain a value for “days into disease cycle”, St, It, and Rt. You’ll also need to have placeholders for some input model parameters: infection rate (?), recovery rate (d), and ∆t, which you’ll use to fill in the equations that calculate the values for St, It, and Rt. Start your model at “0” days into the disease cycle (this row will represent the initial conditions).

Below is some additional information you’ll need to build your model. Simulate your system for 50 days (again, each row in your model should correspond to a day, so you’ll have 51 rows in your model – one for the starting conditions, and one row for each of the 50 days you simulate).

The initial susceptible population of New York City at the time was 7,899,990 people

• The initial number of people infected was 10

• d = 0.33/day (so the flu lasted 3 days)

• ? = 2.5 x 10-7 per day

• Set ∆t = 1 day

NOTE: even with the provided parameter values (for d, ?, and ∆t), you may observe an unusual data point in the Susceptible Population. That doesn’t mean your model is wrong. Remember, “all models are wrong, some are useful” (we content that this model is still useful, despite the unusual data point).

1. your original excel model, named as follows:

“PA_yourlastnamehere.xlsx”. This file should include the following:

a. a table of values St, Rt, and It across the 50 day time frame. Your table will need the following columns: time (days), susceptible population (St), infected population (It), and recovered population (Rt). Each row should calculate the value of the state variables at the current value of t.

b. a table giving the values of the controllable model parameters: ?, d, and ∆t. (NOTE: the table in part (a) of this question will need to refer to these values in the calculation of state variables).

2. A single, typed word or pdf document with the following information:

a. A list of any assumptions that you think went into the development of this model.

b. A single XY scatter chart (copied and pasted from the Excel file) showing the variables St, Rt, and It as a function of time (time = x axis, the number of people in each group = y axis of graph). The graph should have three lines on it, each line corresponding to a different variable (one line for St, one for Rt, and one for It). Be sure the graph has a title, a legend (showing which color line represents which variable), and appropriate labels for the axes (including units!). The title of the graph should indicate what the recovery and infection rates were for the data you graphed.

c. A second XY scatter chart of St, Rt, and It over time (also pasted from Excel), but which is based on a different ? and/or d parameter. For this graph too, be sure the graph has a title, a legend (showing which color line represents which variable), and appropriate labels for the axes (including units!). The title of this graph should also indicate what the recovery and infection rates were for the data you graphed.

d. A brief (sentence or two) written conclusion (based on your model) about the impact of changing the recovery and/or infection rates. How did it impact the rate at which each of the groups changed in size?

e. A brief (a paragraph or so) discussion of how a model like this of the spread of disease might be useful (e.g., why might it useful to have a model that allows you to explore different scenarios about the cycle of a disease? who would be likely to use this model?)

FOR YOUR ASSIGNMENTS TO BE DONE AT A CHEAPER PRICE PLACE THIS ORDER OR A SIMILAR ORDER WITH US NOW