The closest to perfection a person ever comes is when he fills out a job application form.

- Stanley J Randall

Anyone who has ever interviewed a candidate to fill a position (requiring strong quantitative skills) has discovered what I shall refer to as the quality chasm: the large divergence in abilities that often appears between an interviewee’s purported accomplishments as reflected in his resume and the performance he actually demonstrates after having been hired for the job.

I’m an economist. I’ve interviewed many individuals seeking jobs as analysts. Qualified individuals must have strong quantitative skills, in particular be adept at market research and working with data.

After being frustrated by countless individuals who interviewed well, but who didn’t follow through with expected levels of performance, I devised a simple tool to help me quickly evaluate the actual performance of interviewees. I call my tool The Excel Test.

My spiel to interviewees is that I’m not looking to evaluate their Excel skills. Rather, I’m looking to see how they think and approach problems. I tell them that they are free to ask me any questions they wish, and I emphasize this point.

Task 1: Data Entry

I sit them in front of my computer with a new Excel worksheet open. I hand them a piece of paper with the following information on it:

Table 1

During one project I worked on, we had hired a bunch of temporary analysts to help us key in survey data. One analyst was much quicker than all the others at entering the data and was amply lauded by all. Later, however, much to our dismay, we discovered that he had made a huge number of data entry errors. Speed without accuracy is worthless in this type of task. In fact, it’s worse than worthless; it’s downright destructive. There is no easier way to discredit any quantitative analysis than by finding errors in the data or the calculations.

After handing the interviewee the paper, I ask him to enter the data into the worksheet. When observing individuals perform this task, my main concern is that they check the accuracy of their data entry; that is, they check to make sure they’re typing in the numbers correctly.

Task 2: Data Manipulation

Anyone who works with data will know how often they have to rearrange the data to suit their purposes. One of the more common forms of rearranging arrays of data is to transpose (flip) the rows and columns. An analyst who is not familiar with transposing matrices must quickly learn how to do so.

Students should learn about matrices and matrix transposition when they take Linear Algebra in school. Most quantitatively-oriented students will take Linear Algebra in high school and use the principles in all subsequent levels of mathematical analysis, such as calculus.

I continue my Excel Test by telling the interviewee that we often use data to make tables for use in our analyses. What I want the interview do is to make a table using the data they just typed in. I want the table to have the dates running across the top, with the Quantity, Units Price, and Unit Cost in rows below the dates. Note that I purposely do not use the words transpose or flip (rows and columns) in my request. This is what I’m asking the interviewee to create:

Table 2

One individual I was interviewing proceeded with the task by re-entering the data in the appropriate format. Not what I was looking for.

Most interviewees respond to my request by staring at the data. I can tell they're trying to figure out how to transpose the data, but they don’t usually say that. After a few seconds of silence, I ask the individuals what they’re trying to do. At that point, they usually say that they want to flip the rows and columns. I tell them that this is called data or matrix transposition and ask them if they know how to do it. They usually say “no”. So I show them:

• Highlight the data in the worksheet
• Select Copy
• Move to a new cell
• Select Paste Special
• Select Transpose

Voila! Excel creates a new matrix with the transposed data. Most of the interviewees smile and say “that’s cool!” If nothing else, I’ve taught them something interesting during the interview.

While interviewees should be familiar with matrix transposition, I’m not really expecting them to know how to do this. What I’m looking for is for them to not waste a lot of time staring at the screen, wondering how to do what I asked. What I want them to do is to ask for help. There’s no shame in asking for help. There is, however, shame in unnecessarily wasting a lot of time trying to figure out things that other people probably already know how to do.

At this point, whether or not the interviewee formats the data - adds dollar signs to Unit Price and Unit Cost - is not so important. However, if they do make the effort to do so, I give them extra points for understanding the big picture.

Task 3: Calculations

The next task I ask the interviewee is to calculate Total Revenues, Total Costs, and Total Profits for each year in rows below Quantity, Unit Price, and Unit Cost. This is what I’m looking for:

Table 3

Again, any individual seeking a job as an analyst (in economics) should know how to calculate Revenues (Quantity x Unit Price), Costs (Quantity x Unit Cost), and Profits (Total Revenues – Total Costs). However, I’m not really expecting them to know how to do this. Again, what I’m looking for is for them to ask for help if they don’t know how to do it. Furthermore, if I show them how to do the calculation, say, for Total Revenues in 2009, are they able to apply what they’ve learned to the calculations of

• Total Revenues for 2010 – 2012
• Total Costs for 2009 – 2012
• Total Profits for 2009 – 2012

Not knowing how to do something, even something relatively basic, is not completely inexcusable. What is inexcusable, however, is not being able to learn something new and apply it in the future when circumstances call for it.

Task 4: Totals and Averages

The last set of tasks is calculating totals and averages. I ask the interviewee to add a column at the end of each column with the Totals across the years. This is what I’m looking for:

Table 4

Most people know conceptually what totals are, but again, the issues here are (i) whether or not they ask for help if they don’t know how to do the calculations, (ii) if they do get help, whether or not they can apply what they’ve learned, and (iii) whether or not the candidates add totals for Unit Price and Unit Cost, which are nonsense numbers.  If they do include Totals in these two columns, then they clearly do not understand the big picture. 

Finally, I ask the interviewee to add a column computing “average” prices and costs. The question is purposely vague. This is what I’m looking for:

Table 5

Most people will calculate a simple average of the annual prices and costs. When they do this, I say “what you’ve just calculated are the simple averages. Do you know how to calculate weighted averages?” Sadly, not one person I’ve ever interviewed has ever understood what a weighted average is, let alone be able to explain why, in this case, the Simple Averages are greater than the Weighted Averages (there are greater numbers of sales at the lower unit prices and costs than there are at the higher unit prices and costs).

Conclusions

I have found that my little Excel Test is an easy way to quickly weed out undesirable candidates. While, ideally, I would like to hire analysts who are already familiar with the basics, my experiences have been that most people do not have good understanding of the basics. What’s more important, though, is that candidates are willing to ask for help when they need it, and they are able to quickly learn and apply new concepts to their future tasks.