Difference between revisions of "Information Systems:Auditors"
Jump to navigation
Jump to search
| (26 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
| + | ==New Process== |
||
| − | =Create Excel Files= |
||
| + | The old queries have been replaced with InfoNet programs that allow more flexibility, and that Finance can do themselves - maybe even the auditors can. |
||
| − | 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’. |
||
| + | ===Accounts Receivable=== |
||
| − | When updating ‘select records’ in query, don’t get mixed up between calendar dates and fiscal periods. |
||
| + | We want an Excel file of debtor number, name, account type, and balance as of the fiscal year end. Use [[Auditors - Accounts Receivable|Summarize A/R From Transactions in InfoNet]] to get this. This file will have more information that the auditors require, so you can remove some lines and columns. |
||
| − | Run the queries interactively, as they put files into library QTEMP. |
||
| + | Sort by 'Original Amount' and delete any lines with a zero balance. |
||
| − | In the CPYTOIMPF commands, change 'uwd staging/sheilav/’ to your own path, and YYYY to the closed fiscal year. |
||
| + | As only one summary level was requested, columns for the second and third levels can be deleted (C, D, E, and F). |
||
| − | Look at the files from the previous year to see how to format them in Excel. |
||
| + | The columns for 'transactions', 'remaining amount', 'banner', 'area', and address can be deleted. |
||
| − | ==Accounts Receivable== |
||
| + | ===Accounts Payable=== |
||
| − | Run query XX2480BP/AUDITAR01, after changing the ending period, to build summary file of A/R balances. |
||
| + | We want an Excel file of vendor number, name, account type, balance, and preliminary invoices. Use [[Auditors - Accounts Payable|Summarize A/P From Transactions in InfoNet]] to get this. This file will have more information that the auditors require, so you can remove some lines and columns. |
||
| − | Run query XX2480BP/AUDITAR02 to add customer information. |
||
| + | Sort by 'Original Amount' and delete any lines with a zero balance. |
||
| − | CPYTOIMPF FROMFILE(QTEMP/FILE2) TOSTMF('uwd staging/sheilav/arYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) |
||
| + | As only one summary level was requested, columns for the second and third levels can be deleted (C, D, E, and F). |
||
| − | 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’. |
||
| + | The columns for 'transactions', 'remaining amount', 'banner', 'area', and address can be deleted. |
||
| − | ==Accounts Payable== |
||
| + | ===Inventory=== |
||
| − | Run query XX2480BP/AUDITAP01, after changing the ending period, to build summary file of A/P transaction balances. |
||
| + | We want an Excel file of warehouse, item number, description, item account group, class, brand, quantity, unit cost, and extended cost. Use [[Auditors - Inventory|Year End Inventory in InfoNet]] to get this. This file will have more information that the auditors require, so you can remove some lines and columns. |
||
| − | Run query XX2480BP/AUDITAP01B, after changing the ending period, to build summary file of A/P transaction type ‘EIN’ (preliminary invoices in process). |
||
| + | ===Sales Order Shipping (First and Last of Year)=== |
||
| − | Run query XX2480BP/AUDITAP02, after changing the ending period, to build summary file of preliminary invoice balances. |
||
| + | We want to find the last 10, and first 10 invoices of the year. Use [[Auditors - Sales Order Shipping|Item Transaction Detail in InfoNet]] to get the order and pick list number for these. Then use [[Auditors - Pick List Status|Pick List Status]] to get the invoice and customer numbers. |
||
| − | Run query XX2480BP/AUDITAP03 to merge files and add vendor information. |
||
| + | ===Expenses=== |
||
| − | CPYTOIMPF FROMFILE(QTEMP/FILEU) TOSTMF('uwd staging/sheilav/apYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) |
||
| + | We want an Excel file of all expense transactions for the year. Use [[Auditors - Expenses|Extract G/L from Transactions]] to build this. |
||
| − | 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’. |
||
| + | ===Purchase Order Receiving (First and Last of Year)=== |
||
| − | ==Inventory== |
||
| + | We want to find the last 5, and first 5 purchase order received over the fiscal year end. Use [[Auditors - Purchase Order Receiving|Purchase Order Receiving (Last and First)]] to identify these, and get all the receiving lines into a spreadsheet. |
||
| − | Run query XX2480BP/AUDITINV01, after changing the period, to build file of onhand values. |
||
| + | ===Sales Comparison=== |
||
| − | CPYTOIMPF FROMFILE(QTEMP/INVENT) TOSTMF('uwd staging/sheilav/inventYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) |
||
| + | We want to build a spreadsheet for sales and cost of goods sold G/L accounts for the last day of the last three years. Use [[Auditors - Sales Comparison|Sales Comparison]] to get this. |
||
| − | 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’. |
||
| + | ===Trial Balance=== |
||
| − | ==Sales Order Shipping (Last and First Invoices)== |
||
| + | We want to build a spreadsheet of opening balances, period totals, and closing balances for all main accounts. Use [[Auditors - Trial Balance|Trial Balance]] to build this. |
||
| − | 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. |
||
| + | ===Journal Entries=== |
||
| − | * 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.** |
||
| + | We want a list of all journal entries (voucher type 50) done for the year. Use [[Auditors - Voucher Type 50|Voucher type 50]] to get this. |
||
| − | Run query XX2480BP/AUDITLF02, to link the pick lists to the order lines, then the order lines to the invoices. |
||
| + | ===Detail Behind Financial Reports=== |
||
| − | CPYTOIMPF FROMFILE(QTEMP/LSTFST) TOSTMF('uwd staging/sheilav/lstfstYYYY.csv') MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) |
||
| + | We want the detail of all the accounts that make up each line on the financial reports. To get this, use [[Auditors - Financial Reports|Financial Reports (Report Writer)]] |
||
| − | Open this file in Excel, add headings, then save to the correct file under ‘auditors’. |
||
| + | ==Superseded Process== |
||
| − | ==Expenses== |
||
| + | [[Information Systems:Auditors - Superseded Process]] |
||
| − | Run query XX2480BP/AUDITEXP01, after changing period range, to pull out expense G/L transactions. |
||
| + | [[Category: Auditors]] |
||
| − | 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 |
||
Latest revision as of 12:18, 30 June 2016
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
We want an Excel file of debtor number, name, account type, and balance as of the fiscal year end. Use Summarize A/R From Transactions in InfoNet to get this. This file will have more information that the auditors require, so you can remove some lines and columns.
Sort by 'Original Amount' and delete any lines with a zero balance.
As only one summary level was requested, columns for the second and third levels can be deleted (C, D, E, and F).
The columns for 'transactions', 'remaining amount', 'banner', 'area', and address can be deleted.
Accounts Payable
We want an Excel file of vendor number, name, account type, balance, and preliminary invoices. Use Summarize A/P From Transactions in InfoNet to get this. This file will have more information that the auditors require, so you can remove some lines and columns.
Sort by 'Original Amount' and delete any lines with a zero balance.
As only one summary level was requested, columns for the second and third levels can be deleted (C, D, E, and F).
The columns for 'transactions', 'remaining amount', 'banner', 'area', and address can be deleted.
Inventory
We want an Excel file of warehouse, item number, description, item account group, class, brand, quantity, unit cost, and extended cost. Use Year End Inventory in InfoNet to get this. This file will have more information that the auditors require, so you can remove some lines and columns.
Sales Order Shipping (First and Last of Year)
We want to find the last 10, and first 10 invoices of the year. Use Item Transaction Detail in InfoNet to get the order and pick list number for these. Then use Pick List Status to get the invoice and customer numbers.
Expenses
We want an Excel file of all expense transactions for the year. Use Extract G/L from Transactions to build this.
Purchase Order Receiving (First and Last of Year)
We want to find the last 5, and first 5 purchase order received over the fiscal year end. Use Purchase Order Receiving (Last and First) to identify these, and get all the receiving lines into a spreadsheet.
Sales Comparison
We want to build a spreadsheet for sales and cost of goods sold G/L accounts for the last day of the last three years. Use Sales Comparison to get this.
Trial Balance
We want to build a spreadsheet of opening balances, period totals, and closing balances for all main accounts. Use Trial Balance to build this.
Journal Entries
We want a list of all journal entries (voucher type 50) done for the year. Use Voucher type 50 to get this.
Detail Behind Financial Reports
We want the detail of all the accounts that make up each line on the financial reports. To get this, use Financial Reports (Report Writer)