Data Analyst and Clinic Management

Introduction

During my internship at BioGroup, a COVID Testing Lab with multiple clinics across London, I worked as a Data Analyst and Clinic Manager to address operational challenges that arose during the height of the pandemic. The main objectives were to streamline the inventory management system across the clinics, prevent stockouts and last-minute deliveries, and provide actionable insights to decision-makers through advanced reporting tools.

The existing workflow faced several challenges: inventory mismanagement that sometimes led to costly emergency deliveries, suspicion of potential theft in one of the clinics, and a lack of visibility into day-to-day operations. My task was to implement effective solutions that leveraged data to create a clearer operational picture for clinic managers and executives alike.

Inventory Management System

Objective

The goal of the inventory management system was to ensure that seven clinics were adequately stocked with essential supplies for conducting COVID tests without excess or shortages. This meant reducing last-minute inventory requests, preventing stockouts, and tracking usage in an organized way.

Implementation Process

To address these needs, I implemented a weekly reporting process for inventory tracking across all clinics. Here's how the system worked:

1. Data Collection: Each clinic would complete a standardized inventory report in an Excel workbook every week, detailing their current supplies and anticipated needs. This report would then be emailed to me for aggregation.
2. Data Aggregation: The automated script in PowrBI would run when scheduled to process the new invenetory data. Each report was saved in a centralized data directory, and the script would extract relevant data from the Excel sheets and append it to an existing master inventory file. This master file served as the comprehensive inventory tracking document, which was updated weekly without manual data entry, thereby reducing human error and saving time.
3. Forecasting Inventory Needs: The system also included a forecasting component, where the weekly inventory submissions were analyzed to predict the following week's needs. This ensured clinics received enough supplies, accounting for anticipated surges while preventing excessive inventory buildup.
4. Dynamic Visualizations: I created dynamic visualizations directly in Excel, enabling clinic managers to quickly understand inventory status. These visualizations included:
   • Weekly Inventory Levels: A bar chart showing the current stock levels across all clinics, color-coded to highlight those at risk of shortages.
   • Usage vs. Delivered Inventory: Line charts tracking supplies delivered vs. inventory used at each clinic, helping to identify any discrepancies or inefficiencies.
   • Anomaly Detection: A flagging system that identified unusual inventory consumption patterns. This helped us investigate potential misuse or theft at one clinic, where inventory was disappearing faster than anticipated.

Challenges Overcome

Initially, clinics struggled to adhere to the standardized reporting procedure. I worked closely with each clinic manager, providing step-by-step guidance on filling out the reports and troubleshooting any issues they faced. Eventually, the standardized approach resulted in consistent and accurate inventory data across all locations.

SQL and Data Wrangling

Objective

Alongside inventory management, another key part of my role was to provide the company's executive team with deeper insights into their operations through data analysis. I leveraged the existing data from two main sources—the COVID testing database and PayPal—to provide valuable KPIs and metrics for decision-making.

Data Sources

• COVID Testing Database: Included data on test results, patient information, timestamps, and test types.
• PayPal API: Provided financial information on the tests sold, including revenue per clinic and payment statuses.

Process

I used SQL to perform data wrangling, extracting and cleaning the raw data from the COVID testing database. I created various views and tables to support KPI generation, including:

• Test Completion Rates: Aggregated data on tests completed per clinic, broken down by week and test type.
• Revenue Insights: Summarized sales and revenue figures for each clinic, allowing us to compare financial performance.
• Inventory Efficiency: Correlated the number of tests performed with inventory usage, highlighting any discrepancies.

For further analysis, I used Pandas in Python to manipulate and process the data. Pandas allowed me to handle missing values, normalize the data, and conduct ad-hoc analyses.

Power BI Dashboards

Objective

To help the CEO and managers make informed decisions, I developed interactive dashboards in Power BI. The goal was to provide real-time insights into clinic performance, inventory efficiency, and financial metrics.

Key Features

• Main KPIs: The dashboards provided key insights such as:
  • Revenue by Clinic: Visualized using bar charts to highlight which clinics were generating the most income.
  • Test Volume Patterns: Line charts showing the number of tests conducted over time, with filters for different test types and clinics.
  • Inventory Utilization: Interactive charts correlating inventory usage with the number of tests conducted, identifying any wasteful practices.
• Interactivity: The dashboards featured filters to allow users to drill down by specific clinics, test types, or time periods. This interactivity empowered the executive team to gain granular insights into clinic operations.
• Refresh Schedule: The dashboards were updated hourly, ensuring that decision-makers always had access to the latest information.

Impact

The Power BI dashboards allowed the management team to make data-driven decisions regarding clinic performance, resource allocation, and inventory planning. It also helped identify underperforming clinics that required additional support or interventions.

Python Automation and Advanced Reporting

Objective

Reduce the manual workload for inventory tracking and ensure consistent, accurate reporting.

Automations Implemented

The script was scheduled in Power BI to run automatically each week, pulling all inventory reports, updating the master inventory file, and generating a summary report. This automation not only saved time but also ensured data consistency.

For advanced reporting, I used Matplotlib to create visual representations of weekly trends in inventory levels and Pandas to generate analytical reports that highlighted anomalies in usage patterns.

Reporting

The generated reports highlighted key metrics, such as unusually high consumption rates, which were then flagged for review with the clinic managers. I also ran "What-If" analysis scenarios to simulate potential future shortages based on different testing demand scenarios, ensuring that clinics were always prepared.

Collaboration and Challenges

Stakeholder Management

During the project, I collaborated with several key stakeholders:

• Clinic Managers: I worked directly with clinic managers to implement inventory tracking and ensure data accuracy. Regular check-ins were essential to keep everything on track.
• Clinic Director: I often found myself taking on additional responsibilities due to a lack of support from the clinic director. This included personally visiting clinics, ensuring compliance, and even stepping in to resolve operational issues.

Challenges

• A major challenge was standardizing the inventory process across clinics that had previously operated independently. To address this, I organized individual training sessions for each clinic manager to ensure they understood the reporting requirements and felt confident in using the new system.
• Another challenge was gaining buy-in from the clinic managers, who initially saw the new system as an additional burden. By demonstrating the system's benefits in terms of efficiency and transparency, I was able to secure their support.

Strategic Impact

Cost Savings

By streamlining inventory deliveries and optimizing weekly shipments, we were able to eliminate unnecessary emergency deliveries, leading to significant cost savings for the company.

Operational Improvements

The inventory management system provided real-time visibility into stock levels across all clinics. This visibility helped to prevent stockouts, improve clinic operations, and identify discrepancies in inventory usage.

Insights

Through my analysis and the Power BI dashboards, I was able to provide strategic recommendations, such as reallocating resources to high-performing clinics or investigating unusual inventory consumption patterns. These insights led to targeted interventions that improved overall operational efficiency.

Application Setup and Integration

Objective

To ensure that all clinics had access to essential data for managing COVID tests efficiently.

Implementation

• I set up the company's custom application on each clinic's computer. The application included features for tracking test progress, accessing test results, and managing patient communications.
• I configured the appropriate permissions and trained clinic staff on using the application. This application was then integrated with the inventory system, ensuring that test volumes and inventory data were linked. This linkage allowed for more accurate inventory planning based on actual testing activity.

Training

Training was a key aspect of this implementation. I provided clinic managers with a user guide and conducted hands-on training sessions to ensure they were comfortable using the system. This helped improve the transparency and efficiency of test operations.

Long-Term Sustainability and Handover

Documentation

To ensure that the systems I implemented were sustainable, I created detailed documentation for all workflows, including the Python scripts, Excel templates, and Power BI dashboards. This documentation covered both technical aspects and step-by-step instructions for clinic managers.

Training

I conducted training sessions to ensure clinic managers and relevant staff could maintain the systems. I also left behind a troubleshooting guide to address common issues they might face.

Legacy

By automating repetitive tasks and simplifying inventory management, I left behind a more efficient and streamlined system that required minimal manual intervention, allowing staff to focus on more critical aspects of clinic operations.

Conclusion

My internship at BioGroup provided me with the opportunity to tackle real-world operational challenges using data-driven approaches. Through the implementation of an automated inventory management system, the development of interactive Power BI dashboards, and the streamlining of daily operations at multiple clinics, I was able to contribute significantly to BioGroup's operational efficiency.

This experience enhanced my skills in SQL, Python, Power BI, and stakeholder management, and it reinforced the value of data in driving operational improvements. I learned the importance of collaboration, persistence, and communication in achieving project goals and delivering impactful results.