Experienced financial modelling practitioners know that Excel True/ False checks (as well as Max and Min functions) can be used as a shorter neater alternative to If functions. If there’s a prize in financial modelling of course it should be for finding the shortest neatest solution to a problem, and we do have some neat alternatives to the standard If functions. You’ll see advanced modellers using those.
Constructing True/ False checks
True/ False checks are very easy to construct in an Excel financial model. They can be used for example to check that a balance sheet is balancing, debt is paid down, financial ratios are met or check that other key outputs of your financial model are within a particular range. In the True/ False checks download example attached you can see a simple True/ False check being used at line 8 to check that a model is producing no balance sheet error. All that’s entered in the cell is a simple function in the form “=[Target]=[Value]”. In column D the Target is zero and the actual value produced by the model is 0.3. The check produces “FALSE” and highlights an error. This check is a bit quicker and easier to construct than the alternative at line 7 which uses an If function.
TRUE = 1, FALSE = 0
When you see “TRUE” in an Excel cell, you think you are reading text but Excel sees TRUE as being the same as the value of 1. FALSE is the same as zero.
Shortcutting If functions: an example
With FALSE = 0, multiplying any cell in your model by a FALSE (= zero) check result will result in a value of zero in your model. This is often the kind of problem you might otherwise wish to solve using an If function (unless a certain condition is met, you want nothing appearing in your model). So constructing a True/ False check and then multiplying through by the check can sometimes shortcut an If function. Creating a separate line containing the True/ False check can also make it clearer to a new user which parts of a model are switched on/ off.
Once you’ve discovered, as above, that you can simply multiply a number by a TRUE/ FALSE logic test, you may pledge never to use a full IF function again (when manipulating values). If you’ve made that promise pat yourself on the back: you inhabit the dwelling place of properly-advanced financial modellers!
Next in the Excel financial modelling course extract
Coming up in the next lesson we look at using Excel data validation to drive users to enter particular values in a financial model.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.