Chart of Accounts Automated Statements

Chart of Accounts Automated Statements

Chart of Accounts Automated Statements

User Guide
for the Electronic Reporting of

Chart of Accounts

for Offshore Institutions

(version 1.0)

Bank van de Nederlandse Antillen

1Table of Contents

PART ONE: Installing and using the Electronic Forms

1.INTRODUCTION

2.INSTALLATION INSTRUCTIONS

2.1Install Options

2.2Directory Structure

2.3Enabling Macros

3.HOW TO COMPLETE THE CHART OF ACCOUNTS (COA)

3.1Overview of the COA System

3.2Due Date

3.3Data Entry

3.4Color Coding

3.5The Logical Sequence of Data Entry

3.6Checks

3.7Functions: Save, Import, Export, Exit, Clear, Option, Print

3.7.1General Remarks:

3.7.2Import from Halfdone:

3.7.3Import from TXT in READY:

3.7.4Import from TXT in IMPORTDIR:

3.7.5Import from EXCEL flatfile in IMPORTDIR:

3.7.6Import Supporting Schedule “Code Listing”

3.7.7Export Function:

3.7.8Exit Function:

3.7.9Clear Function

3.8Authorization

3.9Levels of Reporting Institutions

3.10Reporting Frequency

3.11Changes in the COA sytem

3.12Completion of the Schedules

3.13Revisions /Corrections

PART TWO: Connecting to VPN & Automatic File Transfer

4.BNA AUTOMATIC FILE TRANSPORT SYSTEM

5INITIAL CONNECTION SETUP

5.1Setup of a VPN Connection to BNA

6.SETUP OF A BNA AFT FOLDER ENVIRONMENTS

6.1Transaction Folder

6.2Communication Folder

7SETUP OF A FTP CONNECTION TO THE BNA AFT SERVER

7.1Setup of Ace FTP freeware for Windows ver. 2.0

7.2Setup of FTP Explorer

8ESTABLISHING A VPN CONNECTION TO THE BNA

9ESTABLISHING A FTP CONNECTION TO THE BNA AFT FTP SERVER

9.1AceFTP ver. 2

9.2FTP Explorer

10DOWNLOADING OF FILES

1112 STEPS A TO PROPER BNA AFT SYSTEM

12TECHNICAL CONFIGURATION ITEMS

13FREQUENTLY ASKED QUESTIONS (FAQ’S)

PART ONE: Installing and using the Electronic Forms

1.INTRODUCTION

Electronic Reporting System forthe Chart of Accounts (COA)

In January 2003, the Bank introduced an electronic reporting system for the Chart of Accounts for the financial institutions. For this purpose, the Bank designed a standard MS Excel-based report forms, containing all the reports that have to be submitted for the COA. The reporting institutions have to enter their data into these report forms and submit them in a secured way via the Internet to the Bank.

Each reporting institution will receive a useridandpassword, shared by the same group of reporting institutions, from the Bank to connect to the secured area of the website of the Bank. From this area the reporting institutions can download the User manual including installation instructions and the installation files designed for each specific group of reporter’s institutions. This secured area can be found on:

Each reporting institutions will also receive two unique userid and passwordfrom the Bank. One is to complete the MS Excel-based report form and the other to upload their completed reports in a secure way on a dedicated server at the Bank. Each reporting institution will receive a unique electronic inbox and outbox to submit information to the Bank and to receive information from the Bank.

2.INSTALLATION INSTRUCTIONS

2.1Install Options

On the secured area of our website ( reporting institutions will be able to download the COA electronic form. Two options are available for the user: The executable file or the “zip” file.

Option 1:

  • Download the executable file by clicking the link: COA Self Extractor file (COADB.exe)
  • Save the COAdb.exe file to a directory on you machine or network.
  • Run the COA.exe file.

  • In the “Unzip to folder” field type in a directory or browse to select a directory to install the COA files.

  • Then press the “Unzip” button.
  • Press the “Close” button on the WinZip Self-Extractor Window.

Option 2:

If for reasons of security you are not able to download executable files, you can download the COAdb.zip file from the Website.

  • Download the executable file by clicking the link: COA Self Extractor file (COADB.exe)
  • Save the COAdb.exe file to a directory on you machine or network.
  • Double click on the file to launch WinZip application.

  • press the “Extract files” Button

  • Select a directory to install the files and press the “Extract” button again.

2.2Directory Structure

In the directory you selected for installation in the previous step, you will find the following folder structure:

ICIDB

ICIDB\COAHalfdone

ICIDB\COAReady

ICIDB\COAImp

2.3Enabling Macros

IMPORTANT

In order for the COA.xls file procedures to work, MACROS must be enabled in your EXCEL settings.

Change the security setting before you open the COA.xls file.

To enable Macros, go to the “Tools Menu” on your Microsoft Excel toolbar. Select “Macros”, “Security”.

In the Security Window, select “Low” in the security level tab. This will automatically enable macros when you open any Excel file. If you prefer to be asked before enabling macros, select the “Medium” level option.

Close Microsoft Excel, re-enter and open the COA.xls file.

After installation of the COAdb application, you can view examples of the structure of the TXT files and Excel flatfiles in the following directories:

TXT: COAdb/COAReady

XLS flatfile: COAdb/COAImp

XLS file of “work in progress”: COAdb/COAhalfdone

3.HOW TO COMPLETE THE CHART OF ACCOUNTS (COA)

3.1Overview of the COA System

This excel based reporting system application is designed to make the data entry as simple as possible, to detect inconsistencies early and to validate the data.

The COA-filings are set up in spreadsheets (worksheets) organized together in a workbook. The spreadsheets consist of the following:

  • Main Sheet
  • Balance Sheet- BAL-I
  • Income Statement- INC-I
  • Contingent liability-CLB-I
  • Loans, Leases and Acceptances by Collateral Type-COL-I
  • Listing of Large Loans and Advances-LLL-I
  • Solvency Report -SOL-I
  • Maturity Schedule-MAT-I
  • Liquidity Report -LIQ-I
  • General Provisions for Losses on Loans and Leases-GPL-I
  • Nonperforming Loans-NPL-I
  • Specific Provisions for Losses on Loans and Leases-SPL-I
  • Compensations -COM-I
  • Summary of Advances to Shareholders, Directors, Employees

and Their Related Interest-ADV-I

  • Due From/ Due To Unconsolidated Subsidairies and

Affiliates-DUE-I

  • Large Depositors -DEP-I
  • Country Risk Exposure and provision Schedule-CRE-I
  • Consistency Checks-COR-I
  • Table of Content-TOC-I

3.2Due Date

The reporting institutions are required to submit all reports and supporting schedules at once when applicable. All international banks are required to submit all required reports and supporting schedules on a quarterly basis, with the exception of the Balance Sheet, the Profit & Loss Statement, the Contingent Liability Statement, the Solvency Schedule and Liquidity Schedule. All reporting institutions must submit their reports and schedules within 30 calendar days of the reporting date.

3.3Data Entry

The data items in the files can either be:

  1. Direct Entry Items
  2. Calculated Items
  3. Automatic Transfers
  1. The most important entries to the users are the Direct Entry Items, which are the white fields, in which the amounts are entered manually. When entering the data you can use the TAB key, thereby movingfrom one entry field to the next entry field. The movement of the cursor by using the TAB key will be from left to right. You can also use the arrow keys. The movement of the curser using the arrow keys will be from left to right and up and down. Furthermore, to view the last cell of the working sheet, you are advised to use the scroll bar and scroll down to the last entry.
  2. Calculated Items are the result of a calculation such as a subtotal, a total or a multiplication. Only by changing the original direct entry can the automatic entry be altered, since these fields are protected against direct entry.
  3. Automatic Transfers are the result of a transfer from one worksheet to another. These fields are protected against direct entry.

3.4Color Coding

To simplify data entry, the cells in the spreadsheets are color-coded to differentiate between the following types of fields or areas:

1.Entry fields- white

2.Automatic number fields- light yellow

3.Non entry fields- light purple

  1. The white fields are for direct entry of amounts or descriptions (names, comments etc)
  2. The light yellow fields are used for automatically calculated number fields and automatically transferred number fields. These fields are either automatically calculated or transferred from another worksheet. Subtotals, totals, and dates are some examples.
  3. The light purple fields are locked (non editable) fields, representing headings or fields that cannot contain any value.

Users can only enter information in the white direct entry fields. All other fields are protected from changes by the users.

3.5The Logical Sequence of Data Entry

After logging into the COA application using the supplied userid and password, it is our suggestion to complete the COA-worksheets in the sequence in which they are presented in the COA-electronic workbook (this same sequence is shown in the “schedule menu”).

  1. On the “Main Sheet” the reporting year and reporting month are entered. This information will automatically be transferred to each following worksheet.
  2. In the following worksheets (BAL, INC, CLB, etc), data should be entered in the white direct entry cells.
  3. The worksheets “Consistency Rules (COR)” and “Table of Contents (TOC)” should only be used to check the overall consistency and completeness of your reporting. (see section 3.8). No data can be entered on these sheets.

This however is only the suggested working order. If desired, the user can enter or change information on the sheets in any random order, as long as he/she is aware of the interdependencies between the sheets. As a general rule, one should be aware that data transfers are made from earlier sheets to sheets appearing later in sequence. (eg. Data is transferred from the balance sheet (the 2nd sheet) to the solvency schedule (the ..th sheet))

3.6Checks

The COA application has the following built-in checks: Critical Checks

Validation Checks

Consistency Checks

On each worksheet on the top left hand side, you will find the following abbreviations:

  • Crt. Err (Critical Check)
  • Val. Err (Validation Check)
  • Cor. Err (Consistency Check)

Critical Checks: The critical checks should display a “0” in the color green on the worksheets.In the consistency worksheet an “OK” should be displayed for each of the critical checks. Any critical error display on the balance sheet worksheet will prevent you from exporting the whole COA workbook. If you try to export the file with a critical error, the program will display the following message box:“The workbook contains (1, 2 or 3) critical errors and cannot be exported. Correct errors first and export again.” A listing of the critical errors can be found on the Consistency Rules worksheet.

Validation Checks:Not all schedules have validation checks. A listing of the validation checks can be found on the Consistency Rules worksheet. These schedules should reflect a value greater than zero in specified data

cells, in order for the schedule to be considered as valid. A value should always be reported on these schedules. Any schedule that has a validation error will not be exported.

Consistency Checks:A listing of the consistency checks can also be found on the Consistency Rules worksheet. It is highly recommended that all consistency errors are corrected before exporting. However the export function will allow the user to export a file that contains inconsistencies. It is left to the judgment of the person(s) authorized to sign and certify the Chart of Accounts, if a consistency error is meaningless (eg rounding differences.)

If an error or an inconsistency has occurred on a worksheet, either a critical error, validation error and/or consistency error, this will be displayed in red on the top left hand side of the worksheet.If there are no errors on that sheet, the above mentioned abbreviations will be displayed in green.

3.7Functions: Save, Import, Export, Exit, Clear, Option, Print

3.7.1General Remarks:

If the data has not been entered previously in the workbook, the following steps should be taken:

  1. When opening the Excel file, the reporter should use the supplied user-id and password to login. This will open the workbook belonging to the reporter’s level (see section 3.10). The reporter can activate the button, which allows the application to remember the user-id and password. The reporter should then enter the reporting year and reporting month.
  1. The next step is to navigate to the Balance Sheet and enter the data and so on, until each worksheet is completed.
  2. The workbook can then be exported, if there are no critical errors on the balance sheet (see section 3.7.7).
  3. the Table of Contents sheet should be signed and verified by authorized persons. (see section 3.8)
  4. the “txt file” should be submitted in the inbox on the Central Banks VPN (Virtual Private Network (see section 10.2)
  5. the signed TOC sheet (Table of Contents) should be faxed to the Central Bank. (see section 3.8)

Save Function:

The save function located at the top of your screen allows you to save the file while working in it.

Import Function:

The Import Function, located at the top of your screen should always be used if the need arises to enter either additional data or corrections or revisions in the workbook and data has previously been entered and saved in the workbook.

If the data has been entered previously in the workbook and the workbook has been exported and closed, the following steps should be taken:

  1. When opening the Excel file, the reporter should first log-in with its user-id and password followed by the reporting year and reporting month on the Main Sheet.
  2. The next step is click on “Import File”. This action will retrieve all the data already entered and saved. If you do not import the file, you will open an empty workbook.
  3. The workbook can then be completed/ and or corrected and exported, if there are no critical errors on the balance sheet.

There are four different Import Functions that the Financial Institutions can choose from, depending on their reporting system already in place. All four Import Functions will search for a file in the appropriate folders with a file name structured as follows:

COAbankcodeyearmonth.xls or COAbankcodeyearmonth.txt. The year and month are the data entered on the “Main Sheet”. The Bankcode is assigned when you login.

3.7.2Import from Halfdone:

This action will retrieve an excel file from the COAhalfdone sub-folder. Files are stored in the Halfdone directory when you use the “Export” or “Exit” function of the COA.xls application.

3.7.3Import from TXT in READY:

This action will import a COA.txt file That has been previously created and stored in the COAReady director, using the “Export” function of the COA.xls application.

3.7.4Import from TXT in IMPORTDIR:

This action will retrieve a TXT file. This TXT-file should be stored in the COAImp sub folder. This TXT-file should have the same structure as our TXT-export file, when placed in the COAImp sub folder. You can use this option if your automated system can produce a TXT file containing (some or all) the data fields of the COA. The COA.xls file can then be manually completed if necessary.

3.7.5Import from EXCEL flatfile in IMPORTDIR:

This action will retrieve a an Excel file in a fixed structure. You can use this option if your automated system can produce an Excel flat file containing (some or all) the data fields of the COA. The COA.xls file can then be manually completed if necessary.

This flat file should be saved in the COAImp sub folder. The first sheet of the flat file should contain the data fields of the COA and should have a fixed structure. An example of the flatfile can be found in COAImp sub folder.

3.7.6Import Supporting Schedule “Unconsolidated Subsidiaries and Affiliates”, “Large Loans”, “Compensations”

Since the data entered on these schedule do not change significantly from month to month, an import function has been created “Import from Unconsolidated Subsidiaries and Affiliates(Large Loans, Compensations) from ready” to retrieve the data from the previous month. The following steps should be taken:

  1. The COA file should be opened, with the current reporting month entered in the Main Sheet.
  2. The code listing from the previous month can then be imported by using the Import Function “Import from code listing from ready”. A text box will open, where you can enter the previous month and the current year. The data from the previous month for these schedules will then be entered.

Export Function:

Once all the data has been entered and corrected, and there are no critical and validation errors in the workbook, the file can then be exported.

You must then click on “Export File”. This action will transform the file from an “excel.xls” file to a “text.txt” file. The file is then ready to be sent to the Bank van de Nederlandse Antillen via a virtual private network. A further explanation about the transfer of data via this virtual private network will be given in the section “Automatic File Transport System”.

Exit Function:

You must then click on the “Exit Function” to save the workbook and quit the application. A message box will then be displayed.

If previous data has been entered in the workbook, and new data has been added or corrected, when saving the workbook by clicking on the “Exit Function”, the following message will be displayed: “You have already created …………….. in halfdone. Do you want to replace this file?”You will then have the option to click on “Yes”, “No”, or “Cancel”. The function “Yes” will save the file.

Top View