COVID-19 Vaccine Distribution Tracking Template

Not sure if this is helpful to anyone but I made a COVID-19 Vaccine Distribution Tracking document in Excel. It’s hard to make a spreadsheet that you’re probably not going to use personally yourself. But I tried to envision being in management of a company or nursing home or school that needed to:

 (1) prioritize which employees or residents or teachers should get the vaccine first and

 (2) track which people have actually gotten the vaccine and when.

 

What Order to Distribute the Vaccine

The first item I tackled was how do you decide in what order to give out the vaccine. Knowing that supplies are limited, who gets the first dose?

I use a scoring system to decide the priority order: the highest score is first in line to get their shots.

 Here’s how the score is calculated:

1. Age: the score starts with the person’s age. 30 years old = 30 points. The older population automatically has a higher score.
2. Age multiplier: I made up a tier system based on age. Based on earlier data I’ve seen, the chance of dying from COVID if you’re 59 years or younger is only around 1% or less, but this jumps up to nearly 20% if you’re 80 or older. In my scoring system, those 60 and older get varying amounts of “bonus” points added to their score depending on what age range tier they fall into.
3. Priority person: Yeah, you could just rank vaccination order by age and be done with it but I didn’t want age to be the only limiting factor. There are other variables that I wanted to account for. I added the ability for a person to be marked as “high priority”, maybe due to immune deficiency or because they’re a first responder, or pregnant – whatever you want it to be. This adds 11 points to their score to help bump them up in the distribution order. Again, the spreadsheet is easily customizable so you can change the bonus to whatever value fits your needs.
4. Male or female: This might be controversial, but I added a segment to the scoring where males get a slight score increase over females. From the data I have (and please send me any new information if you think I’m wrong) males have a ~3% higher death rate from COVID than females (women are generally better at fighting off infections I’ve heard) so they get +3 point bonus while females get none. Of course, feel free to change this setting to make it what you want. Give woman 1,000 bonus points and men 0 – it’s up to you and can be easily changed in the spreadsheet.

The total score is calculated by adding up the age, the age tier multiplier, the male/female bonus, and the priority bump. The biggest factor is age, as that is the biggest factor of death rate. While getting the highest score means you’re first in line to get the vaccine, that also means you have the highest risk of dying from COVID.  I then use the RANK function in Excel to rank the individuals from highest to lowest score. There’s your priority list of who gets the vaccine first.

Vaccine Distribution Tracking

The next component, after deciding the vaccination order, is to then track if these people actually got the vaccine. Complicating matters, there are (right now) two different vaccines being distributed in the US requiring two separate doses and the time between doses is different. Each vaccine also has a minimum age requirement that is different so there is a formula to make sure the individual is old enough to get the selected vaccine. If they’re not eligible, meaning you’re too young, your score automatically goes to 0 and I use condition formatting to turn the cells black to show the individual shouldn’t be scheduled to get a shot. Once you get the first dose, enter the date and the spreadsheet will tell you when the earliest is you can get the second dose based on which vaccine you got. Finally, you can quickly see how many of your employees or residents have received the first or second doses of the vaccine.

 Download the spreadsheet here: https://gumroad.com/l/covid-tracker

I’ve been wanting to put my Excel powers to good use rather than just making Super Bowl squares or football related spreadsheets. Not sure the legality of it but if I was in charge of a company with employees working from home I would want to know when it is safe to bring that back into the office. Try it out and let me know what you think!