How to best work with a Financial Model Template

We have been asked before to give some more explanations on how to use a financial model template best. We have therefore written this article to explain in more detail on how to best work with a financial model template.

Most importantly, a financial model is only useful when it’s correctly used and when it’s provided with good assumptions. Users who don’t know how to use a financial model correctly, or use a not very well researched assumptions, might not receive all the merits of working with a powerful financial model template. Therefore, it’s important to shortly review on how you could approach it by offering a structured manner on how to work through a financial model template.

 

1. What’s the model’s objective?

A financial model serves the purpose to answer certain questions when it comes to financial planning. Try to figure out which questions the model you have downloaded it does answer.

Each financial model serves a distinct purpose. Is the purpose of your financial model to value a business via a DCF valuation? Does the model seek to answer how much the project or investor returns should be (IRR analysis)? Or is the model simply a tool to obtain a clear picture of the company’s financial situation in the future? Once you know the purpose, it normally gets easier to understand and work with the financial model.

 

2. For whom the model is made for?

Another important aspect is that each model is based on important inherent assumptions such as to the situation of the company or the asset subject to this model. Is this a model made for a Startup company or an established business? – Who is the audience to which such a model normally is presented to? Sometimes, the model also uses assumptions specific to certain countries; so, it raises the question, if a model made for a real estate property in the US can also easily be applied to value a property in Germany or Asia. Indeed, taxes will be calculated differently, and even some of the assumptions will be of course, different.

 

3. How is the model structured?

The next step is to obtain a clear understanding of how the model is structured. Does the model focus on direct cash flows, or does it include a three statement model or anything else? Where are the assumptions located, where is the operating model and how are the cash flows derived from the DCF valuation or IRR analysis?  Figure out the model structure and ensure you understand which part of the model is located where.

To know more in detail about how a model is structured, see this article about two common financial model structures.

The following are also considered as most critical to understanding:

  1. Which are the sources of revenues and from where the revenues come from?
  2. Which are the major cost items and which ones are fixed and which are variable costs?
  3. How much capital expenditures (CAPEX) and the increase in net working capital is required in the next years to execute the business plan?

For this, you might need to use the detective function in Excel to trace back each cell to its predecessors in Excel. You can simply go to the Tools Menu, Auditing, Trace Predecessor. This allows you to understand where each number comes from and how it is calculated. This is a very useful feature in Excel which you can take full advantage of, to do the checking easier and faster.

Detective Function Excel

Trace precedent cells in Excel

E.g., here he sources revenues for a mobile app.

Financial modeling - sources of revenues

  • Paid downloads mean all revenues which are obtained from sales in the app stores such as Apple’s iTunes and Google Play. By nature, these would be one-time revenues.
  • Subscriptions mean revenues which are obtained on a regular basis (e.g., monthly, quarterly or annually). They have a different quality to the entrepreneur since they are recurring.
  • Advertisement revenues usually depend on how much time is spent on the app and how many times ad impressions are shown.

Now, it might be that some of the revenue sources do not apply to your business, but you simply can eliminate some sources from the template or add your own as needed.

If yes, then you seem to use the right model, and you can go ahead tweaking it.

If no, you might consider searching for another model template or if you can manage to do it yourself, you can go through the model to figure out how the model could be adjusted to serve your needs.

 

4. Which are the assumptions?

Assumptions

Once you understand the objective and the structure of the model, it’s time to pay a closer look at the assumptions. Many financial modelers use a different color code to differentiate assumptions (e.g., blue) from calculations visually (e.g., black). Normally assumptions can either be summarized on one assumption sheet but many times assumptions are spread out across the whole model and different sheet. Using a color code to highlight such assumptions, make it much easier to review a model. In case your model does not have such a color code applies, one trick to better understand a financial model is to add a different font color yourself.

Below is an excerpt of an example assumption sheet. Here, it is shown where all figures which contain assumptions are in blue font and grey background.

Which are the assumptions

How does the model react to a change of assumptions

A next check you can do is to play around with the assumptions to see how the model’s output (e.g., NPV or IRR) change when you change the assumptions. Try to do this systematically to figure out which assumption influences the output the most.

  1. Which are the key assumptions used for revenues?
  2. Which are the key assumptions used for costs?
  3. Which are the key assumptions for CAPEX?

When you got this list, try to make a statement on which ones you think are the top 3 assumptions for this model.

 

5. Which calculations are performed?

Having identified the assumptions should leave you the model with two types of cells, calculation cells, and reference cells.

  • Calculations cells should link back to other calculation or input cells. Sometimes they might contain a hard input such as a number. In that case, it might be easier to understand the model structure if you move that number to a different cell and mark it as an assumption. Calculation cells now can either provide an interim step needed for the next calculation or can result in a final output. To see if a cell is a predecessor for any other cell, use the detective function in Excel (as shown above).
  • Reference cells do not contain a mathematical function; they contain simply a single link to another cell. Those links can origin from the same sheet or from a different worksheet. Some modelers mark numbers which contain links from another sheet in a separate color. This way, it’s easier for the user to work with these links and just change the calculations between those links and the output.

Now, to work with a model, you need to become comfortable that the calculations are correct. The way to do this is to test the model. The easiest way to do the testing is to focus on the key output (such as NPV or IRR) while changing assumption by assumption.

Every time the key output changes, you can ask yourself, did it change in the right direction (up or down) and by the right magnitude? If a change in the assumption does not make sense to you, try to investigate and figure what exactly does change, where, and when, you change the assumption.

 

6. Which adjustments will be needed?

A template is just a template only and serves as a basis for further fine-tuning and adjustments. As your business is very specific, nearly every template needs to be adjusted to reflect your individual circumstances. The way to adjust the model is to work back from the output and adjust/insert additional calculations as needed, e.g.

  • The template uses three different sources of revenues, but your sources of revenues are very different. In this case, just insert a new section with your sources of revenues and make sure the revenue line sums up your new sources. Try to follow the formatting and structure of the old sources of revenues, check if some of the figures were used somewhere else, e.g., to calculate operating ratios or to show in an Executive Summary. Try to replace those figures as well with your new contents.
  • Different cost structure. The same as above, rework until the model properly reflects your cost structure but use the formatting and structure to have your model done in a systematic and concise manner.
  • Etc.

The goal of your customization work is to come up with an income statement which you feel correctly represents the sources of revenues for your business and your business logic. In the case of a three-statement model, you will also need to have to work through a Fixed Asset Schedule, Debt schedule, Balance Sheet and Cash Flow Statement.

 

So, we hope that this list gave you some guidance on how to best work with a financial model template. If you still find it hard to customize the financial model yourself, we can help you out by offering you our customization services by going to this page: Custom Financial Modeling Services. Also, if there are certain points that we failed to add in this article, feel free to comment down below and let us know what you think.

Leave a Reply