Banking Support System Overview

1. Banking Support SystemOverview Presented by: Ian Thomas & Andrew Brokensha Financial & Accounting Services Shared Services SA

2. The development & support team – our background • Ex-DAIS financial systems support (including support to bureau clients eg DWLBC) • Moved to DTF (SPSO) when DAIS was abolished in October 2006 • Moved to Shared Services on 31 March 2009 • Continued to support ex-DAIS systems, until last Business Unit was transitioned to their new agency system on 1 July 2010 • Still support DFW (formerly DWLBC) financial systems • Have developed and support a number of other applications that benefit Shared Services SA in undertaking service delivery processes on behalf of agencies, including the Banking Support System (and its predecessors) 2

3. The old way – juggling spreadsheets So, how did the Banking Support System come about? A need to improve existing processes that were largely spreadsheets based But, there were intermediate steps ……. 3

4. Banking Support System – History Sept 2009 Developed AR Cheque Register System • To account for daily receipts and banking • Spreadsheets with macros (one per Agency) • Used by 20 Agencies March 2010 Developed Bank Reconciliation System • To match Bank and GL Cash transactions & provide reconciliation processes • Access database (one per Agency) • Used by 3 Agencies (Arts SA, DPC, DTEI) 4

5. Banking Support System – History July 2010 Developed Banked Transaction Accounts Receivable Recognition System (BTARRP) • To accounts for bank receipts & code to assist with creating batches to input to Masterpiece AR or GL • Access database (one per Agency) • Used by 8 Agencies July 2011 Developed Banking Support System • To integrate Cheque Register, Bank Rec & BTARRP systems into a single system, incorporating all agencies • Visual Basic interface with SQL Server database (single database including all agencies) • Used by 20 Agencies 5

6. Banking Support System – Available Modules • The Banking Support System is a system developed in-house to integrate the functionality of the existing AR Cheque Register, BTARRP and Bank Reconciliation systems. • The system consists of 3 functional modules :- • Daily Banking (manage receipts/banking) • Receipt Recognition (classify bank receipts) • Bank Reconciliation (match bank receipts to GL) • … and 2 support modules :- • Administration (Agency setup/manage system) • Query Data (allows users to create data extracts) Note - an agency does not need to use all functional modules. 6

7. Overview 7

8. The new way – an integrated solution • Visual Basic Front-End • SQL Server Database Back-End 8

9. Advantages of the System • Multi user • Security • Security profiles control user access to Modules / Agencies • Encrypted password user protection • Integration • Data created in one module is available in other modules eg Daily Banking data flows through to Receipt Recognition and Bank Reconciliation • Integrity • controlled processes, no accidental deletion or changing of data • Simplicity • no spreadsheet files / daily worksheet tabs to manage • no manual data manipulation 9

10. Advantages of the System (cont) • Consistency • Same approach for each agency • Better understanding by staff • Improved functionality • a better way of doing things • reporting • Scope for further automation 10

11. Main Screen • The main screen has a menu bar across the top, as follows .. • Each module appears on this menu bar • Functions within modules appear when you click on the module • If a user does not have the security access to a module / function it will appear greyed out. 11

12. Administration Module - Objective Provides the tools to administer the system This includes the following functions … 12

13. Administration Module - Functions Users Where user access is setup and maintained. Logged users Shows users who are currently logged on. Setup Where a new Agency is setup and maintained. It holds various parameter values used within the system. Password Reset Where a users password can be reset. Table Maintenance Where significant tables used by the system are maintained. Agency, Bank, Holidays and Find Fields are setup and maintained here. 13

14. Daily Banking Module - Objective Record all cheques, cash and money orders received by Shared Services each day Manage the treatment of each receipt including the deposit to Westpac where applicable. This includes the following processes (for each Agency that uses the DB module) … 14

15. Daily Banking- Functions Daily Processes Add a Day Create a new day to enter todays receipts. All receipts entered on previous days and not yet banked will be automatically carried forward to the new day. 15

16. Daily Banking- Functions Daily Processes (cont) Brought Forward Records (Transactions received on Previous Day but not yet banked) Edit records to change Action Code. Records cannot be deleted, but can change Action Code to CANCEL – ENTERED IN ERROR Received Today (Add/Change/Delete) Enter cheques, cash, money orders received today Assign Action Code • If to be banked today, assign a BKD code (agency may have multiple codes) • If not to be banked assign a code from - Info Reqd, Bank Delay, Returned to Payee, Fwd to Agency, Fwd to Other Team Change Action Code (Multi) Search for records, then change the Action Code on all selected records or on a single record 16

17. Daily Banking- Functions Daily Processes (cont) Print Deposit Slip (DB0001) Day Summary (DB0002) Day Detail (DB0003) Finalise Day Day is locked and no further changes can be made for that day. Help Provides access to the modules User Guide Reports(Print/Save/email options available) Daily Banking Stats Report (DB0005) Bank Deposits – By Date Range (DB0006) 17

18. Daily Banking Module – Team Involvement Involves 2 AR teams • Admin Team • open mail • create day in DB module • enter received cheques, money orders, cash into DB module • forward receipts to Receipting Team • Receipting Team • review receipts and assign Action Code • prepare Bank Deposit slip • bank received amounts (at Westpac) • finalise day in DB module 18

19. Receipt Recognition Module - Objective Load all daily bank transactions from Westpac Corporate Online Where appropriate spilt original transactions (either automatically or manually) Record additional information against each transaction to assist with the Masterpiece data entry and Bank Reconciliation processes • Receipt Type • Batch Number This module provides significant benefits to the Bank Reconciliation process, by providing better information for matching transactions. 19

20. Receipt Recognition Module - Functions Setup Receipt Type Codes A list of codes available to classify each receipt. 20

21. Receipt Recognition Module - Functions Setup (cont) Rules Used to invoke special processing on file import to automatically assign a Receipt Type, handle Credit Card transactions and to split Daily Banking Bulk Deposits. 21

22. Receipt Recognition Module - Functions Import Import the Corporate Online Bank file (CSV Transaction Only file created and downloaded from COL as per Export Schedule) Special Processing on Import Assign Receipt Type - The Description field for each record is checked and if the content matches Rule1 and Rule 2 then the Receipt Type against this rule will be automatically assigned. Daily Banking Static Deposit (where rule has been invoked for an agency) - If Rule 1 is a Static Deposit Number then the single “bulk” record on the bank import file will be replaced by the individual receipts from the Daily Banking module Credit Card Transactions (where rule has been invoked for an agency)- If the COL deposit record relates to a Credit Card transaction and the banked amount (eg $165.15) is less than the Gross Amount appearing in the description field of the record (eg $168.85), then the record will be split into 2 records, one for the Gross Amount ($168.85) and the other for the Fee Amount (-$3.70). Batch Records (all records assigned a Batch Number will be archived) 22

23. Receipt Recognition Module - Functions Filtering (Main screen to Code and Finalise Receipts) 23

24. Receipt Recognition Module - Functions Filtering (cont) Search for a Record (to limit the number of records displayed) Update a Record (to include Receipt Type, Batch, Comment) Split a Record up to 5 times (Sum of all split records must equal original amount) Batch Records This moves the batched filtering records to the Archive table and prints a report RR0001. These records then become available for updating the table in the Bank Reconciliation module. At this stage a manual batch will need to be created to reflect the transactions to be entered into the Masterpiece AR Receipting module. 24

25. Receipt Recognition Module – Functions (cont…) View Archived Records Search for a Record Restore a Record (will move the record back to the Filtering screen) View Split Records Search for a Record Restore a Record (will reverse the split) Reports (Print/Save/email options available) Un-Allocated (RR0002) Un-Processed (RR0003) Extract Period (RR0004) Help Provides access to the modules User Guide (in pdf format) 25

26. Bank Reconciliation Module - Objective Match Corporate Online (bank) and GL cash transactions This includes the following processes (for each Agency that uses the BR module) … 26

27. Bank Reconciliation Module - Functions Import Corporate Online (COL) • this is the original COL data already imported through Receipt Recognition module • import up to selected date (to allow end of month reconciliation control) • Validates data (by Bank Account Number, Date) – can only import once General Ledger (GL) • from data extract created from Masterpiece • only records up to current accounting period are imported with future period records held • Validates data (Unique GL Identifier) – can only import once Receipt Recognition • Updates existing COL records imported into Bank Rec with additional information added in the Receipt Recognition module eg Batch Number 27

28. Bank Reconciliation Process – Functions Matching 28

29. Bank Reconciliation Module - Functions Matching Process Search • available in both COL and GL areas • limits records displayed on screen Split • done where need to match part of COL record to all or part of GL record (or vice versa) • works the same as Split function in Receipt Recognition Auto Fill (after search filter) • available in both COL and GL areas • only available on Search criteria • will flag all returned records for matching 29

30. Bank Reconciliation Module - Functions Matching Process One Sided Match • allows matching of COL to COL or GL to GL • Selected records must sum to zero before match can be completed Match COL to GL • Select records from COL and GL areas to be matched • Variance between COL and GL must be zero before match can be completed Auto Match • On file import, system attempts to flag records for match based on fields designated in the System Setup screen (through Administration module) eg Batch Number • Records are returned into Auto Match area of screen 30

31. Bank Reconciliation Module - Functions View Archived Records Search for a record Restore a record (reverse the match) View Split Records Search for a record Restore a record (reverse the split) Reports End of Month (BR0001) YTD Report (BR0002) Monthly Recon (BR0003) COL Un-matched (BR0005) GL Un-matched (BR0006) Daily (BR0008) 31

32. Bank Reconciliation Module - Functions End of Month This finalises the month, produces a reconciliation report and rolls over the outstanding balances to the new month Help Provides access to the modules User Guide (in pdf format) 32

33. Query Data Module - Objective Provides end users with the ability to extract data from the major systems tables This includes the following functions … 33

34. Query Data Module - Functions Create Query Action required • Select Table • Select Fields • Develop Search Criteria • Run Query (this will return the results to the screen) 34

35. Query Data Module - Functions Query results can be - • Exported to Excel • Emailed • Saved (as an Excel file) The SQL created by the query can be Saved for future use 35

36. End of Presentation 36