Bank Reconciliation - Electronic

How to automatically import your bank statement see manual on overview of screens – Download of Manual

Security #626

 

Prerequisities:

• Finish your bank reconciliation in back office.  Balance and print a copy of your Ledger Bank Reconciliation Statement for file – make sure this balances before using Spider Bank Reconciliation.
• You can not change from back office reconciliation  to spider reconciliation half way through.
• Suggest you do bank statement daily – so that it will match your transactions better and save time
• IMPORTANT :  if you have one transaction showing on your bank statement you should be processing your receipts payments the same way e.g.
• Receipts – DD – will show individual transactions;   EF – a total,  EL a total, CA (Cash) & CH (Cheques) will show as one amount on your Bank statement for that batch.
• Payments – BP – will show as individual transactions (usually used for when your bank takes money directly from your bank account);  EL – a total ;  CH – will show an individual transactions
• Also using Electronic Bank Rec you will have to be careful when entering your receipts that you receipt each line that is in your bank statement so you can be matched off:

Example of this incorrect:

Current Banks tested:

BANK	Country	[Select Format] to use:	Type of file to get from your banking software:
ADF	Australia	QIF	QIF
ASB	New Zealand	ASB	csv (do daily statements)
ANZ	Australia	QIF	QIF (4 digit year)
ANZ	New Zealand	CSV	CSV (do daily statements)
BNZ	New Zealand	BNZ (use QIF format)	GIF (used QIF)
BSA	Australia	QIF	QIF
CBA Biz CBA	Australia	Commonwealth Bank of Australia	csv (do daily statements)
RAB (Regional Aust. Bank)	Australia	QIF	QIF
WESTPAC  WESTPAC Business Live only (QIF)	Australia	QIF QIF (is the best format to use)

 

Step 1 Print your Bank Statement from your Bank

Print your bank statements off from your Bank as you would normally do and enter your transactions from your Bank Statement into PCSchool as usual.

 

Step 2 Importing your Bank Statement

To import your Bank Statement, you need to be able to get a transactions file from your Bank. As many banks have different formats, we may have to adjust the program to read in your statement.  If not showing in the list please email the options you have available to help@pcschool.net.

Creating/Import New Statement

1. [Code] select your Entity
2. [Account] select your Bank Account
3. [Create/Import New Statement]

This screen will pop up : (note: various banks will use different ways of importing the data – some require you to enter the closing balance, some will import the opening and closing balances)

 

1. Select the [Period] e.g. if in October use your period set up for October most cases this will be 10
2. [Select Format] – select from the drop down your format you wish to use (see above for your banks format which is acceptable to use)
3. [Date] select the last date of the transactions you are bringing in e.g. 30/09/2018
4. [Opening Balance] check this is correct
5. [Cr] or [Db] select the correct Debit or Credit status
6. [Browse & Import] select where you have saved your bank statement (suggest you create a file for each month so you can pull in the correct bank statement entries) this will then pull in your bank statement entries
7. [SAVE]

With some banks it will pop up with this screen to check your balance or asking you to enter your opening balance as per your bank statements:

Select [OK]

 

 

Next screen will show you that it has imported correct and the statement number.

 

Step 3 Auto matching your transactions

Select the sheet you have created

1. You will see the [Pending Statement Records] – which shows 23 records
2. [Opening Balance] check to make sure this is correct
3. Select icon for [Presentation]

Overview of Screen:

1. Shows the item imported from the Bank Statement
2. Show the items that have been entered into PCSchool
3. [Exact] show the items that the system thinks as Exact matches to your transactions
4. [Possible] possible matches
5. [No Matches] any transactions that it cannot match
6. [Presented]

[Refresh] button at bottom right will refresh the presented or unpresented transactions – do this at any time to update your screen.

 

Step 4 [Exact] matches

[Exact matches]  tab

Check your transactions and make sure you are presenting the correct amount and Name and date – if correct press the [Present this] icon

[Finalise all auto matched] will automatically match all these entries

 

To Unpresent if an error

Select the [Refresh] button the [UnPresent this] button will appear:

 

Now select the entry you wish to [Un-Present this]
[Refresh]
And present the correct entry

 

Step 5 [Possible Matches] Select the Possible matches tab

Note:  If you go back into PCSchool and add more transactions to Debtors, Creditors etc you will need to F5 to refresh Spider – so it will pick up these new transactions

Select the [tick] item that you wish to match
Work thru all the [possible] matches

[No Matches] – select the tab

1. In [No Matches] there is still 9.09 to be matched.
2. Select [Unpresented] items and look or search for this amount  e.g. there is 9.09

Highlight the $9.09 on the left and the $9.09 on the right

1. [Present this] icon appears so you can now present this – e.g. of one more than one amount

The amount will show in the [Present this] so you can check that it balances. Once correct select [Present this]

Step 6 

Create a General Ledger transaction for items with no transactions e.g. Bank fees, interest etc – which go directly to the ledger

This shows there are still 2 transactions that require to be presented but have not been receipted etc within PCSchool

Select the          (Unpresented Consolidation] icon

1. Shows the Batch that the system will create – select the [Create Batch] icon
2. Shows the entries still to be reconciled

1. Select the Icon to push the information over
2. [Account Info] Select from drop down or type in your General Ledger code
3. [Project] select from drop down
4. [GST] change if required
5. [Amount] should populate from the entry
6. [Whom] type in, if required
7. [Description] type in, if required
8. [SAVE]

In back office it has created the following entry:

Path:  Finance > Gen Ledger > Batch Transactions > Batch Receipts

Note this is a finalised batch.

Once you have matched all entries:

1. You will see there are NO [Pending Statement Records]
2. Check your [Opening Balance] and
3. [Closing Balance]
4. Once correct [Print] Statement

 

Step 7   Printing Reports

1. Print the Bank Reconciliation report from the    [Print ] icon
2. From back office print the following reports:

• Print Unpresented Report
• Print Ledger Balance report – making sure this balances with your Bank Statement closing balance (if not investigate further before proceeding)
• File together with a copy of your bank statement for Auditor