Auditing How To: Document Sample Selections in ACL

Hello again, fellow data wonks and wonk wannabes!

Last time, we discussed random sampling in Excel and what factors you should consider when determining your sample size. (Hint: 30 is generally large enough, but not in all cases)
One of the downfalls of Excel is the lack of an audit trail. In these examples, we will provide a high-tech and low-tech way to document your sample selection process in detail. First up, ACL.

The High-Tech Method

I am working with fictional data below. As you can see, our population contains 36 counties. Make note of your population size when working in ACL as this will be important later on. You can count a table by using the shortcut “CRTL + 3”.
ACL sampling pic 1

Next you select the “Sampling” menu and click on “Sample Records”. This also has a shortcut, which is “CTRL + 9”.

ACL sampling pic 2

Change “Sample Type” from “MUS” to “Record”. Then click on “random” on the middle left of the interface. Enter in the “size” of the sample. I pulled a sample of 10. The “Seed” allows you to document and repeat a random sample. Any number will do – just pick the first one that comes to mind.

I know what you’re thinking. However, just because something is repeatable does not change the fact that it is random.

Enter in the “population” we recorded earlier, then define the table name you want the sample sent to.

ACL sampling pic 3

There you have it; a random sample of 10 counties in Oregon, with a full log file and repeatable methodology in case you ever get questioned about how you pulled your sample.

The Low-Tech Method

If you are still hung up on what a seed has to do with random sampling, the low tech way will make it clear to you. Below we have a copy of a random number table. You can find these in the appendix of most statistics textbooks or via Google.

ACL sampling pic 5

The “seed” tells you where to start on the table. If I have a seed of 1, we would start at the 1st number, which also happens to be a 1. A seed of “3” start at the 3rd number in which in this case is 4. This is what makes it repeatable. Our population was 36, so to pull a sample we will be looking at sequences of 2-digit numbers. I will use a seed of “3” and pull just three samples.

In the random number table to the right, I’ve crossed out the first two numbers since our seed was “3”. ACL sampling pic 6Starting with the 3rd number, I looked at each 2 digit sequence. If the number fell between 01 and 36, it was a valid random sample and highlighted in green. If the number was above 36, I moved to the next sequence. Also, if repeats are not allowed in your sample you would move to the next number as well (e.g. 11 would be my next sample, but it was already pulled so I would skip over the repeat). Keep moving right and down until you have pulled the full sample.

In this case, my sample was 01, 11, and 20 or Baker, Gilliam, and Lane (shown below). Functionally, this manual low-tech process is identical to what ACL does.

ACL sampling pic 7

You can apply the Random Number table approach to extremely large files. If you had 1,000,000 records you would look at 7-digit sequences rather than 2-digit shown above.

And there we have it! Two useful methods for documenting sample selection.

If you are stuck on a project in ACL, Excel, or ArcGIS please submit your topic suggestions for a future blog post.

Auditors at Work Data Wonk Featured How To New Audit Release

Oregon State Lottery: Unclear Laws May Let Prohibited Casinos Operate in Oregon

Executive Summary


The Oregon Constitution prohibits casinos, but enforcement is difficult because “casino” has not been clearly defined. The Oregon State Lottery’s current rules and practices may not be detecting retailers that receive most of their income from video gambling machines. We recommend Lottery seek legislation to define “casino” and take several steps to improve compliance.

 

The Oregon State Lottery offers a variety of gambling options including Powerball, Mega Millions, and Oregon games: Megabucks, Raffle, Keno, Lucky Lines, Win for Life, Pick 4, Scratch Its, and video gambling machines.

Machines are the largest annual revenue source with average net receipts of $727 million over the last five state fiscal years. Net receipts as used in this report are dollars deposited in machines minus dollars won. During fiscal year 2014, machines generated net receipts of $743 million, of which $178 million was paid in commissions to retailers and the remaining $565 million was used for state purposes. As of December 2014, there were about 2,300 retailers operating nearly 12,000 machines.

Lottery3The Oregon Constitution prohibits the operation of casinos in the State of Oregon, but does not provide a definition for a casino. In 1994, the Oregon Supreme Court concluded that “voters intended to prohibit the operation of establishments whose dominant use or dominant purpose, or both, is for gambling.” Neither the court nor the legislature has defined the terms “casino,” “dominant use,” or “dominant purpose.”
Lottery has established administrative rules to enforce casino prohibition. Under its current rule, retailers are not casinos if their non-lottery sales are at least 50% of their total income. For retailers whose non-lottery income may be less than 50%, the rule allows the Lottery to consider additional factors such as a visual inspection to determine if a retailer is operating as a casino.

In practice, Lottery is satisfied if a retailer’s facility does not look like a casino, so they perform no review of retailer income.

Lottery has identified Limited Menu Retailers as Lottery1 posing a higher risk of operating as a casino because they tend to have limited sales of non-lottery products, thus, relying more on Lottery income for their business. In 2014, 234 Limited Menu Retailers operated 1,305 or 11% of the nearly 12,000 machines in use and generated about 21% or $158 million in machines net receipts.

We focused our procedures on the higher risk Limited Menu Retailers and found that Lottery’s enforcement practices may not adequately address the Oregon Constitution’s casino prohibition. We followed the procedures prescribed by Lottery’s current enforcement program and found the program does not detect all retailers whose dominant income is gambling.
While most of the Limited Menu Retailers we reviewed did not have the appearance of a casino, over half of these retailers derived more than 50% of their income from machine commissions. Many of these Limited Menu Retailers had difficulty generating non-lottery sales sufficient to comply with the income threshold.

Recommendations

To help Lottery strengthen existing controls and to facilitate compliance with casino prohibition, we recommend Lottery management work with the legislature and other stakeholders to develop a clear and enforceable definition of a casino that aligns with the 1994 supreme court ruling of dominant use/dominant purpose. Lottery should verify gross sales reports when using them to perform an income analysis. For retailers challenged with meeting the 50% non-lottery income threshold, Lottery should evaluate whether removing a machine would enable the retailer to comply with the dominant use/dominant purpose court ruling.

Agency Response

The agency response is attached at the end of the report.

Read the full report

Featured New Audit Release Performance Audit

State Data Center: First steps to address longstanding security risks, much more to do

Executive Summary


Over the last nine years, security weaknesses at the state data center have put confidential information at risk. These weaknesses continued because the state abandoned initial security plans, did not assign security roles and responsibilities, or provide sufficient security staff. The Governor, Legislature, and Chief Information Officer have taken the first steps to fix these problems, but the solutions will take time, resources, and cooperation from state agencies..

Critical security issues were never resolved at the data center

Data CenterData center management and staff are meeting day-to-day computing needs of state agencies relying on its services. However, critical security issues identified throughout the past nine years were never resolved.

Security problems affect multiple components of the data center’s layered-defense strategy intended to make it more difficult for unauthorized users to compromise computer systems.

These weaknesses increase the risk that computer systems and data could be compromised, resulting in leaked confidential data such as social security numbers and medical records information.

Data center was never fully configured for security

Management got a good start on security planning, but during data center consolidation management abandoned the plan thinking they would complete some steps at a future time. Once the data center became operational, staff was overburdened and unable to make meaningful progress toward resolving critical security issues or implement security systems they purchased.

These adverse conditions continued because management did not assign overall responsibility or authority to plan, design, and manage security. In addition, they did not provide the necessary staffing to implement and operate security systems.

First steps have been taken to resolve longstanding data center problems

Data Center 2The Govenor, Legislature and Director of the Department of Administrative Services took steps in the last six months to address data center staffing and organizational issues.

Two key steps that occurred were the state Chief Information Officer (CIO) became responsible for data center operations and the state Chief Information Security Officer was moved to the data center and tasked to oversee its overall security function.

These actions increased management’s focus on security at the data center. However, it will take additional time, perseverance, significant resources, and cooperation to resolve all known weaknesses.

Some computer operations were stable but disaster recovery was only partially tested

Data Center 3Apart from security, data center staff provides important operational support to agencies, including routine backups and monitoring computer processing. Data center staff made significant strides to resolve prior disaster recovery weaknesses identified by earlier audits. Their innovative approach was to partner with the Montana State Data Center to establish an alternate site to store and process data.

However, additional work needs to be done to ensure data at that site is secure, update recovery plans, and test the system.

Recommendations

We recommend agency management take steps to reconfigure data center security to provide the layered-defense strategy needed to protect state data systems. To accomplish this, management should clearly define security roles, responsibility and authority to carry out the plans and provide sufficient staff.

We also recommend management update and fully test disaster recovery plans and ensure data is secure at the remote site.

Agency Response

The agency agreed with all of the audit findings and recommendations. The response includes specific plans to correct longstanding security weaknesses and improve overall security organization, plans and staffing.

Their full response is attached at the end of the audit report.

Featured IT Audit New Audit Release

Trust and Trustworthiness

As auditors, we strive to ensure that government functions fairly, efficiently, and effectively.  Auditing has long been one of the methods used to ensure that government is performing to expectations, and is part of a broader system of accountability that minimizes incidences of corruption and the misuse of public funds.

Despite these measures, it is frequently reported that trust in government has dropped to historic lows. State and local governments have retained higher levels of public trust than the federal government, but they have also seen declines in reported public trust since hitting a peak in 2001.

But is reported public trust a true indicator that something has gone wrong, or right? Is it something that we as auditors need to keep in mind as we go about our work?

In the following video, Onora O’Neill discusses trust, and it’s often ignored cousin, trustworthiness.

Accountability and Media Featured

So that’s how you do a random sample in Excel

We’ve all been there. The boss shows up and says “I want you to select a random sample of files for the audit”. The boss leaves and you frantically begin searching for your old college textbooks.
Fear these technical challenges no longer. The Oregon Audits blog will be rolling out new posts covering practical and useful audit tools. Random sampling will be our first topic, but if you have any requests please don’t hesitate to contact us.

What the heck is random sampling?

Random sampling is useful to gain an understanding of a population without examining every file. By randomly selecting our sample, bias is also eliminated because every “file” has an equal chance of being selected. One word of caution though, if you are trying to look for outliers you will need a large sample size.

That begs the question: How big of a sample do I need to take? The short answer: 30 is usually good. If it is a simple test and not the critical element of your finding, 30 should cover you almost every time.

The longer answer is it depends. You need to consider your objectives, how confident you want to be about your results, how much margin of error is tolerable, and how big and varied the population is. More confidence requires larger samples. Less margin of error also increases sample size. Populations that are less uniform (have higher standard deviations) require larger samples too. And if this is a critical element of your finding, you need even more.

This is a handy online calculator for calculating the sample size needed to estimate the average of a population. Older textbooks like this one are great office resources (this one is super easy to follow). Better yet, it sells for about $10 making it a steal.

Excel: The easy way to pull a random sample

If the population you are reviewing is not numbered, you will need to create an index number for each file.

Excel has a built in random number generator. By using “RandBetween()” we can generate our sample. Enter “1” or the lowest possible index number for the bottom, and the largest possible index number for the top. In this scenario, I will use 1 and 100.

how to 1

Once you have your function looking like this how to 2you copy the formula into other cells. I am pulling a sample of 12, so I will drag the formula down 12 cells.

how to 3

You will note right away that each time you change something on the sheet, the numbers change. So if you want to lock in a sample, you need to copy the cells with the “randbetween” function and paste them as “values”.

how to 4

I prefer to paste over the cells I just copied.

Here’s the sample I got:how to 5
If you come across a duplicate number, you will need to add another row or replace the duplicate with a new “randbetween” function.

Pitfall: Weak audit trail

One of the drawbacks of Excel is that the audit trail is weak. The documentation you have are numbers in a spreadsheet that you could have easily entered manually. If you are working on a piece of evidence that is critical, you will probably want more documentation on how you arrived at your sample.

Our next post will cover how to document a random sample using technology such as ACL and how to document it the low-tech way using a “random number table”.

 

 

Data Wonk Featured How To

Auditors at Work: Spotlight on Sandy Hilton

Once a quarter we will be discussing the wonders of the world of auditing with (you guessed it) actual auditors! Our Summer Spotlight fell on Sandra Hilton, Performance Audit Manager with the Oregon Audits Division, and in her free time, newly adopted Cat Assistant.

What should we call you?
Call me Sandy.

How long have you been an auditor?
I’ve been an auditor for 26 years. I now work primarily on the performance side, but over the course of my career about half of that time was spent working on financial audits and hotline related work.

What led you to auditing?
I wanted to get my CPA certificate! Two years of relevant experience was required. That was the original reason I applied for the job as a staff auditor; however, once I got into it I found that I really, really liked it. I’ve been here ever since.

Many people, upon hearing the word ‘auditing,’ may assume that the job is just deadly dull. Is auditing boring?
I am never bored. Auditing is exciting! There are so many different things to look at, and it’s important work that you get to take on with a whole team. Teamwork, among other things, makes what we do interesting. Of course, after a few rounds of reading the same audit report, it does have its dull moments…

Why is your job important?
Because we can make a difference. We push things along- changing the course of a program or agency by incremental degrees and effecting some substantial long term changes. Similar to altering the trajectory of a rocket, tiny degree shifts make huge differences down the line. Audits build upon previous audit work and are often interconnected. We help the agencies and people we work with know which ‘tweaks’ can make a meaningful difference to achieving their goals.

Any personal victory stories, or tales of challenges overcome and differences made?
The TANF Audit we released a few years ago issued a number of recommendations that DHS used to  overhaul an underperforming program. In following, the most recent Governor’s Budget added $30 million to redesign and improve the program. Some of that money went to investments in support services and smoothing the transition for families off the ‘welfare cliff.’

Any auditing horror stories?
Not exactly a horror story, but it was definitely amusing. Years ago I did a site visit where I saw a safe with an ‘Open’ sign hanging on it. Apparently the safe was difficult to open, so the agency in question just left it open and put a double-sided ‘open-closed’ sign on the front. The safe was full of blank checks, and even had a signature stamp machine. Combined with the lack of segregation of duties and easy access to the safe, it was definitely a ‘one-stop’ shopping opportunity!

What do you do when you’re not auditing?
My number one hobby is gardening, and I enjoy feeding birds as well. I also adopted a cat recently- well, he adopted me. I’ve outfitted him with a break-away collar and a bell to warn the birds that a cat is around. I really enjoy being outside and seeing the new plant growth. When I’m inside I play the piano, and I am very interested in tracking genealogy.

Cats or dogs, and why?
I always thought I was a dog person. I love the affection. Cats are more independent. My husband had a cat before we were married, so I guess you could say I married the cat, too. I’ve learned to respect cats, and I respect them on their terms. I grew to understand and love our first cat. This new cat has also wiggled his way into my heart.

Any words of advice for new auditors?
That’s a tough one! But here goes… Be open to learning. Don’t be close-minded to ideas. Cultivate an attitude of willingness to hear different perspectives- it will help you realize the richness of what we do. As people we tend to focus a lot on ‘what we like.’ You may get a lot out of hearing stuff from people that you may not agree with. Be willing to learn from anybody, no matter who they are. Also, cut each other some slack. You’ll never know everything.

Final thoughts?
I’m really proud of our work here. I want to leave the world a better place, and here you have the opportunity to have a positive impact. That drives my work. When you get a whole group of smart people passionate about making a difference together, you can make change happen.

Auditors at Work Featured

Auditing critical state information systems: Behind the Scenes

The Legislature just approved our request for two more IT auditors to increase our ability to examine the thousands of IT systems in the state. We now have three teams of IT auditors- a 50% increase over the previous two! We make the best of our limited resources by focusing our skilled professionals on the systems most critical to the finances and operations of state government.

We will soon start recruiting for more IT auditors so if you’re interested watch the Secretary of State website in August when applications open.

OurNeal IT Audit Manager, Neal Weatherspoon, was recently featured in the Summer 2015 newsletter of the Willamette Valley chapter of ISACA, an association of IT audit and security professionals.

Auditors at Work Featured IT Audit