Automating Multiple SIOP Reports In One Unified Dashboard

Context

Our client was a manufacturing company in the US with annual revenue of $120M, being a top-3 global suppliers of a niche product used in the commercial construction industry.

The Director of Operations set out to streamline the company’s SIOP (Sales, Inventory & Operations Planning) process, including the underlying reports that supported it. At the time, key reports were manually generated each week by the sales, operations, and purchasing teams, as outlined below:

• Sales created forecasts of upcoming customer bookings to help operations anticipate future production demand beyond the confirmed orders entered in the production system.
• Sales also shared these forecasts with the purchasing team, which used them to generate an inventory drawdown report based on expected demand.
• Operations responded to capacity inquiries from sales, enabling them to sell into weeks with available production capacity.

This manual workflow presented several challenges. Each week, three separate reports were manually prepared, consuming an estimated 9 hours of collective effort—equivalent to roughly 1/4 of a full-time employee’s workload. Additinoally, version control was a recurring issue, with outdated reports often being circulated by mistake. Finally, due to delays in operations' responses, the sales team built their own unofficial spreadsheet to estimate available production capacity—but it was frequently inaccurate.

Objectives

The Director of Operations asked us to automate the aforementioned reports maintained by the sales, operations, and inventory teams. While we set out to automate these 3 reports, we identified an opportunity during our analysis to combine them together, since:

• The sales team needed visibility into scheduled production jobs by week for each production line
• The operations team needed insight into future bookings to layer on top of the already-scheduled jobs by week, for each production line
• The purchasing team's inventory drawdown model used the same underlying data as the sales/operations view

We decided to create a unified dashboard accessible to all three teams, consolidating the critical data into one centralized view.

Project Delivery

Analysis

As with all our projects, we began with a thorough analysis of the existing reports and workflows.

From a data standpoint, we examined the source datasets and identified key data transformations that embedded important business logic. We also reviewed the business processes in which these reports were used to ensure the dashboard design would support both decision-making and any related workflows such as email notifications.

Key data sources included:

• Scheduled jobs (from the production database)
• Customer orders (from the sales database)
• Customer bookings (from the CRM system)
• Production line capacities (via one-time manual input)

The main data challenge was mapping customer orders/bookings to production lines—information that wasn’t assigned until a job was scheduled.

From a process perspective, the reports were typically created weekly, with occasional ad hoc requests. To support more dynamic usage, we opted to update the dashboard daily. We also sat in and observed the newly instituted weekly SIOP meetings, which helped us identify the filters and drill-down functionality required for the dashboard to support those discussions effectively.

Data Load

The required data was spread across various ERP modules. We collaborated with the client’s IT team to replicate key data objects from the MAPICS ERP system into the data warehouse. This involved:

• Writing scheduled extract scripts to pull ERP tables into CSV files
• Dropping those files into Azure Blob Storage
• Using Azure Data Factory to orchestrate the data load into the data warehouse

Data Transformation

Once the data was loaded, we focused on the transformation logic necessary to capture key business logic.

1. Mapping Orders and Bookings to Production Lines

The customers orders and bookings data did not have any production lines assigned to them in the system, since this step was manually performed by the scheduler as part of the job release process, in which a product could be routed to one of multiple production lines depending on material type and height. While it was not in the scope of our project to automate the job release process, we needed to create a methdology that can estimate which production line an order should be assigned to. To solve this, we held several workshops with the scheduler to formalize a set of assignment rules and developed a precedence logic, for example:

• If Material = S and Height > 22' → assign to Line A
• If Material = S and Height ≤ 22' → assign to Line A or C (whichever is available first; default to A if tied)
• …and so on for various combinations of material and dimensions

Rather than hardcoding these rules, we stored them in a configuration table in the data warehouse to make future updates easier without changing the transformation code.

2. Inventory Drawdown Algorithm

We also automated the logic behind the purchasing team's Excel-based inventory projection. First, a components demand schedule was created by expanding all jobs and orders into their consituent components using their products' Bill of Materials—including recursive logic to expand any intermediate manufactured components. Then, a components supply schedule was determined by adding open purchase orders to on-hand inventory. Finally, the depletion scheduled was modeled by subtracting demand schedule from the supply schedule.

This logic was implemented in SQL and scheduled to run nightly. Given the processing time (~15 minutes), precomputing these tables was necessary to keep the dashboard responsive.

Dashboard Development

With clean, transformed data now available in the warehouse, we built the dashboard in Power BI using standard visuals that contains:

• A combined visual for the sales team and the operations team showing scheduled jobs and future bookings/orders against each line capacity, with drill-through capabilities to view underlying bookings, orders, and jobs
• A tabular report for the purchasing team mirroring their original Excel format to minimize the learning curve

Deployment & Iteration

We first reviewed the dashboard with the Director of Operations, then soft-launched it at a weekly SIOP meeting. All three teams—sales, operations, and purchasing—were given access.

Over the next three weeks, we gathered feedback and made iterative improvements, including: styling and layout tweaks, minor refinements to the order-to-line assignment logic, and enhancements to usability and filtering based on real-world use cases observed during meetings.

Outcome

This dashboard fully replaced the three Excel reports previously maintained separately by the sales, operations, and purchasing teams—providing a single, automated, and near real-time tool for the SIOP process. The Director of Operations adopted the dashboard as the main tool for his weekly SIOP meetings to aligning all three teams.

The impact was noticeable:

1. Saved 9 hours of manual reporting effort per week, equivalent to nearly 1/4 of a full-time employee
2. Replaced fragmented reports with a unified dashboard, avoiding version-control and other human errors
3. Nightly refresh schedule vastly improved upon the weekly frequency of the previous Excel reports—with no added labor required