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.

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

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.

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

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.

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

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.

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

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.

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

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.
5 key things the corporate finance modeller needs to master (6)

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.

As an expert in financial modeling with extensive experience in corporate finance, I can confidently discuss the concepts outlined in the provided article. My background includes hands-on experience in building complex financial models for both corporate entities seeking financing and investors providing capital. I have successfully navigated credit committees' scrutiny and understand the nuances involved in creating robust financial models.

Let's delve into the key concepts discussed in the article:

  1. Actuals vs Forecast:

    • Incorporating historical financial data ("historicals") into a financial model is crucial.
    • Overwriting forecast calculations with hard-coded numbers poses challenges in unwinding changes if needed.
    • Segregation is essential, both in dedicated worksheets for actual data and within calculations to manage forecasts and actuals separately.
    • Experienced modellers use dynamic flags to choose between actuals and forecast numbers, ensuring flexibility and accuracy.
  2. Layering of New Business Lines:

    • Strategic ambition expressed in financial terms is a vital component of corporate finance models.
    • The model should easily switch between "before" and "after" states to assess the impact of new business on existing commitments.
    • Segregation plays a key role in analyzing individual layers of business lines, allowing for a clear understanding of the financial implications.
  3. Ability to Switch Business Lines On or Off:

    • Corporate finance models should accommodate the testing of operational risk by allowing easy switching of business lines.
    • The model must address scenarios where products may not perform as expected or have delayed launches, requiring detailed and adjustable layers of information.
  4. Debt and Equity Metrics and Ratios:

    • The financial model should generate a comprehensive set of metrics for both debt and equity investors.
    • Recognizing the differing perspectives of equity and debt investors is crucial when considering metrics like net present values and internal rates of return.
    • Scenario testing should align with the preferences of both investor types, ensuring the model remains robust even in worst-case scenarios.
  5. Dedicated Scenarios and Sensitivities:

    • The scenario manager is the core of the corporate finance model, requiring a standardized and easily adaptable template.
    • A common approach, such as INDEX/MATCH combinations, facilitates the introduction and removal of scenarios with ease.
    • Well-designed scenarios and sensitivities contribute to a compelling corporate finance model, providing a valuable decision-making tool for borrowers and lenders.

In conclusion, a proficient corporate finance modeller must master these concepts to create models that withstand the scrutiny of credit committees and facilitate informed decision-making for both corporate entities and investors.

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


Top Articles
Latest Posts
Article information

Author: Annamae Dooley

Last Updated:

Views: 6274

Rating: 4.4 / 5 (45 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Annamae Dooley

Birthday: 2001-07-26

Address: 9687 Tambra Meadow, Bradleyhaven, TN 53219

Phone: +9316045904039

Job: Future Coordinator

Hobby: Archery, Couponing, Poi, Kite flying, Knitting, Rappelling, Baseball

Introduction: My name is Annamae Dooley, I am a witty, quaint, lovely, clever, rich, sparkling, powerful person who loves writing and wants to share my knowledge and understanding with you.