5 key things the corporate finance modeller needs to master (2024)

A basic mastery of financial modelling focuses on the presentation of three financial statements: Income Statement; Balance Sheet; and Cash Flow. A good model will be dynamic, capable of running sensitivities and introducing scenarios. Bump up the price and watch the cash flows rise.

Financial modellers in the corporate finance space tend to fall into one of two camps:

• Representing the corporate seeking the finance; and
• Representing the investor seeking to provide the finance

In both cases, the finance models they build needs to stand up to scrutiny from credit committees. Their requirements push beyond the three financial statements and a simple “does the balance sheet balance?” consideration.

Here are five key things the corporate finance modeller needs to master if they are to convince the credit committee to make the commitment to invest:

1. Actuals vs forecast, with forecast driven off actuals.
Accommodating actual financial data (also known as “historicals”) is not tricky to do – but nor is it obvious how to do it.

Overwriting forecast calculations with hard-coded numbers (on the basis that the calculations are no longer required) is fraught with problems. The main problem being unwinding the over-write if required. “Can you put the model back to where we started?” is not something you want to hear if you are managing actuals by over-writing forecast calculations.

Segregation is key: a separate worksheet for actual data with links through to calculation sheets. And there is segregation in the calculations as well: forecasts and actuals sit side-by-side with a mechanism that determines which is linked through to the financial statements.

Experienced modellers will deploy flags to manage the choice of whether to pick actuals or forecast numbers. The flag is itself dynamic: driven by a last actuals date (everything up to and including that date is based on actuals; anything after is a forecast).

Where things get even trickier is when forecast numbers are driven off historical performance – to show a persuasive story as the business moves through the present into the future.

2. The layering of new business lines on top of existing business lines.
A key component of good corporate finance modelsis a strategic ambition expressed in financial terms. That financial representation of top-level decision making could be as simple as:

• modelling the impact of new business on operating revenue, the cost base and the tax computation; and
• modelling the increase in the asset base required to achieve the additional margin. There is an impact on the tax computation of increasing the asset base too.

This is a second area where segregation is important. A good model will flip easily between a “before” and “after” state of affairs – and it will be straightforward to assess the impact of new business on existing commitments.

Likewise, there’s value in stripping out existing business and considering new business on its own: to look at financing plans in isolation, for example, or to consider delays in launch plans.

A well-designed, well-constructed model will layer new business lines on top of existing ones with care and with mechanisms in place to allow easy analysis of individual layers.

3. Ability to switch business lines on or off.
Think of corporate finance modelling bringing together three separate stories:

• a comprehensive financial history;
• a strategy for future growth; and
• funding to support that future growth

The strategy needs to be tested for operational risk. If the strategy relies on the launch of 10 new products, what if only 8 fly? In the financial model, it needs to be simple to switch off (for example) products 4, 6 and 7.

Or what if all products fly, but at far lower growth rates than originally forecast? What about a scenario where product 4 launches successfully but fails to grow, whereas products 6 and 7 are both delayed by a year?

Getting the level of detail right so it’s easy to switch business lines on and off requires careful thought. Too much detail and the model becomes unwieldy, overwhelming and difficult to see the wood for the trees; too little detail and the modeller will struggle to strip lines out easily.

4. A comprehensive set of debt and equity metrics and ratios.
Knowing the ratios that the credit committee will want to test, why they want to test them and where covenants are likely to be set is a key output to define upfront.

Calculation logic in the financial models should work towards producing a comprehensive set of metrics both for debt and equity investors. And a good model will recognise the link between output metrics and a model’s scenario manager. For example, equity investors tend to take a sunnier view of things than debt investors – when considering net present values or internal rates of return of equity cash flows, they will be happy to look at base case and more optimistic scenarios.

Debt investors – keen to put in place contractual obligations designed to give them an early seat at the table when things go wrong – will be focused on worst case scenarios. Credit committees will set covenants at a level where even in the worst case scenario the covenant is not triggered. That will have a knock-on effect when it comes to equity returns.

A good modeller will always check to see if scenarios / sensitivities are sensible (a balanced assessment of both likelihood to happen and impact if it does happen) and recognise that if a worst case scenario is too severe then the model will break: there will not be sufficient cash to service the debt, for example, let alone pay out to equity. Modellers should push back with the credit department where possible – a good example of the real life tug-of-war between front-line officers and the credit department.

5. Dedicated / detailed scenarios and sensitivities.
The scenario manager is the engine room of the corporate finance model.

It lends itself to a standard template design that may be repeated across hundreds of models without fundamental changes, so I’m often astonished at how many modellers design their own each time. There is – I think – a common agreement that an INDEX / MATCH combination is a sensible way to approach things, but I have seen plenty of other inventive solutions.

This is not an area where invention is necessarily a good thing. Far better is an easily-recognised approach which allows the modeller to introduce and remove scenarios with ease.

A compelling corporate finance model brings together several stories: the story of what’s happened in the past (“the actuals”); the story of what might happen in the future based on a strategy (“the forecast”); the story of how the past is connected to the future. Add to that a financing plan: what financial support the strategy needs to thrive and how that finance is to be serviced and redeemed. Add to that credit committee’s particular needs: a robust testing of what might happen should things not go to plan.

A well-designed, well-constructed corporate finance model telling convincing stories with numbers is a valuable decision making tool as borrower and lender find that sweet spot where their interests meet.

Our new Corporate Finance Modelling course is coming soon – click below if you would like to be notified when it’s available.