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.
Financial models are planning tools, and therefore consistent and thought-out definitions of time lines is one crucial aspect of the construction.
Continue reading "Time Lines"
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 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.
Continue reading "Debt Covenants"
You have spent days and nights on your model, but something still will not work? That happens all too often. This article gives you a quick overview of the most common mistakes in financial modelling with Excel.
Continue reading "Typical Mistakes in Financial Modelling"
Inflation is the reason why elder generations like to complain about today's prices - even if nothing really has changed. But when and how should you consider it in your financial model?
Continue reading "Inflation"
Financial-Modelling.net, as many other websites about Excel, uses the English names of the Excel cell formulas. If you use Excel in another language, you will not be able to simply copy and paste the examples from the text; perhaps some of the examples are even much harder to understand if you are unfamiliar with the English function names. However, the University of Twente offers a useful overview over the international names: http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html
With increasing complexity, financial models become more prone to errors. While you can never be sure if a model is completely correct (let alone accurate), you can avoid mistakes with a few easy best practices.
Continue reading "Excel: Using Checks"
Sometimes, you will need to derive interest rate assumptions from your financial model. The big agencies make their rating methodologies a secret, but there are quick and dirty approaches such as Altman's Z-Score. Continue reading "Modelling Credit Ratings"
While it may seem self-explanatory, it is a good idea to clarify: What is "Financial Modelling"?
Continue reading "What is Financial Modelling?"