If there’s a prize in Excel financial modelling for the most powerful or the most commonly used or the most important formula you need to know about, the If function is probably the winner!
Syntax for Excel’s If function
Here you can download an example of an If function in action. Click on cell B6 and then click on the “fx” button on your taskbar – or use the “Shift” “F3” Excel shortcut. Excel will give you a break down of the structure of the formula. In the “Logical test” the function is looking at cell B4 and testing to see whether B4 (with a value of 3.5) is greater than or equal to cell B3 (with a value of 3.0). Of course, the condition is true. In this case, the If function outputs the text “Code Red” into cell B6. You can experiment with the values in cell B3 and you will find that, if the condition is false, the If formula outputs “OK” into cell B6.
If functions as error checks in financial modelling
Here we are seeing how the If function can be used as an error check in a financial model, outputting a text-based message. Inside the If function though the “Value if true” and “Value if false” components can just as easily be linked to other cell references within an Excel spreadsheet. If functions are a powerful tool for solving financial modelling problems, and you need to master them.
Embedded/ nested If functions
Sometimes you want to solve a complex financial modelling problem, and you may find yourself tempted to put one Excel function inside another to solve it. Embedded or nested functions (where you put one function inside another) can start to look pretty ugly – if you sandwich too many functions together it’s quickly going to become impossible for another user of your model to work out what you’ve actually done. We would normally recommend that, where you want to solve a complex problem, in your modelling you break the problem into component steps, solving each part on a separate line. This will better enable somebody else who picks up your Excel spreadsheet to track what’s going on.
Nested If function challenge: traffic light system for balanced scorecard
You need to be able to master If functions as part of financial modelling though. So here’s an advanced If function challenge. Can you use If functions to set up a ‘traffic light’ system which flags up a condition green, amber or red, depending on the result being outputted from the financial model? Note, this has been a question on a financial modelling course before, from people who want to incorporate a traffic light system into balanced scorecard reporting for their business – so it’s a real-life problem!
Steps in solving the If function financial modelling challenge
The first step is to build an “amber” trigger into the model (e.g. 2.5). Any ratio above 3.0 is “code red”. Anything between 2.5 and 3.0 should be “amber”. Anything below 2.5 should be “green”. Next build a second If function into the model that triggers “code amber” when the ratio output in the financial model creeps above 2.5.
In the example you can see this has been done for you at cell B9: embedded If function download example.
Embedding/ nesting the If function
If you want the entire function in one place, what you need to do is copy everything to the right of the “=” sign in the new “amber” If function (you can select this part of the new function in the formula taskbar, and use the “Ctrl C” keyboard shortcut for copy, and press “Enter”).
Then you need to paste this second if function (“Ctrl V”) inside and in place of the “OK” in the first function.
Finally replace “OK” in the if function with “Green”. You can now test your traffic light system by changing the values in cell B5. Here you can download an example where it’s been done for you: nested If function. Once you see it working, it shouldn’t seem so complicated!
Making a model error even more obvious
A subsequent challenge will see you combining a traffic light text-based warning system with colours that make it even more obvious when you’ve got a problem in your financial model. Excel’s conditional formatting will help us with that financial modelling challenge!
What’s coming up in the Excel financial modelling course extract?
In the next lesson we examine the role of Excel’s Vlookup function in getting data into the right place in a model.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.