Reconciliation report for open invoices

 

Reconciliation report for open invoices is a tool for reconciling accounts receivable and accounts payable. The report compares the daily balances of accounts receivable and accounts payable to their corresponding bookkeeping accounts. If there is a difference, the report shows which receipts or transactions cause the difference. This way the needed fixes are easily pointed out.

The report is found in Accounting > Reconciliation tools > Reconciliation report for open invoices.

 

View description

 

Buttons in the upper section of the view

• Search button searches for the report in accordance with given search criteria.
• Search default dates button sets the search dates to the first day of the previous month till the current date.
• Search in financial year button searches data from the current open financial year. 
• Update reporting database button has been removed in release version v69. The view updates itself when the page is loading and the button is not needed anymore.

 

Search criteria

• Time period fields are used to set the report’s time period. Note! The report may include difference if the selected period is divided into two financial years. More detailed information can be found at the bottom of this article.
• Ledger has two selections to choose from:

  • Sales - account 1700 is taken into account as default. The accounts that are taken into the search options can be modified in Management > Accounting info > Chart of accounts > Edit receivable and payable accounts.

  • Purchase - account 2880 is taken into account as default. The accounts that are taken into the search options can be modified in Management > Accounting info > Chart of accounts > Edit receivable and payable accounts.

• Status is used to choose if Business transactions or All transactions are shown on the report.
• Ledger accounts shows which accounts are selected in Management > Accounting info > Chart of accounts > Edit receivable and payable accounts for sales and purchases.

 

Report functions

When differences occur, their causes can be sought by the functions above the search results:

• Search open invoices button shows open invoices of the active row (i.e. date) in Receipt search view.
• Accounts and transactions button leads to Accounts and transactions view of the active row (i.e. date).
• Bank statement search button leads to Bank statements and reference payments view of the active row (i.e. date).
• Export button exports the report to Excel or HTML

Only with sales:

• Sales transactions button leads to Sales transaction report view of the active row (i.e. date).
• Reference payment report button leads to Reference payment report view of the active row (i.e. date).

Only with purchases:

• Outgoing transactions button leads to Outgoing payments transaction report view of the active row (i.e. date).

 

Search results and tracking of differences

When search criteria have been set and the report is created with Search button, the search results will include a row for every single date within the searched time period. When the Difference column of a row shows a value of 0,00, there is no difference between open invoices and accounting balances (row is white). If a difference occurs on one day, Change column of this day will show the amount of difference (row is yellow).

The difference is shown also in Difference column of the day that the difference has occurred as well as with all sequential rows. A negative value in Difference column means that the accounting balance is greater than the sum of open invoices. Correspondingly, a positive value means that the sum of open invoices is greater than the accounting balance.

Change column shows if the difference has remained the same or if it has had changes. Therefore, this column shows how much the difference has possibly changed and if the difference has been decreasing or increasing. In comparison, the value in Difference column is shown as cumulative.

When a difference occurs on the report, it is recommended to check if the difference becomes matched at a later date: this means that there is the same sum with different sign in Difference column of a later date. If the difference gets eliminated this way and the reason for the difference is known, additional information about the temporary difference can be entered to Notes columns of the rows in question.

The date on which the difference emerges (as well as the following dates) is highlighted in red color. Because a difference arisen on one day stands also on the following days, the reconciliation must be done in chronological order.

After a difference has been fixed, for example, by making changes to accounting entries, the report updates itself when the page is loading. When Reconciliation report for open invoices is searched again, the fixed difference has disappeared from the report. Now the reconciliation can be continued.

 

Reconciliation of accounts receivable

When reconciling accounts receivable, Sales is chosen from the Type drop-down menu. Because there should be no unfinished transactions in the period to be reconciled, Business transactions is chosen as Status. After the time period of reconciliation has been set, it is time to click Search button.

• Reference payment report can be used for checking reference payments for the chosen date as well as related invoices and sums. Reference payment report also shows if there are reference payments that are unallocated and therefore causing differences.
• Sales transaction report shows which invoices have a sales transaction on the chosen date. Because an invoice is in Paid status only if a reference payment has been allocated to it, the sum of invoices in Paid status should equal to reference payment report’s sum. There might be a difference caused by, for example, Marked paid For this reason, it is recommended to check these statuses (with e.g. credit notes).
• Bank statement search can be used to check if there are deposits without reference numbers on the day of the chosen row. If there are payments without reference numbers for sales invoices, are the payment transactions entered to accounts receivable in the accounting page of the bank statement? Has the invoice in question been marked to Marked paid status?
• Accounts and transactions report shows the entries made to accounts receivable on the day of the active row. Are there incorrect postings?
• Searching open invoices is used to view the open invoices in the sales ledger on the day of the active row. If an invoice is in the sales ledger but it hasn’t been entered to account 1700, this might cause a difference.

 

Reconciliation of accounts payable

When reconciling accounts payable, Purchases is chosen from the Type drop-down menu. Because there should be no unfinished transactions in the period to be reconciled, Business transactions is chosen as Status. After the time period of reconciliation has been set, it is time to click Search button.

• Outgoing payments transaction report shows which invoices have been set to Paid or Marked paid status on the day of the active row.
• Bank statement search shows the payments that have been withdrawn from the bank account on the day of the active row.
• Accounts and transactions report shows the postings on the day of the active row.
• Searching open invoices shows open invoices in the purchase ledger on the day of the active row.

Possible reasons for differences:

• Cash discounts: an invoice has been set as wholly or partially paid, but the corresponding debt hasn’t been adjusted in accounting.
• Exchange date differences: exchange date difference has been allocated to an invoice, but the corresponding debt hasn’t been adjusted in accounting.
• An invoice has been set to Marked paid status, but the corresponding debt hasn’t been adjusted in accounting.
• Withdrawals on bank statements have been posted to accounts payable, but there are no similar invoices as debt that would match the bank statement postings. This kind of situation might occur, for example, when the invoice date is later than payment date. These invoices can be sought in Receipt search if the search criteria are set so that the accounting date is defined to start from the first day of the following month and the payment date is defined until the last day of the month being reconciled.
• If the due date of salaries is in the month to be reconciled but the salaries have not yet been paid, this creates a difference between open invoices and accounting. The difference is the sum of the unpaid salaries because the salaries are not visible in open invoices.

 

Differences caused by dates

Report might include differences also when the invoice date of an invoice is set to the previous month from the accounting (receipt) date (and the accounting date is not the first day of the month). For example, if invoice date is 15.2. and accounting date is 5.4., there will be a difference on the report. The difference is caused by the following reason: When the accounting date of an invoice is postponed into the future, the invoice will be included to the report’s Open invoices column always on the first day of the month (in this case 1.4.). As the actual accounting date is not 1.4. (since it is defined to 5.4.), the invoice will be included to the sum of Accounting column not until 5.4. Between these dates, there will be a temporary difference until it is matched on the accounting date. These kinds of problems can be avoided by changing the accounting (receipt) date to the first day of the month.

There is also going to be a difference on the report when an invoice has been paid before it would be shown in the sum of Open invoices column. An example of this kind of situation is the following: Invoice date is set to 1.1. However, the accounting (receipt) date has been changed to, for example, 28.2. By the report’s logic, the invoice would appear in the sum of Open invoices column on 1.2. (the first day of the accounting date month) and in the sum of Accounting column on 28.2. If the invoice has been paid already in January, it won’t appear in Open invoices column on 1.2. (because it is not open anymore on that day); this means that it will be completely out from the balance of open invoices. However, the invoice will appear in Accounting column balance in 28.2.

It is also important to note that the report may include difference if the selected period is divided into two financial years. The financial year is a calendar year in the following example:

• The report is searched for 1.12.2020-31.1.2021.
• If the calendar year of 2020 is closed, the automatically generated receipt of opening balances includes an entry for 1.1.2021.
• In this case the report will include difference of the opening balance from 1.1.2021 onwards, even though there is no difference in reality.
• Because of this, the report has to be searched separately for December (1.12.2020-31.12.2020) and separately for January (1.1.2021-31.1.2021) in order to see the real situation of possible differences.