This spreadsheet is designed to help you track how quickly PPE is being used at your facility.
- To start, enter dates into Box A below Day 1, Day 2, Day 3, etc. You can enter the date for Day 1 and drag that cell to the right to autopopulate future days.
- Enter the total number of suspected and confirmed COVID-19 patients at the start of each day in the "Suspected and Confirmed COVID-19 Patients" row of Box A.
- At the start of each day, determine how many full boxes of PPE are remaining from the day before. Do this for each type of PPE and enter them into Box A.
- "Type of PPE" refers to the PPE components you have in stock. "Gowns," "gloves," "respirators," "surgical masks," and "face shields" have been filled in for you. Use the next column to fill in the size or brand, if applicable. You only need to fill this column in if you have multiple sizes or brands. Otherwise, leave these cells blank. Examples of sizes/brands have been added for you, but you should modify these cells according to your inventory. Note: Additional types or brands of PPE can be added in the "Other" Type of PPE section as well.
- Make sure that each box of PPE has the same number of individual units (Example: a box of gloves may have 200 individual gloves). If they do not, start a new "Type of PPE" category in the cells that says "Other".
- Enter the number of full boxes of each Type of PPE starting at Day 1 into the yellow cells of Box A. Note: You do not need data for all 14 days, but at least 2 consecutive days of data are needed to calculate a consumption rate (Burn Rate).
- The total number of boxes of PPE used per day will be calculated in Box B. Note: This only works if you have entered values for at least Day 1 and Day 2 in Box A.
-The average rate of PPE consumption (burn rate) will be calculated in the pink box (Box 1) for each individual Type of PPE and by the total type of PPE (such as the sum of all gowns).
- The average rate of PPE consumption (burn rate) per patient will be calculated in the pink box (Box 2) for each individual Type of PPE and by the total type of PPE (such as the sum of all gowns).
- The number of days' worth of remaining PPE will be calculated in Box C based on the average consumption rate.
- The number of boxes of PPE used per patient per day will be calculated in Box D.
Note: if you receive a resupply of PPE, do not add it into the calculator, as it will disrupt the calculations. Continue following the original supply of PPE entered in Day 1. It is suggested you start a new calculator for the resupplied PPE.
- To skip a day, enter the data from the previous day. For example: If you know the number of boxes remaining at the start of Day 1 and the start of Day 3, but not the start of Day 2, you can insert the value from Day 1 into cell for Day 2 and the tool will still work.
Important: Add your data to Box A only. Do not enter data into any other cells.
How the calculator works:
In Box A, your PPE supply from the day prior is subtracted from the current day (Day2 -Day1) and entered in Box B. As additional data are added in Box A, the consumption rate between each two day period is calculated. The total number of consumption of PPE in Box B is used to calculate the average consumption in the pink section (Box 1).Then the number of boxes of PPE entered in Box A is divided by the consumption rate to calculate the number of days' supply remaining in Box C. The boxes of PPE used per patient (Box D) are calculated by dividing the boxes of PPE used per day by the number of patients per day.
Most cells in this worksheet are locked to prevent users from accidentally changing the formulas. However, if you would like to modify the worksheet to better fit the needs of your facility, that can be done by unprotecting the worksheet. To unprotect the sheet, go to File > Info > Protect > Unprotect Sheet or you can also unprotect the sheet by going to the Review tab > Changes > Unprotect Sheet.