From Two Excel Files to a Scheduling System: A Home-Appliance ODM's Data Migration

Last autumn I met a mid-sized kitchen-appliance ODM: 12 parallel assembly lines, 10,000+ units per month, 150+ SKUs, running ODM production for seven or eight brands including Midea, Robam, Mingqi, Hisense and Supor.

Their ERP handled material masters, BOMs and purchase orders perfectly. But at the scheduling layer, things looked very different — the PMC manager kept three Excel files open: a summary sheet, a detail sheet, and a monthly schedule. Every day started with exporting new product codes from ERP, VLOOKUP-ing them into the schedule for validation, then filling quantities cell by cell across the date columns. When the tail numbers didn't add up, the fix was to hand-edit the formula: =800-59, =700-392.

ERP kept the books. But the shop-floor decision layer — PMC scheduling — was still broken. MRP's overnight batch run took 1–4 hours and couldn't handle rush orders, line changes, or parallel-line allocation in real time.

This article is about how we used PMClite to convert years of accumulated Excel data into a running scheduling system — without a single line of code, live the same day.

1. What the customer's raw data looked like

Two Excel files arrived:

File 1: BOM data exported from ERP (the "new-system BOM format"). 28 columns, 5 BOM levels. For one range hood, the structure was:

• Finished product (range hood) →
• Sub-assembly (chassis ground clip) → Purchased part (galvanized sheet)
• Sub-assembly (chassis back-plate assembly) → Sub-assembly (chassis back-plate) → Purchased part (galvanized sheet)
• Sub-assembly (chassis surround) → Sub-assembly (unpainted chassis surround) → Purchased part (cold-rolled sheet)

The part-type column had only two values: Made and Purchased. A made part could be either a finished product or a sub-assembly; a purchased part is what you actually buy.

File 2: Scheduling data with three sheets — Summary, Detail, and Monthly Schedule. Each row in the summary was "line + product code + model + customer + scheduled total", followed by dozens of date columns, with daily quantities chained by formulas.

2. Three core problems

Problem 1 — How do you separate products from materials?

In the BOM sheet, the "parent code" column is always a product; the "component code" column may be a material, or it may be another product (a sub-assembly). The rules were:

• Appears in the parent column → always a product
• Appears only in the component column and is type "Purchased" → material
• Appears in both columns → sub-assembly

The rules are simple, but with 150+ products, hundreds of materials, and dozens of BOM relationships, manual classification takes a full day.

Problem 2 — How do you calculate production plans?

In the schedule, one product often spans multiple lines. For one model: Line 1 had 1,518 units, Line 2 had 3,108 units across four segments — total 4,626. The system should generate 1 production plan (4,626 units) and 2 work orders (Line 1: 1,518, Line 2: 3,108).

Manually, a planner has to add it up row by row with a calculator.

Problem 3 — How do you determine dates?

Dates in the schedule are horizontal, one column per day. The work order's "planned start / end date" must be derived automatically — the first column with a non-zero quantity becomes the start, the last becomes the end. And Excel dates can be serial numbers, text, or formula chains.

3. The solution: a "data converter" is enough

The core idea: before importing, use a converter to "translate" the ERP data into the JSON format PMClite can read. It runs in the browser, locally — no data leaves the machine.

The customer's workflow: upload BOM Excel → upload schedule Excel → click Convert → download JSON → import into PMClite. Four steps.

Why build it as a standalone tool instead of a built-in feature?

Because every customer's Excel format is different: Digiwin, Kingdee, UFIDA — column names, hierarchy notation, and type codes all vary. Hard-coding it into the system means changing code for every new customer. A standalone tool lets conversion logic be customized per customer while the system itself stays stable.

4. What the customer saw after import

After converting and importing the JSON into PMClite:

• Dashboard refreshes instantly — material count, product count, BOM rows, plan count, work-order count, customer count, all live
• Product page shows the full product tree — 150+ products, each with its BOM depth, material count, and reference count. Sub-assemblies in blue, finished products in green
• Production page lists work orders across 12 lines — each with line name, quantity, priority, and kit status. Kit status is computed automatically: recursive multi-level BOM expansion, inventory comparison, shortage highlighted in red
• Gantt chart is generated automatically — each line's timeline visualized: green = on track, yellow = due soon, red = overdue
• Multi-level kit analysis — click any work order's "kit details" to see what materials it needs, how many are on hand, how many are missing, and whether missing items are already on order

The same 5-level BOM kit analysis takes about 20 minutes by hand in Excel.

Worth being explicit: PMClite does not replace ERP — it fills the "last mile" between ERP and the shop floor. ERP keeps the books; PMClite handles scheduling, kit checking, and material picking. The two are connected via JSON import/export, so the customer doesn't need to replace their system or sit through a months-long rollout.

5. The playbook behind this case

First: start from data migration, not feature demos.
The customer's biggest fear is "another system to fill in data." We flipped it — we import the Excel they already have. No need to re-enter 150 products, hundreds of materials, or dozens of BOM rows from scratch.

Second: make "conversion" a standalone tool, not a built-in feature.
Conversion logic is customized per customer; the system stays stable. This is the key to reusing it quickly with other customers.

Third: aggregation is core, not an add-on.
In the schedule, "one product spanning multiple lines" is the norm, not the exception. The data model must support "one product, multiple work orders" — this has to be designed in on day one.

Fourth: date handling needs multiple fallbacks.
Excel dates are all over the place — serial numbers, text dates, formula chains. The tool must recognize multiple formats and, when recognition fails, have a sensible default so the import never fails because of a date column.

6. Closing thoughts

This customer's story is not "another system went live." Their ERP is still running, their Excel is still around — but at the scheduling layer, "2 hours of manual number-crunching a day" became "30 seconds of automatic re-scheduling."

What PMClite does is simple: solve heavy problems in a lightweight way.

Multi-level BOM, kit checking, scheduling, material picking, barcode traceability — these are the pain points ERP doesn't cover, big APS is too heavy for, and Excel is too messy for. The middle ground is where PMClite lives.

Case data has been anonymized; no specific customer information is disclosed. PMClite is an independent product and has no commercial relationship with the ERP vendor mentioned in this article.