I created this challenge in 2016 in response to a discussion going on at the time – the need for some standards on Excel Competence (ICAEW).
Who decides how a spreadsheet is designed? How do we decide that it does the job efficiently? Or whether it does the job at all?
On reflection I could see that the challenge is far too difficult for prime time. But, I think it’s perfect for the really big gurus out there.
(While I rehash the video) I’ll outline it here. See if it’s do-able?
The challenge is actually based on a real-life problem I had.
I run the Beatles Jam Meetup Group in London. Every month we got together in a pub and jam for about 6 hours!
While the instrument parts are pretty well known, I found that we let ourselves down on the vocal harmonies. The ‘guru’ we followed is Galeazzo Frudua, who has expertly deconstructed the harmonies of 93 Beatles songs.
Anyway, to do this challenge you don’t need to be able to sing harmonies. All you need to know is, if you’re not familiar with the Beatles, on any particular song they sing either solo, or two of them, or three of them.
The challenge is to create a model to help with managing a meetup. To be able to do a particular song we need to have people for each of the required parts. So, songs like ‘Yesterday’ is never a problem, because it is solo. But a song like ‘If I Fell’ can’t be in the programme unless there were two people there who knew the two parts (even if they’re crap at singing them parts!)
When a member joins the club he/she needs to indicate which parts of which songs they know, and can do.
Here’s a list of all the songs the Beatles recorded (Download the Excel file TBA). They are 216 in total. The highlighted cells indicate the parts. The darker blue is the main part; the lighter blue is a harmony part, but you don’t need to be bothered by that. The important bit is that any highlighted part needs to have someone singing it.
(BTW it is a rule of the meetup group that all parts must be sung. Not just everyone singing the main part! if so it becomes a singalong or karaoke, or worse. Yuck)
Question 1: So, the first question is, how would you collect this information when they join?
Question 2: The Beatles recorded many more songs than the officially released records during their 1962-69 career. These were released on further LPs/CDs since their disbandment. So, now you want to extend the list by another 50 songs to include these. How would you get the new information to the existing members, and receive their new answers? Also remember that the members should be able to modify their choices at any time. And they will, as they learn new songs/parts.
Question 3: So, now on the next meetup when members sign up to attend, you (the manager) can work out which songs are viable – ie. for which you have all the vocal parts covered. Right? This will be our Play List for the jam. How would you work this out?
Question 4: Now it gets interesting! On the day, some people send their regrets that they can’t attend (cat is sick, trains are on strike, raining too hard etc). But on the positive side, some members turn up unannounced. You won’t know this until the moment the jam event starts. How would you modify the Play List to adjust for this late changes? (You won’t have much time …)
That’s the challenge. Can you do it?
To think of this as a modelling challenge, the input is the data about who can sing what, and who is actually physically attending. The potential list of songs will change, and the members’ choices of what they can sing will change. So, all the inputs should be able to change. Including, who actually turns up.
The logic is, which songs are viable? ie. which songs have someone (however crappy!) for each of the parts?
The output is, the Play List for the jam.
A Simpler Challenge
As I said, this is a tough one.
So I created a simpler one for those who are not yet gurus, but are gliding towards ‘guruness’ – the Celebrity Chef Spreadsheet Challenge.
Both are suitable for a discussion/workshop on how we decide how to setup a spreadsheet model. The fact that it is non-business makes it perfect for focusing on the principles, excluding any need to have any prior technical knowledge of the process (which most financial modelling challenges require you to have to participate).
ICAEW – Spreadsheet Competency Framework (pdf) 2016
Abbey Road 50th Anniversary – 8 August 2019 (who’s that idiot at 0;20?)