Finding the bad number in a meat processor’s production costs

Context

At TOTVS, one of the clients I supported was a meat processing company. Their production costs came out of the ERP, and the operation used those numbers to make real business decisions. The numbers had drifted away from reality, and the standard reports had nothing useful to say about why.

The constraint

Production costing in an ERP is not one number sitting in one table. It is built from quantities and movements spread across several stock-related tables — raw materials consumed, work-in-progress, finished goods — each one feeding the cost calculation from a different angle. If any one of those quantities doesn’t reconcile with the others, the final cost comes out wrong, and the standard reports won’t tell you which table caused it.

The surface area I had to hold in view at once:

• More than 9 tables synced to track inputs and outputs.
• 3 modules: Procurement (input costs), Inventory (cost averaging) and Manufacturing (production and output costs).
• 500+ SKUs and 100+ production recipes.
• At least 7 unit operations: cutting and deboning, grinding and chopping, mixing and blending, emulsifying, forming, curing, and sterilization and packaging.

What I did

I mapped the entire data stream — the tables involved, and the triggers and functions that fed the cost calculation — and then went straight to the database.

• Wrote nested queries across the stock tables, joining them against each other to match quantities at each stage of the process: what should have moved from raw material to work-in-progress, what should have moved from work-in-progress to finished goods, and where those numbers stopped agreeing with each other.
• Worked from the ERP’s own logic, not around it. This wasn’t a simple SELECT * — it took understanding how the tables related to each other and the specific formulas and paths the ERP itself took to reach the result, which meant going back through the technical documentation.
• Simulated the whole process outside the ERP, in Excel — table connections, formulas and macros — to bring the calculation down to a level of abstraction I could actually read. With a full view of how each variable and formula moved the result, the root cause stopped being a guess.
• Corrected the data and recovered the integrity of the cost figures the client depended on for real business decisions.

Results

• Root cause identified and corrected, restoring the integrity of the cost data the client depended on.
• A reusable query repository. I kept the queries I’d built rather than throwing them away, so the next cost-integrity problem — and there would be one — wouldn’t start from a blank query editor.
• A handbook for the problem class, citing the documentation I used and recording step by step how to find and correct the data, the system configuration, and the rest of what the case taught me.

What I’d do differently

• I’d turn the reconciliation queries into a standing check. They ran once, as a diagnosis. The same joins scheduled against the stock tables would have caught the drift when it started instead of after the plant floor noticed.
• I’d write the handbook during the work, and complement it after the fact. Videos and a step-by-step guide on how to use the queries for diagnosis, what to change, and how to validate the result — so the next person to hit this can run the method, not just read about it.
• I’d build the Excel simulation first, not last. Translating the whole process into a more comprehensible level of abstraction — table connections, formulas and macros simulating the ERP outside the ERP — is what finally made the calculation legible. I reached for it after working the queries; starting there would have pointed them at the right tables sooner.

Stack

SQL (nested queries, multi-table joins) · TOTVS ERP (Procurement, Inventory, Manufacturing) · Excel (table connections, formulas, macros) · Technical documentation research · Cost accounting