What should an Excel test look like?
From time to time I get asked about technical tests that people have sat or are due to sit. These sorts of tests seem to be more common and it strikes me that there's real skill in designing a good test. A company's resident Excel expert does not always make a great test designer and there's a real risk the test doesn't measure quite what it should.
For instance a badly designed test could end up measuring whether the candidate can work their way through a garbled set of confusing test instructions (= nothing to do with modelling at all). Or whether the candidate can model on their own at lightening speed, when really their general commercial awareness, teachability and ability to work a little bit collaboratively might be more highly valued.
One person I worked with had been set a test by a US hedge fund ahead of a potential internship. I think wisely they had given him the weekend for the test (so no time pressure) and it was open book so he had the whole internet and anyone in the world he could find to talk to about the test. In this respect I think the test was a little more real world.
Part of the test involved sorting data. The data set wasn't large or complicated but the exercise was a reminder that sometimes things you think should be easy in Excel (sorting data) are a little bit tricky to do simply. So I thought the hedge fund had manufactured a superb test (open book, no time pressure, the exercise was easy to describe but needed a very good level of Excel knowledge to complete elegantly).
If you're ever tempted to ask someone to sit a technical Excel test, I would recommend including an exercise like the hedge fund did. I think it was a great test:
If I were marking the test, I'd have awarded grades as follows:
领英推荐
The story about the prospective intern does almost have a conclusion. It's a bit of a bad news good news story really. The bad news: the test included a section that involved filtering staff based on their trading record. Ones whose trading results had been poor over the last six months were obviously destined for the sack as a result of the sorting exercise. Now we all know that sort of thing goes on but thats a pretty blunt message to send out to a potential new joiner. The place was obviously going to be brutal.
The (more) bad news: my guy was pretty gutted about how hed done in the test. He didn't care that people got the sack there readily. At this stage in his career he just wanted some experience. The test had been set for him months ago. He'd filled in what he could but had struggled with elements of it. He had fallen at this first hurdle.
The good news: it was still a great exercise for us to do together. We could still learn something from it. I suggested he email the hedge fund with his solutions saying that he had gone to the trouble of getting some help/ training on Excel modelling and please would they mind offering a few comments on his new solutions. It turned out they were big fans of sumifs but were surprised to see the conditional sumproduct and arrays. I think those may have been a bit new/ unusual for them. They told him they were definitely interested in him for their next intake.
Sometimes stories really do have happy endings.
(Whether he's started trading for them yet, how his trading record is going, and whether hes lasted his first six months I have absolutely no idea - but I know he's definitely a whizz when it comes to filtering a list of poor-performers so they could always keep him on forever just to do that)!