Account reconciliation is the process of comparing two sets of records to ensure information is accurate and consistent between both sets. Reconciliation is extremely important, especially in your month-end-close process, if you are relying on tools/software outside your ERP to get financial information.
The EZLease Account Reconciliation template makes this process easy by automatically comparing two sets of journal entries and returning a list of variances. This template works for any record that you enter in EZLease (including lessee, lessor, and SBITA records), and for all accounting standards.
Using the template
The template includes four tabs (i.e. worksheets).
Below is a copy of the guidance found on the Instructions tab.
Step 1) Enter journals from EZLease
- Export Journal Entries from EZLease for the period you want to compare balances.
- Use "Spreadsheet" output and check "Single column".
- You can use either the Details (transaction by lease) or Totals formats.
- Copy/Paste entries into the "JEs from EZLease" tab.
Step 2) Enter journals from ERP
- Export Journal Entries from your ERP for the same period as the EZLease entries.
- Copy/Paste entries into the "JEs from ERP" tab in this template.
Step 3) View reconciliation summary at Variance Analysis
- This tab automatically lists total variances by account and lease-level variances.
- Use the data from this tab to post a correction journal to adjust balances in your ERP
Variances may exist because:
- An expected rent payment (or rent received, in the case of lessor leases) was not properly paid, causing the ERP balances to differ from the rent schedule entered in EZLease.
The journals exported from EZlease were not posted accurately in the ERP.
- Leases were modified in EZlease after the journals were booked in the ERP.
In rare cases, an accounting update in EZLease resulted in a retrospective accounting change for existing leases.
To reconcile your ERP with EZLease, book an adjustment entry into your Finance ERP for the same amount as the variances listed.
If the template isn't providing expected results, one of the following might be the reason:
- Journals from EZLease and the ERP were not exported for the same period.
- If you've updated the journal multiple times, Excel may not know to update the variance calculations. In this case, go to "Formulas" in the Excel Ribbon and select "Calculate Now" to force Excel to reperform the variance calculations.
- Over 100,000 rows of activity were entered in the "JEs from EZLease" or "JEs from ERP" tabs. This template only works up to 100,000 rows of activity.
- The order of the columns was changed in the "JEs from EZLease" or "JEs from ERP" tabs
- You can reorganize, rename, and add additional columns within limitation. The lease number, Title, Dr/CR, and Account Number columns must remain in A, D, E, and F respectively.
- Account numbers or account titles were used inconsistently between the the "JEs from EZLease" or "JEs from ERP" tabs.
- This template relies on either account numbers or account titles to compare activity between the two sets of journals. If this information is entered inconsistently, the template will display incorrect variances (e.g. the journals from EZlease contain account numbers, but the journals from the ERP do not include account numbers).
- You cannot sort/filter data on the "Variances Analysis" worksheet because the worksheet is locked. You must copy/paste the data into a new worksheet to manipulate the view.
- If the spreadsheet has a slow response, go to the Formulas tab, click Calculation options, and select "Manual." When you want to update the calculated values, go to the Formulas tab and click "Calculate Now."