Financial Modelling is a process that recreates a hypothetical forecasted scenario of a company’s future operations and financials. Financial modelling uses past data and creates a summary of the company’s likely projected expenses and earnings that can guide investment decisions. Modelling is most used in corporate finance and includes using various models such as the three-statement model, merger model, DCF, Leveraged Buyout Model (LBO), comparable company analysis and the Sum of the parts model.

TABLE OF CONTENTS:

What is Financial Modelling and forecasting?

Financial modelling is the process of using a company’s past performance to model out a hypothetical future. This involves creating a projected income statement, balance sheet, and cash flow statement based on certain assumptions. It requires a basic understanding of finance, accounting, and business metrics.

Creating a financial model can help “predict” a company’s future financial and operational performance, as well as its working capital needs. This information can be useful to investors when determining the value of a company based on its current situation.

In this blog, I will attempt to explain financial modelling in simple terms. Although it is important for private investors to understand, it is important to exercise caution. Complex financial modelling is typically reserved for corporate finance and analyst roles and can be overwhelming for individual investors. As well as not necessary.

I deal in facts, not forecasting the future. That’s crystal ball stuff. That doesn’t work.

Peter Lynch

As Stoic Investors, we must be able to see value beyond just numbers. While analysts may obsess over the details and become fanatical about the models, we need to be able to take a step back and see the bigger picture.

Overly complex modelling can create unnecessary steps in the investment process and complicate the valuation of a business.

Always ensure you don’t get caught up in this unless you love detailed analytical work. Keep in mind that you SEE the trees from the forest. Whilst it can be helpful and a good skill to have, it is not necessary to successful investing.

Personally, I prefer to use simpler valuation models that do not require extensive spreadsheet work and line-by-line analysis. However, this does not mean that the valuation process is easy.

Why is financial modelling important to stock valuation?

Financial modelling is a tool that can help you understand the value of a business. By considering various events that might affect a business, we can see how the value of that business changes under different scenarios.

This information can guide investment decisions. By analysing a company’s operations and determining how each input contributes to its output, we can estimate the intrinsic value of the business and compare it to other companies to understand the competitive differences.

A good financial model will be flexible, easy to understand each of the inputs and provide critical insights into the financial health and potential of the business.

The purpose of forecasting and modelling a stock is to anticipate how certain events might affect a company’s stock in the future. By considering a company’s future and modelling the expected cash flows, financing needs, and opportunities, we can determine whether it is a viable investment opportunity.

What are the benefits of financial modelling?

There are some benefits of modelling out the future potential of an investment. Each of these is designed to give the investor the ability to make more informed investment decisions.

Improved Decision Making

Financial models can help investors to make better decisions that are unemotional. Models are based on analysing data and the historical performance of a business. This process forces the investor to think about a company’s strategy, look at the fundamentals and make decisions based on analysis work and not emotions.

Develops Knowledge of the Business

To model accurately you need to have in-depth knowledge of the business. Understanding the business model, how the company makes money, its working capital requirements, the margins and metrics, and industry standards are all beneficial to improving decision-making. You are investing in a business and not a piece of paper and this part of the modelling reinforces this philosophy.

Valuation backed with Research.

Financial Modelling can help to estimate the future cash flows of a business that can guide other valuation techniques such as the Discounted Cash Flow model. A simple DCF model takes the TTM Trailing Twelve Months Free Cash Flow and uses various inputs such as discount rates and growth rates to determine an Intrinsic Value of present value. A detailed financial model however can provide a clearer picture of evolving free cash flow to use as the basis for other models.

Risk Management.

Forecasting can help to manage risk. If you are valuing or modelling a business with debt, shrinking market share, declining margins, or capital-intensive industries you can model various scenarios. By running through scenarios, you can look at cash or financing demands of business that may impact valuation.

Create various scenarios of the future.

By building a complex model we can quickly calculate certain outputs by playing with the numbers we have built in. By being able to go back to the model and make room for error or increase the potential we can efficiently update our valuation models.

Working Capital Needs

This is one of the key areas of the financial model. Not just to model out sales and earnings. We want to understand the changes in working capital and how efficient a business is. This will impact free cash flow and the efficiency metrics of a business. So, we can determine the impact of capital needs based on strategies, or changing business fundamentals.

What to be mindful of when forecasting?

The work we do before modelling is crucial for building an effective valuation model for a business. It is important to identify the key business metrics that drive the company before starting to model the financial statements. This requires a second-order level of thinking and cannot be achieved by simply adding a growth percentage to the financials.

Before starting the model ask these simple questions to help prepare:

How does this business make money, including all segments?

Do we understand the business model well enough to model it?

Do I understand Customer growth, average sales, and the growth breakdown?

What does the business sell and how much does it sell?

Then the last question is, how to model this?

Once we have identified the key revenue drivers, we can reverse engineer the revenue to drive all other inputs such as the balance sheet and cash flow statement.

This is known as a P x Q Model (Price x Quantity). We can discover the metrics used in the financial statements and use them as a guide to forecast. By looking at what has been sold and how the company brings in revenue, we can build a detailed model without relying solely on the financials.

What happens in the top line flows through to the bottom line. A small change in the sales projection can have a huge variance in earnings and then free cash flow.

The last but most important thing to be mindful of when modelling is “Trash in-Trash out.” Whatever you put into the model is what you can expect out of it. If you get certain line items wrong, don’t link the spreadsheets up correctly or run the right formulas you will value a business based on inaccurate numbers.

An example of P & Q modelling prior to valuation.

To further understand how the revenue build up modelling works let’s run through a simple example (I can not express simple enough). Let’s start with a P x Q analysis to help with the revenue build-up technique. P = Price per Product/Service/Widget and Q = Quantity Sold (Customers).

If we start with a known number and then build out the Price which includes increases and then the Quantity which includes volume increases, we can get an idea of revenue.

	Year 1	Year 2	Year 3	Year 4
P (Price)	$5.00	$5.15	$5.30	$5.46
Increases %		3%	3%	3%
Q (Quantity)	100	105	110.25	115.76
Volume %		5%	5%	5%
Revenue (PxQ)	$500.00	$540.75	$584.32	$632.04
CF Margin %	10%	10%	10%	10%
Cash Flow	$50	$54	$58.43	$63.20

In this example, we can see how to build up the revenue model once we have understood the metrics of the business. One shortcut to using this model is to spend a lot of time understanding the price and quantity in depth. Once you have developed a strong foundation of revenue, we can either work backwards by removing the cost of goods sold, S&M, G&A, and R&D and then work our way down.

Alternatively, if we know that the company has consistent Operating Cash Flow (Cash flow margin) to revenue of say 10% based on past data, we can apply this to the revenue model to get our forecasted cash flow. If the company remains consistent in operating efficiency, our Free cash flow can be determined by the revenue build-up then making adjustments for capital expenditures.

The stages of financial modelling and valuation.

To create a detailed financial model, there are several stages that must be followed. Each of the statements needs to be worked on by following a process. By using the information we obtain from our Revenue Build-up model, we can then work our way down and incorporate what we know from the financials.

It is important to keep in mind that the model’s accuracy is highly dependent on the inputs and assumptions used.

The objective of building a financial model is to determine the business’s Intrinsic Value or Net Present Value (NPV), identify key risks and opportunities, and assess profitability, metrics, and returns of different scenarios. The aim is to forecast various line items such as revenue, sales, profit, cost of goods sold, and changes to working capital with the ultimate goal of determining Free Cash Flow.

Before starting the modelling process, we must select the right modelling technique.

Certain business models are easier to model with a degree of accuracy compared to others. Industries with consistent earnings, simple business segments, and understandable products/services are much easier to create hypothetical scenarios for.

However, modelling can become too complex when it is based on companies that have inconsistent inputs or are incredibly unpredictable. This can be due to a range of factors. In such cases, the model is hard to project a business that is not consistent, and trying to model this is akin to leaning on patterns and guessing what pattern comes next.

The modelled outcome is going to look something like this.

Here are the steps to building a model.

Company and Industry Groundwork.

Understanding the business model as we have discussed is key to being able to model the assumptions. We can do this by also analysing the industry, understanding industry trends, growth rates and how other companies within the industry report earnings and conduct business. It is crucial to understand what goes into the business being able to produce and deliver its products and services. By understanding the industry as well as the company we can ensure we are modelling realistic projections.

Rebuild the Income Statement.

Once the P and Q model is done and you have built the work above the model you can begin to fill in the income statement. This will include looking at gross profit, understanding the cost of goods sold, and operating expenses to then be able to determine net income (EBITDA). It is important to model the metrics in a percentage below each line item. I believe this helps to also look at it from a percentage perspective and not solely financial numbers.

Develop the Balance Sheet.

The balance sheet is important because this will look at the changes in debt, cash, accounts receivables, payable, and inventory changes and help us to determine other return metrics. This will then drive the working capital changes in the cash flow statement. The balance sheet will change depending on the business model you are analysing. Capital-intensive companies with asset dependency or inventory will look very different to capital-light companies that don’t depend on assets to create revenue, such as software.

Build the Cash Flow Statement.

The final step is to build the cash flow statement from the two statements above it. Each input will affect the final output. The cash flow statement is important to determining the free cash flow the business can spit out. This is the key line that will be used for our other valuation models.

A separate Model for Working Capital.

I use a separate schedule for the working capital calculations that guide the changes in working capital forecasting. This schedule includes trade receivables, other receivables, inventory, other assets, deferred tax assets, trade and other payables, income tax and any provisions. This can help me to understand the changes as well as the percentage of working capital to revenue.

Use the Inputs in the selected Valuation Model.

After determining the forecasted free cash flow, we can use it as a basis for our valuation work. If we are using a discounted cash flow model, we use the output from the forecasted modelling work to drive the valuation process. Instead of taking the trailing twelve months (TTM) of FCF and adding a growth number like 3%, we use the forecasted FCF from a detailed modelled-out spreadsheet that incorporates each of the statements. This approach can provide a more detailed cash flow projection as we have taken the time to understand each item in-depth and how it changes over time.

Sensitivity Analysis using different scenarios.

The sensitivity analysis is a process that runs various scenarios to the model to see how it impacts the final bottom line and in theory the valuation. I typically run 3 sensitivity analysis models to the free cash flow. I usually only use sensitivity analysis on free cash flow and the inputs that change are the inputs within the DCF model such as the discount rate, the FCF growth rate and the terminal growth rate. On occasion, I may run sensitivity on the 3 statements to determine how a company produces different results with changing fundamentals. However, the sensitivity of the FCF is sufficient if the modelling is done well.

What are the drawbacks of financial modelling and forecasting?

Investors should keep in mind that financial modelling is just one tool among many to use, and it’s not necessary to use it to value a business. Although it’s important to consider the numbers and how they can impact valuation, modelling is highly dependent on the inputs to get an accurate output.

Many investors run models and update top-line revenue growth without following through on the other line items. This can be problematic because all the line items change, and so do the metrics and ratios. If a company increases its revenue, it doesn’t necessarily mean that other line items remain fixed – working capital, receivables/payables, SG&A, R&D, and cost of goods sold need to be updated in line with sales.

It’s crucial to understand each input and how it changes in combination with everything else. For example, if a company’s operational leverage is improving, the entire efficiency and operations of the business will change, making it difficult to model without an in-depth understanding of how profit flows through to the bottom. Investors must spend time calculating and updating all the other line items, not just top-line growth. This is where the complexity comes in, and it’s important to be confident with your assumptions, especially if you’re using the model to make buy and sell decisions.

The model will always be an assumption on perceived value based on what you have interpreted from the statements, business model and how it should be constructed. I’ve never seen two models that had the same output.

So, be sure you understand the concept behind modelling with a degree of confidence before you practice it and then throw capital after the outputs you come up with.

In Summary…

Whilst this is not a complex guide or explanation of financial modelling it should give enough of an idea behind the concept. As I mentioned I don’t use complex modelling anymore. Initially, when I began, I thought I was ahead of the pack because I ran intricately designed models with all these clever formulas and amazing spreadsheets. It looked great and it was stimulating to build.

However, after years it provided very little overall returns (and chewed up a tonne of time), and that is the purpose of investing, to achieve our financial goals. I don’t want to divert attention away from the core theme of the website, logically investing. There are plenty of websites that are far better at explaining corporate finance and analysis work. If you take an interest in this skill, it is worth pursuing.

There is a big difference when you’re being paid to run these models for a living whether as an investment analysis, someone in corporate finance or for a fund. As a private investor with limited resources, time, is my greatest asset. I also believe there are other ways to determine value.

It is hard to develop an edge by conducting in-depth modelling work on companies that are already well known. There is probably already enough publicly available information to run a valuation on the business. Where modelling can come in handy is where there is a lack of research on a company, or you’re modelling a company that is rapidly evolving, perhaps coming out of negative cash flow and we want to determine how big this thing could get.

It all comes down to the investor. There is no right or wrong way. Try it out and if it helps you to make better decisions then stay with it.