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 have put a chart together which shows the different aspects to consider when building a financial model.
Buidling a financial model in Excel requires us to work through the following aspects which involve financial modeling:
- Definition of the Purpose
- Model Formatting
- Model Structure
- Model Checks
- Things to Avoid
- Operating Model
- Debt Schedule
- Fixed Asset Schedule
- Financial Statements
- Financial Ratios
- Direct Cash Flows
- Discount Rate
- Discounted Cash Flow Valuation
- Key Financial Metrics
- Scenario Analysis
- Executive Summary
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:
- 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.
- 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
- How will any change in the assumptions affect the valuation? – We want to
- What are the most likely downside, base case and upside 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 historic 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 calculates the Free Cash Flows to Firm, which we then can use to discount and 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 differnt 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.
In order to run sensitivities 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 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 Sheeet’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 awereness 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 ddata 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.
There are more things you might want to avoid. Important 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.
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, its 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 practicioners 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.
Conclusion: Building Financial Models
Building financial models in Excel becomes less scary when you break down the required work steps before you start. Also it pays off having a clear gameplan in mind before building financial models and be very clear to yourself how the desired model structure should look like. This avoids a lot of time-consuming changes afterward. To speed up the process further, it pays off to start directly from a financial model template.
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 in building financial models. Building a financial model becomes much easier when starting from a good template. 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 roll and cost structure and calculate Project IRR, Investor IRR and required funding.
I have added all the industry specific financial models all into one bundled package. Included Models: 1. Mining Operation 2. Apple Orchard 3. Hospital 4. E-Commerce (Amazon specific) 5. Real Estate (with Join Venture capability)
A simple and straight forward way to project out growth and churn within a SaaS environment.
The Private Equity Leveraged Buyout Model offers a simple template to calculate the financial returns (IRR and cash on cash multiple) of a leveraged buyout acquisition from a Private Equity investment perspective.
The IRR Project Finance Analysis forecasts the expected financials for a greenfield project and calculates the levered and unlevered Internal Rate of Return (IRR).
This simple DCF model in Excel allows you to value a company via the Discounted Free Cash Flow (DCF) valuation method. The discounted cash flow valuation model uses a three statement model to derive free…