Epidemic Modeling: SIR Models, R0 & Herd Immunity in Excel

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Log-transform two weeks of case counts, fit the exponential growth rate with SLOPE, and turn it into a doubling time and a projection.
• Convert doubling times into growth rates and estimate R0 for four towns under three assumptions about the generation interval, and see how much the assumption matters.
• Write the three formulas that drive a 150-day SIR model for Lakeport (new infections, next day's S and I) and chart the S, I and R curves.
• Read the key numbers off a finished SIR model: the peak number of infectious people, the peak day, the final attack rate, and the share still susceptible at the peak.
• Calculate the herd immunity threshold of six diseases, the vaccination coverage needed with an imperfect vaccine, and the effective R at today's coverage.
• Model a contact-reduction measure that lowers β from a start day, run the SIR model with it and measure how much it lowers and delays the peak.
• Extend the SIR model with an Exposed compartment for the latent period, write the E and I steps, and measure how much the latent period delays the peak.
• Compute the early-phase model curve for five candidate transmission rates, score each against three weeks of observed cases with the sum of squared errors, pick the best β and chart the fit.