Transcription
Using ACL ForJournal Entry AnalysisChristine PedroliSupervisor‐ Advisory Servicescpedroli@bswllc.com6 CityPlace Drive, Suite 900 St. Louis, Missouri 63141 314.983.12001.888.279.2792 www.bswllc.com
AGENDASt. Louis User Group Housekeeping ItemsUsing a Form to Perform AnalysisLedger AnalyticsAnalysis Results 2014 All Rights Reserved Brown SmithWallace LLC- 2 of 18 -
St. Louis User Group Housekeeping Items Feedback on 2014 Meetings and Training Ideas for Training Topics– Analysis Area Specific (e.g. T&E, Pcard, FCPA, etc.)– Continuous Monitoring Scripting Ideas for Meeting Topics– How do you use ACL?– ACL products– Presenting Analysis Results Volunteers for Meeting Presentations 2014 All Rights Reserved Brown SmithWallace LLC- 3 of 18 -
Using a Form to Perform Analysis Identify key input for analysis (rules a relationships Create a user friendly form and control form– Provide explanations/definitions for all input items– Utilize editing controls wherever possible– Pull import data into a control form for import 2014 All Rights Reserved Brown SmithWallace LLC- 4 of 18 -
Form Example 2014 All Rights Reserved Brown SmithWallace LLC2
Ledger Analytics1. Verify completeness of the ledger2. Identify entries posted on holidays3. Identify entries posted outside the audityear4. Analyze the j/e description for:– Related parties– Parties of interest– Unusual terms– Blanks 2014 All Rights Reserved Brown SmithWallace LLC- 6 of 18 -
Ledger Analytics5. Top 25 entries6. Identify entries to unusual/high‐riskaccounts7. Identify manual entries8. Outlier entries9. Average and Total activity/value by accountby month 2014 All Rights Reserved Brown SmithWallace LLC- 7 of 18 -
Ledger Analytics‐ Completeness (#1) Identify Balance Sheet v. Income Statement accounts– FORM INPUT (account number ranges) Import PY ending balance for all BS accounts Import CY ending balance data for all accounts(this may be in GL already for some data sets) Calculate activity for CY from GL data for all accounts Recalculate ending balance, compare recalculated toimported ending balance data and identifyincomplete accounts 2014 All Rights Reserved Brown SmithWallace LLC- 8 of 18 -
Ledger Analytics‐ J/E’s on Holidays (#2) Identify Holidays– FORM INPUT (dates) Compare entry date to holiday dates JOIN on entry date from GL to form inputtable 2014 All Rights Reserved Brown SmithWallace LLC- 9 of 18 -
Ledger Analytics‐ J/E’s Outside Year (#3) Identify date range for audit year– FORM INPUT (dates) EXTRACT IF NOT BETWEEN(j/e date field,beginning date, end date) 2014 All Rights Reserved Brown SmithWallace LLC- 10 of 18 -
Ledger Analytics‐ Description Analysis (#4) Related Parties/Parties of Interest/UnusualTerms– FORM INPUT (names and keywords)– JOIN on description from GL to form input table,or– EXTRACT RECORD IF FIND(form input field, in j/edescription field) Blanks– EXTRACT RECORD IF ISBLANK(j/e description field) 2014 All Rights Reserved Brown SmithWallace LLC- 11 of 18 -
Ledger Analytics‐ Top 25 & Unusual Accts Top 25 (#5):– Calculate absolute value of debit/credit– SORT and EXTRACT top 25 Unusual/High‐risk Accounts (#6):– FORM INPUT (account numbers/names)– JOIN on account number/name from GL to forminput table– Additional stipulations can be added to onlyidentify if it is a debit or credit to the account 2014 All Rights Reserved Brown SmithWallace LLC- 12 of 18 -
Ledger Analytics‐ Manual Entries (#7) Identify j/e sources that are used for manual entries– FORM INPUT (manual identifier) Compare j/e source to form input JOIN on j/e source from GL to form input table Additional considerations to scope results– Amount limitations (expect small or large to be unusual?)– Time stamp limitations– Account specifics (accounts that should never, or alwayshave manual entries) 2014 All Rights Reserved Brown SmithWallace LLC- 13 of 18 -
Ledger Analytics‐ Outliers (#8) Summarize GL data by account numberRun standard deviation for each accountExtract all outliers (more than 5 x’s )Additional considerations– Account expectations Do certain accounts alwayshave consistent or volatileactivity by nature? 2014 All Rights Reserved Brown SmithWallace LLC- 14 of 18 -
Ledger Analytics‐ Trends (#9) Create month data field– CMOY(j/e date, 9) Summarize on Month and Account– Total activity for each month– Number of entries each month Calculate average entry amount Consider analyzing debits and creditsseparately 2014 All Rights Reserved Brown SmithWallace LLC- 15 of 18 -
Analysis Results Summarize results into English JOIN back for entire entry If exporting, be sure to format dates andamounts, and add filters to data headers Consider if additional analysis filters will addvalue to results Consider graphs for presentation– Trends (line graph)– Outliers/Manual Entries (scatter graph) 2014 All Rights Reserved Brown SmithWallace LLC- 16 of 18 -
Sample Analysis SummaryJE ANALYSIS RESULTS SUMMARYYES, 55 records were part of journal entries that were made on a holiday. Go to JE On Holidays.No records were part of journal entries outside of requested journal entry date range.No records were part of journal entries that have a related party in the description column(s).YES, 491 records were part of journal entries that have an unusual word in the description column(s). Go to JE With Unusual Words.No records were part of journal entries that have a blank description.YES, 2 IDs were identified as entering/creating JEs. Go to JE Created By.YES, 88 records were noted to indicate gaps in journal entry file. Go to JE Gaps.No records were part of journal entries that have a duplicate unique ID.YES, 337 records were part of journal entries that have a line greater than or equal to ISI. Go to JE With Signif Line.YES, 94 records were identified with a debit or credit in excess of two times the standard deviation of entires to that GL Account. Go to JE Outliers.YES, 12 accounts were identified with ledger activity but no relating trial balance account. Go to JE No TB Acct.YES, 37 accounts were identified with missing activity in the general ledger. Go to JE IncompleteAcct.YES, 250 records were part of manual journal entries that adjusted Cash. Go to JE Manual Cash.No records were part of manual journal entries that adjusted Receivable.No records were part of manual journal entries that adjusted Inventory.YES, 727 records were part of manual journal entries that adjusted Other Asset. Go to JE Manual Other Asset.No records were part of manual journal entries that adjusted Land.YES, 542 records were part of manual journal entries that adjusted Payable. Go to JE Manual Payable.YES, 73 records were part of manual journal entries that adjusted Owners Equity. Go to JE Manual Owners Equity.YES, 72 records were part of manual journal entries that adjusted Revenue. Go to JE Manual Revenue.YES, 1,174 records were part of manual journal entries that adjusted Cost of Sales. Go to JE Manual Cost of Sales. 2014 All Rights Reserved Brown SmithWallace LLC- 17of 18 -
Questions?Christine PedroliSupervisor‐ Advisory Servicescpedroli@bswllc.com6 CityPlace Drive, Suite 900 St. Louis, Missouri 63141 314.983.12001.888.279.2792 www.bswllc.com
Using ACL For Journal Entry Analysis Christine Pedroli Supervisor‐Advisory Services cpedroli@bswllc.com 6 CityPlace Drive, Suite 900 St. Louis, Missouri 63141 314.983.1200 1.888.279.2792 www.bswllc.com
