We have been asked to give some more explanations how to use a financial model template best. We have therefore written this article to explain in more detail how to best work with a financial model template.
Most importantly, a financial model is only useful when its correctly used and when its fed with good assumptions. Users who don’t know how to use a model correctly or use 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 how you could approach it by offering a structured manner 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? Or 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; therefore 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 different.
3. How is the model structured?
The next step is to obtain a clear understanding 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. See the last article for two common financial model structures.
Most important also is to understand the following.
- Which are the sources of revenues and from where the revenues come from?
- Which are the major cost items and which ones are fixed and which are variable costs?
- How much capital expenditures (CAPEX) and 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.
E.g., here he sources revenues for a mobile app.
- 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 a search for another model template or go through the model to figure out how the model could be adjusted to serve your needs.
4. Which are the 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 an excerpt of an example assumption sheet is shown where all figures which contain assumptions are in blue font and grey background.
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.
- Which are the key assumptions used for revenues?
- Which are the key assumptions used for costs?
- Which are the key assumptions for CAPEX?
When you got this list try to make a statement 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 modellers mark numbers which contain links from other sheet in a separate colour. 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 now to test the model. The easiest way to do this 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 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 – ditto here, 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.
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.
We hope this gives you some guidance how to best work with a financial model template.