Challenge: During the COVID-19 pandemic, the system faced significant challenges in managing the procurement, tracking, and distribution of Critical Supplies and Personal Protective Equipment (PPE). The organization needed an efficient system to Order Supplies, Monitor Inventory, Track Usage, and Forecast needs across departments

Results/Outcomes:

• Improved Efficiency: Streamlined ordering and inventory management processes, reducing total turn around time between order to arrival of supplies across departments

• Better Forecasting: Achieved more accurate demand forecasting with a 15% improvement in aligning supply levels with actual needs based on volume of patients, staff volumes, burn rate/utilization rate, etc.

• Informed Decision-Making: Provided stakeholders with actionable insights through data visualization and trend analysis, enabling better strategic planning and response

Core Team: Quality Department, Procurement/Inventory Management, Healthcare workers, IT and Data Analysts

Timeline: 2 weeks, until replacement with permanent system

Approach:

To address these challenges, a comprehensive Excel-based system was designed and implemented. The approach included the following key components:

Days 1-2: Design and Development:

• Order Management Module for PPE: Created a centralized spreadsheet for inputting and tracking orders, including supplier details, order quantities, and delivery dates.

• Inventory Tracking Module: Developed a real-time inventory tracker that updated stock levels automatically upon order fulfillment (not usage).

• Usage Monitoring Module: Implemented a usage log to track daily consumption rates of PPE and other critical supplies.

• Forecasting Tool: Built a forecasting model using historical data and usage trends to predict future supply needs. Integrated formulas and charts to visualize trends and projections.

  Day 3- End of Week 2

  Implementation:

  • Data Entry and Maintenance: Established protocols for regular data entry and updates by designated team members to ensure accuracy.

  • Performance Monitoring: Regularly reviewed system performance and accuracy, making adjustments based on feedback and evolving needs.

  • Continuous Improvement: Incorporated feedback from users to refine the system and enhance its functionality over time.

  Skills Used

• Lean Six Sigma, Project Management, Excel, Data Analysis, Supply Chain Management, Excellent Communication, Stakeholder Engagement