03 / INVENTORY RECONCILIATION
Inventory reconciliation for telecom modernization
A modernization design can depend on equipment already installed at a site. I developed an Excel/VBA workflow that reconciles live inventory CSV exports with the MasterTable design, making differences in reuse, material recovery, and scrap visible before installation.
My contribution: inventory extraction, an editable model mapping catalog, a site-by-equipment quantity matrix, and visual design comparisons. Decision it supports: requesting a design correction or additional equipment, and reviewing the scope of recovery and scrap.
Approximate times from my operational experience. Follow-up corrections and equipment requests happen after the batch review.
Technical approach · mapping, serial counts, and visual comparisons
The initial tool queried one site at a time from the regional inventory and MasterTable. The current version takes a vertical list of site IDs, deduplicates serial numbers within each site, and maps full inventory descriptions to the short model names used in the design.
An editable Mapeo sheet maintains those equivalences. Extract_INV becomes a quantity matrix with one row per site and one column per model. Models from recognized hardware families that have no mapping appear under Others, with their descriptions retained for review.
Excel lookup formulas bring in the embedded MasterTable data, and conditional formatting highlights quantity differences. Any difference needs attention: missing reuse equipment can block installation, while recovery and scrap differences affect material handling and costs. In my usual batches, about 8–10 of 100 sites may need attention; that is an approximate operational observation.
I kept the workflow in Excel because the inputs and the team's comparison process already use spreadsheets. VBA handles extraction and normalization; the workbook provides a familiar review interface.
Inspect a modernization inventory batch
| Site ID | Region | Review scope |
|---|
Extract_INV: one row per site, one column per model. Counts are calculated after serial deduplication and model mapping. This view uses the same filters as the comparison.
Mapeo: full inventory descriptions map to the short names used in MasterTable. Two sample descriptions converge on RRU5904. Unmapped equipment stays visible under Others.
| Type | Short name | Inventory description (fictional) |
|---|
The browser calculates this synthetic example; it does not execute VBA or read your files. The actual workflow runs locally in Excel. All quantity differences are flagged, including surplus equipment and scrap. Customer inventory, MasterTable files, and workbooks are not published.