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!

9 thoughts on “My models from ModelOff Round 1

  1. Thanks a lot for your generosity and for your openess about your work. Can we share here our work on previous Modeloff case study ? I have a wonderful formula (by my modest and humble standards !) on the Roll Dice case study from the 2016 contest.

    Like

  2. For me and I am not the kind to tackle Math Olympiad or Math Puzzle challenges, the most tricky question was to find out the “Ordered subset of four”. This is my formula : =IF(ABS(G10-H10)1 ; FALSE ; OR(AND(ABS(E10-E10:H10)={0.1.2.3});AND(ABS(F10-F10:I10)={0.1.2.3});AND(ABS(G10-G10:J10)={0.1.2.3})))*50. I use a conditional function to reduce the time of computation considering we have 6000 simulations. Knowing for sure that the 3rd roll and the 4th roll will be consecutive number if there is an ordered subset of four in the turn, I test for this condition before everything else. If the set of number fails the test, my computation stop right there.
    After that, I use the ABS function in substracting the number in the first position with itself (not necessary in fact … ) and the 3 consecutives position of after him. I do the same until the 3rd position. The abs function handles the reverse orders like 5432 or 4321.
    I think the other questions are pretty simple and it’s just a matter of speed (I need to work on it! I can use Excel with the mouse unplugged but I think it’s more a matter of focus and practice of creating algorithms in a stress mode. If you have any tip, I am your man. To be honest, I am not a believer in the myth of the born genius. It’s rare to find one of those special ones. I think, knowing the capacity of our brains, it’s our ability to focus deeply on a task and some kind of obsessive behavior which discriminate between mediocre and excellent programmer : by the way excel is hardcore programming !). This is my take ! Looking forward to reading and to scanning your spreadsheet. I don’t know how to attach mine. Regards,

    Like

  3. the condition is ABS(G10-H10) different of 1.
    By the way, I don’t know how somebody can finish this case in less than 1 hour apart from having the habits to program at high speed. Can you share your journey to this level of achievement in this blog ? It will be very interesting to here from you. I am very admirative of Modeloff champions and I follow the contest since its inception in 2012. It motivates me to unplug my mouse and learn to typewrite first of all (I learned it in 3 months of obsessive and deliberate practice) and to learn the maximum of shortcuts. Thanks for the example you and the other modeloff champions are for us (mere mortals …)

    Like

  4. I have made a huge mistake in my formula : using abs for my computation of consecutive positions for the 1st roll, the 2nd and the 3rd roll. This is the perfect formula : =IF(ABS(G10-H10)1;FAUX;OR(AND(E10-E10:H10=(G10-H10)*{0.1.2.3});AND(F10-F10:I10=(G10-H10)*{0.1.2.3});AND(G10-G10:J10=(G10-H10)*{0.1.2.3})))*50. The 3rd and 4th position give the sense of the ordered subset of four : reverse or straight order.

    Like

  5. Now It’s done and complete and my answers match perfectly the answer provided by Modeloff ! Btw , question 27 is also tricky. i have used matrix algebra and unit matrix function in Excel to solve. The best thing to do is for sure to send you my spreadsheet. It takes me almost 2 hours to solve. I have to confess, I was not deeply focused on the task … Sorry for my many comments (you can delete some of them) and for my poor grammar (I am a french speaking guy with few occasions to write in english …)
    Regards

    Like

  6. I will bother you once more time. I have finished the case “When it rains, it pours”. I think 2 hours of work. By the way, I have checked your model. Don’t tell me you manage to finish it in less than 1 hour. Fantastic !!!
    First time, I see such a counterintuitive use of MEDIAN for capped payment schedule. I will reuse it in my future models.
    I have a spreadsheet with all the answers (I use circularity to manage the cashflow profile switching for my answer formulas to be dynamic). Just tell how I can send it to you ? Regards from france.

    Like

  7. An explanation of the logic of your portfolio of investments structure would be welcome and truly : it’s kind of a hard puzzle. The formulas are pretty simple and there is some pattern (when investment 4 (a or b) is at 0, there is a switch in investment 3 serie of 1 and 0. I can’t understand the logic behind the serie of choices for investment 2 related to investment 3.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s