Metro Auditor: Nature in Neighborhood grant program could improve performance measurement

Last week, the Metro Auditor’s Office published its latest audit report on the Nature in Neighborhoods grant program. With more than $18 million in grant awards since 2006, the program has funded habitat restoration, land acquisition, education, and trails development projects in the Metro area.

The goal of the audit was to check whether the grant program has been well-managed. This is important for Metro taxpayers because, as auditors everywhere emphasize, a well-run program improves accountability and the likelihood that program goals are met.

In their review of Nature in Neighborhoods, Metro auditors found that the program follows some of the best practices for effective management in the grant-making world.

metro audit 12.15

However, they also found that Nature in Neighborhoods could see improvements in some areas, including measuring results and grant monitoring. In particular, the program has assessed the performance of some grants, but does not have a fully developed performance measurement system that can allow managers to evaluate the project’s impact as a whole.

The auditors at Metro offer some great recommendations to improve the grant program. For example, they recommend establishing targets for each of the program’s goals and developing a system of indicators to measure grant outcomes. This may be easier said than done. However, a solid performance measurement system can be a valuable tool for Metro program managers to see where the program is meeting targets and having a positive impact. It can also help identify areas in need of improvement and where Metro’s efforts should be focused.

Read the full audit report on Metro’s website. And if you get tired of reading about audits and best practices for effective grant management (although, why should you be?), check out the cool videos about some of the projects sponsored by the Nature in Neighborhoods program (and watch one below!). The videos certainly inspired this auditor to get up and go for a walk in the forest!

Featured New Audit Release

Audit Release: State Agencies Respond Well to Routine Public Records Requests, but Struggle with Complex Requests and Emerging Technologies

Executive Summary


Oregon state agencies respond well to most public records requests for routine information, but the infrequent complex requests produce challenges. As a result, some requesters believe that agencies deliberately discourage, delay, or block the release of public information.

The Department of Administrative Services should provide guidance and training to help agencies develop procedures, and agencies should create timeliness goals for responding to requests. Better monitoring, consistent fees, use of technology, and third-party mediation could also help with the retention and disclosure of public records and improve trust in Oregon’s government.

Read the full report.

Oregon’s public records law was enacted in 1973. Known primarily as a law of disclosure, the law grants all citizens within the state of Oregon the right to inspect all records – with some exceptions.

When the law first passed, it included 16 classes of records that could be exempt from disclosure for a total of 55 exemptions. Changes and revisions since that time have raised the total number of exemptions in Oregon law to more than 400. The intent, however, remains the same: that Oregon’s government is accessible and transparent to its people.

For our audit, we examined nine agencies of varying sizes and missions to capture a fuller picture of public records in Oregon state agencies. The nine agencies were:

  • The Department of Human Services
  • The Oregon Employment Department
  • The Department of Environmental Quality
  • The Public Employees Retirement System
  • The Oregon Liquor Control Commission
  • The Oregon Department of Education
  • The Oregon Real Estate Agency
  • The Oregon State Board of Nursing
  • The Board of Parole and Post-Prison Supervision

Agencies handle routine requests well, struggle with complex ones

We found that public records requests generally fall into one of two categories. The first is routine requests, or common requests for information that agencies have easy and ready access to. These requests, which generally make up 90 percent or more of an agency’s total requests, can be fulfilled at little to no cost and within a couple of weeks.

The other category is non-routine or complex requests. These are voluminous in scope, ask for “any and all” information, or are otherwise complicated for an agency to complete. These are the requests that can take weeks or months to fulfill and often at a high cost.

In the selected files we reviewed, we found no evidence to suggest that agencies were regularly taking an unreasonably long time, or charging unreasonably high fees, to respond to records requests. But when agencies struggle to respond to complex, non-routine requests, it can foster suspicion and distrust, which in turn can undermine the credibility and transparency of both the agency and Oregon government.

To address this distrust, some states and provinces have established a neutral, third-party entity that helps mediate disagreements between requesters and agencies. An ombudsman or commission can help determine when a request is too broad or when an agency is taking an unreasonably long time to respond. Oregon has no such mechanism. The Attorney General’s role is limited to denials based on exemptions and fee waivers.

Agencies retain public records longer than required

It is important that agencies properly retain and manage their public records so they can be efficiently located and disclosed in response to a records request. To do this, agencies must follow their retention schedules – guidelines, created and authorized by the Archives Division, that determine how long certain records must be kept before they are destroyed or transferred to the State Archives for permanent retention.

But we found that agencies are keeping too many records for too long, resulting in a large volume of information. Some employees are too cautious about accidentally deleting or losing track of a public record, and so have a tendency to “keep everything.”

We found that better management tools and specific training on the issue of record retention may help state employees better manage records. This can reduce the volume of public information statewide and assist agencies to more efficiently respond to public records requests.

Exemptions remain an issue and may require a closer look

Exemptions – those instances in which a record may be exempt from disclosure – make up a major portion of Oregon’s public records law.

Agencies generally understand which exemptions most commonly apply to the records in their care. But due to the sheer number of exemptions in the law, including how they are worded and where in statute they are located, staff sometimes must consult with experts or the Department of Justice.

There is a perception among some requesters that agencies inappropriately use exemptions to block the release of public information. Most of Oregon’s exemptions are applied at the agency’s discretion. After weighing the public interest, these records may be disclosed even if an exemption applies. The exception is confidential information, which is legally prohibited from release.

These issues regarding exemptions are not new. After a national report gave Oregon a failing grade in government transparency eight years ago, state officials closely examined the law and accepted feedback from requesters and public officials. Their findings, published in 2010 as the Attorney General’s Government Transparency Report, found that “Any meaningful overhaul of Oregon’s public records law must reorganize and make coherent sense of the numerous exemptions.”

A bill was subsequently introduced in the 2011 legislative session to address some of these recommendations, but it failed to pass. A task force was recently convened by the Attorney General to examine in greater detail the issues of exemptions in Oregon law.

Variations in responses frustrate some requesters

Requesters expect their government will be transparent and open, that fees charged for requests will be reasonable and records will be made available as quickly as possible. They expect agencies that fail to do so will be held accountable.

But variation among agencies’ responses to records requests – in both fees and timeliness – can lead to confusion and frustration among requesters when they are not sure what kind of response to expect.

Agencies charge differing fees to provide public information. This variation extends to both the fees for copying costs and the charge for staff time to respond to a request. Agencies charge anywhere from $0.05 to $0.25 per page in copying costs, and from $15 to $40 per hour for staff time.

We also found a time variation among agencies in responding to requests, due largely to the differences between routine and non-routine requests. First, agencies have varying internal guidelines for what it means to be timely, if they have any internal guidelines at all. Second, timeliness depends largely on the type of request an agency receives. We found that routine requests were fulfilled within 14 days, while non-routine requests could take upwards of 265 days to fulfill.

We saw no evidence to suggest that adding a specific deadline in law would positively affect agencies’ abilities to respond to requests in a timely fashion. But agencies that set internal guidelines or goals to respond to

requests hold themselves accountable to requesters while maintaining the flexibility provided in Oregon law.

Agencies are not keeping up with changing technologies

Oregon’s public records law was updated in 2011 to extend the definition of a public record to electronic or digital messages. Agencies have taken a longer time to update their own policies to include emerging technologies such as email, text, and instant messages.

More than half of the agencies we examined had policies to address email as it relates to public records. But only one agency had specific language to address the use of a personal or private email account in conducting the public’s business. Only one agency had a policy to address the use of instant messages, and no agencies had policies regarding text messages, as public records.

A few agencies have adopted policies to address social media, which appear to draw language from the Social Networking Media guide provided by the Department of Administrative Services.

Technologies like those mentioned above have changed how government and its agencies communicate with the public. Technology can also help agencies improve transparency by being proactive and making information available online. Several agencies have done so with commonly requested information, which can help reduce the overall number of public records requests.

Recommendations

Our recommendations are addressed to three groups: the Department of Administrative Services (DAS), all state agencies, and the Oregon Legislature.

We recommend the Department of Administrative Services create statewide, standard rates for copying and rates for employee labor, to resolve some of the inconsistency in public records requests fees statewide. We also recommend they provide guidance to agencies regarding communication technologies as they relate to public records, including personal email, text and instant messages, and social media.

For agencies, we recommend they create policies and procedures to clearly address communication technologies under the guidance of DAS. We also recommend they adopt tools to help manage both record retention and public records requests.

For the Legislature, we recommend they consider creating a neutral third-party, such as an ombudsman, to mediate disputes between requesters and agencies. We also encourage them to consider the forthcoming results from the Attorney General’s task force for any recommended changes regarding the public records law.

For a complete list of our recommendations, see page 24 in the full report.

Agency Response

The response from the Department of Administrative Services is attached at the end of the report.

Featured New Audit Release Performance Audit

Interested in Audit Work? Come Meet Us this Month

The Audits Division holds its annual “Meet the Agency” event on Oct. 30, an opportunity for potential job candidates to meet the division’s financial, performance and information technology auditors and learn about our work.

capitol mapThe event runs from 11:30 a.m. to 2:30 p.m. Oct. 30, in Room 50 on the lower level of the state Capitol, 900 Court St. NE. Attendees will hear from current auditors and interact with them in small group discussions. Email audits.recruiting@state.or.us by Oct. 23rd to RSVP.

The Secretary of State’s Audits Division hires three types of auditors:

Financial auditors

  • Duties: Audit Oregon’s financial statements, evaluate compliance with grant requirements and rules, and determine whether agencies have adequate internal controls in place. Perform investigations and issue other public reports on financial and compliance projects.
  • Qualifications: Typical candidates have an accounting major or a degree that includes upper division accounting courses, plus a year or more of auditing experience. Internships are also available.

Performance auditors

  • Duties: Analyze data, conduct interviews and look for best practices to determine how agencies can improve. Write public reports detailing potential improvements.
  • Qualifications: Candidates typically have a year or more of experience in auditing or program evaluation or analysis. The division accepts a Bachelor’s degree or higher in one of 12 majors, such as business or public administration, social services, economics, public health and journalism.

Information technology auditors

  • Duties: Review and evaluate agency information systems and evaluate control effectiveness. Write public reports on agency performance and potential improvements.
  • Qualifications: Typical candidates have a year or more of professional IT experience. The division accepts degrees, or degrees with upper division course work, in computer science, management information systems, accounting or business administration.

This event is an information session. Please contact the Secretary of State’s Human Resources Division at sos.jobs@state.or.us for current job announcements and information on how to apply.

 

Auditors at Work Featured New Audit Release Noteworthy

Audit Release: Oregon Needs Stronger Leadership, Sustained Focus to Improve Delinquent Debt Collection

Executive Summary


Liquidated and delinquent receivables owed to the state of Oregon have almost doubled since 2008, to nearly $3.2 billion, while collection rates on the debt have dropped. The state’s debt collection system needs more leadership, sustained focus and accountability to improve performance over time.

 

Read full report here.

Past due receivables are growing

Oregon’s liquidated and delinquent debt rose from $1.7 billion at the end of fiscal year 2008 to nearly $3.2 billion by 2014, while statewide collection rates on that debt dropped from 13.5% to 11.2%. Nearly $800 million of the debt is tied to the state’s general fund.

Liquidated and Delinquent Receivables

collections audit blog pic 1

Source: Legislative Fiscal Office. Adjusted for PERS errors. Excludes Department of Administrative Services interagency debt.

The recession contributed to the increased debt. Evidence indicates many of the debtors are low-income, and more than half the debt may be uncollectible.

However, bumping up Oregon’s collection rates could still make a substantial difference over time. At 2014 debt levels, every percentage point increase in the statewide collection rate would improve collections by about $38 million. If Oregon had collected delinquent debt at a 13.5% rate in 2014 – last achieved in 2008 – the state would have brought in nearly $90 million more in collections.

Our audit found four key improvements that could help Oregon increase collections:

  • Improved oversight of collections;
  • Enhanced performance measurement and reporting;
  • Increased expectations for private collection firms and the state’s central collection agency;
  • Better use of proven collection tools.

Oregon has not focused on improving collections

collections audit blog pic 2Our audit found Oregon’s highly decentralized approach to collections has contributed to a lack of sustained focus on improvement. This is our sixth collections-related audit since 1997. Significant improvements identified in those audits have not been implemented, some dating back 18 years.

Oregon has not implemented productive collection tools used by other states, has not resolved lingering legal issues that hinder collections, and has allowed inadequate performance measurement to persist.

Individual agencies have made some improvements. Statewide, however, no one has been tracking collection improvement efforts or encouraging them. Our discussions with leading states on debt collection highlighted the importance of having a system “expert” responsible for identifying potential improvements, looking outside the state system for new opportunities, and reporting to decision makers.

In Oregon, the statutory authority and history of the Department of Administrative Services indicate it is the best agency to serve as a statewide strategist on debt collection.

Performance reporting, measurement are flawed

collections audit blog pic 3State agencies routinely collect receivables, or bills for charges and services. Statewide performance reporting focuses on receivables that become “liquidated and delinquent” – past due debt that debtors have had a chance to contest.

The Legislative Fiscal Office prepares an annual report on liquidated and delinquent debt collection, designed 16 years ago by the Legislature to help drive collection improvements. However, the report includes few large-debtor agency details – not even their collection rates – contains noteworthy inaccuracies, and does little to hold agencies accountable for collections performance. It also does not identify potential collections improvements or detail the status of agency improvement efforts, key to encouraging advances.

In addition to reporting, we also focused on “assignment” of debt, accounts sent by agencies to private collection firms or the Other Agency Accounts unit at the Department of Revenue, the state’s collectors of last resort. Private collection firms carried nearly $1 billion of the state’s debt as of 2014 – more than double the 2008 balance – with a collection rate just over 1%. Other Agency Accounts, the state’s central collection agency, had a better rate, roughly 7%, according to Legislative Fiscal Office data. Assignment to OAA has stayed relatively flat, however, hitting $259 million in 2014.

We found the Department of Administrative Services is not evaluating the performance of OAA or private collection firms. We also found some large-debtor agencies are not using performance information to strategically assign debt.

Oregon is not using some proven collection tools

Our research, discussions with other states and interviews with Oregon officials suggested eight tools Oregon could pursue to increase collections, including some the state has considered for years but not implemented.

Among the most promising:

State vendor offset: Forty states are intercepting state payments to debtors who are also state vendors, including corporations and consultants. Our work indicates vendor offset in Oregon would collect at least $750,000 a year.

collections audit blog pic 4Bank levies: Other states have systems that allow for automated matching of a wide variety of debtors to bank account records, a process that yielded $30 million for Wisconsin in 2014.

Internet posting of debtors: Twenty-three states maintain public online lists of debtors, some focused only on large debtors, to increase collections. Many of the debtors pay after they receive a warning letter but before the information is posted.

2015 Legislative changes should help

The Institute for Modern Government at Willamette University drafted Senate Bill 55 in the 2015 legislative session to improve debt collection. We issued an interim report to the Legislature to suggest further legislative changes. Our recommendations were incorporated in the bill, which the Legislature passed and the governor signed in July.

At our recommendation, Senate Bill 55 charged the Department of Administrative Services with monitoring and improving debt collection. DAS’s duties, detailed in the bill, include improving performance reporting and assignment of debt for collection. DAS started a committee last year to address statewide collections, and contributed to Senate Bill 55.

Senate Bill 55, passed by the Legislature, included changes we recommended.

Even with stronger oversight, improving collection of Oregon’s rising debt will not happen overnight. During our audit, we found that improving collections requires meticulous work with agencies.

DAS officials – and policy makers – will also have to be persistent to ensure improvements are made.

Recommendations

Beyond the changes implemented in Senate Bill 55, we found improvements OAA could focus on. We also found other steps DAS could take, including:

  • Preparing meaningful annual reports on debt collection, relevant to the public and policy makers.
  • Helping agencies adopt successful collection tools.
  • Developing short- and long-term plans for a sustained focus on debt collection.

Agency Responses

Both the Department of Administrative Services and the Department of Revenue generally agreed with our recommendations, with DAS noting that it recognizes its oversight role.

DAS said it would focus efforts on current receivables as well as liquidated and delinquent debt. The response also included concerns about the difficulty of adopting a fully integrated vendor offset program.

The Department of Revenue said agency officials will continue to discuss many of the collection improvements noted in our audit with policymakers and stakeholders. A computer system upgrade now underway will help the agency make further improvements, the response said.

The full agency responses can be found at the end of the report.

 

Featured New Audit Release Performance Audit

GAO Report: Incomplete information on collective performance of rental assistance programs nationwide

Last week the GAO released a report looking at the roles federal, state, and local governments play in providing rental assistance and developing affordable rental housing for low-income households.

“Affordable Rental Housing: Assistance is Provided by Federal, State, and Local Programs, but There is Incomplete Information on Collective Performance” (GAO-15-645)

GAO partnered with 25 state and local audit offices to complete the study, including the Oregon Audits Division, the Multnomah County Auditor’s Office, and the City of Portland.

GAO found that the information on collective performance of federal, state, and local rental assistance programs nationwide is incomplete.

“Without information on the government-wide performance of rental assistance, the Congress, decision makers, and stakeholders at all levels of government are hampered in their ability to set priorities and allocate resources. While complete and reliable information is a vital component of assessing effectiveness, GAO recognizes it is difficult to identify relevant federal, state, and local programs; collect performance information from multiple levels of government; and synthesize the information to reflect collective performance. HUD, the nation’s leading housing agency, in consultation with the RPWG, is well positioned to capitalize on its existing collaboration among federal agencies and with state and local jurisdictions.”

(Excerpt from the highlights page of the GAO report.)

GAO report blog pic

Featured New Audit Release Noteworthy

Oregon State Hospital: Significant Actions Taken to Improve Safety and Promote Patient Recovery, but Further Improvements are Possible

Executive Summary


The Oregon State Hospital has undergone enormous change. To better promote patient recovery, management has taken significant action to improve safety and patient care. The hospital offers more treatment options and strategies to create a safer environment. It has also undertaken efforts to reduce overtime, and implement an electronic health record system. However, more action is needed in these areas to further improve safety and promote patient recovery.

 

Read full report here.

Oregon Moves Towards Recovery- Oriented Mental Health Care

The first treatment mall opened at the hospital in 2006, marking a shift from decades of unit-based treatment. The hospital operates much like a college campus. Patients reside on the living units, attend class-like treatment groups on the treatment malls separate from their living space, and eat in cafeteria-style dining rooms. Treatment groups help patients learn skills like handling difficult emotions, developing healthy relationships, managing medication, and understanding the legal process.

OSH pics 1New Salem campus facilities were completed in 2011 to further create a sense of well-being. Architectural features incorporate design elements intended to minimize physical safety risks while promoting patient recovery. The buildings look and feel similar to a college campus with plenty of green space. Holding an average of about 600 patients, the facility offers them opportunities to interact with their peers and simulate community experiences such as visiting a coffee shop or a salon.

The adoption of treatment malls is part of Oregon’s larger move towards recovery-oriented mental health care. This approach takes the view that individuals with mental illness can improve their health and wellness, live a self-directed life, and strive to reach their full potential through the recovery process. The recovery focus guides mental health services in Oregon, including the Oregon State Hospital.

Improving Treatment Plans and Groups Could Help Patient Recovery

OSH pics 2.1Hospital staff work with patients to develop treatment goals to address challenges that stand in the way of patient recovery. Patients attend treatment groups directed toward their treatment goals and group leaders evaluate their progress.

Case formulation is an important tool to help clinicians create effective treatment care plans that guide patient treatment. Formulations identify the signs and symptoms of mental illness, motivations behind patient behaviors, and patient strengths and skill deficits at a particular point in time. The process distills critical elements from the huge amount of information available and places them into a narrative context. It can be used to help develop treatment goals and guide patients to the treatment groups most likely to benefit them. We found that the hospital provides very little guidance and training on how case formulations are developed. As a result, case formulations are not consistent.

Treatment groups should be aligned with patients’ treatment goals given their importance in addressing patient challenges and evaluating patient progress. However, it is unclear whether hospital staff designed therapy groups to help patients address these goals. Hospital staff did not use patients’ treatment goals when selecting classes to offer on the treatment malls.

Also, the hospital does not have policies and procedures to ensure patients schedule classes that address their treatment goals and hospital staff do not use treatment goals to evaluate class effectiveness.

OSH pics 3The hospital initiated improved treatment by first implementing strategies to improve patient safety and adopting a new culture centered on patient recovery. Management is committed to further treatment improvements. However, the hospital has not yet developed a formal plan for implementing additional treatment improvements.

We recommend Oregon State Hospital management develop a formal plan for implementing treatment improvements to ensure the consistency of case formulations and integrate treatment goals with the treatment mall groups offered. The plan should include steps for communicating the needs for continual improvement, strategies, and timelines for implementation, milestones to monitor progress, and measures designed to evaluate the plan’s success.

We also recommend Oregon State Hospital management develop policies and procedures for developing and documenting case formulations; and designing, selecting, and scheduling treatment mall classes that consider treatment goals.

Fewer Incidents of Seclusion and Restraint Improved Patient and Staff Safety

Patients need to feel safe in order to make progress towards recovery. Hospital staff also need to feel safe to form therapeutic relationships with patients that support their recovery. Reducing patient aggression can reduce the safety risks their behavior can pose, and so reduce the need for staff to place patients in either seclusion or restraint.

OSH pic 4The hospital adopted the National Association of State Mental Health Program Directors’ (NASMHPD) strategies for safely reducing seclusion and restraint (S/R) use. These strategies address underlying reasons for patient aggression and, if implemented, can help organizations reduce the need to use seclusion or restraint. Management has made progress in implementing each of the six strategies, and their continued efforts can further reduce the use of S/R, and improve safety.

To improve safety at the hospital, we recommend Oregon State Hospital management:

  • continue to address organizational culture, training needs, and attitudes;
  • continue to use data to inform decision-making and practice in S/R reduction efforts;
  • continue Collaborative Problem Solving and Safe Containment implementation to ensure staff competency;
  • update policies and procedures that guide the on-the-job training of nursing staff to ensure consistency among the programs;
  • consider reestablishing the nursing staff mentoring program;
  • continue efforts to integrate S/R reduction tools and assessments into individual patient treatment;
  • provide adequate resources to the Peer Recovery Services Director to help ensure the department’s success;
  • continue to ensure stakeholders and consumers have a role in S/R reduction efforts;
  • continue to work with the Governor and legislature to fill vacant seats on the Oregon State Hospital Advisory Board; and
  • continue efforts to finalize the hospital’s debriefing policy.

Overtime Has Been Reduced— Fatigue Concerns Remain

OSH pic 5Excessive overtime can lead to fatigue, affecting nursing staff’s ability to deliver good patient care, make good clinical decisions, and communicate effectively. Nursing staff provide the bulk of direct-patient care at the hospital, comprising registered nurses (RNs), licensed practical nurses (LPNs), mental health therapists (MHTs) who are licensed certified nursing assistants, and habilitative therapy technicians (HTTs).

The hospital has worked to reduce overtime by hiring nursing staff to fill vacancies, using ratios to ensure appropriate staffing levels, creating a float pool of nursing staff to cover unscheduled absences, revising weekend shift times and hours, and addressing patient aggression to reduce the need for additional staff. Additional actions could further reduce overtime and its effects on patient care.

We identified several staff whose overtime hours indicate they may be at risk for fatigue and its effects. There are no policies that limit overtime hours or consecutive days staff can work. Nor does the hospital offer training on fatigue and its effects, recognizing fatigue, or on employee obligation to ensure they can provide safe patient care.

To reduce overtime and its adverse effects on patient care, we recommend Oregon State Hospital management develop strategies to limit unscheduled absences and manage individual staff’s overtime. Management should also provide training to staff on fatigue and its effects on patient care.

We further recommend Oregon State Hospital management consider the analytical framework used in our 2012 audit of the Department of Corrections to explore other strategies to further manage personnel costs while meeting patient treatment needs and maintaining a high level of patient and staff safety.

Automation Can Improve Patient Care

The hospital is implementing an electronic health record system, but parts of the system remain incomplete. The incomplete system adversely affects organizational efficiency and potentially, the quality and cost of patient care.

OSH pic 6Completing the system would help automate several key manual processes. For example, the hospital could replace its manual process for dispensing patient medication with an automated system it purchased several years ago. The automated system would provide safeguards designed to prevent medication dispensing errors.

The hospital is working to convert patient records from paper to electronic records but critical medical records such as patient prescriptions, allergy information, and “do not resuscitate” and “advanced directive” documents are still maintained as paper. Hard copy record systems can lead to additional costs, lost productivity, and limited accessibility.

We recommend Oregon State Hospital management complete implementation of its electronic health record system, prioritizing automation of processes that significantly impact patient care and conversion of critical patient information to electronic format.

Agency Response

The agency generally agrees with our findings and recommendations.  The full agency response can be found at the end of the report.

 

Featured New Audit Release Noteworthy Performance Audit

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