How do you build Financial Models in Excel?

How to Build a Financial Model

Financial models are essential tools for determining the worth of companies and businesses. They use financial models to assess risks, determine the feasibility of investments, and even run different scenarios to determine the profitability of a project. Having said that, this is how useful financial models are for entrepreneurs and executives.

While financial models are simple to access and use, the process of creating one requires more than an ounce of patience, effort, skills, time, and knowledge. The ability needed on how to build financial models in Excel has become a key skill, and you already know that learning financial modeling will increase your employability in finance or financially focused fields, as well as provide substantial know-how for when you decide to invest or start your own business.

In this article, we will go over the fundamentals of how you can build financial models from scratch. Despite the fact that building financial models in Excel requires a lot of know-how and skills, starting your journey now will help you eventually acquire the necessary skills. We will teach you the essence of financial modeling while guiding you step by step until you are able to learn how to build financial models on your own. Let’s get started:

What is Financial Modelling in Excel?

Financial modeling definitions may vary from one person to another. But in this article ,financial modeling meaning is the task of building financial modes in excel. Building financial models in Excel today has become a common tool to understand how key financial metrics such as the Internal Rate of Return (IRR), Net Present Value (NPV), Payback Period, and Yield  are calculated. They are excellent tools for better understanding investment propositions, business or asset valuations, and are the foundation for financial decision-making. Financial models use numerical reports that are expressed through the use of accounting—the language of business. Finance, which may be broadly defined as the science of managing money and other assets, is based on accounting. As such, it is critical to realize the major role accounting, or the tallying of business transactions, plays in building financial models.

financial model  is a required component of nearly any business plan. Anyone interested in starting a new business, starting a new line of business within an existing company, assessing the operations of an existing or proposed business, and/or comparing the operations of two or more businesses, among other tasks, should know how to build financial models in excel, use, and modify a financial model. It is also valuable for companies and businesses as the financial model report is their blueprint in making decisions.

You may have thought that a financial model was basically just an Excel spreadsheet, but as you know, not every spreadsheet is a financial model. The process of how to create a financial model from scratch requires you to know the process of how to do financial modelling. People can and do use Excel for all kinds of purposes. But it doesn’t necessarily represent what a financial model really is. In comparison to a normal spreadsheet, a financial model is:

  • Structured – A financial model contains a set of variable assumptions such as inputs, outputs, calculations, and scenarios as well as include a set of financial forecasts such as the financial statements which are based on the set of assumptions.
  • Dynamic – A financial model contains inputs that when altered, it affects the calculations and the results. Thus, a financial model is always flexible to display different results or final calculations by just simply changing the key inputs.
  • Connected – A financial model contains variables that are connected. This means that when the user changes any of the assumptions, a chain reaction often occurs. Like in cases such as changing the growth rate, this will change the sales volume which in turn changes the revenue, sales commissions, and other variable expenses will also change along with it. This is why proper care is critical when changing the inputs to ensure that the financial model report will produce a good outcome.
  • Forecasts – A financial model is often said to be always looking into the future. Creating financial projections is one of the common uses for a financial model. Hence, forecasting goes hand in hand whenever a financial model is mentioned.
  • Scenarios – A financial model includes different scenarios which are applied to conduct an analysis. This is to further support the projected financials as well as come up with different results which will be helpful for coming up with the best solutions or strategies.

In essence, a financial model is a structure that contains inputs and outputs as well as flexible and dynamic.

Who Uses and Needs Financial Models?

There are many kinds of people that build and use financial models for different purposes and objectives. Financial models are usually built to solve real-world problems, and there are as many different financial models as there are real-world problems to solve. Generally, anyone who uses Excel for the purpose of finance will, at some point in his career, will somehow try building financial models in excel for himself or others to use; at the very least, he’ll use a model template which somebody else built.

The biggest users of financial models are entrepreneurs and chief financial officers (CFOs), bankers, financial institutions, and accountants. Financial models are a lifesaver. Not only do they need it for capitalizing huge benefits, but also for cash flow management, determining financial risks and strategies, analyzing the EBITDA, and, most importantly, using financial models to raise funding. Through financial models, these individuals can manage all the financial actions of a company. Their main duties include tracking the cash flow, financial planning, and analyzing the company’s financial strengths and weaknesses, as well as proposing strategies or corrective actions that will help the company develop more financially.

Why is Financial Modeling Useful and Important?

You might be wondering why financial modeling  is useful and important. You must take note that a financial model is built to represent a real-life situation in numbers in order to help people make better financial decisions. Wherever there are financial problems or situations in the real world that need solving, analyzing, or translating into a numerical format, financial models are a great help. Sometimes it could be just an idea or a concept that needs to be converted into a business case or feasibility proposal. Especially with the help of financial models, one can build a working business plan.

A financial modeler with a substantial amount of experience and a wide range of know-how can put meaning to such ideas by supplementing the details enough to get a working model upon which decisions can be made, investor funds can be gained, or staff can be hired. They are the individuals who can exemplify mastery of how to create a financial model from scratch. For example,  financial models can help investors decide which project to put their money into, an executive track which marketing campaigns have the highest return on investment, or a factory production manager decide whether to purchase a new piece of machinery or equipment. These are just some of the reasons why financial modeling is useful and important.

 

Financial Modeling Work Plan

How do you build financial models? This might be a frequent and common question for a person who wants to embark on their financial modeling journey. Building financial models in Excel requires mastery of the principles and skills in finance, business and accounting. It is only your skills and knowledge that will dictate how long does it take to build a financial model.

Building a financial model in Excel can be very tricky depending on the objective. There are many aspects to consider in order to make the model truly great and adhere to best practices. In this section, we will give you an overview of the financial modelling basics and the important elements of financial model building.

So, let’s assume, you want to build a 5-year financial plan in Excel and, based on that, you want to value the company via the Discounted Cash Flow Valuation Method. Furthermore, the financial model should be highly dynamic, so that you can change the assumptions easily and run different scenarios. How do you go about building such a model in Excel?

Below, we created a graphic together which shows the different steps in building a financial model:

Execution: How to Build Financial Models from Scratch

Building financial models in Excel requires us to work through the following aspects, which involve financial modeling basics. It is important to know the foundations of  how to build financial models in Excel before even creating more complex models. Remember, that financial model building requires an in-depth know-how of finance and business principles. So, it takes a rigorous process to learn how to build financial models from scratch. In this section, we will focus on the execution stage of building financial models in Excel. Here are the important parts of financial models which you should consider when building your own:

The graphic depicts the entire process of building financial models in Microsoft Excel. It will assist you in visualizing how the entire process will unfold and what your financial model should include in order to demonstrate that it will assist companies or businesses in determining the value of their company. The following are the various steps in developing a financial model:

Let’s dig deeper on the crucial elements of financial modelling:

1) Purpose of the Model

The model should be built to optimally suit its intended purpose. In this case, we have to build a valuation model in Excel based on the DCF Method. So, what questions does our model need to answer? – We need to answer the following questions:

  1. What is the valuation date? – The DCF method requires us to discount future cash flows only. Therefore, we need to be very clear as to which date we perform the valuation and discount only future cash flows to the present value as of the valuation date.
  2. What is the resulting DCF Valuation based on the Business Plan? – We want to calculate the company’s Equity Value, which means we deduct Net Debt from the NPV.
  3. How will any change in the assumptions affect the valuation? – We want to explore different assumptions with different scenarios which will affect the valuation.
  4. What are the most likely downside case (worse case), base case, and upside case (best case) scenarios?

So, we need a highly flexible model where the key assumptions will affect the valuation outcome.

2) Model Formatting

A good model needs to be easily readable. So that’s why we also need to think about formatting. We want to e.g. separate all assumptions from calculations or output so that we can quickly grasp which parameters we can change to influence the valuation. Furthermore, sometimes we also want to compare our forecast with historical figures. Historic figures do have a different quality than the forecast as they are real. Therefore, we e.g. need to mark the columns of historic figures with “A” and the columns with the forecasted figures as “F”. So, we can quickly understand which figures are estimates and which ones are real. There are more aspects to consider, e.g. the layout of the model. We simply can use any of the financial model templates which can serve as an example here.

3) Model Structure

The next step is to think about the model structure. We need to build a three-statement model, which means, the output should show the forecasted Income Statement, Balance Sheet, and Cash Flow Statements. From there, we will need to build a section which calculates their net present value as part of the DCF valuation.

In order to build the three-statement model, we need to work backwards, at least by doing some calculations. We do this by building a model first. Here, we will have to go into details and explain, row by row, where revenues and costs originate. The operating model should be linked to the assumptions, which can either be incorporated into the operating model or stated separately in an assumption or Executive Summary sheet. More details will be needed to forecast the different positions, such as a Fixed Asset Schedule (which includes CAPEX and depreciation), a Debt Schedule (interest, drawdowns, repayments, debt balances).

We also need an Executive Summary where we can find an overview of all the key figures of relevance to our valuation. But this is often created after all the calculations and results are derived, since all the data will then be summarized and placed in the Executive Summary.

In order to run sensitivities on how any change in key assumptions will affect the valuation, we will also have to build sensitivity tables whose input parameters need to be linked to the operating model. We, for example, want to know how a price increase will affect the valuation, so we have to connect the input to the sensitivity table to the operating model. This can be done once the basic model is working.

We should model from top to down, left to right, and, therefore, we can arrange our sections accordingly. So, overall, we now have a better idea of how the Excel model should be structured.

4) Model Checks

When building a financial model in Excel, the model might not be correct or inconsistent. Therefore, we should think about some automatic checks we can add so that the Excel model will tell us when something is wrong with the model. Standard checks which can be in-built into the model are e.g. the following:

  • Balance Sheet’s Total Assets should match total Shareholder’s Equity and Liabilities
  • End year cash position as per the Cash Flow Statement should match the cash position on the Balance Sheet
  • All financial breakdowns such as e.g. a revenue breakdown should add up again. The same is true for a relative breakdown e.g. % of sales should all add up to 100% again

There are many more checks you can think about. By adding more checks, your model logic will become more solid and you will start to get to know the financial model inside out and understand much better how the model reacts to any change in the assumptions.

5) Things to Avoid

What we can also do to speed up the modeling process, is to think about things we like to avoid. This will raise our awareness during the modeling process, what not to do, and therefore, can save us significant time. Here are some examples:

  • Avoiding circular references. We want a highly dynamic excel model and all sensitivity tables to work. Also, many users are not familiar with how to resolve circular references and will think the model is wrong. Therefore, we should avoid any circular references and keep the model simple and user-friendly.
  • Links to external files need to be replaced with hard-coded data. There is nothing worse than opening an Excel file and just realizing that our input sources are gone. We, therefore, need to be careful when copying data from external files and hardcoding them. Also, we want to check regularly in Edit Links that no links to external files show up.
  • Linking back and forth should be avoided. The model needs to be understandable and this is done best by modeling from left to the right, from top to down.
  • Using Macros in the model. In cases where you’re not the only one working on the model, macros don’t always get carried over and work. As such, we recommend that the use of macros should be avoided.

There are more things you might want to avoid. The more important thing is to think these things through at the beginning.

6) Creating the Operating Model, Fixed Asset Schedule and Debt Schedule

Now, it comes down to the actual process on how to build a financial model. We start building financial models by preparing first the operating model, we separate the assumptions in the respective section and continue by building the Fixed Asset and Debt schedules. Depending on how complex the business model is, this will require more or less time to do so. Important is to break down revenues, costs, and CAPEX to their key drivers. So that if we e.g. change the planned sales volume, the model calculates how this will affect CAPEX, Net Working Capital, revenues but also cost positions.

 

7) Building the Financial Statements and Financial Ratios

Building the financial statements is much easier to do when the operating model is done properly. Normally, you should be able to get all the figures you need from the operating model by just putting them in the right spots on the Income Statement, Balance Sheet, and Cash Flow Statement. Another important aspect is financial ratios. They can now be calculated quite easily once a three-statement model has been built and allows you to cross-check if your forecast actually looks realistic.

Some assumptions, such as e.g. receivables, inventory, and payables, might go directly into the financial ratios and can be used to calculate the respective net working capital positions such as receivables, inventory, and payables.

As you have prepared a check to know if the balance sheet’s total assets match the shareholder’s equity and liabilities, you will need to fine-tune your model until the checks show you that the model is correct.

8) DCF Valuation

The next step will be to build the DCF valuation model. For this, you will need a discount rate, the Weighted Average Cost of Capital (WACC). You can either calculate the WACC in this model again or simply put the discount rate as the input factor. The next step is then to forecast the Free Cash Flows to Firm, calculate the Terminal Value and the NPV of your DCF valuation. Here you can find examples of financial model templates which utilize the DCF method.

Internal link: https://www.efinancialmodels.com/knowledge-base/valuation/valuation-methods/what-you-should-know-about-the-discounted-cash-flow-valuation-method/

9) Executive Summary

You now are nearly done building your financial model in Excel. All you need to do now is to build a meaningful Executive Summary which summarizes the key figures of your model. Please note, it’s easier to check your sensitivity calculations if the Executive Summary already has been built. You can then simply go through worst and best case scenarios for your input parameters and directly see the results in your Executive Summary.

10) Sensitivity Analysis

Lastly, you will need to build the sensitivity tables and check that each parameter you use in those tables flows correctly through the entire model. Experienced practitioners look mostly at the result. Does any change in the parameters (e.g. EBITDA margin) result in the expected impact on the valuation? If not, you will have to go back and check that the sensitivity tables are correctly linked with your operating model. Going through this process will allow you to check your model in an analytical way and fine-tune the calculations so that you get more and more comfortable that you can rely on the outputs of the model.