How Do I Import My Bank Statement for Bank Reconciliation?

How Do I Import My Bank Statement for Bank Reconciliation?

A Bank Reconciliation is simply a process that matches your Money In and Money Out Records with your Bank Account. 

It is an important process because it provides you with a check as to the accuracy of your records and can therefore save your accountant a lot of time at the end of the year.

The balance of your bank account may be different from your records because someone hasn’t banked the cheque you sent to them, or bank charges have been applied but you haven’t recorded them as you haven’t seen your statement. Or maybe someone has paid a direct deposit into your bank that you haven’t recorded.

You need to record your Opening Bank Balance when you first start using the program and then the program will automatically track the Balance per your Records.

The monthly Bank Reconciliation is simply checking the deposits and payments on your Bank Statement against your records and making sure that everything on your bank statement is in your records.

Sometimes there are items in your records that are not on your bank statement, such as cheques you have posted that have not been banked by the persons you sent them to.

These are called ‘outstanding’, ‘un-presented’ or ‘uncleared’ items, and when your Bank Statement Balance is adjusted to take these into account, your Bank Balance should be the same as your Records Balance.

To process your Bank Reconciliation, click on the Bank Reconciliation tab and a ‘wizard’ will take you step by step through the bank reconciliation process. 

If you are doing a Bank Reconciliation for the first time, ensure that the month you have selected is the first month of records that you want to reconcile. 

The wizard will take you to the Opening Balance. On later occasions the Opening Balance wizard can be accessed from the Settings button, but you should only ever need to record your Opening Balance when you first start using the program.

 

Bank Reconciliation and Financial Institution Settings:

When you first click on the Bank Reconciliation tab the Bank Reconciliation Settings section will display.
You can choose to either reconcile your Cashflow Manager records to the paper statement you receive from your bank or to a downloaded file if you use Internet Banking.

So the question asks: How do you want to reconcile your Cashflow Manager records?

If you want to reconcile to your paper statement that option is already selected so you can click on the [Next] button.

If you want to reconcile to an online file downloaded from your bank, click on that option.

Unfortunately, the banks all have different formats for their downloaded files so you must select your bank from the Financial Institution list so that Cashflow Manager can match it, or add it if it does not appear in the list of pre-loaded banks. 

Regardless of which option you choose to reconcile, you must record your Opening Bank Balance together with any transactions that relate to previous time periods but have not appeared on your bank statement.

When you click on [Next] , a wizard will walk you through the process of recording your opening bank balance.

The Settings window will no longer be displayed once you have made your choice and recorded your Opening Bank Balance. You can return to it at any time by clicking on the [Settings] button in the Bank Reconciliation section. 

 

Adding a New Financial Institution:

If your bank is not listed, please contact Cashflow Manager Support to have it listed for a future addition. To get started immediately, you can add it yourself by following these steps:

1. Download a file from your Bank’s website to use as a template. 
2. Click on the [Add New] button to set your default Financial Institution.
3. Browse to the Bank file that you downloaded. Open the file. Your data will be displayed on the left side of the pop-up window.
4. Answer the questions in the bottom left of the pop-up window:
- Is there a header? This refers to whether there are column titles in the data. For example date, description etc. 
- Are the fields separated by tabs or commas? Most banks use commas, try this option first.
- How many fields are there in each row? For example if your data has Date, description, debit and credit, there are 4 fields.
Note: sometimes it is difficult to tell if there is no header row. If you see 2 commas together, the second comma indicates another field, even though it is empty. For example, this will occur if the bank file has a separate field for debits (money out) and credits (money in).

5. Assign the fields. For each of the fields in your data, select a field type to map to in Cashflow Manager. There is a drop-down list in the field called “Field type”. 
6. Provide a name for this Bank. 
7. Provide the URL that you use to download. This will enable you to navigate to your bank site and download your statement at the time you are doing your bank reconciliation.
8. Click on [Save].
9. Click on [Next].

You are now ready to enter your Opening Bank Balance.
 

Record your Opening Bank Balance:

Your Opening Bank Balance is the balance that would actually be in the bank if all deposits that have been put into the bank and all cheques written on or before the date you are starting to use the program appeared on your bank statement.

If you have not previously recorded an Opening Bank Balance you will have the opportunity when you do your first reconciliation.

1. Record your Closing Bank Statement Balance for the month immediately preceding the month of your first reconciliation (or $0 if you are reconciling a new account.)
2. Click on [Next] to continue.
3. This brings us to the Uncleared Deposits (Money In) page where you need to record any deposits from previous months that have not yet appeared on your Bank Statement. This is unusual with Bank Deposits but sometimes might occur. If it does, you only need to record the total deposit, not the individual transactions as we only need the amount to match the amount that appears on our bank statement.

Simply record the last day of the month in Day,tab to Total Amount and record the amount of the deposit. Allocate the deposit to any Allocation Column (it is before your records commence so should not affect your reports) and then record the amount in the Bank Deposits column. 

4. Click on [Next] to go to the page for recording any Uncleared Payments. This is more common and will occur when you send a cheque to someone and they don’t bank it until after the end of the month.
5. Record any Bank Payments from previous months that have not yet appeared on your Bank Statement. Remember to record the cheque number as well as the amount in the Total Bank Payments column.
6. When you have recorded all uncleared deposits and payments, click on [Save] to save your reconciled Opening Bank Balance. Your Opening Bank Balance will now be displayed in the first line of the My Records information on the Bank Reconciliation window.

If you wish to change between the manual and ‘downloaded statement’ options, or process your Opening Bank Balance again, click on the [Settings] button from the Bank Reconciliation window.
 

Reconciling with a Downloaded Bank Statement:

If you are registered with your bank for internet banking you can download your Bank Statements and automate much of the Bank Reconciliation process. This can potentially save you hours of time, particularly in looking for discrepancies.

You will need to have selected ‘reconcile to a downloaded file’ and the name of the bank in the Bank Reconciliation Settings.
There are a number of steps in this process as indicated by the tabs at the top of the Bank Reconciliation screen but this can have a number of advantages.

For example, many of your transactions can be automatically matched to save you hours of time. Some banks are better than others in this regard because some list cheque numbers separately, while others include the cheque number as part of a description that is not easy to separate. Plus, any transactions on your bank statement that have not been entered into Cashflow Manager can be imported and quickly allocated the correct columns.

Step 1: Download your Bank Statements. 

You can do this at any time you are logged on to the internet and also from within the Bank Reconciliation wizard.

1. Select the name of your Bank and click on the [Download] button to go to your Bank's website. If you have already downloaded your bank statement, click on [Next]
2. Login and follow the Bank's instructions to 'Download' or 'Export' transactions. 
3. Unfortunately, the Bank's export files do not generally include the Balance so while you are signed in for online banking with your account selected, click on the 'Statement' option and select the last day of the month to find out your Closing Bank Balance on this day. Write this balance down for future reference. 
4. Click on [Next]
 

Step 2: Import your Bank Statement File

1. Browse to select your downloaded file. 
2. When you have selected the file, click on the [Next]. You should receive a message advising that your file has been successfully imported. 
 

Step 3: Closing Bank Balance

1. Type the closing bank statement balance for the month in the box provided.
2. This is the balance on your bank statement after the last transaction for the month. You may need to look up your closing bank statement balance in your internet banking statement as this information is not included in your downloaded file. (Hint if the balance is in overdraft, record the amount as a minus.)
3. Click on [Next]

Step 4: Reconcile Money In (Deposits)

There are several stages in the Reconcile Deposits process.

1. Money In –Auto-Matched.
Your Money In Record deposits are matched to your Bank Statement on the basis of date and amount. Review these entries to ensure they are correct. If any are not correct, click on either the Records or Bank Statement checkbox to remove the match. Then click on [Next].

2. Money In –Unmatched i.e. Deposits Not Found in Your Records
Your unmatched entries are listed in amount order, although you can click on the header for any column that you may wish to sort by.

To match a transaction, click on the checkbox in My Records list, and the checkbox for the corresponding transaction in the Bank Statement list. These matched entries will be moved to the top of the list.

Please Note: Transactions will only match if the amounts are exactly the same. If you recognise a match but the amounts differ, you will need to correct the amount in your Money In Record before finishing your Bank Reconciliation.

After making any corrections, any un-ticked amounts in the My Records section should be deposits recorded in the Money In Record but not appearing on your Bank Statement (i.e. outstanding deposits).
Click on [Next].

3. Money In –To Add
This will only appear if selected in Options –Advanced Preferences. It displays any entries for deposits listed on your bank statement but not entered in your records and allows you to select any entries that you wish to include in your records. Note: this option is turned off by default because it would be unusual not to add all such entries on your bank statement into your records.

When you have done this, click on [Next]

4. Allocate Money In
The Money In Record screen will now list the transactions you have selected to add so you can apply the amounts to the correct Allocation Columns.
Arrow to or click on the amount you wish to reallocate and press your ‘Delete’ key to delete the amount from that column. Arrow across to the correct column and press ‘Enter’ to save the amount in that column.
You can also breakdown the transaction into more than one Allocation Column.
When you have finished, click on [Next]

Step 5: Reconcile Money Out (Payments):

There are several stages in the Reconcile Money Out process.
1. Money Out –Auto-Matched.
The Total Bank Payment transactions from the Money Out Record are matched to your Bank Statement on the basis of cheque number, date and amount. Review these entries to ensure they are correct. If any are not correct, click on either the My Records or Bank Statement checkbox to remove the match. Then click on
[Next].

2. Money Out –Unmatched i.e. Payments Not Found in Your Records
Your unmatched entries are listed in cheque number then amount order, although you can click on the header for any column that you may wish to sort by. To match a transaction, click on the checkbox in My Records list, and the checkbox for the
corresponding transaction in the Bank Statement list. These matched entries will be moved to the top of the list.

Please Note: Transactions will only match if the amounts are exactly the same. If you recognise a match but the amounts differ, you will need to correct the amount in your Money Out Record before finishing your Bank Reconciliation.

After making any corrections, any un-ticked amounts in the My Records section should be payments recorded in the Money Out Record but not appearing on your Bank Statement (i.e. outstanding payments).
Click on [Next].

3. Money Out –To Add
This will only appear if selected in Options –Advanced Preferences. It displays any entries for payments listed on your bank statement but not entered in your records, and allows you to select any entries that you wish to include in your records. Note: this option is turned off by default because it would be unusual not to add all such entries on your bank statement into your records.

When you have done this, click on [Next] to continue.

4. Allocate Money Out
The Money Out Record screen will now list the transactions you have selected to add so you can apply the amounts to the correct Allocation Columns.
Arrow to or click on the amount you wish to reallocate and press your ‘Delete’ key to delete the amount from that column. Arrow across to the correct column and press [Enter] to save the amount in that column.
You can also breakdown the transaction into more than one Allocation Column.
When you have finished, click on [Next]
 

Step 6: Balance and Lock your Records:

The summary information from both your records and the bank will be displayed. If you reconcile then the Records Balance and the Bank Balance will agree.
Click on the [Finish] button to continue.

A message will ask whether you wish to lock your records at this time. When you have completed recording transactions for any month and it has been reconciled it is advisable to lock your records to prevent any accidental changes that might affect your records.
To lock your records, click on [Lock]. If you do not want to lock your records at this time, click on [Don’t Lock].

If there is a discrepancy between your records and bank statement, the [Finish] button will be replaced by a [Next] button that will save the deposits and payments you have checked off to give you the opportunity to find what is causing the discrepancy without having to check everything off when you process your Bank Reconciliation again. 

When you lock your records a padlock will appear next to the Account Name in your Money In, Money Out, Bank Reconciliation and Cash Reconciliation sections.

If you need to unlock your records for any reason such as changing an allocation, go to the Bank Reconciliation screen and click on the [Unlock] button.

If you unlock a month and change anything in the Bank Deposits column of your Money In Record, or the Total Bank Payment column in your Money Out Record this will affect your reconciliation, not only for this month but for any future month as well.

Therefore we recommend that you print your Bank Reconciliation report every month.

Please Note: The Bank Reconciliation process has Tabs that allow you to select a different step in the process. However, we highly recommend that you use the [Next]
d and only use the Tabs to make changes such as changing the Bank Statement balance.


    • Related Articles

    • Map your downloaded bank statement

      Bank Statement Mappings allow you to take a portion of the transaction descriptions that are always present in the downloaded bank statement (typically Customer and Supplier names) and select which column you want these transactions placed in when ...

    • How to unlock a month - Bank Reconciliation

      There are 2 ways to unlock a month depending on the colour padlock you have. To remove a red padlock: 1. Select either Money In or Money Out on the left under Cashflow. 2. Go to File in the top left 3. Select Accountant Export 4. Then Unlock Months ...

    • Formatting your downloaded bank statement

      1. Download a file from your Bank’s website to use as a template. 2. Click on the [Add New] button to set your default Financial Institution. 3. Browse to the Bank file that you downloaded. Open the file. Your data will be displayed on the left side ...

    • How Do I Reconcile My Bank Statement Manually?

      A Bank Reconciliation is simply a process that matches your Money In and Money Out Records with your Bank Account. It is an important process because it provides you with a check as to the accuracy of your records and can therefore save your ...

    • Completing your Cash Reconciliation

      A Cash Reconciliation is simply to check that the Cash on Hand per your Records equals the Actual Balance of Cash on Hand, in the “Cash” Account of your file. This can be done as often as you wish. The Cash on Hand per your Records automatically ...

    • Popular Articles

    • Completing your EOFY payroll

      Finalising your year-end payroll It's important to note the final pay period for the financial year is the one that is PAID before the end of the financial year. The first pay period of the next financial year is the first one that is PAID during the ...

    • Starting a New Financial Year in Wages

      You can start a new financial year by following the below steps: 1. In the Employees section of Wages, select [Tools] in the top left 2. Select [Financial Year] 3. Select [Start New Financial Year] 4. The financial year ending will read 202X, select ...

    • End of Financial Year Checklist

      Be prepared and organised this end of financial year, with our easy-to-follow checklist. You can view and download the checklist here. We’ve provided seven steps to make your reporting as stress-free as possible, and if you’ve been using Cashflow ...

    • Single Touch Payroll Guide

      As a subscriber of Cashflow Manager, all fees involved with lodging your records via Single Touch Payroll are included with your Cashflow Manager Gold, Wages Manager or Wages 1-4 subscription fee. If you have any questions regarding Single Touch ...

    • Deleting Old Pays from the STP Event Lodgement Screen

      Continue if you have previous pays listed in the Single Touch Payroll screen that you do not want to send. From the [Tools] menu or on the right-hand side of the Employees screen in wages, select [Single Touch Payroll] to open the Single Touch ...