Coronavirus: Coding a simple model using a spreadsheet

Here is an approach to coding a simple epidemiologic model using a spreadsheet and the principles discussed in the last few posts. The example uses Microsoft Works SPreadsheet, but Exel or similar program should work as well.

Figure 1

Figure 1 shows the basic structure. The "A" column just contains the labels for variables that are entered in the "B" column. Voumn "C" is where each day's new cases are computed and displayed. Enter a "0" in cell C2 and =D2*E2 in cell C3. This computes each day's new cases as the product of the current "r" value and the previous day's active cases.

The "D" column contains the active cases. It is the running total of the daily cases minus the cases that drop out because they are no longer infectious. Enter the starting number of active cases in cell D2, and =D2+C3 in cell D3. Once we have finished with the basic structure we will make a slight change to column D to account for the cases that are no longer active.

Column "E" is where the value of "r" is computed. Enter =B2 into cell E2 and 

= $E$4*(E2-$B$3)+$B$3

into cell E3. This formula is to model exponential decay of r from the initial value to the final value. The $ characters inserted in the variable names are so that the program will use the specific value contained in the referenced cell, and not increment the index for subsequent values.

Now select the cells C3, D3 and E3, and drag the lower right hand corner down the page to do subsequent computations. the result should look like this:

Figure 2.

Now, account for the cases that are no longer infectious, start at cell D2 and count down the number of cells equal to the number of days that corresponds to duration of infectivity being modeled. For example, if this value is 8 days count down to cell D10. Modify the contents of that cell from its current formula (=D9+C10) by appending -C2. In the example, the new entry in cell D10 should be 

=D9+C10-C2 

Now select this cell, drag the lower right hand corner down the page until it ends at the same row as columns C and D. We can change the number of days simulated by selecting the last three cells in the columns and dragging them down to correspond to the desired number of days. We can then use the graphing utility to plot values of interest, e.g. the daily number of new cases. For 75 periods we get the following graph:

Figure 3.

We could also plot the active cases:

Figure 4.

Those are the basics. Now we can change parameters to see how those affect the shape of the curves, or the numbers. We can for instance change the value of r to simulate the effects of lessening mitigation processes, and vary the points in time when we do this to gauge when such changes might be prudent. We can add additional columns to compute the total number of cases and compute rations such as new daily cases to total cases and compare these we with actual numbers reported in real places.