Create Audit Department Metrics with Excel

I was at a conference a few years back; sitting in a crowd of at least 300 auditors, most of whom were in leadership at their companies.  The facilitator asked if anyone had a process for tracking department productivity and project realization.  Being an extrovert who loves spreadsheets, I was unable to keep my hand down.  Oddly enough, very few other hands went up.  My memory is more than one but less than ten.

Before long, it was break time, but there would be no trips to the ladies room or coffee station for me.  A line of peers eager to learn my secret had formed.  I told them that I had no secret, I just use Excel.  The only difference between me and the auditors I chatted with was that I had taken some time to put together a workbook that met my needs.

This post is about two critical audit metrics, productivity and realization, how they connect and play off one another, and how auditors can calculate these ratios and create meaningful information for stakeholders using tools available to anyone with basic computer skills.

Productivity and Realization, Defined

Productivity is billable hours worked divided by total hours worked.  Billable hours have a few synonyms; charge hours, productive hours, project hours, etc.

Productivity = Billable Hours / Total Hours

Realization is hours worked divided by hours budgeted, for any given project.  You could also call it project efficiency.

Realization = Hours Worked / Hours Budgeted

Both of these metrics are easy to calculate, but require you have time tracking and budgeting methods in place.  If I get too far into time tracking or budgeting, both worthy of their own posts, we’ll miss the forest for the trees.  For this post, I’m going to assume you have some sort of process in place.  If you don’t, I will provide a solution so keep reading!

Breaking Down Productivity

So, you have a team of all 100% productive people.  Not good.  100% productivity is not attainable!  What about administrative time?  PTO?  Holidays?  Jury duty?  A direct report who tells you they are 100% productive is not being honest.  Now, if you have three auditors in the mid-seventies and one person in the fifties, you have some evidence that the workload is not fair and adjustments (and at least one difficult conversation) are in order.

What is a good productivity range for an audit department?  It depends, but I’d say you should be in the seventies.  If you can’t get there, you need to look into what is holding you back.  If generous PTO is the root cause of low productivity, well, good for you!  But maybe your team is struggling with too much administrative work, or onerous continuing education requirements.  Research, ask questions, and try to be objective.  If you’re tracking in the eighties, I would again advise taking a deep dive.  Maybe staff are neglecting their continuing education, avoiding PTO, or are not taking the time for water cooler chat.  Yes, being social is part of your job when you work in an audit department!

… and Realization

Maybe it goes without saying, but if each project tracks at 100% realization, you have a problem.  Your staff might be reallocating hours from easy projects in order to meet the budget for a complex project.  I’m pretty sure that’s fraud.  If each project is under 100% realization, you have a problem.  Your team might not be completing their work; cutting corners that could expose your department to loss of reputation.  They might be eating hours, which is also fraud!  If each project is over 100% realization, you have a problem.  Your team might not be qualified to do the work that you need them to do, or you might have under allocated project hours across the board, meaning you will not be completing your plan this year.

Ideally, as your plan year progresses your realization will be all over the place.  And because you’re on top of things and check in with your team regularly, you’ll know why one project went over, another went under, one was spot on, and…  Oops!  Look at that, you forgot to budget something important, but that’s okay because you have the information on hand to reallocate that time as judiciously as possible.  It’s a process, not an exact science.

Productivity and Realization: Two Sides of One Coin

When a team focuses on productivity, productivity inevitably goes up but may sacrifice efficiency.  In other words, anyone can work a ton of hours.  The great teams know what they are doing with those hours, work with purpose, and recognize when they need to step back and recharge.

When a team focuses on budgets and realization, efficiency gets better but may sacrifice productivity.  Having a team run off to happy hour because they wrapped a project under budget is great, assuming the work was completed and quality standards are being met.

Being busy is good, being efficient is good, and the goal is to find a good balance.  When you are collecting the data, creating the information, and using it to identify imbalances and fix them, you are being a leader.

Who Needs this Information?

So now we know how to calculate productivity, how to calculate realization, and how those two ratios relate.  Let’s identify the stakeholders, discuss why they need this information and think about how to present it to them.

First, the audit leader needs to have this information, updated daily if possible.  You see productivity trending in the sixties for a team member who is usually in the seventies.  What is going on?  You see a project tracking at 115% realization, and you have not yet reviewed workpapers or seen a draft report.  What is going on?  Data, information, decision making.  So consider yourself the most important customer of department metrics.

I also recommend giving your team full access to this information.  Why not let them know what everyone is working on, their own performance metrics, and how they stack up to their peers?  As an audit leader, I kept my master file on a shared work drive for the team.  My staff would update their hours daily, and have real time information on each team member’s productivity and project status.  We would discuss the results at our monthly team meetings.

Also, the Audit Committee needs to see a summary of this information with some regularity.  Do you want them to keep you on because they like you, or because you provide them with meaningful evidence that your team is productive and efficient?  When they ask why a project is behind schedule, do you want to roll your eyes and blame a last minute request from another executive, or do you want receipts ready to show them that you have three projects under budget that will absorb the project that was more complex than you anticipated?  Which example sounds more like an accountable leader?

Finally, you should have some version of this information for your CEO.  An engaged CEO will ask questions about your plan details, your process to create it, and how it is being implemented.  They don’t need to know all the inputs, so I recommend bringing a printed summary of department metrics to each check-in meeting you have.  If you don’t trust that your CEO will keep this confidential, then this solution won’t work for you, and you should probably start looking for a new position…  Another topic for another day!

Every once in a while you may have to share your results in broad terms with the company at large, your Board, perhaps some regulators depending on your industry, or external auditors if you engage a Quality Assurance Review.

But Really, Why?

Okay, you prepare executive summaries for your stakeholders using the data you gathered and information you created.  Now what?

Well, the majority of people you talk to might not follow.  Their eyes might glaze over as you explain the different ways productivity improved three basis points from last month.  Be honest; are you 100% engaged in every presentation you see or report you read?  The value of this information is not in monthly, quarterly or yearly reporting.  The value will come on those not so pleasant days, when you need support.

Let’s say budget cuts are coming.  The CEO informs you that the board has decided to reduce FTEs, and your department is potentially on the chopping block.  Isn’t it helpful to pull out your spreadsheet, show how your productivity is trending up and how losing a team member will affect the plan?

Which statement is more powerful?

I really like my team!  We work great together, and I don’t want to lose anyone!

Alright, I took Bob, our newest hire, out of the plan.  We would have 800 fewer charge hours at our disposal for the rest of the year.  That is equivalent to approximately two fewer audits being completed, and Bob has implemented the fraud monitoring that saved us over six figures to date.  I would have to redirect and train one of the other team members, which would further sacrifice our output.  Bob has also been handling some of the administrative work for the more experienced auditors; I can make some preliminary calculations of what effect that will have on the blended productivity rate.  What other information do you need?

It’s not all bad, though!  Let’s demonstrate a happier scenario, where your Audit Committee asks for a proposal to hire a new person.  What would you rather say to them?

I would love to have another direct report, there is so much they can do!  We are too busy anyway.

Here are the list of projects which were tabled during the planning phase last year, but considered high risk.  With an extra 800 billable hours, factoring in some new hire training and a start date of June 1st, I think we could tackle two or three of these, depending on the skill set of our new hire.  Is that in the ballpark of your expectations?

And Excel Can Do All of This?

Short answer: Yes!  Qualifier: If you know what you’re doing.

If you are a subscriber to The Audit Library, you can download our Audit Plan Template.  You simply input your budget hours, then start tracking time.  Each team member has their own tab, and enters their time daily.  As soon as the time is entered, the spreadsheet will calculate realization to date for each project, and productivity for each auditor.  There are some built in checks along the way.  The first tab is formatted to be printed or saved as a PDF file and shared with stakeholders.

Templates are available for departments with five staff members or less.  I also published versions tailored for credit unions.  For groups larger than five, you may want to invest in a more sophisticated technology solution, because it can be cumbersome for multiple people to coordinate accessing a shared Excel workbook.

If you’re not a subscriber to The Audit Library, I hope you consider becoming one.  My Audit Plan Template document is just one of many designed to help Internal Audit practitioners become more organized, accountable, influential, and effective.

Finally, thank you for reading!  I hope this post has shed some light on these often underutilized ratios.  Do you agree with my assumptions that metrics are critical and sometimes misunderstood?  What other questions do you have?  Leave a comment!


Share on facebook
Share on twitter
Share on linkedin