Case Study: Automated BOM Distributions for Sales, Production and Supply Chain Teams

Background & Problem

A mid-size industrial client approached us with a recurring challenge. Each week, their operations analyst had to manually download the Bill of Materials (BOM), current child-SKU data, and single-SKU inventory availability. The goal was to calculate compound SKU allocations according to business rules that maximized the sales team’s monthly goals.

This task was essential — sales needed to know what was available to sell, production needed to plan additional orders, and supply chain needed visibility. However, the process was so time-consuming that it was only performed once per month, since each report took around 8 hours to complete.

Objectives

• Automate the full BOM compilation and allocation report.
• Increase reporting frequency from monthly to weekly.
• Ensure compliance with IT policies (on-premise execution only).
• Reduce manual workload while keeping validation and control mechanisms intact.

Solution Design & Implementation

After analyzing the workflow and infrastructure, our team at Tirion proposed an on-premise automation leveraging open-source tools to ensure cost efficiency and security compliance. The solution was implemented within the client’s local network, integrated directly into a Windows Server 2019 environment hosting their ERP system.

We developed a Python-based automation using pandas for data processing. The script fetched input files from local shared folders, merged BOM, child-SKU, and inventory data, and calculated compound SKU allocations according to predefined business rules. The output report was automatically generated, formatted, and stored in a shared drive accessible to sales and production teams.

Results & Impact

• Processing time reduction: from 8 hours per month to ~20 minutes per run.
• Frequency increase: from monthly to weekly — a 4x increase (300% improvement in throughput).
• Information latency reduction: Sales and production now have access to updated allocation data every week instead of once per month.
• Time saved: from ~32 hours of potential monthly processing to ~1.3 hours, yielding a net saving of ~30.7 hours/month (~95% reduction).
• Operational benefit: improved coordination between sales and production, faster response to inventory fluctuations, and reduced decision lag.

The automation runs fully on-premise, following all corporate IT and data security guidelines.

Technology Stack

• Python — data processing and automation.
• pandas — joining, cleaning, and aggregating ERP data.
• Windows Server 2019 — on-premise execution environment.
• Bubble, n8n, and Zapier — low-code / no-code tools we often use for similar automation projects.

Methodology & Philosophy

Like all Tirion projects, this initiative followed our core automation methodology:

1. Identify the process and quantify its business impact.
2. Design an efficient solution respecting technological constraints.
3. Develop and validate with stakeholders through transparent iteration.
4. Deploy securely and maintain with minimal overhead.

Every automation we build aligns with business objectives — not just technical feasibility. Whether via Python, Google Apps Script, or low-code platforms like Bubble, we ensure each solution creates measurable operational value.

Results Summary

• Process duration: reduced from 8h → 0.33h
• Frequency: 1x → 4x per month
• Information latency: cut by 75%
• ROI: achieved in under 1 month of operation

Outcome: A robust, low-cost, on-premise automation improving decision-making speed across departments — built entirely with open-source technology.

Sources & References

• pandas Documentation — high-performance data analysis in Python.
• McKinsey: Digital Supply Chain Transformation — insights on automation and throughput gains.
• n8n — open-source automation for integrations and workflows.
• Bubble — no-code web app development platform.

At Tirion, we combine low-code, no-code, and code-first automation to empower business operations. For more information or tailored automation consulting, feel free to reach out to our team.