The Excel file ESTIMATING THE FREEZING CAPACITY for a vaccination campaign (see Toolbox) contains several tabs, some of them protected.
This file automatically calculates needs as a function of the data entered (campaign schedule and duration, cold chain equipment, sites and number of vaccination teams, freezer inventory, etc.).
Using the worksheets
- Each file contains:
- three sample worksheets: vaccination schedule by location, vaccination schedule by team, estimating freezing capacity;
- three “Freezing estimate” worksheets, labelled A to C. Use one worksheet per zone or district. If necessary, create new worksheets.
- Fill in only the yellow boxes, do not enter data into other boxes as this may modify the automatic calculations and generate errors.
For each worksheet
1) Define the cold chain equipment for one vaccination site:
- One vaccination site can accommodate one or two teams, at most.
- One cold box and one vaccine carrier are needed for each team.
- The number of ice packs is adjusted depending on the outside temperature.
Check the yellow boxes for the temperature (less than or ≥ 40 °C) and the interval for replacing the ice packs in the cold boxes (1, 2 or 3 days).
The worksheet automatically calculates:
- Table A:
- number of cold boxes and vaccine carriers per team;
- number of ice packs per cold box and vaccine carrier;
- total number of ice packs at D1.
- Table B: number of ice packs needed per day for one and two teams at one site.
2) Estimate the number of ice packs needed each day for all of the teams and sites. In Table C, enter:
- Name of the district or zone covered.
- Name of the towns/locations and sites.
- For each site, specify the number of teams and their identification according to the vaccination schedule established.
- Then enter the number of ice packs needed per day and per site as a function of the number of teams, taken from Table B.
The total need in ice packs per day is calculated automatically.
3) Determine the available and required freezing capacities.
From the inventory, fill in one line in Table D for each type of working freezer available.
The worksheet automatically calculates:
- total freezing capacity available;
- total ice pack storage capacity;
- total freezing capacity per day;
- number of days to allow so that all of the ice packs can be frozen by D1.
4) Change in ice pack needs per day for the sites
On D1, note the maximum number of ice packs that can be stored or the maximum number of frozen ice packs available on D1.
The worksheet automatically calculates:
- balance = number of ice packs available each day;
- out = total number ice packs given per day to vaccination site teams;
- in = total number of ice packs that can be frozen per day.
Any negative quantity means that there will not be enough ice packs to supply all the teams. Note: this amount must not exceed the number of ice packs that can be stored in all of the available freezers combined.
If the number of freezers is insufficient, increase freezing capacity: add enough additional freezers in Table D to supply all of the sites for the anticipated duration of the campaign.