NICK PANNERI, CFE
 
  • Home

Key Steps for a Successful Internal Audit Analytic Project 

6/30/2013

0 Comments

 
There are many challenges you may face when integrating analytic work into a COSO or process audit. These key steps can help ensure success:
Hold a ‘kickoff’ meeting as soon as the audit begins – It’s important to get the audit and analytic teams together to discuss expectations from the beginning. What general area will the analytics be in? What’s the anticipated timeline for delivering on an analytic? Will a connection to the data need to be set up and, if so, how long will it take? Will data validation be needed? 
Review the analytics request – Once the audit team has decided what analytics they need it’s time for everyone to get back together. Does the audit manager agree each analytic in the list will be helpful in meeting his/her objectives? Which analytics will be prioritized first? Which, if any, will need validation testing and how will that affect the timing of the deliverables? What will be the anticipated delivery date for each analytic?
Data validation planning – If a particular analytic is sensitive enough you’ll probably plan on performing some level of validation testing. This will likely be a team effort between the analyst and the auditor. Does the auditor have support from his/her manager or supervisor to devote time to the validation process? What validation will be performed (testing for completeness, reasonableness, reliability of computed fields, unmatched records if a join is involved)?
Delivering the analytic – Do the auditors want to use excel or ACL? Should a process flow or narrative be created to help the auditors articulate to the auditee how the data was obtained/analyzed? Does the SQL code or ACL script need to be archived for future audits? 
These simple steps will go a long way to ensuring the success of your analytic project!
0 Comments

Feds Lose Big from Lack of Fraud Analytics?

1/26/2013

0 Comments

 
Picture
Well, the article mentions an investigation by a US Attorney turned up a Texas death certificate from 1984. This seems to indicate there was a record of her death with the State of Texas (just waiting to be found by the SSA)!

In three minutes I found a record of her death at http://www.deathindexes.com/texas(check out the screen shot below) – and the search was free! There’s only 50 states. It sounds like the SSA could use some fraud analytics. How about setting up some “vital record” FTP feeds from the states and cross referencing it with the SSA database? They could even get fancy and throw in some data mining of social media sites (just in case the death doesn’t get reported timely but the family members tweet about it)!

CLICK HERE for the full story….>

That’s not all! The article reports the other recent occurrences listed below and that CNN recently estimated $40 million in SSA benefits have been paid to dead people!

Willa Mae Shaughnesy – $231,000
Thomas Parkin – $44,000
Judy Dinsmore – $75,000
Gail Andrews – $73,000
​
Total savings from an analytic initiative – $423,000……and counting. Now, how much have you paid in social security taxes this year?

Picture
0 Comments

Are Your Employees Selling Your Business’s Products on the Black Market?

1/20/2013

0 Comments

 
Hard to believe, but your employee’s could be stealing from you and selling the goods online. In the linked story below, an employee stole $376k of his employer’s property and sold it all on the black market.
Using the ‘Advanced’ search functions of sites like http://www.ebay.com and http://www.craigslist.org, you can do some basic checking to try and get out in front of this. The screen shot below is from the ‘Advanced’ search function at http://www.ebay.com and allows you to search for specific products near you. In this way, you can potentially uncover this type of fraud.
​
Do the search, you might be surprised what you find!
CLICK HERE for the story


Picture
0 Comments

Learn from this Employee Confession in 60 Seconds

1/17/2013

0 Comments

 
In the screenshot below (from http://www.krogerforum.com – a public website) an employee confesses specific tactics used to steal while on the job. This info could certainly be used by Kroger to develop better fraud prevention policies and/or training.
The lesson: search the web for employee posts about your company. If you’ve never done this, you might be missing out on potentially valuable information to assist in your fraud prevention program.
Try this Google search: “(insert your company name here) sucks”
(Awkward I know, but it works:)
You might be surprised what topics employee’s are comfortable discussing online.
​

Picture
0 Comments

Be a Rock Star at Your Employer, Find Rewards Fraud

1/12/2013

0 Comments

 
Picture
Apparently Rewards theft at Marriott has been going on for some time. In this separate incident, a hotel manager creates 6 fake Rewards accounts and uses them to steal Rewards worth more than $100,000.

An analytical test to compare Marriot Rewards account holder names to customer names would have likely flagged these transactions much earlier.

Does your employer have a rewards program? Run some analytics, stop the abuse and be a data rock star!

CLICK HERE for more details​

Google Search Term: Stealing Marriot Rewards

0 Comments

Fuel Card Programs

5/8/2012

1 Comment

 
Is your organization taking advantage of fraud detection analytics for your fuel card program? There are a lot of opportunities in this area, especially when it comes to fraud detection. All fuel cards should have controls placed on them to limit the number of transactions per day, dollars per transaction and/or times of day purchases are allowed. Some cards can even be restricted for usage at a particular gas station. Additionally, each employee should have their own unique 6+ digit PIN code. For audit trail purposes it is important employees do not share their PIN codes. You’ll want to work with the fuel card provider to ensure you are able to get detailed reporting on transactions, card controls and active cards/driver lists. Fraud detection techniques will vary depending on how the cards are used in your business. Some potential tests would be: 
– Purchases at odd hours of the night 
– Purchases closer to an employee’s home than to their work location 
– Fuel cards often require the user to enter their odometer reading at the pump. Use this information to ‘back into’ unusually frequent fill-ups based on miles driven and investigate anomalies 
– Develop reporting to look for unusual upticks in usage on a particular fuel card (aka, velocity reporting) 
– Combine the data from card control and transaction reports to identify fuel cards whose restrictions (i.e., transactions per day/dollar per transactions, etc) might be too ‘loose’ when compared to actual usage 
Occasionally, you might fall victim to fuel card skimming schemes (“white plastic”) in which magnetic codes are stolen by concealed readers placed on gas pumps. These types of fraud rings will hit hard and fast, often filling several large gas containers in one visit. You can identify these by looking for several back-to-back transactions of an even gallon amount (i.e., 100 gallons) which will correspond to the size of the gas containers they are using.
1 Comment

Using Excel to Uncover Potential Conflicts of Interest

5/8/2012

0 Comments

 
Picture
A nightmare for any organization is discovering they have been the victim of a fictitious vendor scheme.  In just the past several months, guilty pleas have been entered in two local cases (Cepia, Inc – think Zhu Zhu Pets – and Goodwill) with estimated exposures in the hundreds of thousands of dollars each.
The most basic step you can take to protect your organization from this nefarious activity is performing an employee/vendor address match project.  With any luck an employee setting up a fictitious vendor might send checks to their home address or the address of a beneficiary or emergency contact.  The good news is you do not need expensive tools such as IDEA or ACL to do these match ups.  With a little savvy, Microsoft Excel can be turned into a powerful tool for automated fraud detection.
​

The Basics:
The key to a successful employee/vendor address match project is understanding address fields in the HR and Accounts Payable systems are ‘free form’.   They are likely to contain abbreviations, special characters and even misspellings.   For example, an Accounts Payable clerk will likely input an address into the Accounts Payable System slightly differently than an HR employee would into the HR system: 

Example:  1200 Main Street       vs.        1200 Main St.

To successfully match these addresses simply go through the three steps listed below to ‘clean’ them into a standardized form:
                     Step 1) Change all letters to uppercase
                    Step 2) Remove all spaces and special characters
                    Step 3) Shorten the address to its first 9 characters
The addresses above will now look like those below and they can now be reviewed for matches.
​

 Example:  1200MAINST         vs.        1200MAINST

Automating in Excel:
These steps are easy enough to perform manually with a handful of addresses.  But what if you have hundreds of employees and vendors to match?  Or thousands?  This is where Microsoft Excel macros come in handy.  Using Microsoft’s built in Visual Basic editor you can automate Excel to perform the three steps mentioned above on all of your addresses in a visually appealing way.  Better than that, you can re-run the macro any time you want with new or updated addresses at the click of a button. 
The screen shot below is a snapshot of an Excel macro I built to illustrate this point.  The ‘Tests’ in the ‘Evaluation Area’ correlate to the three steps mentioned above.  If an address match is found between an employee and vendor the macro will display a green check mark and list the full address and other details in the ‘Potential Matches’ area.  If there is no match the macro displays a red ‘X’. 


​

​Your Next Steps:

Obviously, creating a macro like this takes a little bit of practice.  The good news is everything you need to know can be self taught using online resources.  I recommend doing an internet search on ‘Microsoft Excel macro tutorial’ to find resources and you tube video’s that can get you started.  CLICK HERE to watch a video on how this excel macro works here.  Good luck with your fraud detection projects!

0 Comments
Forward>>
    The views expressed in this blog are mine and do not necessarily reflect the views of Enterprise Holdings, Inc.

    Archives

    January 2017
    December 2016
    September 2016
    August 2016
    February 2015
    July 2014
    June 2014
    July 2013
    June 2013
    January 2013
    May 2012

    View my profile on LinkedIn

    RSS Feed

  • Home
  • Blog
Powered by Create your own unique website with customizable templates.