Development and Use of Electronic Spreadsheet Tools to Manage Vaccine Inventory and Ordering In a Community Health Setting

Background: With increased numbers of pediatric vaccines routinely recommended, large and costly amounts of VFC vaccine are ordered at clinics.   To educate vaccine ordering staff and reduce potential loss of vaccines due to large inventories on hand, a standardized system for tracking usage and ordering vaccines to maintain adequate levels was sought.

Setting: Denver Health serves the City and County of Denver with multiple components including a 500-bed inpatient hospital, Level 1 Trauma Center, County Public Health Department, and a Community Health Services division with family health centers spanning 8 geographic locations and 13 school-based health centers.

Population: Community Health Services cares for 112,000 patients at Internal Medicine, Pediatric, Women’s Care and Family Practice centers and administered 171,046 pediatric, adolescent and adult vaccinations in the year 2009.

Project Description: A comprehensive vaccine ordering and receiving guide was developed, with four accompanying electronic worksheets.  The first spreadsheet calculates average vaccine usage, then links to the next sheet which tracks current inventory and provides suggested amounts of vaccine to order for 30 – 45 day level.  Another page monitors ongoing flu shipments received.  The final tool is a table for converting vaccine dosage volumes.  Each workbook becomes site-specific based on that clinic’s inventory. After piloting at a Pediatric Clinic, the tools were refined and trialed at two Family Practice sites before expansion to all Community Health VFC ordering sites.  Rollout to Adult patient care sites ordering privately purchased vaccines will follow.

Results/Lessons Learned: Results at the pilot sites include increased compliance with VFC guidelines to maintain a 30 – 45 day inventory of vaccines.  This is minimizing the potential for loss in a cold chain event while ensuring an adequate supply of all vaccines.  Future plans include the standardization of the ordering process across the agency for both VFC and privately purchased vaccines.