Managing a financial modeler? Here is some advice

You’ve just become a decent financial modeler, and then it’s over! You become responsible for other modelers, perhaps even needing to hire a team.

Whether you have been a financial modeler or not, managing technical professionals can be challenging. And a lot weighs on the financial model being correct, perhaps billions of dollars. How much of the responsibility for a correct model can you take on as a manager? What can you and can’t you delegate?

Whilst there isn’t one answer to these questions, I have been responsible for both teams developing financial models, and for teams completing ‘model audits’, or model due diligence (checking financial models). I am by no means perfect (just ask the analysts who worked for me) and I have made many mistakes, but I do have some guidelines to follow, even if I didn’t manage to always follow them.

These guidelines focus on the ‘hard skills’, rather than the type of person best suited to a role, or the types of communication styles or inquisitive nature required (which is equally important). Soft skills recommended and guidelines for recruiting will be covered in an upcoming blog.

Guiding principles for any manager:

1. Trust, but verify
   1. Always have the model reviewed by someone else, for every change in the model
2. Have a defined set of questions you ALWAYS ask, for every model update
   1. Give structure to your team, so they know what to look out for
   2. You may have noticed that multiple doctors will ask you the exact same questions of you upon arriving in hospital. They can’t afford to miss something critical, and although it is somewhat unfair to compare financial modeling to the importance of the role of a doctor, we can learn from their approach
   3. And trust me, it’s when you don’t ask the questions that things go wrong
   4. Define the exact scenarios that you will run to review the model and transaction, before modeling even starts
3. Teach financial modeling analysts to solve problems
   1. Don’t give analysts the answers, and definitely DON’T fix their financial models for them. We learn from experience.
   2. Constantly provide learning sessions, and openly share errors from different models, so we can all learn from it
4. Have a sign-off process
   1. Have a simple checklist to complete before the financial model is sent to another party or used to make a decision
   2. Standardize the outputs for decision making (i.e. graphs required, etc) and the language you use. For example, if talking about the ‘return’ or ‘IRR’, be precise – is this the ‘nominal post-tax levered IRR’?

With these principles in mind, what are the processes required to ensure a correct model? Managers of financial modelers need to own this – it is your responsibility too.

Recommended process

It goes without saying that before even looking at the model you should ask your analyst about the deal. Who are the investors, what are their assumed hurdle rates, what is the corporate structure, etc. Assuming this has been done, the process I have adopted is:

Ensure the inputs are the correct ones, with proof

Ensure that all the inputs are from the right location, file name, and document version. The common approach is to create a ‘data book’ linking every input to its source and ‘sign them off’. I will provide an example in an upcoming blog

Explain the critical outputs for defined scenarios

Review the defined scenarios that the analyst must run. Have the analyst explain to you exactly what changed in each scenario on every critical output line and why (i.e. revenue, returns, etc).

Run comparisons between model versions

Run a comparison between different versions of the model, showing how many formulas were changed, and explaining exactly what key outputs changed and why

• Comparisons can be run in Excel showing the number of formula changed and which ones changed
• See the example below of reviewing differences in core outputs for a renewable energy project finance model

Stress test

Run boom-bust scenarios (i.e. set debt to 0, grow capex exponentially, make operating cash flow 0 in a period). Does your model still work when stress-tested?

Include the checklist

Make sure that you complete any checklist you have prepared

My pet hate – DO NOT send your boss an email with “attached is the new / updated model”

The model exists to make a decision, or review decisions. Decision makers need to know the impact of changes in the model. Spend the time to prepare an email outlining the broad changes in the model (i.e. logic, inputs etc) and, more importantly, what changed to the core outputs (i.e. from an investment or decision making standpoint).

Example of variation analysis between models or different scenarios

Extracted below are some outputs from a renewable energy project finance model, including the cash flow waterfall and several other important outputs shown below the waterfall. These are the live outputs of the model, based on the scenario which is currently active

Every model needs some sort of comparison between scenarios or model versions. One way of doing this is with a data table or with a layout as below.

The comparison below gives the option to compare a ‘base’ version and a ‘changed’ version of the model (in cells C3 and C4).

The outputs are shown for each version beneath in F7:H21, with the $M (or absolute %) delta change between the selected versions in C7:C21 and the % delta change in D7:D16.

The task for the analyst to explain (and for the manager to understand) is why (for example) revenue decreased by 17.65% between version 1 and 2, but costs only decreased by 15.53%. The reason here is not important, but for your project it is. You and the analyst need to be clear on this for every major output line.

Hire the right people

It’s ok if you (as a manager) don’t know how to sanity check the results of something like the table above…. as long as you hire the right person or people to do so. In truth, it’s not that complicated to sanity check a model, and can be learned relatively quickly. But don’t run the risk of major model errors. Trust but verify.

What scenarios should the analyst run?

It depends on the project type. In my blog next week, I will list many of the common scenarios that should to run for renewable energy and infrastructure project finance transactions.

If you would like to make sure you receive the next blog, please register for our mailing list here. If you would like to speak about best practice modeling in your organization, please contact me.

Happy modeling