How to best work with a Financial Model Template

This article aims to provide some financial model help for those who are working for 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 assumptions that need to be thoroughly researched and understood to make the best use of a financial model spreadsheet in Excel or Google Sheets.

When used correctly, financial models can become powerful tools to enhance and support your financial decision-making in a rational manner. For this, we would like to offer some valuable tips when using your financial model template. Let us start first to understand what 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:

  1. Assumptions: Cells that include hard numbers serve as an input to the model. Sometimes, financial models mix assumptions and calculations in one cell. In these cases, unless there is a good reason, financial model assumptions and calculations should be separated from good financial modeling best practices.
  2. 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.
  3. References: These types of cells normally contain links from either a) another cell or b) 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 arranged logically and 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
  • Budgeting
  • Cash Flow Planning
  • Restructuring

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:

  • Investors
  • Banks
  • Shareholders
  • Management
  • 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 about 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

Starting from a financial model template makes financial modeling easier as you can benefit from previous work and follow an already developed financial modeling structure.

You can search for a suitable financial model template by Industry and/or by Use Case. Many templates offer a free PDF version where more details about the financial modeling structure are visible.

5. How is the model structured?

Once you have decided on which financial model template you would like to work with, the next step will be to understand how the financial model is structured clearly. For example, a financial model structure might be as follow:

  • Assumptions
  • 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 revenue. Which sources of revenue are included in the model? There can be revenues from the sale of products, services, subscriptions, rental income, and other sources of revenue. Each model template foresees different sources of revenue.

Furthermore, additional questions to clarify: What kind of costs are foreseen in the template? Which cost positions are fixed costs, and which are variable costs? How much CAPEX is expected, and how much Net Working Capital (Receivables, Inventory, Payables) can be expected?

Models that rely 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 a financial model looks like.

6. Finding and Updating the Financial Model Assumptions

Once you understand the financial model template’s objective and structure, it is time to look closely at the financial model assumptions (we usually use blue font color and a grey background for inputs). However, there are also many financial models that do not use any separating color coding.

If your financial model template does not have such color coding, you could consider adding a different font color yourself during your initial review.

Financial model assumptions can be summarized in one single assumption section but more often spread across the model. You might need to review the model thoroughly to determine where the assumptions are.

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 have identified the model’s assumptions, we need to understand 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 years 5, 10, etc.).

If the model changes are in line with what you expect, there is 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, you will need to investigate why this is. 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

Suppose the financial model template uses MS Excel; in that case, 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 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 highlight 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 understand easier where each number comes from and how the calculations and the model logic work. 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 revenue, and has a different cost structure. In most cases, 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 also reflecting the change in other parts of the financial model as well. Make the auditing of formulas in Excel 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 of 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, refer to the custom financial modeling services, or contact us.

Feel free to leave us your comments. Happy Modeling!

One response... add one

Hi. I need a 5 year startup car park model to be presented to my investors. there are 4 locations to start off. Can model be re-designed to accommodate these specifics? this would reduce vital adjustments which have pervasive effects on the whole excel file.

Leave a Reply