Reblog: Oregon Economic and Revenue Forecast, Dec 2015

“Oregon’s economy continues to make significant gains. Job growth has slowed just a bit from early 2015 rates, yet remains more than strong enough to bring the unemployment rate down and account for the influx of new workers as population growth picks up.”

The Oregon Office of Economic Analysis released its latest quarterly forecast. As positive as the overall picture is right now, there are a few dark spots, including a persistent disparity between urban and rural economic health.

Interested in knowing more? Read the full forecast here.

Source: Oregon Economic and Revenue Forecast, Dec 2015

Data Wonk Featured Noteworthy

How To: Quickly Document Inflation Adjustments

Sam Ewing once said “inflation is when you pay fifteen dollars for the ten-dollar haircut you used to get for five dollars back when you had hair.”

cocacola

Source: Library of Congress

Inflation seems to be a constant in our lives. What used to cost a nickel no longer does (my grandparents loved to tell stories about their youth and the cheap Coca-Cola – see advertisement ).

What does this have to do with auditing? Well when we compare the value of dollars over time, it is important to make sure we have an apple-to-apple comparison. We can do that by adjusting for inflation to make sure the purchasing power of dollars today is equal to the purchasing power of dollars of yesteryear.

Luckily for us, the United States Bureau of Labor Statistics has a handy inflation calculator that lets us easily calculate what a dollar in 1980 is worth in 2015 dollars (answer: $2.89).

If I only need to adjust a few figures, I tend to take a screenshot of the calculator and document them in a PDF. Below is an example.Inflationcalc

In our next installment of auditor how to, we will show you how to document more complex inflation adjustments using Excel.

Ian Green, CGAP and OAD Senior Auditor

Ian Green, CGAP and OAD Senior Auditor

Data Wonk Featured

Material Deprivation, Poverty, Child Care and Inflation

Does the relative lack of material deprivation among impoverished Americans mean they have an easy life? Not necessarily. Many of the poorest Americans have indoor plumbing, cars, and cellphones, and yet still struggle to meet their basic needs.

Read more at the Oregon Office of Economic Analysis weblog about evolving poverty measurements, census data tracking, and our accelerating relationship with technology.

Source: Material Deprivation, Poverty, Child Care and Inflation

Data Wonk Featured

Oregon’s history of auditing parallels the growth of the profession

Frequency of word occurrence in scanned books

The field of auditing is taking on a larger role in government, and a growing subject of authors.  Google has scanned many published books and you can use their ‘Ngram Viewer‘ to chart the frequency of a term. Here is the mention of ‘audit’ and ‘accountability’ in English language books since 1800. Auditing was written about much earlier than the broader term of accountability, though the two are running nearly parallel since the 1970s.

The topic of auditing is also reflected in the history of auditing in Oregon. Oregon’s Secretary of State is the ‘auditor of public accounts” as set forth in the 1857 State Constitution. The responsibilities of the auditor of public accounts had already been outlined in Oregon’s Territorial statutes of 1854.

Starting in the territorial days, the auditor was the general accountant of the territory and was also responsible for reporting to the legislature those recommendations deemed “expedient for the support of public credit; for lessening the public expenses; for using public money to the best advantage; for promoting frugality and economy in public offices; and generally, for the better management and more perfect understanding of the fiscal affairs’ of the state.”

Here is an interesting example of a performance audit by Secretary of State Kincaid in 1897. The state purchased paper by weight in those days. Thousands of dollars worth of paper were bought every year but had never been weighed to verify correctness. Secretary Kincaid bought a pair of scales and, in the very first shipment, found it was several hundred pounds less than its claimed weight, resulting in a $19 overcharge. The current value of that overcharge would be $540.

On the national scene, the GAO was created in 1921, which coincides with the first jump in ‘audit’ mentions among the writings. ‘Accountability’ was not a commonly used term during this entire time period.

Then, starting in the early 1970s, both ‘audit’ and ‘accountability’ started their steep increases. GAO developed and issued the first version of Government Auditing Standards, also known as the Yellow Book, in 1972. Correlation doesn’t mean cause because other factors or events could have triggered this growing dialogue about accountability and auditing. For example, the Watergate investigation  leading to President Nixon’s resignation could have sparked increasing use of these terms.

In the early 1980s the Oregon Secretary of State started assigning staff to conduct these performance audits and promoting their value. Secretaries of State Paulus, Roberts, and Keisling all grew the performance audit capacity in the office, during the period that ‘audit’ and ‘accountability’ usage continued their steep climbs. New technologies and analytical tools have allowed the profession to grow in sophistication in the past decades, and information technology audits are now a common genre of performance audits.

One note of interest: the seeds of the Division’s performance audits were planted in Oregon’s Territorial Statutes, and subsequently grew into the Constitution’s current office of Secretary of State.

Compare the previous blue, territorial auditor responsibilities with the definition of performance auditing in the latest Government Auditing Standards:

Performance audits provide objective analysis to assist management and those charged with governance and oversight in using the information to improve program performance and operations, reduce costs, facilitate decision making by parties with responsibility to oversee or initiate corrective action, and contribute to public accountability. [2.10]

There is a remarkable overlap in responsibilities between the Oregon territorial auditor and the modern Secretary of State Audits Division, which complies with these standards.

Auditing has 160 years of history in Oregon and growing more important, as the profession’s trend continues its path here and nationally.

Accountability and Media Data Wonk Featured Performance Audit

How To: Analyze payroll data using Excel’s SUMIFS function

A few years ago, I worked on an audit at the Oregon Department of Corrections (DOC). Elected officials were concerned that the DOC was spending too much money on overtime. We used a combination of ACL and Excel to conduct this audit work. We followed the National Institute of Corrections Net Annual Work Hours Post Relief Factor methodology.

First, we accessed our state’s central payroll database. We pulled the tables we needed that recorded hours worked by pay code by month. The way the table was organized was not right for Excel, so we first needed to prepare the information by using the Summarize function in ACL. For this example, you will need to summarize on employee classification, pay code, and location. Aggregate both hours and dollars.
With the data prepared, it should like something like this:

Table 1 – Data

sumif post pic 1

The first column denotes the prison. The next is the classification of the correctional employees (e.g. sergeant, corporal, officer etc.). The following column shows pay codes – we had 50 different pay codes in our data set. The next three are straight forward – dollars, hours, and counts. Lastly, we have a short description of the pay codes. For example, CD is career development/training.

After developing this table, I calculated the average for each row by dividing by the FTE in each classification at each prison. For example, there were 3881.5 hours of CD and 184 FTE, yielding an average of 21 CD hours per staff. Now that I have my data ready I can start analyzing it. I want to know if there are differences between locations, classifications, and pay codes to see if this is driving any overtime.

I set up a table in excel, shown below. CCCF stands for Coffee Creek Correctional Institution and TRCI is Two Rivers Correctional Institution. Pay code descriptions are above. Classifications range from officer (C6775) to captain (X6780).

Table 2 – PRF

sumif post pic 2

I can now use this table within my SUMIFS function to pull average hours from the other table.
The SUMIFS function has three main parameters: Sum_range, Criteria_range, and Criteria.

sumif post pic 3

Sum range is the range of data you want summarized. In this case, I wanted to pull average hours from my payroll data. You only have one sum_range, although you can have as many criteria as you want. I will have three criteria. I want the average to come from (1) the correctional institution (2) the employee classification and (3) the pay code.

Columns a, b, and c, from the table 1 will each be a criteria range. I will use table 2 as my criteria. Here is what the formula looks like. Not I use a combination of absolute and relative references (our next post will delve into this in more detail). Absolute references ‘lock’ in cell ranges in a formula so when you drag the formula it does not change. Absolute references are denoted by a “$”.

SUMIFS function

sumif post pic 4

Reading left to right, the function is asked to summarize the averages calculated from the table 1. It is to look at the first column for the prison acronym to match to “CCCF” highlighted in blue. Next it searches employee classification in column b for “C6775”. Finally, it matches pay code “CD” highlighted in purple.

Once I have the one formula set up, I can drag it over and down and calculate over 500 different averages in a few seconds. After setting up one year of data, all you need to do is copy the tab and re-link to the next data and you can compare year-over-year trends in minutes.

There’s a few steps I’m skipping over, but the end result from these calculations looks something like this:

sumif post pic 5

So what did we find in our audit? Overtime is not as big of a problem as people perceive it.

Most people think that overtime has to be more expensive because you are paying time and a half. What is often left out is the cost of leave time and other benefits, which often add up close to 50% of salary making the pay difference negligible. Furthermore, if you hire an officer to replace overtime you must pay them for about 2,000 hours per year. Whereas with overtime, you only need to pay it when you need it. What is cheaper? $65,000 for a new officer or $25,000 for 500 hours of overtime?

So if you pay only 500 hours of overtime per year in a given shift, it doesn’t make sense to hire an officer to cover that time, because they would be paid for hours not needed. Below is a great example.

Overtime at CCCF

sumif post pic 6

You can see that overtime is quite varied. It peaks around hunting and fishing seasons, flu season, and winter holidays. This is not that surprising as more people are calling in sick during these times and someone needs to work the overtime to replace them.

If CCCF hired an additional officer to work these hours, they would only reduce overtime by a small fraction. At best, CCCF could eliminate all of the overtime between 0 and 8 hours. To eliminate all overtime, CCCF would need to hire 6 FTE on the graveyard shift, or 48 hours of coverage, which is vastly more expensive than the cost.

As we found out in this audit, sometimes your gut, i.e. overtime is costly, is wrong.

ian

Post prepared by Guest Blogger and OAD Senior Auditor Ian Green

Data Wonk Featured How To