Methods (to our Madness): Disaggregating Data to Improve Evaluation and Transparency of Economic Development Programs

 Periodically, we will highlight some of the methods used in a recently released audit. Every performance audit is unique and can require creative thinking and methodologies to answer our audit objective. Some of these methods could be replicated or present valuable lessons for future projects.

 

Disaggregating data can improve transparency and evaluation of programs. In a recent audit of Business Oregon, the audit team divided data about business finance and forgivable loans by different programs to estimate job growth and return on investment, as well as rural investments for each program.

Business Oregon reports jobs created for all of its programs combined, in one Key Performance Measure. The audit team saw their more detailed analysis as an approach that Business Oregon’s analysts could emulate and improve on, in order to foster greater transparency and improve understanding of the investments and outcomes of individual programs.

I recently sat down with Jon Bennett, a Performance Auditor, to learn about their analysis strategy and any lessons learned.

Business Oregon’s programs contribute to job growth

Jon’s team analyzed different business finance programs at Business Oregon and calculated net job growth for participating businesses over a four year period. They found that about two-thirds of businesses had net job growth. They also found that most of the awards went to businesses that paid wages below the county average, important since Business Oregon has a mission to create living wage jobs.

They also looked at investment by geography and found that most awards go to non-rural areas. This is important because rural areas contain 40% of Oregon’s population and were struggling to get out of the recession.

While it is valuable to look at the big picture, separating data into different programs and different measures can provide greater insights into the effectiveness of each program and how each program’s investments reflect the agency’s priorities.

Lessons Learned: Time management and planning ahead

Jon had a few different lessons learned, but the big take away is one I’ve experienced before – time management. Doing data analysis always seems to take longer than you expect it to. One of the time consuming aspects Jon faced was combining data from two different data sources. He thought it would be simple because he had a unique identifier, but it turns out that some of the businesses he was looking at had multiple locations and he had to look at the data more carefully.

Next time, Jon would also like to do a better job of planning how to document his work before he does it. As auditors, we always have our work checked for accuracy, which can be challenging if there is not a clear documentation trail. That is one of the benefits of using ACL, since it automatically creates a log. But sometimes other tools can be more useful. Jon interestingly switched between Excel, ACL, and STATA to use the tool that could do the task most efficiently in the way that he knew best.

 

CZ_photo

Caroline Zavitkovski, OAD Principal Performance Auditor, MPA

 

Auditing and Methodology Featured Performance Audit

Audit Release: Automated Medicaid eligibility is processed appropriately at OHA, yet manual input accuracy and eligibility override monitoring need improvement

AUDIT PURPOSE

In Oregon, over one million individuals have Medicaid coverage. Medicaid expenditures totaled $9.3 billion in fiscal year 2016, including $1.2 billion in state general funds. We conducted this audit to determine if two critical automated computer programs managed by the Oregon Health Authority accurately verify Medicaid client eligibility and accurately issue payments to healthcare providers. If these programs do not function properly, clients may inappropriately receive, or be denied, Medicaid benefits.

FINDINGS IMPACT

Manual input errors and lack of monitoring of overrides can cause inappropriate eligibility determinations and payments to providers. If agency leadership implements more effective monitoring of caseworker eligibility overrides and improves manual input accuracy, the state will better comply with eligibility requirements and increase accuracy of payments. Inaction will allow overrides and manual input errors to continue causing inappropriate payments to providers.

Read full report here.

KEY FINDINGS

  • Two critical automated computer programs appropriately determined eligibility, enrolled Medicaid clients in coordinated care organizations, and made appropriate payments to those organizations based on eligibility information received.
  • Automated computer processes appropriately validated the Social Security number and citizenship status of applicants over 99.7% of the time in our review of over 425,000 records.
  • We reviewed 30 eligibility determinations and found seven (23%) had manual input errors. While only one error resulted in a client being determined eligible when they were not, each of the errors related to application information that could have resulted in inappropriate eligibility determinations.
  • Although their volume has significantly decreased over time, overrides of eligibility are not sufficiently monitored, meaning unauthorized overrides of Medicaid eligibility could occur.
  • Our review of 72 overridden eligibility segments showed caseworkers did not take proper action to clear 25 (35%). Overridden segments are not subject to automated processes that redetermine eligibility for certain clients.
  • Our 2011 audit recommendations to OHA and DHS concerning access to the Medicaid Management Information System have not been fully implemented, increasing security risk.

RECOMMENDATIONS SUMMARY

  • OHA should continue efforts to improve caseworker manual input accuracy through additional training, and implement a review process for input where errors negatively affect eligibility determination.
  • OHA managers should monitor eligibility overrides to prevent unauthorized validation and ensure state resources are spent appropriately.
  • OHA and DHS should fully implement our 2011 audit logical access recommendations.
Featured IT Audit New Audit Release Performance Audit

Speaking Truth to Power (Users): Why data visualization matters to researchers, auditors, and program evaluators

“I’m not a data visualization expert, but I am a data visualization enthusiast” Rebecca Brinkley joked as she started her presentation on including visual elements in reports to the State of Oregon Research Academy (SORA).  Several dozen researchers from around the state listened intently as she walked through the importance of including visual elements and gave examples from recent audit reports from the Oregon Audits Division.

Why Visuals Matter

“Our brains are visual processors not word processors” explained Rebecca Brinkley. Most people think visually rather than verbally.  Furthermore, visuals have been found to be processed at a much higher rate than text alone.  The visual elements help the reader process complex information and improve retention.

Many times visuals can help tell a story or make a connection that would be more difficult to do using text. For example, Rebecca highlighted this image of the water cycle in a recent report on water management in Oregon.

waikato

I don’t know about you, but I’d much rather see this image describing the water cycle than reading a long paragraph describing the water cycle.

We will be highlighting some more example of useful data visualization techniques in future blog posts.

Rebecca will also be presenting to the Pacific Northwest Intergovernmental Audit Forum in March.

Ian Green, CGAP and OAD Senior Auditor

Ian Green, CGAP and OAD Senior Auditor

Auditors at Work Featured Performance Audit

Talkin’ Shop With: Larry Stafford, Clark County Audit Services Manager

Join us as we talk shop with our neighbor to the North and former OAD performance auditor, Larry Stafford.

About Clark County’s Audit Services

clarkcountylogo

Per county code, internal audit is an independent appraisal activity for the review of operations within the county. The objective is to assist management in the effective discharge of their duties, and to promote efficiency and economy consistent with the public interest.

Clark County government is comprised of several separately elected officials, including the County Auditor.

The Audit Services Department conducts performance audits, internal control reviews, and provides other services to county management. Performance audits are objective and systematic reviews of program quality and the results achieved. Internal control reviews include analytical reviews, interviews, observations, and tests with the intent of evaluating the security of county assets and the accuracy/reliability of financial reports.

The Clark County Auditor

The Clark County Auditor is elected to a four-year term and our current Clark County Auditor is Greg Kimsey. In addition to being the county’s chief financial officer, the auditor also oversees several essential county services: Audits, Auto Licensing, Elections, Marriage Licensing, Financial Services, and Recording. Additionally, the Auditor works with other elected officials, various state agencies, and the state legislature regarding issues that affect these services.

ft-vancouver-clark-county-historical-musuem

Fort Vancouver (image from Clark County Historical Museum)

Working with an elected auditor is interesting because they can bring a different perspective and things to consider. We do debriefs with the auditor throughout our audits, and at the end of the Scoping, Planning, and Fieldwork phases.

Can you describe your role as Audit Services Manager?

stafford

As the Audit Services Manager for Clark County, I serve as the principle auditor and am responsible for the quality of the Audit Services team (2016 Peer review). This includes reviewing audit work and administrative responsibilities such as developing and maintaining policies and procedures, hiring staff, performing the annual quality assessment, and coordinating peer reviews.  I also serve as a liaison between County departments and external auditors, like those from the Washington State Auditor’s office.

When I left OAD, Gary Blackmer, who was the director at the time, told me that I would have to “live with my audits” at the county. He was right. At the state, we would audit an agency and leave. But at the county we see the people we audit every day. We still report to a separately elected Auditor and so we are external to those that we audit, yet we are still internal to county government. We have to build relationships while always being aware of our independence.

What got you into performance auditing?

Luck! I spent ten years in the automation/ engineering field. That was mostly robotics-related work including electrical design, project management, and programming. I also spent some time in the financial services industry.

I wasn’t happy with my work-life balance, so I went to graduate school and earned my MBA as a path to a new career. I attended a job fair with a classmate who went to speak with the Oregon Audits Division about financial auditing. At the booth they also told me about this interesting and challenging field called performance auditing. I’ve always been driven to learn new things and this sounded like just the right fit. I was fortunate to get a position with OAD and to work with some very good auditors.

Larry’s take on performance auditing

One of the most important things I learned at OAD was that performance auditing is about understanding the issues and finding solutions – not placing blame. Also, reporting when you find things are happening the right way is just as important as identifying problems. It provides objectivity to your report and delivers the assurance expected from an audit.

For example, the first audit I worked on at OAD examined if state agencies hired former employees as contractors.  We found some instances, but all of the required procurement processes were followed and the award decisions were appropriate. Saying that in an audit report certainly didn’t get headlines but it did provide assurance.

Working at OAD was really interesting.  I was involved in a lot of issues at different levels, auditing different agencies and topics.

Coordinating with state auditors

clarkcountyThe Washington State Auditor’s office is doing some work around accountability. As part of this work, they are reviewing the financial affairs of municipal governments, state and local laws, and contracts to assess risk and evaluate compliance, mostly looking at internal controls. What I help with is making sure there is no duplication of work, and help facilitate communication and work with Clark County staff. I also help provide context and clear up any confusion, similar to an internal auditor.

Providing training

Internal control failures are common issues identified in our audits. So each fall, we host a half-day seminar on fraud and internal controls for County managers. I’ve found this kind of training is helpful for managers who are often experts in their field but have not been exposed to management tools like the COSO framework or the fraud triangle.

The training doesn’t take a lot of our time, but there is a big pay off. When these fundamental issues are addressed, then our performance audits can focus on higher level issues. The trainings also help to build relationships and credibility, and we get good feedback from managers who attend. They’re very interested in this information and how they can apply it to improve their departments.

How do you choose your audit topics?

We complete a biannual risk assessment and create a schedule of proposed audits. This includes input from elected officials, employees, citizens of Clark County, and our auditors. This proposed schedule is presented to our Audit Oversight Committee, of which the Clark County Auditor is the chair. The committee recommends audit priorities before we finalize our schedule. The County Auditor can approve audit work on issues that emerge between biannual risk assessments.

What are some interesting audits you’ve worked on at Clark County?

An audit I did on economic development was probably the most interesting audit I’ve worked on. Economics has a lot of intertwined theories, so it’s hard to nail down criteria. We wanted tangible criteria because it doesn’t matter what’s happening if you can’t agree on how to measure things. In the end, we also adjusted our conclusions based on the more questionable evidence. This audit received an ALGA Knighton Award as a result of this approach and how we treated the evidence.

Audit of Clark County’s Job Creation- Fee Waiver Program 

“The Clark County Auditor’s Office evaluated an economic development program designed to spur job creation through waiving government fees. The audit found that the fee waiver program cost Clark County approximately $8 million in its first year in forgone revenue and was not cost effective. Objective in its treatment of evidence and sensitive to its policy implications, the audit makes two strong recommendations: 1) either discontinue the program, or 2) implement significant changes to improve the program. The audit won the exemplary award for its scope and methodology, significant impact and for its clear and concise report writing.”

fee-waiver-audit

We’re also completing a series of inventory audits that look at high-risk assets, like ammunition and fuel. These material management audits are more interesting than you’d think! We’ve found opportunities to improve operations while decreasing costs, risk, and liability.

Sheriff’s Office High Risk Equipment and Supplies Management Audit 

“This was an impactful audit that focused on internal controls of high-risk materials utilized by the Sheriff’s Office, taking into account public safety as well as accountability. Findings and conclusions directly tied to objectives. Recommendations when implemented will not only have a cost savings but will increase efficiency and enhance accountability. The report was well organized with effective use of photos and charts.”

What is your favorite part about your job?

When I was a kid, I would research different topics and learn all about them. In doing this work, I have the opportunity to that for a living. It’s also all across a wide spectrum of services — it’s very hard to get bored! My favorite part about this job, though, is getting to work with other auditors. They’re so intelligent and passionate about their work.

Auditors at Work Featured Performance Audit Regional Roundup: Talkin' Shop

Audit Release: Business Oregon Can Improve the Evaluation and Transparency of Economic Development Incentives and Loan Programs

Executive Summary


Business Oregon, the state’s primary economic development agency, can strengthen its evaluation of incentives and loans given to private businesses, focusing on performance outcomes such as jobs created and retained, wages, and return on investment.

Business Oregon can also help improve the transparency of individual business awards by better reporting information about them to the public and policy makers. And it can improve its selection and modification of the Governor’s Strategic Reserve Fund awards to private businesses.

These improvements would help the agency, policy makers, and the public ensure that business incentives and loans are as strategic, cost effective and productive as possible, important given high statewide competition for Lottery and General Fund dollars.

Read the full report.

Business Oregon can Better Evaluate the Results of its Business Incentive and Loan Programs

Awards to businesses that Business Oregon has some role in are estimated to total $680 million in the 2015-17 biennium. This includes $145 million of state loans, loan guarantees, grants and tax incentives, and $535 million in locally controlled property tax exemptions initially authorized by the state.

Business Oregon conducts some evaluation of these incentives and loans. But it does not regularly evaluate and publicly report some key program-level outcomes, such as jobs created and retained, wages and return on investment.

We analyzed Oregon Employment Department data that Business Oregon already obtains to illustrate the potential benefits of more in-depth evaluation, and the value of reporting evaluation results to the public and policy makers.

We focused on the agency’s top discretionary programs for private business: the Strategic Reserve Fund (SRF) and Business Expansion Program (BEP), and Business Finance loans and loan guarantees. Unlike Business Finance loans, SRF and BEP loans are forgiven if a business meets job goals. The Governor approves SRF loans.

We analyzed 21 projects that received funding through the SRF or BEP, and an
additional 210 Business Finance projects from 2011-12. These are the most recent years with at least three years of job and wage data after Business Oregon issued the award.

The analysis indicated both positive results and issues that need more examination and public discussion. The programs helped add new jobs for Oregonians, the analysis suggested, and workers filling those jobs generated positive returns to the state through their income tax payments.

workers

Business Oregon’s mission includes supporting businesses that provide living wage jobs.

However, the majority of 2011-12 awards went to businesses with average wages below that of the county they operated in, an important result given Business Oregon’s mission to encourage living-wage jobs. Agency officials say they focused on saving all types of jobs in the down years of 2011-12.

Additional analysis of incentives and loans awarded from 2006-2015 indicated that most of them went to urban areas, which already have faster employment growth than rural areas.

Business Oregon’s strategic plan and key performance measures do not have targets for rural investment and living wages, making it difficult to evaluate these results. Setting targets could facilitate deeper agency evaluation of award cost-effectiveness, the agency’s investment in rural communities, and its ability to encourage projects that pay livable wages.

The audit also focused on enterprise zone property tax exemptions, Oregon’s broadest program of local incentives. Evaluation of enterprise zone performance is sporadic, and several issues need more attention.

Our analysis of 2015 enterprise zone data showed a high amount of zone job growth in the Portland metro area, which is relatively strong economically, and in non-distressed areas. Urban areas have economic development needs too, but the concentration of benefits in relatively well-off urban and non-distressed areas is at odds with the enterprise zone programs’ original focus on lagging areas that have more economic need.

The analysis also found high tax exemptions per job in the long-term rural enterprise zone program — $54,500 in 2015 versus $4,200 in standard zones. And it found high exemptions per job for data centers built by Apple, Facebook, Google, Amazon and others. These projects could be costing counties more than typical projects, though fees and other taxes paid by the businesses involved may help offset the property taxes foregone.

In general, state income tax incentives receive more scrutiny, including “sunset” reviews when tax credits are scheduled to expire. But they also need more frequent objective evaluation.

Oregon Can Improve the Transparency of Individual Economic Development Incentives and Loans

In recent years, Oregon has substantially improved its reporting of economic development awards to individual businesses, putting it near the top in various state rankings and improving public accountability.

But policy makers and the public still do not have enough information on many economic development incentive and loan programs to assess their value, determine which businesses are receiving awards, and review the jobs and wages generated by subsidized businesses.

The transparency reporting generally meets state statutes and guidelines. But ten of the 15 programs we reviewed did not disclose outcome information, such as jobs and wages, for individual businesses receiving incentives and loans. For 11 of the programs, determining whether a particular business met program requirements given the information publicly reported was difficult or impossible.

Business Oregon does not identify which businesses receive SRF, BEP, and Business Finance loans, or how many jobs those businesses retain and create.

Agencies did not report the amount of the tax incentives given to individual businesses for four of the eight economic development tax credits and subtractions we evaluated, in part because of tax return confidentiality. And county tax assessor reports for enterprise zone tax exemptions often contain incomplete information on jobs and wages.

Business Oregon Can Further Improve its Selection Process for Strategic Reserve Fund Awards

Business Oregon has developed a new, more thorough selection process for SRF awards, typically forgivable loans. We reviewed a sample of business projects that received SRF awards, and found several areas for further improvement.

The agency should make sure it completes risk reviews before businesses begin their projects. It should also more directly incorporate risk reviews into decision-making, and evaluate the state’s full investment in projects before sending proposed awards to the Governor for approval.

We reviewed seven large projects that included SRF awards from 2008 to 2015. Business Oregon calculated projected returns for those projects based solely on the SRF forgivable loan, and did not include other state investments in the return calculation.

For five of the seven projects, adding estimated state tax credits alone to the return analysis would have significantly reduced the estimated return. For the five projects, estimated worker income taxes were projected to pay back the SRF award amount in three years on average. Adding state tax credits would have increased the estimated payback period to 24 years on average, a substantially increased risk.

Recommendations

Our complete recommendations are included on pages 26 to 27 of the full report. We recommend that Business Oregon develop additional metrics and targets for incentive and loan performance, using them to evaluate the awards and report performance to policymakers and the public.

We also recommend transparency improvements. They include reporting individual SRF, BEP and Business Finance loans, and working with the Legislature, other state agencies and counties to improve the quality of information reported.

And we recommend that the agency improve its selection and modification of the Governor’s Strategic Reserve Fund awards to private businesses.

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 Performance Audit

Oregon Water Resources Department: Enhancing Sustainability Efforts and Agency Planning Needed to Better Address Oregon’s Water Supply Needs

Executive Summary


Oregon is facing growing pressures and concerns related to its water supply. The Water Resources Department (WRD), charged with managing the state’s water resources, could better balance water rights issuance and management with actions to sustain current and future water needs. The agency can also enhance its focus on groundwater protection, data collection and analysis, and workload and staffing. A long-term agency plan would help WRD strategically focus and prioritize the agency’s efforts and align them with available resources.

Read full report here.

Better balance needed to ensure water sustainability

owrd_logo_color_smwebParts of the state are experiencing regular and large scale water supply availability issues. There are indications that this trend will continue, intensify, and spread. Many water sources in the state have been fully allocated, and groundwater levels are decreasing in several areas. By 2050, Oregon could be faced with a need for an additional 424 billion gallons of water per year to meet irrigation needs and municipal and industrial demand. Though Oregon is known as a rainy place, there is a limited amount of consumable water available for meeting all existing needs and new uses.

While issuing water rights has always been a key responsibility for WRD, actions to restore and protect stream flows and watersheds for long-term sustainability have received less attention. Related programs are limited in number and in participation. The demands that are putting pressure on Oregon’s water supply are likely to continue to grow, which raises the need for action to ensure the ongoing sustainability of our water.

Groundwater protection needs more focus

Groundwater usage is increasing, and a large and growing number of wells go uninspected. Poor well construction may result in higher levels of groundwater contamination or wasted water. Contaminated groundwater would harm the overall groundwater supply. WRD has few well inspectors to inspect all wells in the state. For those wells that are inspected, WRD staff have noted an increase in well drilling deficiencies at a time when well construction has also increased. This could be due in part to minimal requirements to become licensed as a well driller in Oregon. Also, WRD could better coordinate with other agencies to address well risks, such as water contamination and public safety concerns, for the overall health of groundwater resources.

Data challenges hinder efforts to manage and conserve water

WRD collects a lot of information on surface water and some on groundwater. However, given the size of Oregon and its complex geology and aquifer systems, many areas of the state have not had detailed groundwater and surface water investigations. Not all water users are required to report their use, and as such, the amount of water being used in the state can only be estimated. Also, some of the data collected has not been entered into databases and analyzed, so the agency is not able to use it for water management decisions.

Increasing demands and other limitations impede monitoring and regulating water

Dry creek bed in Central Oregon

Dry creek bed in Central Oregon

Growing and changing demands coupled with a limited number of field staff impact WRD’s capacity to effectively monitor and regulate Oregon’s water supply. Field staff coverage overall has steadily declined and there have been some extended gaps in time where positions were vacant. Field staff have to cover a vast geographic region and associated workload. This, along with limited external support, impedes the agency’s ability to protect water and the rights of users, and to curb illegal water use. WRD should regularly assess field staff workload to ensure it aligns to resources and that staff time is dedicated to critical responsibilities.

Long-term agency plan needed to help focus efforts on future water sustainability

While the Integrated Water Resources Strategy provides a long-term multi-agency plan for managing water resources in Oregon, WRD needs an agency plan to strategically focus and prioritize its efforts, and align them with available resources, to better meet its mission. This would help WRD balance its efforts to ensure both consumptive and environmental water demands can be met now and into the future, and address areas needing increased focus, such as groundwater protection, data collection and analysis, and workload and staffing issues. Priority-based planning can help clarify and direct agency efforts that are vital to protecting Oregon’s water supply.

Recommendations

This audit recommends ways WRD can build on its efforts to help address the current and future sustainability of the state’s water supply. Our detailed recommendations for agency management are included on Page 26. They include recommendations for further integrating sustainability considerations into water management decisions, helping to ensure water laws and rules meet current and future needs, enhancing well regulation and groundwater protection efforts, strategically collecting and analyzing information, aligning staff workload with mission critical priorities and resources, and developing an agency long-term plan.

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 Performance Audit

Methods (to our Madness): Leveraging Administrative Data to Understand a Management Issue

Periodically, we will highlight some of the methods used in a recently released audit. Every performance audit is unique and can require creative thinking and methodologies to answer our audit objective. Some of these methods could be replicated or present valuable lessons for future projects.

Anyone who pays attention to the news or lives near a fire prone area, knows that Oregon’s fire seasons have been extreme the past few years. But I sat down with Amelia Eveland, Senior Auditor, and Luis Sandoval, Staff Auditor, to learn about more than Oregon’s formidable wildfires: how the team used data to understand workforce issues at the Department of Forestry, as described in the recently released audit, Department of Forestry: Actions Needed to Address Strain on Workforce and Programs from Wildfires.

Department of Forestry staff had described fire seasons in terms of acres burned and other fire activity measures, but hadn’t put numbers to what they intuitively knew; those large and frequent fires were affecting all of their programs. The team was able to quantify some of the impact of fires on department programs and staff by analyzing the actual hours worked by employees.

Don’t Overlook Administrative Data Sources

One of the things that I found most interesting was their data source: payroll data. Payroll data is collected for administrative purposes. But administrative data should not be overlooked as a source of information for management analysis. Payroll data provided the information that the team needed and was possibly more accurate than other data sources, since accuracy is important when people are being paid.

Understand Your Data and Its Limitations

Using a data source that is collected for another purpose can have downsides though. The payroll data only went back 6 years and only showed hours billed, not worked. The hours worked by some staff who weren’t eligible for overtime weren’t captured.

The team also had to understand the data and parameters. To do this they worked with the department’s financial staff who were familiar with it. They asked the department staff to pull the data and to check the team’s methodology. In the course of this process, they eliminated pay codes that would double count hours. For example, if someone was working a night shift on a fire line, they could receive pay differential (a supplemental payment) on top of their regular salary. Pay differential hours were logged separately from the hours logged for regular pay, despite applying to the same shift. Initially the team had been counting these hours twice, but working closely with the agency helped them pinpoint and correct potential methodological errors.

Putting Numbers to the Impacts on Staff and Programs

The team overcame these minor obstacles to conduct some pretty interesting analyses. They found that the past three fire seasons had been particularly demanding in terms of staff time, mostly due to regular and overtime hours from permanent employees (as shown in the graph below). This suggests that these employees may be pulled from other activities, and may also feel overworked.

 

forestry-chart

Payroll Hours Billed to Fire Protection by All Oregon Dept. of Forestry Employees

 

The team was also able to get a more accurate picture of which programs were contributing to fighting fire through specialized incident management teams. Because many Forestry employees split their regular time between different programs (for example, someone may split their time 80/20 between Private Forests and Fire Protection), it can be hard to track which programs are being affected when that person goes out to fight a fire. The audit team totaled the regular hours billed to each program and used the proportion of this total to arrive at a proportion of contributing programs.

Get the Power Pivot Add-in (so cool)

I asked the team for advice on using payroll data. They suggested manipulating the data as much as possible in the data query tool before exporting the data for analysis. The team used excel for analysis but used the power pivot add-in to be able to summarize the large quantity of data.

Auditing and Methodology Data Wonk Featured Performance Audit