Successfully building a financial model can be a complex process. This article shows you the main steps you should follow.
Creating a Financial Model Step by Step
Custom Number Formats in Excel
It is essential to define number formats in Excel in order to improve the usability of your model and the readability of its reports. This article explains how to do that.
Speed Up Your VBA Macro
For more complex financial models, macro can become an integral part of the calculations. Letting them operate can slow down the work - in extreme cases up to hours. This article explains ways to speed up your macros.
Modelling Asset Utilisation Plans
Asset utilisation plans are a simple way of presenting clearly the use of assets over a projected period. They are particularly important in industries with high variability or flexibility when it comes to renting and using assets.
Working with Flags
One practice established among Excel power-users is to use "1" and "0" within data tables to indicate a "yes" or "no" or similar two-way attributes. The usage of such "binary" flags will be explained in this article.
Using Colours and Formats
Financial models are complex constructs with sometimes gigantic sheets. Using cell formatting within Excel helps you to keep an overview.
Organising Sheet Tabs in Your Excel Model
In theory, a model consisting of only a few sheets would be neat. In practice, 50 or more sheets are normal. In order to maintain usability, you should organise your sheet tabs:
- Sort sheets by categories and sub-categories
- Use sensible sheet names (a "nomenclature", if you will):
- Use names that refer to the categories and sub-categories, most commonly as a pre-fix
- Use additions like "monthly", "quarterly", "annual" for different time period lengths
- Use colours that refer to the categories and sub-categories
- Use index sheets for each category: In this article, the importance of a table of contents and a "cockpit" is explained. If your model exceeds 50 sheets, just one index sheet with hyperlinks is not enough. Additionally, you should add index sheets for each category/section, always to the left of the respective sheets. That will greatly increase usability.
- Excel: Sheet Tabs
Some practitioners tend to hide sheets - for example, if they feel they are too technical and do not want the users of the model to be confused or to mess with any settings. You should never do that! If someone else is trying to follow calculations - be it your user, or perhaps a colleague assisting you - hidden sheets will only lead to confusion. Or you may forget to remove sheets that were formerly important but were rendered useless by some model changes.
So, instead of hiding sheets that are still needed, just put them to the far right, clearly label them as auxiliary calculations or similar and, if needed, protect them:
- Right-click on a sheet tab and choose "Protect sheet..."
- In the new dialogue, choose a password for protecting the sheet, and choose what users should and should not be able to do within the sheet.
Time Lines
Financial models are planning tools, and therefore consistent and thought-out definitions of time lines is one crucial aspect of the construction.
Excel: Annuity Calculations
Annuities are fixed payments over a period of time. In financial models, annuities are especially relevant to modelling interest and redemption payments for loans, bonds or leasing contracts. Continue reading "Excel: Annuity Calculations"
Debt Covenants
Debt covenants are agreements between a company and a creditor usually stating limits or thresholds for certain financial ratios that the company may not breach. Their projection is a vital component of a financial model.