Star Alliance Airline Soars with Alteryx Server
At a Glance
1000+
Workflows Governed
10
Departments Migrated
25
Alteryx Champions Trained
Overview
Service
Data Engineering & Infrastructure
Industry
Airlines
Stack
Machine Learning & Gen AI
Optimizing Supply Chain Efficiency for a Multinational Healthcare Wholesaler
Author(s)
Tyler Nagano
Data Engineer
Technology Stack
The Challenge
Our client, a multinational healthcare wholesaler, relied on an efficient supply chain to meet customer demands while minimizing inventory costs. To optimize stock levels, they developed a base model in Alteryx that calculated key supply chain metrics, including Economic Order Quantity (EOQ).
While the model provided a solid foundation, evolving business needs required additional rules and adjustments to better align with operational realities. These modifications were manually applied in Excel, creating a fragile and unscalable process. As the complexity grew, maintaining the calculations became increasingly difficult, leading to inefficiencies and potential errors.
The client recognized the need for a more structured and maintainable approach—one that could seamlessly integrate new business rules while ensuring accuracy, efficiency, and scalability in supply chain decision-making.
The Solution
Expanding upon the base model created in Alteryx, Compass Analytics developed a set of workflows to integrate additional data sources, apply business rules to calculations, and validate results. The updated approach captured all the logic that was previously handled manually in Excel, but now structured it within Alteryx workflows. By reading in template Excel files, the system applied business rules consistently, ensuring accuracy and eliminating the risk of errors introduced by manual adjustments.
This centralized approach provided a clear, transparent way to track all applied rules, making it easier to monitor and adjust calculations as business needs evolved. The solution was designed for scalability, allowing it to be deployed across all products while ensuring simple maintenance and long-term flexibility.
Impact
$50M
One-Time Inventory Savings
$5M+
Cash Flow Benefit
6
Months to 2 Days: Fresh Data
8+
Hours Over Several Days to Under 1 Hour
Stack
Our Client’s Context
The multinational healthcare wholesaler was struggling to maintain its supply chain optimization model. Originally built in Alteryx, the model calculated key metrics essential for optimizing stock levels to meet demand while minimizing costs. However, over time, the knowledge required to maintain the model eroded, and new business requirements emerged that needed to be incorporated into the process. To accommodate these evolving needs, the team turned to Excel, a tool they were more comfortable with, to manually apply business rule adjustments.
These adjustments introduced significant complexity, requiring multiple layers of logic and generating additional files across different business units. The process became increasingly fragmented, with changes relying heavily on a single individual who possessed the expertise to implement them. This created a single point of failure, where the loss of that individual’s knowledge could disrupt the entire supply chain optimization effort.
Beyond the risk of knowledge loss, the growing reliance on Excel made the business logic increasingly difficult to follow and maintain. The calculations and rules applied through these manual processes had already saved the company millions in optimizing stock levels, but as the process became more cumbersome, it was clear that a more scalable and structured solution was needed to sustain these savings and ensure long-term efficiency.
Additionally, the company’s main system for setting optimal stock levels was open to changes from multiple users across the organization. This sometimes led to discrepancies between the calculated optimal values and adjustments made manually within the system. To address this, the company had developed a validation tool in Excel to compare system values with model outputs. However, like the rest of the Excel-based process, this tool was also becoming increasingly difficult to maintain, further highlighting the need for a more robust and automated solution.
The ABCs of Challenges
Lack of Maintainability
The primary challenge for the client was the increasing complexity and lack of maintainability of the business logic built within Excel. Over time, the process became difficult to follow, requiring significant effort to understand the transformations being applied. Without a fundamental shift to a more structured approach, the model risked becoming unmanageable.
Expert Knowledge Required
Working with the business rules in Excel required deep expertise due to the intricate web of multiple tabs, complex formulas, and linked connections. The knowledge of how to implement and update these rules was largely concentrated in a single individual, creating a critical dependency. If this person left the organization, the business would face significant risks, as no one else had a complete understanding of how the model functioned.
Ensuring System Accuracy
Beyond the challenges of maintenance, the client also struggled with ensuring system accuracy. Since many employees had access to modify stock values in the source system, discrepancies could arise between the calculated optimal stock levels and the actual values in the system. While the company had developed a validation tool in Excel to compare these values, it too was becoming difficult to maintain. A more automated and scalable approach was needed to ensure that the correct stock levels were consistently maintained across the organization.
Eliminating Bottlenecks for Success
To address the challenges, the Compass Analytics team began by thoroughly reviewing the existing Alteryx workflows and Excel-based business logic to gain a deep understanding of the process. After completing the exploratory phase, the team designed a structured solution consisting of three core workflows: one for data cleaning and calculations, another for applying business rules, and a third for validating source system results against model outputs.
The data cleaning and calculation workflow integrated multiple data sources, including inputs from various databases, Excel files maintained by other teams, and internal data sources. Once consolidated, the workflow performed stock quantity calculations and generated business flags based on product characteristics, ensuring consistency in how stock levels were determined.
The business rules workflow applied key transformations and logic to the cleaned data. Using Excel-based template files, the system dynamically applied rounding rules, categorization logic, and other key transformations. Additionally, multiple summary reports were generated, including ABC classifications, providing executives with high-level insights into inventory performance.
The validation workflow ensured that stock levels remained accurate despite potential manual changes made in the source system. By comparing model-generated optimal stock levels with the actual values recorded in the system, the workflow quickly identified discrepancies, enabling the business to flag and correct any misaligned data.
To further enhance visibility and historical tracking, the finalized business rule outputs were archived, creating a record of previously generated values. This allowed the company to track changes over time, ensuring better auditability and long-term decision-making based on historical trends.
Keys to Forecasting Long Term Benefits
The new solution developed by Compass Analytics provides the multinational healthcare wholesaler with a scalable and maintainable approach to running supply chain calculations, applying business rules, and generating insightful reports. By automating complex processes and eliminating reliance on manual Excel adjustments, the company can continue optimizing stock levels efficiently—resulting in millions of dollars in annual savings while ensuring long-term maintainability.
A Maintainable and Scalable Solution
By converting business logic from a complex web of Excel formulas into a structured Alteryx workflow, the solution eliminates manual dependencies while maintaining flexibility for changing business needs. Business users can now quickly compare different scenarios, gaining deeper insights into optimal stock levels without requiring extensive technical expertise.
Simplified Process with Less Specialized Knowledge Required
Previously, only a single individual had the expertise to navigate the Excel-based process. Now, clear documentation and standardized templates allow any team member to adjust parameters and apply new business rules. The streamlined approach reduces complexity, leading to faster decision-making and a shorter time to insight.
Ensuring System Accuracy with Built-In Validation
To safeguard against manual discrepancies, the solution includes a validation workflow that automatically compares model-generated stock levels with values in the source system. Any inconsistencies are flagged, and the output is formatted for easy updates back into the system, ensuring that only the most accurate stock levels are used. This reinforces cost efficiency and prevents costly errors in inventory planning.
By transitioning to an automated, structured, and user-friendly system, the company has eliminated inefficiencies, reduced operational risks, and ensured long-term savings through optimized stock management.
