Best Practices – How To Import Historical Monthly or Yearly Trial Balances to Cetec ERP System
If you’ve been on an accounting ERP system for many years, you can run a Balance Sheet or Income Statement “as of” any given month in any given year in the past. To convert your financial data to Cetec ERP and be able to do the same kind of historical financial reporting and financials statement generation “as of” any given month in any given year after migration, you’ll need to plan to run a Trial Balance for the month-end for each of the 12 months in each of the previous years you’ve used your previous system.
You have the option to import those Trial Balances into your new Cetec ERP system at the time of cutover.
But first, please note that you can’t simply post the ending Trial Balances of each month, month after month, because the next month’s ending “Trial Balance” is the result of that month’s previous GL activity (i.e. debits and credits).
When importing GL data into Cetec ERP, you simply enter the Trial Balance dollar amounts as GL entries, i.e. dollar values to debit and/or credit in x GL account number.
For example, let’s say you had a balance of $9,693.13 in Account 1010 on 2010-01-31, and a Trial balance of $10,693.13 in Account 1010 on 2010-02-28. The data you would need to provide us is the delta between those two accounts.
So, you would need to provide a debit dollar amount of $9,693.13 on 2010-01-31, and then the delta debit dollar value of $1,000.00 on 2010-02-28, so that the resulting balance on 2010-02-28 would be $10,693.13.
On the other hand, if we were provided with a debit dollar value of $9,693.13 on 2010-01-31 and a debit dollar value of $10,693.13 on 2010-02-28, and we imported both of those as entries, the resulting Trial Balance as of 2010-02-28 would be the sum of those two debit values = $20,386.26 (incorrect!)
A simple solution:
There’s a straightforward solution to historical monthly Trial Balance data conversions. You’ll create each month’s Trial Balance GL entry and import/record that entry. Then, on the first and last day of the month, you’ll reverse it via a click of a button on the GL entry edit screen. To do so, you’ll follow these steps:
- The first entry will import/record on the first of that month. That first entry will be the normal, correct historical Trial Balance debit or credit amount per account.
- Edited from the first GL entry edit screen, the reversal entry will post on the last day of that month (via a click of a button on the GL entry edit screen). That reversing entry will simply be a reversal (debits/credits reversed) of the first entry, dated on the last day of that same month.
Rinse and repeat for each historical Trial Balance month-end you want to import.
Finally, SUSPEND making a reversal entry for the final month’s Trial Balance import posted on the first of that month, prior to “go-live” (i.e. the day before individual G/L entries’ activity will be responsible for automatically generating your ongoing balances in each account).
Going forward, if you want to run any historical Trial Balance reports in Cetec ERP, you would need to run the Trial Balance report on any day in that month except the last day of the month.
A complicated solution:
There is another possible solution, which we do not recommend using. This would be to, in the GL debit/credit $ history you provide to your data import team, begin with the starting Trial Balances (up from $0.00) in your very first historical GL import (let’s call this “month 1 Trial Balance”) but then in each subsequent import, run a delta between subsequent month 2’s balances and month 1’s balances, and so on to most recent closed period end prior to Go Live date. It is that delta which must be imported/posted as a debit or credit GL transaction into Cetec ERP in order to achieve the month 2 balance you want to see in each GL account at month 2 end, and so on month to month thereafter.
This can be a difficult problem to model in your .csv or .xls spreadsheets; you would have to start with month 1’s normal balances, but in every subsequent month, run a “VLOOKUP” formula in Excel to locate the previous month’s GL entry, the account to post to, and that accounts’ previous monthly balance. Then use the result of that VLOOKUP to subtract against the present month’s account balance in order to derive the delta needed to post in that month to ultimately result in the GL account balance you intend for that month end.
If you do run historical Trial Balance report(s) on the last day of the month for any historical month, the report will only reflect balances of $0.00.
Importing historical Trial Balances into Cetec ERP is a powerful way to maintain continuity in your financial reporting and ensure access to accurate, backdated financials. While it may seem complex at first, following the recommended best practice keeps the process clean, auditable, and much easier to manage long-term. Avoiding the more complicated delta calculation method will save you time and reduce potential for error. With careful preparation and attention to the import guidelines, your transition to Cetec ERP can preserve the full integrity of your financial history.