There are two strategies in migrating your books to between accounting solutions in general:
- Choose a cut-off point and migrate your balance sheet and income statement per that day as an opening balance
- Migrate the complete history of the books from the old solution to the new one
These approaches apply even when the source is a spreadsheet (i.e. "manual") and LedgerSMB is the target. The second approach requires a full mapping of data from the source system to the target. This is usually quite involved and a reason not to use it. On the other hand, businesses are generally required to retain their records for a specific amount of time (multiple years, for audit purposes) and the second approach migrates the history-to-be-retained to the target system. With the first approach, a separate solution needs to be found to retain the required history.
Steps to migrate your opening balances into LedgerSMB
The goal of the migration is to "cut-over" accounting as per the chosen date. The general approach to that is to create an opening balance and opening income statement per that date and continue from there. Generally, a natural date to be chosen for such a cut-over is at the year-end when the income statement starts out clean for the year to come and an opening balance is created in the balance sheet.
The above works for relatively simple balance accounts such as a bank account or petty cash. When the account is a summary of underlying items, this approach is more problematic: in order to pay invoices, the system needs to know about them; migrating the total balance doesn't work well. The same applies to inventory where available inventory attributes to Cost Of Goods Sold (COGS) on a FIFO basis.
To construct a beginning balance with sufficient detail to support a complete cut-over, I have followed these steps:
- Set up your chart of accounts
- Migrate fixed assets
- Create the assets at their original value at the acquisition or depreciation start-date using their original depreciation scheme
- Run depreciation all at once on the day before migration resulting in the correct fixed asset account balance
- Migrate inventory
- Identify when remaining stock was acquired (in order to initialize COGS)
- Set up "Goods & Services > Parts" for stock to be migrated
- Create an import file listing per day which items in stock were acquired and at what price
- From the file above, exclude any stock from unpaid invoices; it will be added in the next step
- Note that for assemblies "acquisition date" should be interpreted as "production date" -- the date the assembly was entered into inventory
- Import the file into the system (how)
- Migrate open invoices
- Create customers and vendors at the bare minimum for which there are open invoices
- Create open sales and purchase invoices at their original creation dates
- Apply payments to the invoices on the original payment dates
- Migrate the remaining balance
- Subtract the balances in the accounts in the new system from the balances to be migrated
- Create a GL transaction on the date before the cut-over with the calculated differences
- Post the GL transaction
- Verify the migrated numbers by running a balance sheet on the day before cut-over
- The numbers should be the same as those in the original balance
- Freeze the migration balance to prevent hampering
- Go to "General Journal > Year End", tab "Close Period"
- Enter the date before the cut-over in "Close As-of"
- Click "Close Period"
Lets say that the cut-over date is "2020-01-01"; then the date to close the books at is "2019-12-31". After completing the procedure above, no transaction can be entered or modified before or at "2019-12-31", making this a truely static opening balance.