Guides
How to model 2D Corkscrews
In this guide, we will consider how to model a number of balance accumulations using a corkscrew structure and 2D modelling techniques.
Timeline Conversion – Flows: Quarterly to Annual
Working with more than one timeline in a model is common. Modellers need to be able to take series data calculated in a high resolution timeline (e.g. quarterly) and aggregate it in a low resolution timeline (e.g. annual). In this modelling guide, we will explain how to aggregate quarterly series data in an annual model… [Continue Reading]
Data pickup functions: Lookup
In this guide, we will look at how LOOKUP might be used to match constants with corresponding dates in a horizontal timeline. Our example is based on escalation factors – where the last date dependent constant is assumed to apply to all remaining periods in the timeline. Hence our requirement is for an approximate match.
Data pickup functions: SUMIF
In previous guides we have considered the use of VLOOKUP and INDEX / MATCH as a means of matching constants with a corresponding date on a horizontal timeline. In this modelling guide, we will look at SUMIF.
Data pickup functions: Index Match
In this modelling guide, we will consider how to match a date dependent constant with its corresponding date on a horizontal timeline. The functions used are INDEX and MATCH.
Data pickup functions: Vlookup
Model assumptions that are date dependent are presented as constants to facilitate sensitivity analysis. How should such constants be best spread across a horizontal timeline so they match their corresponding dates? There are a number of Excel functions that might be used to pick constants and allocate them to a corresponding column in a timeline…. [Continue Reading]
Die Anwendung von Berechnungsblöcken in financial modelling
Financial Models sind oft komplex und schwierig zu lesen. Lange Formeln, die auf weit entfernte Stellen im Modell hinweisen, tragen dazu bei. Berechnungsblöcke helfen dabei, Modelle einfacher zu lesen und zu navigieren.
Conceptual Modelling Using SmartArt
It is tempting to dive straight into the spreadsheet when faced with a strict deadline. However, taking the time to plan and create the model structure visually can be effective in ensuring the logic of the model is clear to the model developer and to anyone that uses the model later. This guide concentrates on… [Continue Reading]
Profitability Index
There are a number of tools that are commonly used for project evaluation: net present value, payback period, IRR and profitability index (PI). Profitability index measures value created per unit invested. It helps in assessing which project to choose from a number of projects: the higher the profitability index, the more attractive the project.
Loan Calculator
This modelling guide looks at how to model key aspects of a loan – including a choice of debt repayment profiles (level debt service vs level principal). The accompanying spreadsheet includes a presentation sheet that shows the main features of the loan.
How to perform a Monte Carlo simulation
To simulate is to try to duplicate the features, appearance and characteristics of a real system. The idea behind simulation is to imitate a real-world situation mathematically, to study its properties and operating characteristics, to draw conclusions and make action decisions based on the results of the simulation. The real-life system is not touched until… [Continue Reading]
How to model Net Present Value
In this guide we will consider how to model a net present value (“NPV”). We will also consider the Excel functions available that are specific to calculating an NPV.
Inventory Modelling
Inventories are short term assets held as part of an organisation’s core business operations. Inventory management is an important part of working capital management. Where inventory levels are significant, a good model should show the impact on cash of holding such significant levels. We will look at how to model three types of inventory: raw… [Continue Reading]
Corporation tax frequency of instalment payments
Corporation tax is a levy on profits earned by companies. A corporation is often required to make periodic payments of tax in respect of its estimated tax liability. Modelling the frequency of tax instalments in a financial model is important if an organisation’s cash flows are to be modelled appropriately. It is also possible to… [Continue Reading]
Modelling Rolling Reductions
This modelling guide deals with the challenge of modelling rolling reductions. Assume that a company signs up customers. Some of those customers subsequently drop out: 1% in the 1st month, 3% in the 2nd month and so on. Every new tranche of customers signing up is expected to follow the same attrition pattern. In this… [Continue Reading]
Share capital for SPVs
Organisations issue share capital (effectively certificates of ownership) often as a means of raising funds. This guide assumes a specific situation relating to a single purpose vehicle (“SPV”). An SPV is a company established for a particular project and then wound up once that project is complete. In this guide, we assume that: 1. Share capital is issued… [Continue Reading]
Accounting depreciation
Accounting depreciation – Initial balances and ongoing capital expenditure (This guide replaces the guide – Advanced depreciation using SUMIF) Ongoing capital expenditure programmes give rise to modelling challenges when it comes to accounting depreciation. Care must be taken since assets start and stop depreciating at different times. In this guide, we will also consider how… [Continue Reading]
Quick charts
Charts and graphs are used to make information clearer and easier to understand. They play a critical role in helping people to visualise large amounts of information, make better decisions and communicate their results to others. This modelling guide explains how to make quick charts and how they can be useful in analysing data. In… [Continue Reading]
Tax loss expiration
Losses sustained by a company might be available to match against future profits. Lower future profits mean less tax paid. A “tax loss carry forward” refers to the practice of matching the losses of previous periods with a current period’s profits. This modelling guide explains how to calculate and account for tax loss carry forwards… [Continue Reading]
Timeline Conversion – Balances: quarterly to annual
Working with more than one timeline in a model is common. Modellers need to be able to take data calculated on a quarterly basis and present it on an annual basis. The principles used in this guide may be used whenever balances calculated using a more frequent time period (e.g. quarterly) need to be presented… [Continue Reading]
Dual Timeline IRR
A project’s internal rate of return (IRR) includes all relevant cash flows regardless of when they occur. Construction period cash flows may be modelled on one timeline (e.g. monthly) and operations period cash flows might be on a different timeline (e.g. quarterly). So to calculate an appropriate internal rate of return, we use an approach… [Continue Reading]
Calculation modes in Excel
Excel, by default, recalculates all open workbooks whenever there is a change in an input or a formula in any of the open workbooks. Excel has a number of options that allow you to control when open workbooks are recalculated. There are two types of calculation mode in Excel: automatic and manual This modelling guide… [Continue Reading]
Modelling Advance Payments & Retentions In Construction Contracts
This modelling guide focuses on advance payments and retentions in construction contracts – this financial modelling approach can also be applied to other contracts where similar mechanisms are applied.
Taming the Goal Seek
Goal seek is one of the most powerful tools in Excel. It can be useful in a wide range of situations. This guide will show you how to get the best from goal seek and present some tips to make our lives easier while working with it. This guide assumes that you know a bit… [Continue Reading]
Debt Service Coverage Ratio
This modelling guide is under review – a new version will be coming soon. Debt Service Coverage Ratio (DSCR) is the ratio of cash available to service debt, to interest principle and where applicable lease payments. It is a widely used benchmark to measure an entity’s ability to meet it’s debt service obligations. The higher… [Continue Reading]
How to model index linked bonds
An Index Linked Bond is one in which bond cash flows are calculated with reference to future inflation rates. As at 2008, government issued index linked bonds exceeded $1.5 trillion. This modelling guide explains how to model index linked bonds.
Profile selection using INDEX
There are numerous different ways of repaying the principal on a term loan: annuity style; level principal; bullet; balloon and sculpted repayment profiles. A financial model should be able to switch easily from one scenario to another. A good Excel function to use is INDEX. IF, CHOOSE and OFFSET are also frequently used by modellers… [Continue Reading]
L’utilisation des Blocs De Calcul en modélisation financière
Les modèles financiers sont parfois complexes et difficiles à lire. Un élément important créant cette complexité : l’utilisation de longues formules référant à d’autres cellules du modèles. Les blocs de calculs aident à faciliter la lecture et la navigation dans un modèle. This guide was superbly re-produced and translated into French by Yohan Larochelle. Yohan… [Continue Reading]
Installing and using productivity shortcuts
We’ve taken three operations that we perform most often when constructing calculations and automated them using macros. These additions will make a significant contribution to your construction skill efficiency, by removing the need to repeat lengthy keystroke combinations.
Relocating Inputs
Everybody knows that in financial modelling, inputs, calculations and outputs should be separate. When you’re in the “build phase” of a model, there can be short term benefits of locating inputs next to the calculations they are driving. The guide explains those benefits, and shows you how to quickly and easily move the inputs to… [Continue Reading]
Using Placeholders
Building financial models is rarely linear. Often we start with incomplete information. Sometimes, in the act of trying to model something, we find out that our conceptual understanding was not as clear as we thought it was. Placeholders give us the freedom to mark lines as temporary. Sometimes because we don’t yet have sufficient data,… [Continue Reading]
Including Actuals in a financial model
Unless you are modelling a greenfield project or a brand new startup, financial models often require us to include several years of “actuals”. This modelling guide explains how to incorporate “actuals” in both balances and flows.
Day Count Conventions
Banks quote interest rates on a simple annual basis. These are known as quoted (or nominal) rates. They often need to be manipulated in order to undertake modelling calculations. Since different money markets quote using different conventions, it is important that the modeller understands how the quoted rate should be manipulated.
Modelling Balances
There are two basic types of line item in financial models – flows and balances. Balances are amounts at a point in time. Balances can be financial or non financial. Every balance has similar properties. This guide explains what those properties are, and gives a standard model component that can be used for all balances.
2D Calculation Blocks
Calculation blocks are a key feature of FAST models; they help to make models more readable. Sometimes however, we have to repeat the same kind of calculation many times. Having lots of the same kind of calculation block is not always the best approach.
The devil’s guide to spreadsheet creation
Modelling can be tough – by following this guide you can shift your stress and make your colleagues lives hell! The consequences for falling for some, or all of the following temptations are described in more than eighty spreadsheet problems on the Eusprig website
How to reduce file size in excel
When building models in excel sometimes files become large, slowing down your modelling progress and making simple calculations painstakingly slow. Size is often a function of the number of calculations and complexity of formulae. There are also causes of excess file size that are not related to model complexity. This modelling guide explains how to… [Continue Reading]
Calculating reserve account target balances using MMULT
Sometimes we need to multiply a vertical range of numbers, by a horizontal range of numbers. This requirement occurs frequently in project finance in calculating reserve account balances. This modelling guide shows you how to use the MMULT function effectively for this requirement.
FAST Financial Model Design
In financial modelling, consistent, uniform design increases efficiency and reduces error. This modelling guide sets out some recommendations for a “default” model design. This will often have to be adapted, but it’s a good place to start.
How to model debt annuity repayment
An annuity debt repayment profile involves “level debt service” – with interest reduces and principal increasing over the term of the debt. This modelling guide explains how calculate an annuity payment profile.
How to model partial period factors
Events in the real world have an annoying habit of not occurring on your financial model period end dates. This modelling guide gives you reusable code to calculate partial period factors. This will allow you to easily deal with events that occur in between period end dates.
Creating and using links in FAST financial models
Links are a core component in FAST financial models. This guide explains what they are for, how to create them and how to use them.
How to display forecast and actuals on one excel chart
If you’re preparing a chart to show historic performance alongside future projections, it will be helpful to make the difference between them clear. This modelling guide shows you how to combine “actuals” and “forecast” on the same excel chart.
Setting up excel for financial modelling
The default settings in excel aren’t necessarily the most useful for financial modelling. In this guide we recommend a few simple changes that will increase the efficiency of your excel modelling.
The use of calculation blocks in financial modelling
Financial models are often complex and difficult to read. Long formulas which reference remote areas of the model contribute to this. Calculation blocks help make models easier to read and navigate.
Recent handbook discussion