Sage 200 BOM Data Reconciliation
Client: Small spice and sauce producer
Published on: 22 September 2025
We developed a custom data reconciliation tool to rescue a flawed Sage 200 implementation for a specialty seasoning manufacturer, correcting thousands of complex Bill of Materials (BOM) entries and enabling accurate production planning.
The Challenge
a manufacturer of complex spice blends, had invested in Sage 200 to manage their production. However, a flawed initial implementation by a previous consultant left their Bill of Materials (BOM) data in disarray. Their complex recipes, which often use micro-quantities (e.g., 0.0001kg of a specific spice), were not handled correctly by Sage's standard import tools, resulting in thousands of missing or incorrect BOM entries. The client was left unable to trust their new system and could not use it for its primary purpose: production planning.
Our Solution
The client's historical BOM data was accurate but locked away in dozens of legacy Excel spreadsheets. A manual comparison would have taken months. Instead, we built a rapid, automated data reconciliation toolkit.
Data Export: We used Power Query to connect directly to the Sage 200 database and export the complete (but flawed) BOM data as a structured XML file.
Data Transformation: We built a Python script to ingest all the legacy Excel spreadsheets. The script parsed, cleaned, and transformed this data into a single, clean file that perfectly mirrored the Sage 200 XML format.
Comparison & Reporting: A second script then performed a high-speed comparison between the "as-is" data from Sage and the "should-be" data from the transformed Excel files. It generated a clear, actionable report detailing every discrepancy: completely missing BOMs, missing components within a BOM, and incorrect component quantities.
The Outcome
With the precise discrepancy report, the client was able to perform targeted, rapid corrections to their Sage 200 data in a matter of days, not months. They could finally trust their new ERP system, begin using it effectively for production planning, and unlock the full value of their investment.