Financial modelling and forecasting is a crucial life skill for anyone with ambitions to be an entrepreneur or who wants to be in management in business. The expertise in accounting and Excel that you will develop will lead you inevitably to understand where value comes from in a business and where it is destroyed. You will also be able to see what the potential consequences of your own or management’s decisions may be.

Being able to build financial models can feel a bit like a superpower, because whilst you can’t tell the future you can get a much stronger idea of what it is likely to look like, and assist with planning for that future.

In short, financial modelling is the basis of rational, risk-based planning. Building robust and flexible financial models is crucial for many finance roles and requires knowledge of accounting, finance, Excel modelling, and analysis skills.

 

What is a financial model?

A financial model is a mathematical representation of a company, project, or financial asset that projects expected future performance based on assumptions and scenarios. The model shows projected revenues, expenses, assets, liabilities, and cash flows. Models typically will use historical data as a basis for making assumptions about business drivers to forecast financial statements and Key performance Indicators. The outputs help us to value a business and evaluate options like investments, acquisitions, capital raising, and operational changes.

Financial models allow scenario analysis by changing inputs and assumptions to assess different conditions. Models help assess risks, optimize operations, support valuations, evaluate strategies, and aid decision making. Often the value of a model can be in helping us understand what will happen if the future is very different from the past: a war creating high inflation or a new technology changing the economics of a business.

Based on inputs to value drivers, such as revenue growth and expected profit margins, we can make a model of a company or a transaction and forecast possible future financial scenarios in all types of scenarios:

• We can model what a company is worth today
• Financial models can determine when a business will have burnt all their investors’ money
• We can model a potential transaction, such as a takeover of a company, and infer how much we can afford to pay
• We could model a physical investment in a plant and machinery for removing a bottleneck from a production process. This can tell us whether an investment is worth making or not.
• Modelling a windfarm project can tell us how much we need to charge for electricity in order to make a reasonable return in the most likely wind scenario. How does the result change when the actual offer from lenders is not what we expected?

 

Types of Financial Models

There are numerous types of financial models used for different purposes:

 

Discounted Cash Flow Models (DCF)

A discounted cash flow model is used to value assets and firms based on projections of future cash flows discounted to present value. DCF models are commonly used for both listed and private company valuations.

 

Mergers and Acquisitions (M&A) Models

M&A models evaluate the pro forma combined financials of two companies after the acquisition of one by the other. Using assumptions about expected synergies, a model will allow us to see the impact of financing in different ways, using different mixes of share and debt capital and the impact of the price paid on expected consolidated earnings.  This will help us structure a deal to meet the bidder’s capital structure targets – how leveraged they are prepared to be. It also allows us to assess affordability – how much can we afford to pay and achieve an acceptable accretion or if necessary, a temporary dilution of earnings.

 

Leveraged Buyout (LBO) Models

A Leverage Buyout model analyses the acquisition financing, debt repayment and returns for private equity investors acquiring a significant or potentially controlling stake in a business. An LBO model will be used by the P/E investors to decide on affordability and lenders will use an LBO model to assess the credit risk.

 

Initial Public Offering (IPO) Models

IPO Financial models are used to assess offering size and valuation for a private company going public in an IPO. These are relatively simple models for assessing relative value of companies based on trading valuation multiples and potential stock price. The sort of result we will get from this model is:

• If we are valued at a similar multiple to our peers on listing and raise the minimum amount required by free-float rules, how much cash will be raised?
• If x million dollars needs to be raised by the company through issuing of new shares to fund growth, how much cash will the existing shareholders receive from partially selling their existing shares alongside the new issue and how much will their ownership be diluted?

 

Competitor Analysis Models

Benchmarks a company’s financial performance, KPIs, ratios and growth against peers. Identifies areas of strength and weakness.

 

Forecasting Models

Projects future financial performance based on growth rates and trends for business planning and budgets.

 

Project Finance Models

Forecasts cash flows, shareholder returns, financial risk metrics and how they are impacted as we change the mix and terms of debts and equity assumed to fund the project. This kind of model will be the basis for all of the commercial contract negotiations for major projects in infrastructure, real estate development and oil & gas. In short, the model is a key tool to assess financial feasibility.

 

There are also financial models used for credit analysis, financial institution risk management, real options valuation, and more advanced models like Monte Carlo simulation. The type of model depends on the specific business need.

 

How to build a financial model

Building a good financial model requires following steps:

1. Define the purpose – Modelling starts by defining the key objective, metrics, decisions, and outputs needed. This guides model design and formulas.
2. Research assumptions – Making reasonable assumptions is crucial. Research historical data, management projections, industry trends and benchmarks to derive assumptions.
3. Identify inputs – Determine the critical inputs needed to calculate the desired outputs. Common inputs include revenues, fixed/variable costs, taxes, capex, and debts.
4. Build model structure – Build a framework with projected income statement, balance sheet and cash flow statement. Integrate statements to balance.
5. Input historical data – Enter at least 2-3 years of historical data for base projections.
6. Develop assumptions – Populate assumptions for revenue and cost growth rates, margins, capex, etc. Link assumptions to formulas.
7. Project future performance – Project revenues, expenses, assets/liabilities, and cash flows for the forecast period (e.g. 5 years).
8. Add supporting schedules – Supplementary schedules to derive granular projections. E.g. Revenue buildup, debt schedules, depreciation, etc.
9. Analyse sensitivities – Create scenario analysis by varying inputs to assess impact on outputs. Test worst/best cases.
10. Finalize formatting – Format for readability. Add summary dashboard, charts, notes, and descriptions.

 

Take our Best Practice Financial Modelling course to learn more.

 

Common Challenges for Financial Modelers

• Data Availability and Quality – Obtaining accurate, reliable, and comprehensive data can be a challenge, particularly for private companies or new market segments.
• Complexity vs. Usability – Striking a balance between building a model that captures all relevant factors without becoming overly complex and unwieldy is a constant struggle. Parsimony is perhaps the most under-rated quality in modellers: give me a modeller any day who wants to keep it simple. The 80/20 rule is often true in modelling. We get 80% of the value from (the first) 20% of the work.
• Ensuring Accuracy Quality control – Mitigating the risk of errors in the model, especially when working with large datasets and complex formulas, requires meticulous quality control. Build checks and balances all the time and have a trouble-shooting control panel as part of your model. Every car has a temperature gauge and oil warning light. Your model should too. Figuratively speaking.
• Model Obsolescence/irrelevance – Models used in transactions can become complex and difficult to use. Your client/boss keeps asking you to model different options and potential changes that are being negotiated in a deal. Once those aspects of negotiations are settled, the flexibility in the model is no longer needed, but the complexity is still there. As such, rebuilding models and initially prototyping are good disciplines. My experience as a modeller is that my “first-cut” model is a learning process. Once complete, I have a much better idea as to how I should have attacked the problem to begin with and have a much clearer picture of the important sensitivities that need to be modelled flexibly. The second model should take considerably less time and be much more user-friendly and robust for future use.
• Documentation – modellers often neglect this aspect, but it is essential to ensuring that the models underlying structural assumptions are documented along with the definitions of inputs and the methods for running scenarios. Documentation is essential for reliable use, even by people who didn’t build it.
• Assumption Validity – Ensuring that assumptions are realistic, unbiased, and based on solid foundations is always a challenge, given the uncertainty and variability in future projections.
• Regulatory Compliance – In certain sectors, ensuring that models comply with evolving regulatory and financial reporting requirements can pose a challenge.

 

Best Practices in Financial Modelling

Some best practices for developing robust, and error-free financial models include:

• Model structure should flow logically from inputs to intermediate calculations to outputs.
• Apply consistent formulas and formats across rows/columns for easier checking.
• Link cells rather than inputting numbers to allow for sensitivity analysis.
• Use colour coding, notes and labelling for clarity.
• Limit hard coding numbers to clearly defined input sheets only.
• Cross check totals against historical data for reasonableness.
• Test different scenarios and outline key assumptions.
• Ensure balance across the 3 financial statements
• Assiduously avoid circular references – in my long experience as a modeller the increased “accuracy” from circularity is rarely of any commercial significance – it doesn’t make a significant change to the numbers or their implication. Circularity does however make your model slow, potentially crash, and difficult to audit.
• Include summary dashboard, charts, descriptions, and notes.
• Review annually against actuals and update for latest business drivers.

 

What is required to be a financial modeller?

Essential skills for financial modelling include:

• Advanced Excel = Expert level skills and shortcuts, with an emphasis on logical and lookup and reference functions like VLOOKUP, HLOOKUP.
• Accounting knowledge – Understanding financial statements and principles.
• Finance fundamentals – Valuation principles, ratios, and growth metrics.
• Quantitative skills – Numeric aptitude for analysis.
• Critical thinking – Assess assumptions and outputs for viability.
• Attention to detail – Identify errors and check accuracies.
• Communication skills – Convey insights from models clearly.

Financial modelling requires combining domain expertise in finance and Excel technical abilities. Modelers often specialize by sector. Surprisingly Finance degrees and masters programmes do not necessarily give graduate a strong grounding in these areas. Capital City’s IBAC – Investment Banking Analysts Certification programme is designed to give exactly these skills.

 

Career Paths for Financial Modelling

Roles involving financial modelling span corporate finance, investment banking, private equity, equity research, project finance, management consulting and more.

Typical career paths include:

• Investment Banking Analyst – Build LBO, M&A, IPO and pitchbook models.
• Private Equity Associate – Create LBO models and portfolio company models.
• Financial Analyst – Prepare valuation models, forecasting models and peer benchmarking.
• Corporate Development – Develop M&A models, integration analysis and synergy quantification.
• Project Finance – Model infrastructure and project cash flows, liabilities and returns.
• Equity Research – Financial modelling of public companies to derive price targets and recommend actions.

 

Financial planning and analysis (FP&A) roles also involve budgeting and forecast models. Developing advanced modelling skills early allows progression along modelling career tracks. Experienced modelers are highly sought after for strategic finance roles across various industries.