Moving from spreadsheets to an ERP: a step-by-step checklist

Before you start

Spreadsheets are not the enemy. They are flexible, everyone knows how to use them, and they probably got your company to where it is. The trouble starts when several people depend on the same files, the files depend on each other, and only one person understands how it all fits together.

Moving to an ERP replaces that web of files with one system where sales, purchasing, stock, people and accounting share the same records. If you are not sure that is what you need yet, read what an ERP is first.

The ten steps below are grouped into five stages. They assume you have already chosen a system, but most of the work is on your own data, so you can start the first stages before you sign anything. For an overview of what changes when spreadsheets go, see replacing spreadsheets.

Stage 1: Know what you have and what moves

Step 1: List every spreadsheet that runs the business. Walk through the company and write down every file people rely on. For each one, note:

• what it tracks (customers, price lists, stock, orders, commissions, leave, payroll inputs)
• who owns it and who updates it
• which other files it feeds or reads from
• how often it changes

You will find files nobody told you about. That is the point of the exercise.

Step 2: Decide what moves, and when. You do not have to move everything at once. Split the list into three groups:

1. Moves at go-live. Usually the chart of accounts, customers, suppliers, items, open invoices and bills, bank balances and stock.
2. Moves later. Processes you will switch on once the first set is running, such as payroll or leave.
3. Stays as a reference. Old analysis and history you want to keep but do not need in the new system.

Then pick a go-live date. The start of a month is simplest, and the start of a quarter or financial year is cleaner still, because your opening balances line up with a period you already report on. Check with your accountant, as reporting and tax deadlines differ by country.

Stage 2: Clean your data and map your accounts

Step 3: Clean your master data. Master data is the information you reuse on every transaction: customers, suppliers, items and employees. Clean it before you import it, not after.

• Remove duplicates. "ABC Trading", "A.B.C. Trading Sdn Bhd" and "ABC Trading (old)" should become one customer.
• Fill the gaps. Tax registration numbers, billing addresses, payment terms, default currency.
• Make codes consistent. One item code format, one unit of measure per item, one naming style.
• Retire the inactive. If a supplier has not been used in two years, decide whether it needs to come across at all.
• Agree an owner. Someone signs off each list before import.

This is usually the slowest step. It is also the one that pays back for longest.

Step 4: Map your chart of accounts. Decide whether you keep your current chart of accounts or take the chance to tidy it. Either way, write a mapping table from every old account to its new account.

Keep the table. Your accountant will want it when comparing this year with last year.

Stage 3: Prepare opening balances and history

Step 5: Prepare opening balances. Opening balances are the figures your new system starts from on go-live day. Take them from your closed trial balance on the day before go-live. You will need:

• A trial balance as at the cut-over date, with total debits equal to total credits.
• Open customer invoices, one line per invoice, not just a total per customer.
• Open supplier bills, also one line per bill.
• Bank balances, agreed to bank statements on the same date.
• Stock quantities and values, per item and per location, ideally from a stock count.

Then check that the detail adds up to the totals. Say your trial balance shows receivables of MYR 7,000. Your open invoices are MYR 4,200, MYR 1,850 and MYR 950. They add up to MYR 7,000, so the detail and the control account agree. If they do not, find the difference before you import, not after.

Foreign-currency balances need a little more care. An invoice for USD 2,000 should come across in USD, with the exchange rate you booked it at, so that later payments and revaluations work correctly. Our guide to multi-currency accounting explains why.

Step 6: Decide how much history to bring. It is tempting to import every transaction you have ever made. Usually you should not. Old transactions in a new system mean more mapping, more checking and more chances of error.

A common approach is to bring balances and open items only, then keep the old files as a read-only archive for look-ups and audits. If you want comparisons with last year in the new reports, ask whether you can load monthly totals per account instead of every transaction.

Keep the archive for as long as your local record-keeping rules require. This varies by country, so check.

Stage 4: Test, then run both systems

Step 7: Test with a small sample. Before the full import, load a sample: ten customers, ten items, a handful of open invoices. Then check:

• Did every field land in the right place?
• Do names, tax numbers and addresses look right on a printed invoice?
• Do the sample balances match the source spreadsheet?

Fix the template, not the data in the new system. Then run the full import and repeat the checks on totals.

Step 8: Run both systems for one period. For the first month, keep your old spreadsheets running alongside the new system. Enter real transactions in the new system and compare at month end:

• Do sales and purchase totals match?
• Do bank balances match the bank statement?
• Does stock on hand match a spot count?

One parallel month is usually enough to build confidence. Longer than that and people start to treat the old files as the real ones. Our month-end close checklist gives you a structure for that first close.

Stage 5: Train your team and retire the spreadsheets

Step 9: Train people on their own tasks. Train each team on the screens they will use every day, with their own data, not a general tour. A sales coordinator needs quotations, orders and invoices. A storekeeper needs receipts, transfers and counts. Write one-page notes for the tasks people do only once a month.

Step 10: Retire the spreadsheets. When the first close in the new system matches, make the old spreadsheets read-only and move them to an archive folder. Tell everyone the date. If a file stays editable, someone will keep updating it, and you will have two versions of the truth again.

Then review after three months. List anything still being done outside the system and decide whether it should move in.

Where Nyra fits

Nyra's import wizard reads CSV and XLSX files and gives you a template with the right headers for each list. It shows every row, flags the ones that need fixing, and saves nothing until you confirm. You can import customers, suppliers, items, employees, the chart of accounts and journal entries this way, and enter opening balances as a dated journal. Documents can be in any currency, and invoices, credit memos and goods receipts post as journal entries from day one. For a week-by-week switching plan and a data checklist, get the free e-book, or book a demo to see the import step with your own files.