High Performance Training in Financial Modelling

Content from Dan Mayoh’s presentation at the ModelOff GTC in London (3-4 December 2015) is now available for download.

Click here to download the zip folder, which contains:

1. High Performance Training (Slides)
2. Design – Acquisition Financing – Past ModelOff Question
3. Index files
4. Array files

If you have any questions, follow up with Dan directly: dan@fintega.com

Joseph Lau crowned ModelOff 2015 World Champion

ModelOff 2015 Champion

ModelOff is delighted to announce that Joseph Lau from the Commonwealth Bank of Australia has won the 2015 Financial Modelling World Championship, with Willem Gerritsen of the Netherlands coming in Second Place and Third Place going to Alvin Woon from Bank of America Merrill Lynch in Hong Kong, who famously got the perfect score for Online Round 1. In an energetic Finals Awards Ceremony held at Microsoft’s Cardinal Place offices in London, Lau received the honour ahead of 15 other finalists and several thousand who had competed in the online qualification rounds. As World Champion, Lau wins £10,000 and the now iconic Golden Keyboard.

The win came as no surprise. Lau, in his second consecutive ModelOff Finals, achieved the highest aggregate score from the two Online Rounds, scoring 119/120 (second only to Woon) in Round 1 and topping Round 2 worldwide with 115/120. Despite being one of the favourites to take out this year’s title, the extremely humble Lau sought to downplay his chances.

Lau is nicknamed ‘The Fine Wine’ because he just keeps getting better with age. He is the most experienced modeler out of the 16 Finalists, and indeed the oldest ever ModelOff finalist at age 43. His experience proved to be invaluable, soaking up the pressure of 4 hours and 10 minutes of Finals testing – even after 2014 World Champion Diarmuid Early completed and walked out of a 30-minute challenge with 15 minutes to spare. That special feat by Early earned him the Innovation Award. In his acceptance speech, Lau joked that “I guess this proves age is no barrier” and thanked his family “who are probably fast asleep (in Sydney)”. As it turned out Lau (43), Gerritsen (36) and Woon (36) were the three eldest finalists and finished in order of age. So much for modelling being just a young kid’s game!

Overall the Finals results were very tight at the top and Question Design Team members Dan Mayoh, Alex Gordon and Hilary Smart had their work cut out to separate the Top 3. In the end the competition’s most experienced professional delivered Australia their first Financial Modelling World Champion.

GTC Preview: Part 2

We’re only days away from the ModelOff Global Training Camp 2015 in London, presented by Microsoft at their Cardinal Place offices. The agenda is almost fully set and there are 32 world-class training sessions open to the 250-plus registrants.

Three streams will run throughout the GTC: Financial Analysis and Modelling, Corporate Finance and Advanced Excel. With more than 40 world-class trainers and presenters from the UK, Europe and around the world also descending on London for the event, participants have the opportunity to meet, learn from and share with some of the industry’s foremost financial and analytical minds. It makes for some tough choices ahead for GTC participants! In Part 1 of the GTC Preview, we looked at some of the highlights across the Financial Analysis and Corporate Finance Streams.

ExcelHere is Part 2, which focuses on the sessions hosted by the Microsoft Excel Product and Engineering Teams in the Advanced Excel Stream. It is in no way intended to be exhaustive and we encourage you to keep an open mind for all GTC sessions to see which suit your skills, interests and needs the most.

1. What’s new in Excel 2016?
Presenter: Eli Schwartz
Time: Thursday morning

Brief: Are you using Excel every day to analyse data and derive insights from it? Are you interested in learning what’s coming in Excel 2016 and how we’ve improved it as the hero tool for business analytics? If so, then this session is for you! Come see the innovations coming in 2016, learn how Excel can better empower you to gather the data around you, get to the insights that matter, and share them to make an impact.

2. Advanced Analysis & Power Pivot
Presenters: Ashvini Sharma and MVP Henk Vlootman
Time: Thursday late morning

Brief: In this session, we’ll cover Power Pivot, a set of features that make it incredibly easy to perform analysis with large volumes of data in Excel, and DAX, a calculation language designed with you in mind. We’ll also briefly look at how to perform analysis over uncertain data!

3. Excel Everywhere
Presenter: Sangeeta Mudnal
Time: Friday morning

Brief: In this session you will learn about our vision for Excel on iOS, Android, Windows mobile, Mac and Excel Online including our approach to shared code. We will also share some of the amazing strides we have made across the mobile platforms with great demos, so be sure to come with Excel installed on your mobile device and follow along.

4. Data acquisition in Excel using Power Query and Data cleansing & manipulation
Presenters: Dany Hoter & Joe McDaid
Time: Friday late morning

Brief: not available at time of publication

5. Keynote: Spreadsheet archaeology: what can we learn for examining spreadsheets?
Presenter: Felienne Hermans
Time: Friday late afternoon

Brief: When the Enron energy corporation went bankrupt, university researchers acquired a subset of their emails. Felienne Hermans of Delft University of has examined those emails, specifically looking for attached spreadsheets.

Felienne found that emailing spreadsheets was very common within Enron, because the set of emails spanning 15 months of emails contained over 50.000 attached spreadsheets! In this session Felienne will explain:
• The most interesting results from the Enron set
• What conclusions we can draw from it
• Methods and techniques to battle spreadsheet problems”

View the full GTC agenda here

GTC Preview: Part 1

We’re only days away from the ModelOff Global Training Camp 2015 in London, presented by Microsoft at their Cardinal Place offices. The agenda is almost fully set and there are 32 world-class training sessions open to the 250-plus registrants.

Three streams will run throughout the GTC: Financial Analysis and Modelling, Corporate Finance and Advanced Excel. With more than 40 world-class trainers and presenters from the UK, Europe and around the world also descending on London for the event, participants have the opportunity to meet, learn from and share with some of the industry’s foremost financial and analytical minds.

Such a jam-packed schedule will invariably force GTC participants to make some tough choices in their session selections. In Part 1 of our GTC preview, here are some of the ModelOff Team’s highlights across the Financial Analysis and Corporate Finance Streams. It is in no way intended to be exhaustive and we encourage you to keep an open mind for all GTC sessions – choose sessions that suit your skills, interests and needs the most.

1. Three World Champions
Presenters: Alex Gordon, Hilary Smart, Diarmuid Early
Time: Thursday late morning
A unique moment when we bring all three ModelOff Financial Modeling World Championship winners together in the same room. They’ll discuss their successes and run through the ModelOff questions which led them to glory. To have a specific past ModelOff question discussed, email us in advance.

2. The Great Debate
Panellists: Levi Bailey, Simon Hurst, Morten Siersted, Rickard Warnelid, moderated by Dan Mayoh
Time: Friday late morning
The topic is “Why can’t we agree on Global Financial Modelling standards and best practice?”. If there’s an upside of why we can’t, maybe this panel is it.

3. Dan Mayoh’s Workshop: High Performance Training in Financial Modelling
Time: Friday morning
Mayoh is a world-leading financial modeller and current head of the ModelOff Question Design Team. This workshop will be a brief masterclass showcasing a few tips and tricks for anyone aspiring to be a great modeller, and an interactive discussion facilitated by Dan exploring the most useful content and methods to provide advanced and relevant training to those people who are already the star Excel user / financial modeller in their office.

4. Alastair Matchett’s Seminar: Private Equity and Venture Capital
Time: Thursday morning
This is a seminar ideal for any advanced financial professional. Matchett has some serious cross-industry experience in private equity. Learn best practice modelling and techniques for M&A, LBO Analysis, buyouts, synergy identification and venture investment.

5. The Innovation Panel
Panellists: Alastair Matchett, Felix Zumstein, Antoine Vettes
Time: Thursday early afternoon
The world’s rapidly evolving, and so is FinTech. Learn more about how disruption and innovation are changing financial services as you know it – from M&A to due diligence and even business analytics. Experienced panellists Matchett, Zumstein and Vettes bring unique insights from exciting start-ups.

6. Financial Modelling Technical Leadership Workshop
Presenters: Levi Bailey, Ben Norris
Time: Thursday late afternoon
This session will be a cracker. Bailey and Norris are both top 25-ranked financial modellers and Bailey was also a ModelOff Finalist in 2014. This interactive workshop will cover creating a culture of excellence as well as the technical complexities of advanced modelling.

7. Real World Simulation of Investment Banking
Presenter: Rahul Sonthalia
Time: Friday morning
This seminar will appeal to anyone interested in investment banking. Learn to understand the complexities of a full-service IB. The session will help you think holistically around financial analysis, transaction analysis and research.

View the entire agenda here

Reflections on My 2015 ModelOff “Season” (Pt 2)

by Stevenson Yu
Find Stevenson on LinkedIn:

Part 1 is available here.
How did I approach Round 2?
First of all, realistically speaking, there is no chance of qualifying for the Finals. Estimating a total of 8,000 ModelOff participants this year (probably there’s more), there would be a full 1,600 who are ranked at least as good as me, and 800 who are definitely better. My goal was just to test myself (and get some free test materials for my Finance classes, mum’s the word).

Second, something was being cooked up by the Exam Team. Why are they extending the exam time by 15 minutes? Why is the Theory portion only 5 questions long, instead of the usual 20? Not that I wasn’t grateful for the break, but this had to mean that the cases were going to be harder than usual.

Third, the case titles seem to be more informative than usual:

  • “More Money Please” could only mean some debt modeling was in the cards. To prepare, I brushed up the theory on bond amortization, and the Excel functions to do so.
  • “All About Performance” implied that there was going to be some kind of standard that will be compared to some kind of actual performance. There wasn’t much to do to prepare for it, since I was already familiar with the VLOOKUP function, and its reverse equivalent, INDEX+MATCH.
  • “The Price is Right” brought the game show to mind. I expected this to be the 2015 equivalent of last year’s “Snakes and Ladders”, except that we were going to be modeling spins on a wheel. I expected the type of questions to focus on each player’s betting patterns, and probably simulate their chances of winning using Data Tables (my lack of programming knowledge is perhaps my biggest weakness in this type of contest).
  • So, how did I find Round 2 2015? These thoughts are written down immediately after the exam. As such, I can only speculate about how I did, but here goes:

    • Section 1 wasn’t as difficult for me as Round 1 was. The topics tested were vanilla accounting and finance (annuity due vs ordinary annuity and payback period!), and I think I’d get at least 4 out of 5.
    • Section 2 was just as I expected – somewhat-precise debt modeling. However, there was a rub. The first question outright disagreed with my model. Usually, I could answer the first few questions in ModelOff cases. This time, no matter how I tried it, I couldn’t even get the first question – I had nice round numbers while all the choices didn’t. After a fruitless 5 minutes trying to figure it out, I gave up – the first question was a preliminary input – if I can’t get it right, there was no point in continuing because everything else would be wrong. For the second time in two weeks, I found myself guessing on six multiple choice answers. I know my probabilities as well as the next guy; with random guessing, the probability of getting at least 1 question right was 52% [=1-BINOM.DIST(1,10,1/6,TRUE)], but drops to 22% for 2 questions and 7% for 3 questions. I don’t expect more than 2 points for this section, honestly!
    • Section 3 was again as expected, and I actually found it to be quite a lot of fun. I think the tricky part here is to associate the deduction for normal calls with the appropriate band, but an approximate VLOOKUP does the job easily. The main problem I encountered here was the NPV portion. No matter how hard I tried, I couldn’t get close to the choices. Now that I have time to think, I’m sure I erred when I compounded the interest on a quarterly basis (=1.025^1) instead of following instructions and compounding the interest on a fourth-year basis (=1.1^0.25). Assuming I have no other mistakes, I should get at least 7 out of 10 here.
    • Section 4 was completely different from what I expected. This was another financial forecast, with a fair bit of optimization thrown in. From the get-go, I made a huge mistake of not reading all three pages, and instead soldiering on with the first two pages. Needless to say, this made the job of optimizing quite difficult, because I didn’t realize until after the exam that we were supposed to zero-out the short-term debt together with the cash balance. I got the first few questions, but had to guess on the remaining ones that had optimization. My estimated score here should be around 35% to 50%.

    Overall, I found ModelOff 2015 Round 2 quite challenging. Despite the extra 15 minutes, I could not answer anything in Section 2 with absolute confidence (and I still can’t). I’m sure the top modelers did much better, and I congratulate you as you all model off in London next month. Happy Halloween, fellow Excel masochists, and I’ll be back for more punishment next year!

    Reflections on My 2015 ModelOff “Season” (Pt 1)

    by Stevenson Yu

    Find Stevenson on LinkedIn:

    Stevenson YuLast year, after I did fairly poorly in Round 2 of what I considered were subjects I should be good at (Finance and Accounting), I resolved to practice hard for ModelOff 2015. Alas, it turned out to be yet another New Year’s resolution, pretty much in the vein of resolving to go to the gym and losing weight!

    Commitments and the hustle of life pretty means that practice is out of the question. Not that I was completely out of practice. Part of the job is to create fiendishly challenging exams for my Management Science and Finance graduate students, and you bet I got some mileage out of some past ModelOff questions. Heh heh. Please don’t tell my students I adapted your questions.

    Was this year’s ModelOff harder than last year? I’m not so sure about that. Round 1 was undoubtedly hard for me, to the point where I thought I wouldn’t have any chance of qualifying for Round 2. From the get-go, the multiple choice questions were harder than I remembered.

    However, I found “Bread and Butter” uncharacteristically easy. It helped tremendously that I had recently discussed pro-forma basic financial statement modeling (thank you, Prof. Benninga, God rest your soul). In any case, this certainly was a bread-and-butter test of financial forecasting ability!

    When I finished the first 2 parts within the recommended 60 minutes, I was quite pleased. For the first time ever, I was on time! That happiness completely vanished about 5 minutes into the second case, “Tax”. While I had expected something on deferred taxes and read up on the accounting treatment after receiving the case study information pack a few days before the competition, I wasn’t prepared for the main challenge of the case.

    The modeler had to use MIN and MAX functions to return the appropriate number of days within the applicable period to compute for tax. Looking back, the rest of the model is easy once you get past this hump; unfortunately for me, I blanked out after VLOOKUP didn’t quite work, and skipped the case for the fourth one.

    When I had read “Options to Call”, I was expecting the use of derivatives, and prepared beforehand my templates on the Black-Scholes Option Pricing model, as well as my spreadsheets on binary option valuation. Alas, it was a clever play on words, and was actually a comparison of various mobile phone contracts. After a few wasted minutes lollygagging at how to model the unlimited text messages and data, I settled on artificially putting in a ludicrously high number to serve as the allocation. When I re-attempted the problem after time expired, it was far easier to levy a charge of $0 instead.

    By the time I muddled through Case 3, there were only 10 minutes left. This was not enough time to model much of anything in Case 2, and so I simply guessed on all of the questions, and finished the exam without submitting any spreadsheet for “Tax”.

    In my estimation, that was by far my worst showing in ModelOff Round 1 – I thought I might have gotten maybe 60% of Part 1, and maybe 80% of Part 2. Guessing randomly with 6 multiple choice options is a losing proposition, so I guessed I got 0% of Part 3. I wasn’t sure how well I did on Part 4, but thought maybe half of the answers were correct.

    While I have always joked about getting my butt kicked in Excel by ModelOff, it is still a big psychological hit to do poorly on an exam, especially when I read that there was this chap who got a cool 100% perfect score (I for one welcome my new Excel overlord, Mr. Alvin Woon). So, imagine my delight when I was told that not only had I qualified for Round 2, I had gotten eight points from wildly guessing – and apparently this was better than majority of the contestants!

    Stay tuned for Stevenson’s reflections on Round 2

    A ModelOff Finalist’s Round 2 Wrap

    Tales of Finalist John Lim

    Round 2 is finished and apparently I’m in the Finals again (I’m going to book my flights before the organisers realised I just guessed “c” to everything!).

    Seriously though, it was another set of challenging but well-structured questions, and as I’ve been saying to my team, both the questions from last year and this year are great for aspiring modellers who are looking to enter the industry or further hone their skills.

    How I approached it?
    It was a bit tough in Australia because of it being 11pm when I started.

    Doubly so because my wife just gave birth 48 hours before! So it involved leaving the hospital at 10pm to go home, setting up at home on my ultra-wide screen watercooled dual GPU set up (neither of which actually helped – I needed a watercooled dual brain!), and drinking lots of coffee. Actually the most difficult part happened after the round was over – trying to get back into the hospital at 2am which had been locked down for the night … and the midwife asking me about what business I had trying to access the back entrance!

    At least I had the place at home to myself – one of the KPMG team who was also participating in Round 2 was disturbed by Halloween trick-or-treaters right in the middle of the round!

    The questions?
    The MCQ was a bit random but at ten minutes long, didn’t take up much time. Kudos to anyone who can quickly figure out the XXth Saturday in the year 19ZZ.

    The second question was around building a refinancing debt facility on top of a small existing model for a business – I lost time because I didn’t check the file properly and I started building the model from scratch, before I realised we just needed to modify the existing one that was sitting in a different tab! (Hint: read all the info provided carefully).

    I ran out of time but the key thing for those doing the question was the last parts of the question around “debt sculpting” (as the industry calls the optimisation of debt principal payments in models) – it can be quickly solved algebraically, via reworking the formulas that the question itself provided.

    The third question was another “ModelOff” special – a business case model in its essence, around analysing the financial performance of an emergency ambulance response service, given various pieces of information around the performance of the service. The main thing was around setting out the modelling well, so that the key assumptions could be easily tweaked – which it had to be in later parts of the question.

    The final question, for those who didn’t recognise it, is a typical P3 / PPP (public private partnership) style of model, where the private sector bids the revenues that the government is required to pay to them over the life of a contract to provide operating and lifecycle (aka capex) services for an infrastructure asset. Being an unusual question, the key was to read it really carefully.

    The last part of this question had me stumped (and from what I hear, most of the finalists too) – it required solving for two variables simultaneously so goal seek (which solves for one variable only) doesn’t work. Apparently the solver feature could be used?

    Humm I wonder how London is at this time of the year?

    Congratulations to John and his wife on the birth of their newborn. The ModelOff Team wishes you all the best at this joyous time. Well done John on making it to a second ModelOff World Finals. To learn more about John, visit his Finalist page.

    © Copyright 2017 - Vumero Institute