Financial Modeling – How to Build a Complete Model with Excel

How to Build a Financial Model

Financial modeling skills demand has increased exponentially in recent years and many job listings for finance positions now include “financial modeling” as a core skill as well as an essential skill for either earning more money or saving money. You’ve probably already discovered how important this skill is, and you know that learning financial modeling will increase your employability in finance or financially focused fields as well as have substantial know-how for when you decide to invest or start a business of your own. Here, we’ll talk about financial modeling — what it is, who uses it, and why it matters. There will also be some examples of financial model templates in Excel which you could use as a reference to better understand how to build a complete financial model on your own. If you’re brand-new to financial modeling, this article is a very good place to start.

The 3 W’s about Financial Modeling

What is financial modeling?

Simply put, financial modeling is the task of building a financial model. A financial model is a numerical representation of a company’s past, present, and future business operations. This numerical report is 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.

A 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, use, and modify a financial model.

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. 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 model 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 uses 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 build a financial model 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 the entrepreneurs and chief financial officers (CFOs). For entrepreneurs, 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 the most important of all, using financial models to raise funding. As for CFOs, it is their job to 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.

Bankers, particularly investment bankers, are heavy users of financial models as they use it to better understand a company’s future financial scenarios. Basically, they use financial models to ensure that they are not pouring out money to the wrong place. Due to the very nature of financial institutions such as banks which have a major role when it comes to fundraising, financial modeling became a part of the rationale analysis of any company. In other words, the business’s core is built on financial models.

But of course, the Banks and financial institutions must still comply with the current regulatory restrictions, and the tools and controls in place are forever changing and adapting. Due to the risk associated with lending and other financial activities, these institutions raised very complex financial modeling systems in place to ensure that the risk is managed effectively. Therefore, anyone working in the banking industry should have at least some know-how about spreadsheets and financial models.

Other major users of financial models are accountants. If the bankers are often evaluating other companies for credit risk and other measures, an accountant’s models, however, are often more inward looking, focusing on internal operations reporting and analysis, budgeting, performance analysis, variance analysis, project evaluation, pricing, and profitability.

Why is Financial Modeling Important?

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. For example, the 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 deciding whether to purchase a new piece of machinery or equipment.

Example Financial Models

Now that you know the significance of utilizing financial models, here are example financial model templates which you can use as a reference as you build your very own financial model. But first of all, you need to determine what kind of financial model you want to build. As such, here are a variety of financial models according to its use case:

  • Three Statement Models – These are models built to forecast the financial statements to determine the financial position of a company as a whole.
  • Valuation Models – One of the most used financial models which are mainly used for valuing assets, properties, or businesses for the purpose of joint ventures, refinancing, acquisitions, or other kinds of transactions.
  • Project Financial Models – Best used for large infrastructure projects to asses its viability as well as determine the capital and structure of the project.
  • Pricing Models – These models are built for the purpose of determining the price that can or should be charged for a product.
  • Tracking Models – Specifically built for tracking financial performance, financials, and other key points which affects the results of the business.
  • Reporting Models – These are models specifically built to summarize the history of revenue, expenses, or financial statements.
  • Fundraising models – they outline an investment case, determine the required amount of financing and show the expected profitability for this investment.

Some people argue that reporting models are not really financial models at all. But the principles, layout, and design that are used to create a reporting model are identical to other financial models. In fact, reporting models are often used to create actual versus budget reports, which often include forecasts and rolling forecasts, which in turn are driven by assumptions and other drivers. Reporting models often start out as a simple income statement report but end up being transformed into fully integrated financial statement models, pricing models, project finance models, or valuation models.

There are still many use cases for using and building financial models. If you want more selection of example financial model templates, you can refer to here: Example Financial Model Templates.

Building a Financial Model in Excel

Building a financial model in Excel can be very tricky as depending on the objective, there are many aspects to consider in order to make the model truly great and adhere to best practices.

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 now, building such a model in Excel?

Below, we created a chart together which shows the different aspects to consider when building a financial model.

Building a Financial Model

As you can see, financial modeling consists of a complex series of analytical tasks which are all put together to make sense for a company. Each task serves a purpose as well as it is connected to the other tasks. Basically, the data realized is carried over to use as either a reference or a supplement to such results to reach the purpose of a financial model – help with decision making. To help you better understand each task, we’ll discuss it below.

Building a financial model in Excel requires us to work through the following aspects which involve financial modeling:

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, meaning, the output should show the forecasted Income Statement, Balance Sheet, and Cash Flow Statements. From there, we will need to build a section which calculate their net present value as part of the DCF valuation.

In order to build the three statement model, we need to work back, at least doing some calculations. We do this in building a model first. Here, we will have to go into details and explain row by row, where revenues and cost origin from. The operating model should be linked to the assumptions, which can either be incorporated in 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 e.g. 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 will be working.

We should model from top to down, left to right, and therefore, can arrange our sections accordingly. So overall, we have now a better idea on 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 also can 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 hardcode 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 to work 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 doing part of financial modeling. 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 take all the figures you need from the operating model by just putting them in the right spots of the Income Statement, Balance Sheet, and Cash Flow Statement. Another important aspect is the financial ratios. They can now be calculated quite easily once a three statement model has been built and allow you to cross-check if your forecast actually looks realistic.

Some assumptions such as e.g. day receivables, inventory, and payables, might go directly in 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 asset match 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 utilizes the DCF 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 parameter (e.g. EBITDA margin) result in the expected impact on the valuation? If not, you will have to go back and check through 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.

Building Financial Models – Conclusion

Building financial models in Excel becomes less scary once you break down the required work steps before you start. Also, it pays off having a clear game plan in mind before building financial models and be very clear to yourself on how the desired model structure should look like. This avoids a lot of time-consuming changes afterward. To speed up the process further, it’s worth to start directly from a financial model template.

Tips for Financial Modeling in Excel

Now that you know the process of what to go through as you build a financial model, here are some of the most useful tips for financial modeling in Excel.

  1. Use keyboard shortcuts as possible to avoid having to spend more time when building a model e.g. copy and paste variables and formulas.
  2. Keep your formulas and calculations simple. It’ll be easier to understand how you came up with the results that way.
  3. Organize your sections in the financial model by utilizing the grouping function. A clean and well-organized model to work on will make it easier to read by the user.
  4. Build your model in a logical format. Aside from organizing your sections into groups, you also need to ensure that it makes sense and in an understandable structure e.g. aligning all calculations should be done from left to right, top to bottom, etc.
  5. Use the search function in Excel to locate the specific data you’re looking for e.g. hardcoded formulas, variables, or numbers.
  6. Remove gridlines when presenting, sharing, or printing the financial model.
  7. Calculate only once by linking the independent cells to the source. This is so that you will only have to change the source formula once and the change will be automatically applied throughout the model.
  8. Avoid macros and circular references. This is due to macros not working on different units and circular references make the model more complex and harder to run sensitivity analysis.
  9. Make use of the Checking function of Excel to ensure that there are no mistakes in calculations.
  10. Create different versions of your financial model while working. Sometimes Excel fails so it is best to have a separate copy just in case.

When building financial models in Excel, you will have to go through a series of trial and error before you can confidently say that you know how to build one. It’s a good thing that there are several financial model templates available to download so, you will have many sources you can use as a reference and start as a base with.

Find the best strategies and resources you might want to try when building a financial model in Excel. eFinancialModels offers truly great resources, templates, and assistance for users located in countries such as USA, Canada, India, Germany, Australia, Japan and many more countries that are in need of assistance with their financial modeling tasks. Building a financial model becomes much easier when starting with a good template. Many different financial modeling examples in Excel can be found here.

The Mini Storage Business Plan Templates provides a financial plan to start a mini storage business. Enter your expected rent…

Add to wish list
Purchase Excluding 7.7% tax

I have added all the industry specific financial models all into one bundled package. Included Models: 1. Mining Operation 2.…

Add to wish list
$200.00 – Purchase Excluding 7.7% tax

A simple and straight forward way to project out growth and churn within a SaaS environment.

Add to wish list
$45.00 – Purchase Excluding 7.7% tax

The Private Equity Leveraged Buyout Model offers a simple template to calculate the financial returns (IRR and cash on cash…

Add to wish list
Purchase Excluding 7.7% tax

Average rating:

The IRR Project Finance Analysis forecasts the expected financials for a greenfield project and calculates the levered and unlevered Internal…

Average rating:

This simple DCF model in Excel allows you to value a company via the Discounted Free Cash Flow (DCF) valuation…

Leave a Reply