Excel
VBA
Power AutomateWhat it is
An Industry 4.0 project to integrate the company's ERP (TOTVS) with its shop-floor MES (ProLeiT). The idea was to make the two systems talk to each other, reducing manual entries and the discrepancies between what happened on the production floor and what was recorded in the ERP.
Challenge
Data volume was the biggest obstacle. TOTVS reports had millions of rows, had to be cross-referenced with other equally large reports, and then with data coming from the MES. The work involved reconciling inventory, finding where processes were failing, and identifying stock discrepancies, all in a corporate environment using the tools the company already had.
My role
- Analyzed and compared large volumes of data, cross-referencing ERP reports with MES data to validate consistency and reconcile inventory.
- Identified process failures and stock discrepancies from those comparisons.
- Built models and analyses in advanced Excel: Power Query to clean and combine datasets, Power Pivot to model the data, pivot tables, KPIs, Solver, and complex index-and-lookup formulas.
- Automated routines with VBA and Microsoft Power Automate, including automatically filling in information that used to be entered by hand.
- Read the code of a custom TOTVS screen, built by an outsourced vendor, to spot potential failures before it went live.
- Took part in meetings with the board to present findings and discuss the project's progress.
- Worked heavily in TOTVS day to day to extract and check data.
Result
Visibility into the gaps between production and the ERP, with analyses that supported the board's decisions on the integration's direction, and manual routines replaced by automations.
Tech Stack
- Advanced Excel (Power Query, Power Pivot, pivot tables, KPIs, Solver)
- VBA
- Microsoft Power Automate
- TOTVS (ERP)
- ProLeiT (MES)
Technical Highlights
- Cross-referencing reports with millions of rows between ERP and MES
- Data modeling with Power Query and Power Pivot on large datasets
- Inventory reconciliation and discrepancy detection by comparing systems
- VBA and Power Automate automations to eliminate manual data entry
- Code review of a custom ERP screen built by a third party
- Presenting technical findings to the board