Audit Program 4.0 - User Manual
5. Importing Data for Deliverables:
5.5 Creating a List of Ledger Excel File Names
E. Complete the Excel File Names for each Account in the COA Mapping Form
For each account, complete the File Name of the Ledger excel file for that account, and include the file format extensions, such as, .xls, .xlsx, etc.
The user may also copy the list of file names generates using the Extraction Tool.xlsm as explained in section 5.4, and paste into Column P of the COA Mapping Form.
F. Complete the Cell References where the Ledger Data Starts:
Open any one of the Excel ledger files, and locate the Cell References where the data starts for each of Voucher Number, Voucher Date, Description, Debit and Credit.
Sample Ledger File:
In the following opened sample ledger excel file, you will be able to identify the following cell references are where the data for each data type starts in the excel file:
- Voucher Number: Cell A6
- Voucher Date: Cell B6
- Description: Cell C6
- Debit Entries: Cell D6
- Credit Entries: Cell E6
For Credit entries, although there is no data in this cell, the data would have been here if the first row of data is a credit entry. Therefore, we would still identify that Cell E6 would be the first cell with data.
G. Complete the Cell References for Each Account in Columns Q to U in COA Form:
Based on the identified cell references from any sample ledger excel file, you may safely assume that all ledger excel files will have the same presentation format of data. The user may then copy and paste these cell references in each row in the COA Mapping Form, between columns Q to U. The screenshot below shows the completed cell references for this sample ledger file.
Please apply this concept accordingly based on the cell references in your client’s ledger excel file.
