What the simulation does
Open file 5-Expansion.xlsx on sheet “Epidemic.” In this simple simulation, we follow the course of an epidemic (e.g. the flu) based on certain variables in column H.
In general, epidemics follow a more or less fixed pattern. Initially only a few people get sick, but soon the number of sick cases rises exponentially until stabilization sets in, and more and more people recover.
We need some essential parameters, although they may not always be exactly known. We will only focus on transmission rate, recovery rate, and death rate—without going into issues such as mutation rate for the virus or bacterium.
What you need to know
The model that we apply is the standard SIR model, commonly used for many infectious diseases. The name of the model reflects the three groups of individuals that it models: Susceptible people, Infected people, and Recovered people.
There are a number of important thresholds in this model. Reaching, or failing to reach, these thresholds is a crucial feature of managing the spread of infectious diseases. The system is sensitive to some changes and not to others, so this may give some insight into when and where the problem should be attacked.
In order to make the appropriate calculations, we use the Euler’s method, without explaining it any further. You can find it explained elsewhere.
What you need to do
- Place in cell A3: =A2+$H$1. This adds a “step” period of any value (in our case 5, for hours or days or whatever). Copy down to cell A302.
- Place in cell B3: =B2-($H$6*B2*C2)*$H$1. This formula is derived with Euler’s method. Copy the formula down to see what happens to the number of susceptible people.
- Place in cell C3: =C2+($H$6*B2*C2-$H$7*C2)*$H$1. Copy the formula down to see how the number of infected people changes.
- Place in cell D3: =D2+((1-$H$8)*$H$7*C2)*$H$1. Copy the formula down to estimate the number of recovered people.
- Place in cell E3: =E2+($H$8*$H$7*C2)*$H$1. The number of deaths depends heavily on the death rate (cell H8), in addition to the number of recovered people and their recovery rate (cell H7).
- Place in cell K3: =D302. This is the latest number of recovered people. We have set the limit to row 302—that is, 300 time periods—but for certain situations you may need more.
- Place in cell K4: =E302. This is the latest number of deaths.
- Place in cell K5: =B302. This is the last value in the column of susceptible people.
- Place in cell K6: =MAX(C2:C302). This is the highest number of sick people per period of time in column C.
- Change some variables in the cells H1:H8 and watch their effect on the epidemic. (You may have to hit Sh F9).
- Obviously, this simulation is still rather deterministic. We could have included probabilities, but I leave that up to you.