Financial modeling (internationally financial modelling) has advanced tremendously over the last two decades, evolving into a true science. Nobel Prizes have been awarded on the back of financial modeling and research, including the 2013 Prize in Economics to Eugene F. Fama, Lars Peter Hansen, and Robert J. Shiller “for their empirical analysis of asset prices”. Multibillion-dollar hedge funds have been set up around specific financial models, such as David Li’s Gaussian Copula function or the notorious Black Scholes model, all of which spurred a wave of new investment vehicles looking to leverage the models’ determination of price movements in different asset classes.
Whilst these are clear examples of extremely advanced financial models, the overall point remains clear: financial modeling plays a fundamental role in modern day financial and business decisions.
Finding top financial modelers is no easy task. The ability to assess financial modeling skills comes with practice. This guide is aimed at helping you source such talent effectively by putting candidates through their diverse paces.
Organized modeler, organized model
The bread and butter of a good financial model is that it be well-structured, clean, and easy to follow. After all, these models are usually shown to or referenced by other members of an organization. If financial models are sprawling instead of streamlined, they will largely be useless. A CFO cannot be spending precious time to understand a document as opposed to focusing on its outputs and takeaways or how it relates to the business KPIs.
Reflecting this, good financial modelers will tend to be structured, organized people, who enjoy data analysis and making well-constructed and easy to grasp models. A love of minutiae, such as Excel formatting, is likely to reveal a top-notch financial modeler, who takes pride in what they craft.
A good first step, therefore, is to use a simple modeling exercise. This is not aimed at determining their financial skills or their intrinsic Excel knowledge, but mainly their propensity to create clean-looking models that are orderly and coherent.
An appropriate simple modeling exercise could be the following:
Q: Company X has been approached by a local boutique investment bank with an opportunity to purchase a smaller competitor in an adjacent market. The CFO has asked you to create a financial model with an upside case, base case, and downside scenario to assess the growth prospects and risks of the company.
The following guidelines on a financial model’s structure will rapidly identify good financial modelers, no matter the financials:
- Inputs: A model must clearly show where the inputs are. This is particularly important for questions of portability. As the model is handed over to colleagues, they must be able to determine where the inputs are, in case they need to perform a sensitivity analysis on the key inputs, for example.
- Number of tabs: Financial models require interaction between many different sections (P&L, Balance Sheet, etc.). The right build will be structured so as to lead users naturally between various financial statements, sheets containing operating models, sheets that model returns, sheets that model particular functions such as capital expenditure, and more.
- Output tabs: Financial modeling almost always involves reaching an output or conclusion. In the scenario depicted above, the CFO is looking to understand what the prospects of the company are, meaning the outcome would concern growth and profitability projections Whatever the purpose of the model, good modelers will make sure that the outputs or the conclusions are clearly laid out.
- Key to financial terms used: It is unwise to assume every person looking at a model is fully financially literate. Capex and ROIC are common enough abbreviations, but if a model mentions NCAVPS, a key explaining that this means Net Current Asset Value Per Share can be a blessing.
Excel still rules the roost
While Microsoft Excel spreadsheets may have a reputation as the aging veteran of financial modeling tools, there is a good reason for its staying power. The breadth of options it provides allows a financial modeler to dominate any inputs needed. There are hundreds of relevant Excel formulas, with a good financial modeler needing to be familiar with a multitude of these to achieve any particular result needed.
The best formulas have two advantages:
- They achieve a particular result in a lightweight way, without encumbering the model too much so that the file remains fairly light and easy to load.
- They can be understood and modified by others and the formula must continue to work when other parts of the model are altered. This is particularly relevant when formulas are combined to achieve a particular result.
Q: Company Y is updating its business model ahead of a Series A round of fundraising. However, its first attempt at financial modeling in Excel was not done proficiently, resulting in a very heavy model, which is unclear to use and takes a long time to recalculate. Please update, improve and simplify it.
Examples of how a modeler should be making models more lightweight are as follows:
- Non-volatile formulas. Certain types of formulas in Excel are considered “volatile” as they get recalculated every time a change is made. This makes an Excel sheet much slower. A good modeler will identify these and change them to non-volatile types. A specific example would be changing OFFSET formulas to INDEX formulas.
- Pivot tables: A common reason for slow Excel sheets is large amounts of data. If summary tables or output tabs are made using formulas referencing large amounts of data, the model will slow down significantly. A good way to avoid this is through the use of pivot tables.
- Tables can hold data: Tables are a good way to store data due to their more dynamic nature. If data is added or removed, the table still works, which allows for easier referencing in formulas (for instance, by referencing a table column as opposed to the exact array of cells).
- Macros: Macros allow for a host of added functionality. Whilst many models will not need a macro, a candidate who understands how to create and use focused macros can clearly provide more advanced Excel functionality when the time comes.
A real data sleuth
“An unsophisticated forecaster uses statistics as a drunken man uses lamp-posts - for support rather than illumination.”
—Andrew Lang
Financial modelers will often be tasked with producing models in the absence of much guidance or data. When faced with this situation, experienced modelers will know which type of information they need to source, where to find it, and most importantly, how to filter out data which is irrelevant to their exercise. In other words, strong modelers should be able to construct their own scenarios and guidance to produce the results they have been asked to produce.
A good way to test a candidate’s ability in this regard could be as follows:
Q: The CEO of a Fortune 500 company is considering a takeover bid for a major competitor. He has asked you to perform a valuation analysis of the takeover candidate, through a financial model, while applying the right methodology.
A financial modeling expert must be familiar with the main methods of company valuation. But more importantly, for this exercise, they should be familiar with the sources of data one would use in order to perform a company valuation. The candidate would first need to model some estimates of the company’s performance going forward.
Forward financials can be estimated by analyzing historical figures and predicting future performance, learning about overall market dynamics and using the forward estimates of comparable companies.
A good financial modeler would be expected to investigate in the following way:
- Request specific historical company information.
- Analyze historical performance and ask questions to understand numbers driving factors behind the company’s performance.
- Become familiar with market the company operates in: competitors, products, market share, etc.
- Seek information on comparable companies, such as private industry reports (Pitchbook, Thomson One, Capital IQ, Bloomberg). For American companies, the SEC provides original filings of the companies’ financials, while the likes of Google Finance and Yahoo Finance can be used to look into publicly listed companies.
- Look for external analysis (analyst research reports, media articles), so as to build an understanding of company’s reputation and overall industry drivers.
The above exercise is not to have the candidate perform a valuation analysis but to hear from how they would go about sourcing the information needed to build their model and provide the analysis.
Step back and see the bigger picture
Despite the above, an extremely common pitfall in financial modeling and business modeling is the inability to take a step back and see the bigger picture. It is all too easy to get bogged down in the details of a model and forget that a model is supposed to simulate a real world situation. seeing the bigger picture. Top financial modelers need to constantly be asking themselves the following questions:
- Do the numbers I am showing make sense?
- Is the outcome of my model feasible?
- How do my numbers compare to real-life comparables?
If financial models are meant to guide senior executives in their decision-making, the modelers must anticipate what the executives need and the questions they expect the model to answer. If done well, this answers the need for structure, no superfluous data being included, and avoids time loss right due to unreasonable outcomes in the financial model.
It is worth testing a candidate’s thought process at this stage.
Q: A CFO is considering investing in a new production facility, and has been soliciting bank proposals for financing. His bankers have sent him a model of what sort of financing structure they could provide as well as their forecasts on the investment’s performance. The CFO has asked you to analyze the numbers and draw some conclusions and recommendations.
In performing the analysis, a top financial modeler would be expected to do the following in order to immediately understand the structure of the model:
- Understand where the key inputs are (pertaining to operational projections as well as to the financing structure).
- Define key outputs/metrics CFO is looking for to make a decision. Here, the most appropriate metric is likely to be an analysis of the Net Present Value (NPV) of the projected investment. Strong modelers would be familiar with the concept behind NPV but should also ask what key metrics the decision will be based on to include them prominently in the model.
- How the model ties everything together. Once the key inputs and outputs have been determined, the modeler should figure out how the model uses the inputs to create the desired outputs. In this case, the two key parts of the model will be the operational projections of the investment and the financing structure being proposed by the bank.
Once this is established, a stress testing process should be carried out to put the model through its paces.
- Providing for variables - Once the model structure is defined, the modeler should see how key outputs change with a given change in the inputs. This also allows for forecasts to cover more extreme scenarios.
- Trying different financing structures - In this example, the modeler can see how different financing structures might influence the outcome. For instance, a comparison of how debt and equity instruments affect the desired outputs provides the CFO with some good perspective.
- Test operational assumptions - The model can only work if the assumptions within it make sense. Wildly optimistic assumptions on the cash flows of the investment would produce skewed results and help no one. A good modeler is likely to question these assumptions until they are satisfied.
To summarize, a good modeler cannot focus only on the model and its complexities. Putting themselves in the shoes of the decision-maker allows them to understand what drives these decisions and make the model reflect that.
Pride and joy in their work
As a final note, it has become very clear that the best modelers tend to really enjoy modeling. Like an architect approaching the completion of a building, they will tend to see it as an art. They will enjoy discussing modeling tips and tricks with their coworkers. And they will spend time on their own learning about new skills.
When assessing a freelancer, they should be allowed to wax lyrical, to share their favorite model or speak of their proudest creation, revealing from where their pride comes from or how its complex parts were put together. An interviewer should leave this process to flow organically, allowing themselves to be given the grand tour of a financial modeler’s portfolio. The pride and fulfillment they derive from their work is a clear benchmark of how they will approach future challenges set before them.
Financial modeling is a mission-critical task for many companies. Having strong financial modelers on the financial consultant team can make a CFO’s life far easier, streamline decision-making and often help gain an edge over one’s competitors. As such, hiring expertise talent in this area is fundamental. Nevertheless, doing your due diligence and judging a candidate’s ability to model is not easy. Much like a model itself, the interview process cannot be static but ebb and flow to test out the various pointers laid out in this guide. Dive in.
