Bank Reconciliation Overview
A bank reconciliation is the process of reconciling a bank account balance against the balance reported by a financial institution in the most recent online bank statement. It is essential for businesses to ensure that there are no differences between the two figures, and if there are discrepancies that appropriate rectification is performed. A bank reconciliation statement is prepared by a financial institution which sets out the entries which have caused the difference between the two balances.
The SapphireOne Bank Reconciliation function allows users to compare transactions in SapphireOne against their online banking statements. SapphireOne databases are updated in real time allowing businesses to run online bank reconciliations with concurrent data.
There are a number of ways for the user to reconcile bank accounts within SapphireOne, these include:
- Using an OFX file supplied by your bank, outlined below.
- Using SapphireOne’s Bank Sync functionality.
- Using the Bank Reconciliation function. (Manual Reconciliation)
When the Bank Reconciliation function is first selected a pop up will be displayed listing all the users bank accounts currently recorded within SapphireOne. This allows users to select the bank account that they wish to reconcile. If the bank account selected contains multiple accounts, such as multi currency Foreign Exchange (FX) accounts, users will be redirected to a second interface where they must select the relevant bank account.
All inquiry screens have the option to Customise the list of items that are displayed on page. The Custom Inquiry Option allows you to select the data fields that are displayed, and the order in which the columns of data are displayed across the screen. While a list of clients has been used to demonstrate below, any type of inquiry may be customised within SapphireOne.
The list below is the Standard Inquiry list.
When the ‘Swap to Custom Inquiry‘ button in the lower left hand corner of the screen is selected SapphireOne will display the default Custom Screen as seen below. This Custom List screen gives the user user two main options when organising the order that the data is displayed:
- To add or remove the data field columns that are displayed on screen. To add or remove a column, go to the ‘Setup’ button in the bottom right corner of the screen. Within the Setup screen you can right click on the column lines and select from Delete, Move Left or Move Right. Further information on the Setup screen is documented below.
- To change the order in which the data field Columns are displayed. To change the order of the columns, go to the ‘Setup’ button in the bottom right corner of the screen. Within the Setup screen you can right click on the column lines and select from Delete, Move Left or Move Right. Further information on the Setup screen is documented below.
Once the user has customised the Custom Inquiry screen, the screen view will become unique to the user and each time the user logs in they will be shown the same custom view. The user also has the option to select and save unlimited custom views. This can be done in the ‘Setup’ screen as documented below.
The Inquiry screen below is the initial Custom Inquiry screen which may be modified by the user.
Custom Inquiry Screen ‘Setup’ Function
To commence setting up your Customised List screen, select the Setup button as seen above. The Setup screen will be displayed as seen below.
From this screen the user is able to add and remove columns of data as required. Almost every field within the SapphireOne data tables is able to be included in the Custom List screen if the user is authorised.
There are a number of menu items across the top of the screen. These are as follows:
- Reset to Default – Selecting this button will set the display back to default as determined by SapphireOne. When a new data field is selected SapphireOne will add it to the right hand side of the columns of data. You can then click on the field heading and drag it to wherever you want across the screen.
- Show Related Record – You can also add related fields from other tables. Clicking on the
checkbox at the top of the screen, allows you to choose a related record and a mini view of the related record for the chosen record will be displayed for the currently selected record. You will need to select the fields displayed. - Favourite Set – You can use this option at the top left of the screen to save a custom created screen for later use. For example, you can have a simplistic Inquiry screen for general use, and a more complex screen view when auditing certain information.
Bank Reconciliation Page
Reconciliation Date Range Area
On the Bank Reconciliation page the user is provided with a number of options. One of these being the ‘Date Range‘ that has two options ‘From’ and ‘To’ in the lower left-hand corner of the page.
As the words are underlined, selecting them will display a date picking calendar for the user to select the dates from. The user can select a date ‘From‘ and ‘To‘ if they want to narrow the data displayed. SapphireOne will then display transactions in the reconciliation page that are between these two dates. For example, for December from 01/12/2034 to the 31/12/2034.
SapphireOne’s implementation of both Dictation (MacOS) and Speech to Text (Windows) is a game changer for data entry. Any data entry field you can type into using a keyboard can use this feature.
You can also use this functionality on all inquiry screens within SapphireOne. For example, the user is in a Contact within a Client. They may have made a phone call to that particular client using the Softphone technology. Consequently, this will automatically date and time stamp both the contact and the phone number you called.
As soon as the call is completed, the user has the ability to use Speech to Text (Windows) or Dictation (MacOS). They can dictate into the memo field of the CRM contact, adding as much description as they require.
Dictation and Speech to Text Procedure
This functionality applies across every single Data Entry screen. For example, the SapphireOne user is entering a Vendor Invoice (VI). Additionally, you may want to add a memo within the Vendor Invoice (VI). The user can utilise the tool and simply dictate the memo.
Another example is if the user wants to add additional lines to the General Ledger account, within each General Ledger account. When the user is completing data entry using a General Ledger, there’s always a unique memo field for each GL account line. The user can then utilise this feature to quickly add the information they need to add. Alternatively, you may have an interview with an employee. After the interview process is completed, the user can then make notes about the employee.
Speech to Text or Dictation is an extremely powerful tool. It is something that we at SapphireOne, as well as our clients, utilise daily. It is a massive timesaver.
Transactions Area
The Opening Bank Balance is viewable on the top right hand corner of the Bank Reconciliation Page, above the Transactions Area.
The Transactions Area will list all transactions that have occurred under the selected bank account, with the relevant data displayed under the following headings:
- ID – This column will display the Client or Vendor ID for which the transaction relates to where applicable.
- Group – This will display the group number that the transaction has been labelled with. This would typically consist of a batch group number which is asked for when the user is initially saving the transaction. A number of 0 or zero, indicates that the transaction is not grouped.
- Date In – This will display the date in which the transaction occurred.
- Type – This will display the type of transaction such as Cash Payment (MP), Cash Receipt (MR), Vendor Payment (VP) or Client Receipt (CR).
- Period – This will display the period when the transaction occurred.
- Status – The status of the transaction will be listed here. Ent and Yes being the most common, Entered correctly and Yes it has been posted.
- Internal Ref – This will display the internal reference in SapphireOne for the transaction.
- External Ref – This will display the external reference for the transaction.
- Sequence – This will display the sequence number for the transaction.
- Withdrawal – This will display how much was withdrawn from the selected bank account for this transaction.
- Deposit – This will display how much was deposited into the selected bank for this transaction.
- Unnamed Column – When a transaction is marked, it will either be marked with a ✓ for Apple or a + for Windows. This will indicate that the transaction is ready for reconciliation. If a U is displayed, the transaction cannot be reconciled because the transaction is un-posted and will need to be posted before the reconciliation may be processed.
Transaction Details Area
The Transactions Details area displays the following additional details for the currently selected or highlighted transaction.
- Due – This will display the date the transaction is due for the selected transaction.
- Amount – This will display the dollar value amount of the transaction.
- Tax – This will display the tax applied to the selected transaction.
- Total – The total – the total amount minus tax.
- FX (Foreign Currency) – If the transaction is in a foreign currency, the currency type will be listed here.
- Conv – If the transaction is in a foreign currency, the conversion rate will be listed here.
- Discount – This will display the percentage discount of the selected transaction.
- Amount – This will display the total dollar value of the transaction.
- Tax – This will display the tax applied to the selected transaction.
- Total – This will display the total dollar value of the transaction minus tax.
- Batch Total – This will display the total dollar value of the selected transactions. If this value is in brackets this indicates that the total dollar value of the selected transactions is a withdrawal or negative amount.
- Closing Bank Balance – The closing bank balance is the opening bank balance minus the batch total.
- Actual Closing Bank Balance To – This will display the Closing Bank Balance minus the batch total and tax.
- Difference – This will display the closing bank balance plus the value entered into the actual closing bank balance into box.
- Available – This will display the available balance in the selected bank account plus the selected transactions.
- Overdraft – If an overdraft value has been entered into SapphireOne General Ledger bank account, the value of the overdraft facility will be shown. This value will be applied to the closing bank total and the result will be displayed here.
Controls Area
The Controls Area at the bottom of the Bank Reconciliation Page provides the user with options to enhance the efficiency of bank reconciliations. Additionally, the buttons within the Controls Area allows the user to cancel or process the current online bank reconciliation.
- Mark All – Selecting this option will mark all of the transactions.
- Unmark All – Selecting this option will unmark all of the marked transactions.
- List To Date – This button can be used once two or more transactions are marked and will open a pop-up where the user can put in date parameters to sort marked transactions.
- Look – This button can be used when a transaction is selected and when clicked will open a pop-up which will display details about the client or vendor, transaction and all related data.
- Save – This will save a record of all transactions that are marked. Use the Load button below to re load the bank reconciliation.
- Load – This will load a record of marked transactions that have been previously saved using the Save button.
- Collapse – This will collapse any collapsible transactions into a single transaction. (Default is collapsed)
- Expand – If any deposits are made that have receivables from multiple clients are made, they will be indicated in the ID field by the Σ symbol as a prefix. The number immediately following the Σ symbols indicates the number of deposits that are collapsed together. When one of these deposits is selected, the button will become active. You may now individually select these deposits for reconciliation although when the button is selected, SapphireOne will automatically select them for you.
- Expand All – If this button is selected, SapphireOne will expand all lines that commence with the Σ symbol. In the case of a large number of deposits this will present a large list of transactions
- Load File – This button will open a pop-up where users can select a CSV file from their computer containing transactions.
- Find – Clicking the find button will open the Find pop-up where users can search transactions listed by date in, internal reference or amount.
- Reload – If transactions have been expanded or marked in the Bank Reconciliation interface, clicking reload will remove any expands or marks applied and reload the original transactions list.
- Open – The open button can be used when at least one transaction is highlighted. This will open an interface where the user can see the selected transactions and apply different metrics to sort transactions by using the Custom Inquiry function.
- Export – Clicking the export function will create a CSV file tabulating the data from the marked transactions.
- Cancel – Clicking the cancel button will remove any changes made to the Bank Reconciliation page and close the interface.
- Process – Clicking the Process button will open the Posting pop-up where users can begin the posting procedure. No further modification will be permitted on the selected transactions. The user can select from the options: Print Reconciliation, Print Reconciliation + Outstanding Reports, Process Later and Process in Background. Once the button is selected the user will also be given the option of attaching a PDF copy, as an example, of the online reconciliation which will store a copy of this in the Historical Bank Reconciliation list.
OFX Bank Reconciliation Options
Another way that the Bank Reconciliation function may now be completed is by using a bank supplied OFX file. Once the Bank Reconciliation function is open, the way to complete it would be to select the “Load File” button at the bottom of the page. SapphireOne will then display a standard search pop up allowing the user to select the relevant OFX file.
Do not modify these OFX files by opening and saving them with an editing software. While the information within them may be viewed using some software programs, they are encrypted and should not be re-saved as the software will alter the file and make it unusable when attempting to import it into the SapphireOne.
All Banks will now send a standard OFX file to their customers provided they have electronic banking setup. It should be noted that from bank to bank there are some differences when the user is asking for the OFX file. Two examples are as follows. ANZ will offer a list of options so select the MYOB(OFX) option. Wespac will ask for a date CC, YY, MM, DD, Century,Year, Month and Day. Other banks will be different so the user will have to ask the Bank or SapphireOne support for additional details.
When SapphireOne reads the OFX file, the criteria for a successful link being confirmed from the OFX file for your bank account transactions in SapphireOne is a follows:
- A simple checking of the $ amount in each transaction. It recognises a deposit or a withdrawal as separate amounts even if they are the same $ value e.g $200.00 or $-200.00.
- If there is more than one transaction with the same $ amount SapphireOne will then check for a different date.
If SapphireOne can find a match it will populate the Auto Bank Reconciliation screen. Once the Actual Closing Bank Balance To is correct, SapphireOne will display a value of zero in the Difference field.
If SapphireOne cannot find a match, you have the option of matching the transactions manually with un-cleared transactions in the Bank Reconciliation, adding them, or skipping them altogether.
The columns in the screen shot above from Description to Deposit, are drawn from the Banks OFX file.
The columns Link Seq to Link Amt are the transactions within SapphireOne that it is reconciling with.
The list will either comprise all transactions, or those transactions within the date range selected.
If there are any errors, please refer to the Bank Reconciliation Errors section at the bottom of this page.
If there are any errors, please proceed to the next item in this article where the procedure for a Manual Bank Reconciliation has been documented as it may helpful when troubleshooting is to be done.
Manual Reconciliation
The screenshot above shows the local currency account and contains all posted and un-posted activities awaiting reconciliation for the bank account selected. Compare the Bank Reconciliation Page to your online bank. Mark off the required items by using the mouse. SapphireOne will display a ✓ for Apple or + Windows until the closing balances are aligned.
It is essential to enter all Bank charges, interest, and other such items through the Transaction Entry Page to maintain the Closing Bank Balance figure as true to your bank statements. The printing of an Audit Trail from this page only prints the marked ( ✓ Mac or a + PC) items and not a full list of the items to be posted.
We have selected, the button for the screenshot above.
- Reconcile indicator – When the transaction has been posted without error and reconciliation is selected, here, SapphireOne will display a Mac and a + symbol PC in the last column.
- Un-posted transactions – These will display the letter U in the last column and transactions with an error in them will as normal have err in the Status column.
- Grouped Transactions – Any ID’s that have the Σ symbol in them followed by a number indicate that the transaction is grouped. The number immediately after indicates the number of transactions in the group.
- Expand Buttons – These are used for deposit slip based entries which are marked with a Σ symbol.
- Expand Button – This button will expand the single transaction that the focus is currently on.
- Expand All button – This button will expand all entries in the list on screen that have been marked with the Σ.
- Sequence Number – Once the expand button has been selected, for any transaction/s SapphireOne will display the individual sequence number for all of the transactions in the group.
- Collapse Button – The button will collapse the single transaction back to its original condition.
- Process Button – Selecting this button updates the reconciliation date. For items that have already been posted from Receivables and Payables selecting Process will reconcile only.
- Look Button – This button enables you to look at the transaction that is currently highlighted in the list.
- Find Button – This button brings up the Find dialog page. You may search for a transaction by the Date In, Int Ref (Internal Reference Number) or the Amount in the transaction.
- Save Button – Selecting this button stores the current selection. This allows you to leave the page to make any corrections and then come back in. Once you have Stored a selection the Load button will become active.
- Load Button – The button is used to load the stored selection when you come back into the page. This button will only be active, not greyed out, if there is a currently stored selection in SapphireOne.
Common Errors
Here are some possible reasons as to why your SapphireOne Bank Reconciliation Opening Balance could be different from your Online Bank Opening Balance:
- A General Ledger Journal has been incorrectly processed between two bank accounts.
- To prevent this occurring, use a Cash Receipt (MR) or Cash Payment (MP) to transfer money through a Clearing Account.
- A Non-Bank type transaction has been entered into SapphireOne and posted to the Bank Account.
- To locate the transaction(s), navigate to Utilities Mode > History > Audit Lines and perform a Query on the General Ledger Bank Account code for transaction types equaling VI, VC, CI or CC.
- A Bank Reconciliation has been completed between your last known reconciliation and the current one.
- Search for the date greater than or equal to your last Bank Reconciliation.
How to Post a Bank Reconciliation
1) Post the Opening GJ
In order for the bank accounts to show an opening balance, you need to first post the opening GJ that was created to bring in your opening balances.
The easiest way to find this is by going to: Accounts > General Ledger > Transactions
Then search for an external reference of “Opening Transaction Balances”.
Right click > Post OR Tools > Process > Post
2) Reconcile the Opening GJ
Find the GJ you just posted in your Bank Reconciliation screen and then click on it. This will create a tick on the end of the line.
Then select Process. Now when you go back into the Bank Reconciliation for that bank account, there will be an opening balance.
3) Bank Reconciliation of the Individual Transactions
Click on the column of Date In to sort by date of the transaction:
Then go through all transactions methodically by comparing your bank statements to the value showing on your Bank Reconciliation screen and if they match, click on the line. This will create a tick in the last column.
If instead of a tick, you get a “U” that means it is unposted.
It can make sense to reconcile the bank with all unposted transactions first, as this will allow you to amend any incorrect ones. Either by going through your normal transactions inquiry, or click “Open” on the bottom of the screen, which takes you to the highlighted transaction.
Once you have made all necessary amendments, don’t forget to post all transactions, otherwise you won’t be able to process the Bank Reconciliation. Go back into the banking screen and select “Mark All” to save you the time of selecting each line.
Hint: If you mark all unposted transactions, it will show you the closing bank balance up to date.
If this matches your closing balance on your bank statement, you can be 99.9% sure that all transactions were entered correctly. If so, you simply can batch-post all of them and then go back to your Bank Reconciliation screen to process them all in one go. If it does not match, you will need to go through the transactions one-by-one as described above.
4) Compare Opening and Closing Bank Balance
Once you have ticked all transactions, the closing bank balance on the bottom of the screen should match your actual closing for that hour, day, week, month, etc. that you’re reconciling (or up to date if you are doing them all in one go). It’s recommended to perform a Bank Reconciliation on a daily basis.
If it does not, enter the closing balance on your bank statement into the white field. This will show you the difference that you are out by to assist you in finding the incorrect transaction.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.