Information Systems:Auditors
New Process
The old queries have been replaced with InfoNet programs that allow more flexibility, and that Finance can do themselves - maybe even the auditors can.
- Accounts Receivable
- Accounts Payable
- Inventory
- Sales Order Shipping (Last and First)
- Expenses
- Purchase Order Receiving (Last and First)
- Sales Comparison
- Trial Balance
- Financial Reports (Report Writer)
Superceded Process
Create Excel Files
The prepared Excel files are stored in superserver / tech / common / auditors, then in a folder that is named for the fiscal year, and either ‘interim’, or ‘year end’.
When updating ‘select records’ in query, don’t get mixed up between calendar dates and fiscal periods.
Run the queries interactively, as they put files into library QTEMP.
In the CPYTOIMPF commands, change 'uwd staging/sheilav/’ to your own path, and YYYY to the closed fiscal year.
Look at the files from the previous year to see how to format them in Excel.
Accounts Receivable
Run query XX2480BP/AUDITAR01, after changing the ending period, to build summary file of A/R balances.
Run query XX2480BP/AUDITAR02 to add customer information.
CPYTOIMPF FROMFILE(QTEMP/FILE2) TOSTMF('uwd staging/sheilav/arYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings and total. Go into G/L transaction inquiry to get the closing balance of 120000, and compare it to the total. If they are not the same, find out why and document it. Save this spreadsheet to the correct folder under ‘auditors’.
Accounts Payable
Run query XX2480BP/AUDITAP01, after changing the ending period, to build summary file of A/P transaction balances.
Run query XX2480BP/AUDITAP01B, after changing the ending period, to build summary file of A/P transaction type ‘EIN’ (preliminary invoices in process).
Run query XX2480BP/AUDITAP02, after changing the ending period, to build summary file of preliminary invoice balances.
Run query XX2480BP/AUDITAP03 to merge files and add vendor information.
CPYTOIMPF FROMFILE(QTEMP/FILEU) TOSTMF('uwd staging/sheilav/apYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings and totals. Go into G/L transaction inquiry to get the closing balance of 210000, and compare it to the total. If they are not the same, find out why and document it. Save this spreadsheet to the correct folder under ‘auditors’.
Inventory
Run query XX2480BP/AUDITINV01, after changing the period, to build file of onhand values.
CPYTOIMPF FROMFILE(QTEMP/INVENT) TOSTMF('uwd staging/sheilav/inventYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings and totals. Go into G/L transaction inquiry to get the closing balances of 130000 (MAI) and 135000 (RET), and compare them to the totals. If they are not the same, find out why and document it. Save this spreadsheet to the correct file under ‘auditors’.
Sales Order Shipping (Last and First Invoices)
Run query XX2480BP/AUDITLF01, after changing dates and times, to identify the last 10 and first 10 A/R invoices of the year. Press F5 to see the pick lists that were invoiced within the date and time range you have entered. Adjust this range until you have 10 (or a few more) records both before and after fiscal year end. Remember that the warehouse closes early on Friday, is not open on Saturday, and opens late on Sunday. F3 to save and run the query.
- Note that the same pick list will show twice when two invoices are generated (which will happen when there are two different payment terms). When the invoice number is found, it will be whichever invoice is for the first line of the pick list (the one originally generated by ASW; not the custom one that is resequenced by pick location). You will have to manually deal with this; which may mean removing duplicate records from the generated CSV file, or adding records for the missing invoices.**
Run query XX2480BP/AUDITLF02, to link the pick lists to the order lines, then the order lines to the invoices.
CPYTOIMPF FROMFILE(QTEMP/LSTFST) TOSTMF('uwd staging/sheilav/lstfstYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings, then save to the correct file under ‘auditors’.
Expenses
Run query XX2480BP/AUDITEXP01, after changing period range, to pull out expense G/L transactions.
Run query XX2480BP/AUDITEXP02 to add KPMG statement section, and only pull out transactions included in statement sections.
This will include –
- Sections ADV, AMORT, COMPUTERS, DIRECTORS, EQUIPMENT, FREIGHT, GL, INSURANCE, INTEREST, MISC, OCCUPANCY, PRINT, PROFF, RM, TELEUTIL, TRAVEL, SAL, and WAREHOUSE only include cost centers (account part 4) from 1000 to 2409.
- CONSULTING section includes cost centers (account part 4) 1000-1129, or 1171-2399, or 2225.
- INCENTIVES section includes main (account part 1) 480250, 480550, or 480600 with cost centers (account part 4) 1000-1119, or 1126-2409. Or includes main (account part 1) that is not 480250, 480550, or 480600 with cost centers (account part 4) 1000-2409.
Run query XX2480BP/AUDITEXP03 to add consulting fees that are to be included in Salaries and Benefits. Which is CONSULTING section, cost center (account part 4) 1130 to 1170, or 2225.
Run query XX2480BP/AUDITEXP04 to add conferences (recovered). Which is main (account part 1) 500000 to 900900 and cost center (account part 4) 2410 to 2440.
CPYTOIMPF FROMFILE(QTEMP/EXPENS) TOSTMF('uwd staging/sheilav/expYYYY.csv' ) MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings and totals, then save to the correct file under ‘auditors’.
Purchase Order Receiving
Run query XX2480BP/AUDITREC01, after changing dates to last receiving date of last year and first receiving date of this year, and identify last five and first five purchase orders.
Run query XX2480BP/AUDITREC02, after keying in the ten purchase orders identified above, and the same dates as above. Items from the same purchase order can be received over a period of time, so including the dates in the selection will include only the transactions we are interested in. As well as speeding up the query. This will extract the warehouse transactions for the first sheet of the spreadsheet.
Run query XX2480BP/AUDITREC03, after keying in the ten purchase orders identified above, and the same dates as above. Items from the same purchase order can be received over a period of time, so including the dates in the selection will include only the transactions we are interested in. As well as speeding up the query. This will extract the PO close transactions, from the order log file, for the second sheet of the spreadsheet.
Run query XX2480BP/AUDITREC04, after keying in the ten purchase orders identified above, and the same dates as above. Items from the same purchase order can be received over a period of time, so including the dates in the selection will include only the transactions we are interested in. As well as speeding up the query. This will extract the G/L transactions that were generated, for the third sheet of the spreadsheet.
CPYTOIMPF FROMFILE(QTEMP/TRANS) TOSTMF('uwd staging/sheilav /transYYYY.csv' ) MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
CPYTOIMPF FROMFILE(QTEMP/CLOSE) TOSTMF('uwd staging/sheilav/ closeYYYY.csv' ) MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
CPYTOIMPF FROMFILE(QTEMP/GLTRN) TOSTMF('uwd staging/sheilav/ gltrnYYYY.csv' ) MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open these files in Excel, add headings and totals, and put into the first three sheets. For the fourth sheet, manually link them together by line (see last year’s spreadsheet). Then save to the correct file under ‘auditors’.
Sales Comparison
Run query XX2480BP/AUDITSLS01, after keying in the end date of the last three years. This will extract and summarize sales and cost of goods sold G/L transactions for the requested dates.
CPYTOIMPF FROMFILE(QTEMP/SALES) TOSTMF('uwd staging/sheilav/ salesYYYY.csv' ) MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Open this file in Excel, add headings and totals, then save to the correct file under ‘auditors’.
A/P Invoicing ?????
Run query SHEILA1/AUDIT2013Z, after changing dates and times, to identify the last 10 and first 10 A/P invoices of the year for each warehouse.
Trial Balance
Run query XX2480BP/AUDITTB01, after keying in the fiscal year you are reporting on. This will build a file for the opening balances (from ANOBOB).
Run query XX2480BP/AUDITTB02, after keying in the fiscal year you are reporting on. This will build a file of transactions total by period for the year (from ANOBAL).
Run query XX2480BP/AUDITTB03, after keying in the fiscal year following the one you are reporting on. This will build a file for the closing balances (from ANOBOB).
Run query XX2480BP/AUDITTB04 to link all these files together, with the Analyser key file (ANOSTK).
Run query XX2480BP/AUDITTB05 to link with the G/L master file (SROKTO) to get descriptions.
From a command line, run
CPYTOIMPF FROMFILE(QTEMP/TRIALBAL) TOSTMF('uwd staging/sheilav/trialbal.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
=Financial Reports (Report Writer)
Report Writer
ASW / Business Reporting / Work with Reports.
*VA/480B* Work with reports 3/04/15 10:34:48 RWRD10001
-------------------------------------------------------------------------------
1=Add 2=Change 3=Copy 4=Delete 5=Display 6=Print 7=Columns 8=Layout
9=Versions 10=Lines 13=Copy to company 14=Print specification
Report Report class Description Report type
_ CSTMONTH - SALES COST BY MONTH (TIME SERIES) Version
_ CUSTACTGRP SALES CUSTOMER ACCT GROUP MONTHLY Version
_ FIN00 FIN BUDGET DETAILS MTD/YTD Version
_ FIN01 FIN TRIAL BALANCE BB/DR/CR/EB Version
_ FIN02 FIN TRIAL BALANCE MTD/YTD/TY/LY Version
_ FIN03 FIN DETAIL STATEMENT OF INCOME Line
6 FIN03-DTL FIN KPMG DETAIL INCOME STATEMENT Line
_ FIN03-KPMG FIN KPMG SUMMARY INCOME STATEMENT Line
_ FIN03-0000 FIN SUMMARY INC.STMT-COMPANY LEVEL Line
_ FIN03-1000 FIN SUMMARY INC.STMT-DISTRIBUTION Line
_ FIN03-1100 FIN SUMMARY INC.STMT-OPERATIONS Line
_ FIN03-1125 FIN SUMMARY INC.STMT-CALGARY DC Line
Find the one you want, and use option 6 to print.
*VA/480B* Work with reports 3/04/15 10:36:48 RWRD15001
Print selection
-------------------------------------------------------------------------------
Report.............. FIN03-DTL KPMG DETAIL INCOME STATEMENT
Actual year/period.. 1602 Last closed period.. 1510
-------------------------------------------------------------------------------
Year/Period......... 15 12
Budget offset....... ___
Connected report.... _________
Connected version... _________
Key in the year and period, and press enter.
*VA/480B* Work with reports 3/04/15 10:38:06 RWRD15002
Print selection
-------------------------------------------------------------------------------
Report.............. FIN03-DTL KPMG DETAIL INCOME STATEMENT
-------------------------------------------------------------------------------
Print option........ 1 1=Printer
2=On-line
3=Printer and on-line
Print specification. N
Create work file.... N
Library/File name... __________ __________
Remarks............. _______________________________
Printer queue....... PRT01
Number of copies.... 1
Hold on spool file.. Y
Select to print, and to hold on spool file, then press enter. The report will run in batch.
Spool File
When it completes, key in WRKSPLF and press enter. Find the report, and key an 8 (attributes) in front of it. Make note of the job, user, job number, file, and file number for the next process.
Work with Spooled File Attributes
Job . . . . . . . . : LIN_REPORT File . . . . . . . . : RWRP166
User . . . . . . . : SHEILAV Number . . . . . . : 000002
Number . . . . . . : 867464 Creation date . . . : 11/22/13
Job system name . . : BART Creation time . . . : 13:45:49
Status . . . . . . . . . . . . . . . . : READY
Output queue . . . . . . . . . . . . . : PRT01
Library . . . . . . . . . . . . . . : QGPL
ASP file resides on . . . . . . . . . : 1
Form type . . . . . . . . . . . . . . : *STD
Output priority . . . . . . . . . . . : 5
Copies left to produce . . . . . . . . : 1
Total copies . . . . . . . . . . . . . : 1
Maximum records . . . . . . . . . . . : 100000
Number of separators . . . . . . . . . : 0
File becomes available . . . . . . . . : *FILEEND
Hold file before written . . . . . . . : *NO
Follow these instructions for each of the reports, and companies, needed. In the following instructions, ‘xx’ is the company code.
FIN03-DTL
CPYSPLF FILE(RWRP166) TOFILE(UP1480BFVA/AUDITREPF) JOB(867464/SHEILAV/LIN_REPORT) SPLNBR(2) TOMBR(xxFIN03)
CLRPFM UP1480BFVA/AUDREPOUT
OVRDBF FILE(AUDITREPF) TOFILE(AUDITREPF) MBR(xxFIN03)
CALL AUDREP01
DLTOVR FILE(AUDITREPF)
CPYTOIMPF FROMFILE(AUDREPOUT) TOSTMF('uwd staging/sheilav/fin03xx.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
FIN04
CPYSPLF FILE(RWRP166) TOFILE(UP1480BFVA/AUDITREPF) JOB(867464/SHEILAV/LIN_REPORT) SPLNBR(2) TOMBR(xxFIN04)
CLRPFM UP1480BFVA/AUDREPOUT
OVRDBF FILE(AUDITREPF) TOFILE(AUDITREPF) MBR(xxFIN04)
CALL AUDREP03
DLTOVR FILE(AUDITREPF)
CPYTOIMPF FROMFILE(AUDREPOUT) TOSTMF('uwd staging/sheilav/fin04xx.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
FIN05-DTL
CPYSPLF FILE(RWRP166) TOFILE(UP1480BFVA/AUDITREPF) JOB(867464/SHEILAV/LIN_REPORT) SPLNBR(2) TOMBR(xxFIN05)
CLRPFM UP1480BFVA/AUDREPOUT
OVRDBF FILE(AUDITREPF) TOFILE(AUDITREPF) MBR(xxFIN05)
CALL AUDREP02
DLTOVR FILE(AUDITREPF)
CPYTOIMPF FROMFILE(AUDREPOUT) TOSTMF('uwd staging/sheilav/fin05xx.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
To copy report definition from one company to another
CPYF FROMFILE(UP1480BFVA/ANORWL) TOFILE(UP1480BFUC/ANORWL) MBROPT(*ADD) INCREL((*IF RLRENO *EQ 'FIN05-DTL2'))
CPYF FROMFILE(UP1480BFVA/ANORWC) TOFILE(UP1480BFUC/ANORWC) MBROPT(*ADD) INCREL((*IF RCRENO *EQ 'FIN05-DTL2'))
CPYF FROMFILE(UP1480BFVA/ANORWD) TOFILE(UP1480BFUC/ANORWD) MBROPT(*ADD) INCREL((*IF RDRENO *EQ 'FIN05-DTL2'))
CPYF FROMFILE(UP1480BFVA/ANORWY) TOFILE(UP1480BFUC/ANORWY) MBROPT(*ADD) INCREL((*IF RYRENO *EQ 'FIN05-DTL2'))
KPMG Reports
Print reports (FIN03-KPMG and FIN05-KPMG) from Report Writer to have something to compare to.
Run query SHEILA1/AUDIT2013F to extract G/L records from ANOSTK.
Run query SHEILA1/AUDIT2013G, after changing fiscal year, to extract G/L records from ANOBOB.
Run query SHEILA1/AUDIT2013H, after changing fiscal year and calculation of total, to extract G/L records from ANOBAL.
Run query SHEILA1/AUDIT2013I to build file of G/L main descriptions.
Run query SHEILA1/AUDIT201JI to build file of G/L group descriptions.
Run query SHEILA1/AUDIT2013K to build file of G/L sub group descriptions.
Run query SHEILA1/AUDIT2013L to build file of G/L cost centre main descriptions.
Run query SHEILA1/AUDIT2013M to build file of G/L project descriptions.
Run query SHEILA1/AUDIT2013N to combine all these files into one.
CPYTOIMPF FROMFILE(QTEMP/AUDIT1) TOSTMF('uwd staging/sheilav/kpmg.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Copy previous KPMGyyyy spreadsheet, clear the first sheet, except for maybe headings, and copy this in. Sort by the G/L number.
Clear the entries in the ‘value’ column of the next two pages – do this manually, cell by cell so you do not clear the totals and sub totals. Fill in values with the current G/L amounts.
Balance to the printed reports. If they do not balance, check definition of the financial report.
Go brep Option 1 – Work with reports Find the report, and option 10=Lines Find the line that is the problem, and option 5=Display If the line is not defined here, press F8=Selection