From time to time we get to work with people who have to sit an Excel test as part of a job they are applying for. On one 1-2-1 financial modelling course we had a delegate who came in with a modelling test he’d been asked to sit. He was applying for an internship with a US hedge fund. The bad news was that 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. That’s a pretty blunt message to send out to a potential new joiner as part of an Excel test. The place was obviously going to be brutal. Maybe they were happy to scare people off.

Our guy was made of the right kind of stuff. He didn’t care that people got the sack there readily. At this stage in his career he just wanted some experience. He was more concerned about the fact that the test had been set for him a couple of months ago. He’d filled in what he could but had struggled with elements of it. He had fallen at this first hurdle and he didn’t know when his next test might be coming up.

So we decided to work through the test together. It remained a great exercise for us to complete. We could still learn something from it.

The data set wasn’t large or complicated (employee down the side, time period across the top, financial performance in the middle) but the exercise was a reminder that sometimes things you think should be easy in Excel, like sorting data, can a little difficult to accomplish simply. So the hedge fund had manufactured a superb test. It was easy to describe but difficult to complete elegantly. If you’re ever tempted to test someone’s real Excel experience, you might want to think about including an exercise like the hedge fund did:

  • Sort data in two dimensions.
  • Amalgamate by time (year across the top).
  • Amalgamate by category (employee down the side).
  • Produce a summary table.

You can click on the picture below to download the test:

Excel test

Here are some potential solutions that get the job done but won’t win any prizes:

  • Use Excel’s manual sorting or filtering functions.
  • Use a series of regular/ manual sum functions.

A better class of solution would see someone demonstrating some knowledge of advanced Excel functions. You could use a specialist and useful advanced amalgamation function like sumifs. Something like this would need to be applied in series so it’s still not the shortest neatest solution:

Excel test

Here are some even better solutions demonstrating knowledge of ‘hidden’ Excel features and a lot of prior experience. They solve the problem with a certain artful elegance:

Sumproduct and Array functions for sorting data

The functions above definitely win in the hunt for the shortest neatest solution to the hedge fund’s problem of filtering out poor performing staff!

The Excel test: a happy ending

The story about the 1-2-1 course training for the prospective hedge fund intern does almost have some kind of happy ending. During the course we suggested he email the hedge fund with his new solutions explaining that he’d 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 array functions. Those may have been a bit new/ unusual for them. On the last day of the program the hedge fund emailed back saying 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 he’s lasted his first six months we don’t know. But he’s definitely a whizz when it comes to filtering a list of poor-performer so they could always keep him on forever to do that!

Read more

You’re reading stories from the world of Excel financial modelling.

Brought to you by Financial Training Associates Ltd

Read more stories