Moving from Excel to a property ERP: what to migrate, and in what order

It is an ordering problem, not a loading problem

The instinct when leaving spreadsheets is to export everything and import everything. It fails for a reason that has nothing to do with file formats: a document depends on the master data it names. An instalment invoice needs a customer, a unit and an agreement to exist first; a contractor bill needs a contractor, a contract and a project; a receipt needs the invoice it settles. Load documents before their masters and every row is an orphan.

So the migration is a sequence, and the sequence is the same for almost every property business: the accounts, then the things the business is about, then the agreements between them, then the open documents, then the money — with a reconciliation after each layer. What follows is that sequence, learned from doing it, with the reconciliation that closes each step.

Layer 1 — the chart of accounts and the opening balances

Start with the chart of accounts. Map every account the workbooks use to an account in the new chart — and expect to find accounts that were really dimensions (“Contractor cost — Project A”), which become one account plus a project on the line. Then load the opening trial balance as at the cutover date, one journal, with Opening Balance Equity as the balancing side.

The fiscal calendar comes first even here: the accounting periods and fiscal years the opening balances and every later document will fall into must exist and be open before anything posts. A calendar corrected after the fact is one of the more painful migration errors, because every period-dependent posting has to be re-examined.

Layer 2 — what the business is about: projects, properties, units, parties

Next the masters that every document will name. In BuilderOne these are shared across modules, which is the first thing the migration has to respect: one contractor record whether the workbook called them a contractor, a supplier or a payee; one project whether Construction, Selling or Investor Management refers to it.

• Projects — one canonical project record per development, with the code the accounts will carry as a dimension.
• Properties and units — for selling: the unit inventory per project with its current state (available, reserved, booked, sold); for rental: buildings and units with their owners and ownership dates.
• Parties — customers, contractors, suppliers, owners, investors, employees. Deduplicate before loading: the same firm appearing under three spellings in three workbooks is the single most common source of a wrong balance after migration.
• Cost categories, tax settings, departments, pay components — the reference data the documents will classify against.

Layer 3 — the agreements between them

Now the contracts: what the parties have agreed with the business. Each is loaded as an executed document with its own terms, because the open documents in the next layer will be validated against it.

• Construction contracts — contractor, project, value, cost category, retention terms, advance terms; then the advances already paid as advance documents so the recovery balance is right.
• Selling agreements — customer, unit, agreed price, discount, payment plan; then the instalment schedule as it stands, so the system knows which lines are still to invoice.
• Rental agreements — tenant, unit, rent, term, deposit held; the deposit as its own record, because it is a liability, not rent.
• Investor positions — investor, project, principal contributed to date, profit allocated to date, as movements.
• Employee records and their project allocations, effective from cutover.

Layer 4 — the open documents, not the whole history

The decision that shapes the whole project: how much history to load. The honest default is open items only — unpaid invoices, unpaid contractor and vendor bills, unrecovered advances, held retention, held deposits — each as a real document dated as it was, with everything already settled represented by the opening balances alone. Full history is possible but costs weeks, and the workbooks rarely contain it at document grain.

• Customer invoices outstanding, by instalment, with due dates — so ageing is right from day one.
• Contractor and vendor bills unpaid, against their contracts and orders.
• Retention holds outstanding, by bill.
• Rent invoices outstanding, by tenancy.

Layer 5 — the money, and the cutover

Finally the cash and bank accounts, each linked to its ledger account with its opening balance, and the bank reconciliation brought to the cutover date. From this point every receipt, payment, payroll run and payout is entered in the new system only. Run the two side by side for one period if you must, but reconcile them daily; parallel running for longer is how the old workbook quietly becomes the system of record again.

What we learned doing it

• The source is never what it says it is. Workbooks carry synthetic rows, reversed signs and totals that were typed rather than summed. Profile the data before mapping it, and treat every total as a claim to verify.
• Load through the product’s own documents, not into its tables. A document created through the same path a user would take is validated by the same rules; a row inserted directly bypasses them and is the one that will not reconcile.
• Reconcile per layer, and stop when a layer does not. A control account that is 1 out at layer 1 will be 1 out forever, and finding it at layer 4 costs ten times as much.
• Keep the migration journal visibly separate. Opening Balance Equity, a migration user, a migration date — so anyone can later tell what was loaded from what was earned.
• Do not migrate the workbook’s categories; migrate the business’s. If a “project” in the workbook was really a phase, or a “supplier” really a contractor, the migration is where that gets fixed.