This article aims to provide some financial model help for those who are working the first time with a financial model template.
A financial model template serves an intended purpose and is most useful if it is well understood. A financial model relies on a series of financial model assumptions that need to be thoroughly researched and understood in order to make the best use of a financial model spreadsheet in Excel or Google Sheet.
When used correctly, financial models can become powerful tools to enhance and support your financial decision-making in a rationale manner. For this, we would like to offer some valuable tips when using your financial model template. Let us start first to understand what does a financial model looks like.
1. What does a financial model look like?
A financial model spreadsheet contains a series of numbers and formulas specified in a large number of cells included in the spreadsheet. Financial models can contain one worksheet or multiple worksheets, depending on the objectives, level of details required, and the way the model is presented.
In general, a financial model will contain three different types of cells:
- Assumptions: Cells that include hard numbers and serve as an input to the model. Sometimes, financial models mix assumptions and calculations in one cell. In these cases, unless there is a really good reason, financial model assumptions and calculations should be separated as good financial modeling best practices.
- Calculations: Cells containing formulas to calculate a number. Calculations can use any of the many Excel formulas available or use simple additions, deductions, multiplications, or division calculations to perform calculations. Sometimes calculations are spread out step by step by using several cells so that it becomes easier to follow the model’s logic.
- References: These types of cells normally contain links from either a) another cell or b) from another spreadsheet. No calculations are performed here. The cells are just retrieving a value inputted or calculated somewhere else.
In essence, a financial model consists of a sequence of assumptions, calculations, and references all arranged in a logical and presentable manner. The end product can look something like this.
2. What is the Objective of the Financial Model Template?
There is always a reason why a financial model is developed. Each financial model template was built to develop a financial plan which is needed for a reason. The purpose may include providing better visibility with respect to a company’s financial future or calculating important key financial metrics.
Common reasons to develop a financial model are the following:
- Starting a new business
- Fundraising (from investors or banks)
- Evaluating an investment proposition
- Valuing a business
- Acquiring a company
- Scenario analysis
- Cash Flow Planning
There are many other purposes for which financial model spreadsheets are used. Once you know the intended purpose, it normally becomes easier to understand and best use a financial model template.
3. Who is the Target Audience?
The next important aspect of financial modeling is to determine which are the intended recipients of the financial model? Knowledge about the target audience will help to fine-tune the requirements the model should adhere to or assist in selecting the best appropriate financial model template. Typical recipients of a financial model are the following:
- Business Partners
- Business Buyers
- Internal Use
Knowing the target audience will also clarify in which countries the recipients are located, in which currency and which units (e.g. metric or imperial system) the model should use.
4. Selecting a Financial Model Template
Once you are clear what kind of financial model you want to build, you might prefer to use a pre-made financial model template before building a custom-built model from scratch.
Compared to developing a financial model from scratch, the benefits of using a financial model template are the following:
- Faster and cheaper solution
- Contains Industry Know-How and Financial Modeling Expertise
- Reduces costs
- In many cases includes more details and offers better quality
By starting from a financial model template, financial modeling becomes easier as you can benefit from previous work done and follow an already developed financial modeling structure.
5. How is the model structured?
Once you have decided on which financial model template you like to work with, the next step will be to obtain a clear understanding of how the financial model is structured. For example, a financial model structure might be as follow:
- Operating Model
- Financial Schedules
- Financial Summary
- Special analysis
In this spreadsheet structure, the financial model assumptions are used to calculate the operating metrics, which then feed the financial schedules in a Three Statement Model such as the Income Statement, Balance Sheet, and Cash Flow statement. The sections will not always be named like this but many models follow this logic.
The next aspect to look at is the sources of revenues. Which sources of revenues are included in the model? There can be revenues from the sale of products, services, subscriptions, rental income, and other sources of revenues. Each model template foresees different sources of revenues.
Furthermore, additional questions to clarify: What kind of costs are foreseen in the template? Which cost positions are fixed costs, which are variable costs? How much CAPEX is expected and how much Net Working Capital (Receivables, Inventory, Payables) can be expected?
Models who are relying on cash flow projections either use Free Cash Flows derived from a Three Statement Model or might use the Direct Cash Flow method. Both approaches have pros and cons. Kindly refer to this article about two common financial model structures to view some examples of what does a financial model look like.
6. Finding and Updating the Financial Model Assumptions
Once you understand the objective and the structure of the financial model template, it is time to pay a closer look at the financial model assumptions. Many financial modelers use color codes to designate cells, which are used to input assumptions (e.g. blue font color and grey background). However, there are also many financial models, which are not using any separating color coding. In case your financial model template does not have such color coding you could actually consider adding a different font color yourself during your initial review.
Financial model assumptions can either be summarized in one single assumption section but more often can be spread out across the model. You might need to review the model thoroughly to figure out where are the assumptions located.
Below is an excerpt from an example assumption section in a modeling worksheet. Here, the assumptions are designated with blue font and background colors.
7. Reviewing via Financial Model Testing
Now as we identified the assumptions of the model, we need to obtain a closer understanding of how the model works. First, we can use an analytical approach to understand how the model works by running a series of financial model tests. The way to do this is to change assumptions by assumption and check each time how the key outputs of the spreadsheet change (e.g. NPV, IRR, or EBITDA in Year 5, 10, etc.).
If the model changes are in line with what you expect, no need to drill down and you can go to the next test. But if the model behaves as you would not expect it to, then you will need to investigate why is this. In this case, what works best is to work backward. Take the calculated figure which does not make sense to you and try to understand where it was calculated from.
Doing financial model tests will help you to understand the logic of the model template and identify which parts need closer investigation. Wherever your tests fail, this will indicate where to drill down to understand the financial model template in more detail.
8. Auditing Formulas in Excel
If the financial model template uses MS Excel, you can use the audit (or we call it detective) function in Excel for auditing formulas in Excel to understand where the numbers in the formulas come from.
In MS Excel, click on a cell that uses other cells to perform a calculation, then go to the Menu Tools > Auditing > Trace Precedent. This will add connector lines and highlight which cells the precedent numbers come from.
See below example in cell G12, the audit, trace precedent functions highlights which cells are included in the SUM formula. If you select this function two times, it will even add further links to the cells which are referred to by the linking cells.
Auditing formulas in Excel allows you to easier understand where each number comes from and how the calculations and the model logic works. This is a very useful feature in MS Excel which is highly recommended to be used.
9. Which adjustments will be needed?
A financial model template is a template only and not a fully finished financial model. Every business is specific, uses different sources of revenues, has a different cost structure, and in most cases, some fine-tuning and adjustments will be needed to adapt the template to a specific business case.
Also here, some working backward can be very useful, especially if only minor tweaks are needed to have the model to what you want:
- Inserting additional sources of revenues
- Inserting a different cost structure
- Changing the logic of calculations
Be aware that for highly dynamic model templates, changing one part of the model will require to also reflect the change in other parts of the financial model as well. Make the auditing of formulas in Excel is done properly. So once the model is updated, it needs to be checked for consistency.
10. Further Financial Model Help
So, we hope that this article gave you some guidance and insights on how to best make use and work with a financial model template. If you need assistance in customizing a financial model template, the author of the model would be the best point of contact to check for financial model help. Check the author profile on the product page and refer to the custom financial modeling services or contact us.
Feel free to leave us your comments. Happy Modeling!