1

Jumping on the Analytics BandwagonHow to Utilize the Data Within Your Organization’s Systems to Gain Valuable Insight

• Data vs Information• Garbage In – Garbage Out• Analytics to Intelligence

2

2

Background

4

3

Data vs Information

Data

Data is unprocessed facts and figures without any added interpretation or analysis. 

• “We serve 500,000 constituents.”

Company Data Examples• Journal entries• Customer lists• Vendor lists• Invoices• Expenses• Employee listings

4

Information

7

Information is data that has been interpreted so that it has meaning for the user. 

• “The number of constituents that we serve has increased from 300,000 to 500,000 in 3 years" gives meaning to the data.

Company Information• Financial statements• A/R Aging• A/P Aging• Sources of Funding• Employee Retention

• Data is raw, unorganized facts that need to be processed. Data can be something simple and seemingly random and useless until it is organized.

• When data is processed, organized, structured or presented in a given context so as to make it useful, it is called information.

8

Information

5

9

Information from DataAny thoughts on what this is?

Garbage In – Garbage Out

10

6

GI-GO

• Computers, since they operate by logical processes, will unquestioningly process unintended, even nonsensical, input data ("garbage in") and produce undesired, often nonsensical, output ("garbage out").

• Data is not entered into the system at all

12

GIGO

PO Analysis• 52% of records do not have an associated PO, which totals to

$28m• The next most used PO was PO 100, with $13m in total expenses,

and 3% of all POs identified with an expense• PO 210 had the most associated expense records, 2,397 for $10m

Note: Names, dates, and amounts have been modified for presentation purposes.

7

• Data is not entered in correctly

• The purpose/ usage of the field is not defined

13

GIGO

Causation & Correlation

• Correlation: a relationship between two variables

• Causation: an act that occurs in such a way that something happens as a result

14Correlation does not imply causation

8

15

Correlation Analysis

Analytics to Intelligence

16

9

Historical• Descriptive Analytics: condense large amounts of

data into smaller, more useful nuggets of information• Diagnostic Analytics: determining if the correlation is

causal

Forward Looking• Predictive Analytics: probability of different

outcomes• Prescriptive Analytics: integrate tried-and-

true predictive models into our repeatable processes to yield desired outcomes

18

Types of Analytics

10

• Review the data by skimming through it• Performing various trial summarizations• Identify anomalies or odd data

19

Review the Data

Differences between Transaction Date and Bill Date range from ‐302 days (Transaction Date is 

12/31/2011 & Bill Date is 2/28/11) to 654 days (TRANS Date is 8/13/10 & Bill Date is 6/7/12)  

Differences between Transaction date and Activity Date range from ‐745 days (TRANS Date = 

5/23/13 & Activity Date is 4/28/11) to 360 days (TRANS Date is 12/31/11 to Activity Date 

12/31/12) 

Differences between Bill Date and Activity Date range from ‐798 days (Bill Date of 7/31/13 to 

Activity date is 5/12/11) to 300 days (Bill Date of 2/28/11 to Activity date is 12/31/11) 

Reversing Entries. The amounts do negate out, however, the notation under the reversing entry is ‘update with actual invoice amount’, but the Transaction Date and Activity Date for both the entry and the reversing are the same dates. 

TRANDT BILLDT ACTVDT VEND_NAME INVOICE Total Descr

1/18/2010 2/18/2010 1/18/2010 ABC Company PrePay 2,000,000 Prepay to Secure Date

1/18/2010 2/18/2010 1/18/2010 ABC Company PrePay ‐2,000,000 Update w/actual inv amt

Accounts Payable - Input

• Check Register• Vendor Master• Invoice Master• Employee

Master

11

• Check Gaps and Duplicates

• Vendors– Lacking Key Data– Duplication of Key

Data– Employees Set up as

Vendors• Vendor Payments

– Dormant to Increased Activity

– Rounded Amount Payments

• Duplicate Payments– Vendor, Amount – Vendor, Amount,

Date• Cash Payments• Payments below

threshold approvals• Vendors with

Consecutive Invoice Numbers

• Trend Analysis on Vendor Payments 21

Accounts Payable Analytics

22

AP ExampleCheck Register GapsGap_Start_

Check

Gap_End_

Check

Number_Items 

_Missing

178140 178142 1

178142 178144 1

178260 178262 1

178386 178388 1

178422 178424 1

178466 178468 1

178472 178474 1

178506 178523 16

1782523 178661 137

VendorID VendorTaxID VendorName

Vendor 1099 

Status

Vendor 

Status  Check Amount 

NumberOf 

Checks

85 00‐0000000 Company A Y T 164.44               1

456 00‐0000000 Company B Y T 500.00               1

356 Company C N 1,283,504.16    53

4 00‐0000000 Company D N T 3,687.63            7

36845 00‐0000000 Company E N T 20,487.30         107

6 00‐0000000 Company F N T 19,672.59         103

5674 00‐0000000 Company G N T 9,562.00            3

7897 00‐0000000 Company H N T 61,270.41         390

4667 Company I N 255.00               3

341 00‐0000000 Company J N T 4,199.52            1

99500 Refunds A N 25,633.71         70

9999 00‐0000000 A/R Refunds N T 303,326.44       244

Missing Tax ID

VendorID VendorName  CheckAmount  #Of Checks VendorAddr1 VendorCity VendorState

1 Company A 7,896.29            3 PO BOX 8 Anywhere TX

2 Company A 74,199.82          16 PO BOX 1 Anywhere TX

56 Company B 756.00               4 PO BOX 3 Anywhere TX

77 Company B 186.00               1 PO BOX 3 Anywhere TX

9879 Company C 57.00                  1 1100 Main Here TX

23 Company C 675.00               2 PO BOX 3 Here TX

345 Company D 5,372.00            3 PO BOX 141 Here TX

456 Company D 11,743.00          1 PO BOX 149 Here TX

234 Company E 1,500.00            1 PO BOX 32 Here TX

8421 Company E 52,185.14          9 101 15 Here TX

215 Company F 574.80               2 PO BOX 30 Anywhere TX

8753 Company F 30,314.65          19 PO BOX 26 Elsewhere WA

Multiple Vendor Records for 1 Vendor

VendorID VendorName CheckAmount 2016 total NumberOfChecks

A Sparrow 27,647.51                              28

B Eagle 13,634.83                              11

C Mockingbird 6,632.05                                15

D Hawk 101,411.09                            12

PY Zero Payments, CY Payments

12

23

Payroll

Noted 1 employee record with with no name or address. No payroll disbursements were made. 

15 employees who met scope of GrossPay>=$10,000 and Deduction Ratio < .1

5 employees with PO Box as their addresses.

Only 10 salaried employees who were paid during the year. Calculated an average hourly rate for these EE and they were all paid a consistent rate for all pay periods.

Purchase Cards

24

Several charges on the same day for an amount slightly under the single transaction limit.

Transactions  occurring on weekends or holidays. 

Duplicate transactions made by the same user on the same day

Vendor Analysis.

13

25

Tools

• Microsoft Excel • Microsoft Access

Database• ACL and IDEA

Questions?Reema Parappilly, CISASenior Manager, IT 

Advisoryreema.p@weaver.com

832.320.3254