My models from ModelOff Round 1

Today was the first qualifier round for ModelOff, the World Financial Modeling Championships. I’ve posted my models for the three main questions below, with a few comments on each. Please note these are what I made during the test, with lots of time pressure, etc – I don’t know if they’re complete and correct (I hope so!), and I’m sure there are things I would have designed differently with a bit more time (some of which I’ve commented on below). But since I always find it interesting to see how other people tackle a challenge like this, I thought you might find it interesting to see how I do too.

If you didn’t take the test today, you can access the questions here (and I’d definitely recommend giving them a try before reading this). I’ve posted them in the order I attempted them.

Section 3: When it rains, it pours

This question was about prioritization of cash flows, which was a big part of the main model in last year’s final. I didn’t do well on it back then. I’m hoping it went a bit better today.

A couple of things stood out for me on this one:

• The interest rate schedule on the first loan tripped me up – I was expecting the rates to only change between periods (and had modeled the interest rate line with a simple VLOOKUP), but then realized it changes mid-period and had to build something more complicated to handle that.
• I also almost missed the bit about releasing the excess in the reserve accounts.
• I wrote the closing balance formulas for the DSRA, MMRA, and the overdraft in two parts. Each one has the form IF ( [Cash flow after debt payments] > 0, <what to do if positive>, <what to do if negative> ). I actually wrote the positive parts of all three first, with a placeholder “X” for the negative, so I could see that the logic all flowed in those cases first, then went back and wrote the negative ones. I think that made it a bit less head-spinning, since the order the accounts come in is reversed for cash coming out vs cash going in.
• I love MEDIAN for capped payments – e.g. the payment to the reserve accounts if there’s positive cash flow will be at least 0, and at most the shortfall in that account vs the target balance, so you get a sort-of Z-shaped curve. You can handle that with a MIN and a MAX (that was actually one of the short questions at the start), but I find MEDIAN much cleaner.

Here’s the model. You can switch the active Profile with the drop-down in cell E48 on the Calculations tab.

MO17 Round 1 – Sec 3 – When it Rains it Pours – Diarmuid Early

Section 2: Castles in the air

This question involved building relatively straightforward (if fidgety!) models of 5 different possible investments, then figuring out the best (highest NPV) portfolio you can afford on a fixed budget.

There’s not much remarkable about my individual investment models. I tried to make them as modular as possible so that going from having one built to having five built was easier, but that only worked to a certain extent since the assumptions were structurally different in different cases.

One thing I do have to give myself a bad mark for is that I built the revenue and cost lines with monthly payments differently from the ones with payments only in some months, which means that the assumption of 12 monthly payments becomes structural (i.e. if you change the assumption cell for monthly payments from 12 to 6, there will still be a payment in every month). It’s never good to have an assumption cell that you can’t really change, and it would have been better to use the same ‘payment cycle’ line as I used for the ones with quarterly payments in the monthly ones too so they were capable of changing. The kind of trade-off you make when you’re in a rush…

Another bad mark is for how I changed the assumptions – the last question asked about a change in the months that the costs of property 3 hit (from 4 and 10 to 6 and 12). Because I was hurrying, I just typed in the new numbers in I95 and I97 on the assumptions sheet. A better approach would have been to do something like I did in the valuation section: have the two sets of assumptions, an additional assumption cell to choose the scenario, and then a calculated cell for the ‘applied’ value. But since I didn’t, make sure you change it back if you want to see answers to the earlier questions!

My favorite part about this question (and also the one I laid out least well, since I was in more of a rush by that point!) was the portfolio calculation: given this set of 5 possible investments (2 of which are either / or) and a total budget, which set of investments should you make to yield the maximum NPV within budget. The (mostly) unlabeled section E98:H120 lays out the 23 possible investment portfolios (2 choices for whether to include each of 1, 2, and 3, and 3 choices on #4 to exclude, include without the overhaul, or include with = 24, less the one that’s not buying any of them).

MO17 Round 1 – Sec 2 – Castles In The Air – Diarmuid Early

Section 4: Go with the flow

I found this one a little headspinning (maybe just because I did it last). It models a fairly complex flow of the allocation of waste by a waste management firm between different contracts and different destinations, all with different associated revenues and costs.

I think the logic was mostly pretty clean (except that I got tripped up on how to interpret when the inflation timing applied), but it required a certain relentlessness to follow it all through. I think this model definitely has the simplest formulas of any of the three, at least in my rendering.

MO17 Round 1 – Sec 4 – Go With The Flow – Diarmuid Early

Overall thoughts

I thought the questions for round 1 this year were quite tough compared to previous years. I assume that was deliberate, at least in part, since they also made a decision this year to extend the time from 2 hours to 3 hours (with some bonus marks for finishing early), and a lot of good modelers I’ve spoken to today said they needed the full time.

I thought the difficulty was fairly evenly balanced between the three, which seems to be reflected in my times. Based on the times I saved each file, it looks like I spent 53 mins on section 3 (vs 45-65 min target), and 39 and 38 on sections 2 and 4 respectively (both vs 30-45 min targets), so between 18% and 30% over the lower-end target times on each.

The bonus for finishing early introduced a bit of an extra strategic element, but it was relatively small (up to 5% of the marks if you finished an hour early), so I didn’t try to keep within 2 hours – although I doubt I would have succeeded if I tried. But it was enough that I decided to risk wrapping up 30 minutes early rather than using some spare time to go back and re-check things. I’ll regret that if I missed a big mistake, of course!

 

The one thing I missed in this set was some more non-traditional problem solving, in the style of ‘Roll the dice’ or ‘Tally up’ from last year, or ‘Purple City’, ‘Snakes and Ladders’, ‘Monte Carlo’, etc, from previous years. Of course, that’s a self-interested lament, since those are the kinds of questions I do best on.

Round 2 is in two weeks, so hopefully I’ll have another post after that… wish me luck!