Information Systems:Auditors

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