View More Content

ModelOff round one recap

ModelOff round one recap

Round one has just finished. How did you go? Read the recap to discover how you went and where you can improve in time for round two.

ModelOff 2014 – reflecting on the end of Round 1

Well done to all the participants who took part in the first round of ModelOff 2014, and special congratulations to those who are through to the second round!  To help you measure up how you performed, whether you made it through or not, we have created sample answers to two of the ModelOff 2014 Round 1 questions – Precise Debt Modelling, and Dealing with Data.

Precise debt modelling

In this case study, you are asked to take two loans and calculate the relevant payment amounts, interest paid and loan balances for each, based on a set of payment rules. The payment rules reflect real life situations in adjusting for payments falling on weekends and public holidays.

The case study breaks down hints dealing with separate conditions, but with only 40 minutes allocated to it, it’s much faster to create a general solution that applies to both sets of questions.

The time allocated is just about enough to derive a solution using the WEEKDAY function, which is how the judges thought the problem should be solved.  However, if you have ever used the WORKDAY function, you will know that it compensates for both weekdays and lists of holidays, which is exactly what the question is looking to solve.  Clever use of WORKDAY allows for an elegant solution which cuts the time required in half.

Screenshot 1: Calculating the next payment date using the WORKDAY function

You can download the sample answer for the details, however in general, the logic involves:

  1. Calculating the next business day (excluding any holidays), including the target day if it happens to be a business day
  2. Calculating the prior business day, so that we can refer to it if necessary
  3. Determining if the next day falls into the following month (this is not allowed under the conditions specified), and if so, pick up the prior day

The rest of the case study asks for the use of the Goal Seek or Solver functionality to calculate a fixed monthly repayment, and subsequently determine the balance outstanding and the interest to be paid.

Screenshot 2: Setting up a goal seek macro to calculate your target payment

The allocated 40 minutes is quite challenging if you are working from first principles. You can save time by using the WORKDAY function.  If you haven’t used it before, download the sample answer and try it out.

Dealing with data

You are asked to work through a large set of data in this case study, going through the processes to clean and subsequently analyse the information provided.  This highlights a key issue that analysts face around the world – working with data and manipulating information we get from external systems so that they can be used effectively in Excel.

It would have been tempting to get carried away with finding, replacing and sorting, but it makes it quite difficult to track what processes you have followed.  Instead of find/replace, try the SUBSTITUTE function – this preserves the initial data and provides you the output as if you were to have replaced the results.  If you download the sample answer, we have set up each process as a calculation, so you can see how each step works in the process chain.

Screenshot 3: Converting dates from text to Excel-friendly values

The case study needs you to take a step by step approach to cleaning the data, and selectively dealing with different date formats in particular.  The most challenging element involves looking through several different date types, removing the unnecessary information, and converting it into an Excel-friendly date format that can be used to answer the questions.

Finally, instead of using the Sort functionality in Excel, you can download the sample answer to see how we have used a combination of the RANK, INDEX and MATCH functions to create a dynamic sorted list.  This one works because each data point is unique, but there are also ways to adjust the rankings to help identify and break ties in the data.

Screenshot 4: Using INDEX(MATCH) to sort data dynamically


Preparing for round two

Hopefully this recap has given you some food for thought on dealing with the ModelOff 2014 Round 1 questions, and has inspired you to further develop your skills, either by trying again next year, or by preparing for Round 2, kicking off this weekend!  Remember to check out our blog highlighting the nine essential skills to win the championship, and above all, good luck!

Ben Kwan
by Ben Kwan

Ben Kwan is an Associate Director in Corality Financial Group’s head office in Sydney. He has a strong financial modelling background that was gained in a transaction environment at Big 4 accounting firms both in Australia and the United Kingdom. Prior to joining Corality, Ben worked as a financial advisor on number of projects with the UK government.

Contact Ben Kwan

view all