Real modelling Jedis have worked out that (when you’re dealing with numbers)
you need never use one of Excel’s If functions again. These modelling gurus regularly make use of an Excel trick that enables them to shorten and bring more clarity to their formulas. Once you know this Excel tip you’ll probably find yourself pledging never to use a full If formula again.
TRUE/ FALSE checks in Excel
If you interrogate a regular If formula (e.g. by clicking on the formula and pressing “Shift” “F3” at the same time on your keyboard) take a look at the first box. That first part of the If formula registers “TRUE” or “FALSE”. It stands and functions quite happily on its own, and gives us the chance to bypass and shorten the full If formula.
Using TRUE/ FALSE to construct simple checks
Yes that’s correct, the TRUE/ FALSE part of an If formula stands on its own. That can be handy for example for constructing simple checks in a model. You don’t need a full If formula to check whether your model output equals your target value.
Using TRUE/ FALSE checks for model switching
Apart from constructing simple model checks, Excel’s TRUE FALSE checks help you construct simpler more elegant switches in your modelling. Take a look at the example below – you can click on the picture to download it. You may realise that when you see “TRUE” to Excel that’s the same as the number 1. “FALSE” is the same as zero (look at line 15 in the example). Suddenly we’ve got a way of constructing short neat switches in models, without having to touch a full If formula.
Never using a full If formula again
If you’re getting the hang of this, you may be on the verge of taking the pledge never to use a full If formula again (when you’re manipulating numbers). What you may be realising is that you can happily take the result of a TRUE/ FALSE check and multiply by that. You don’t need the full If formula.
Simplifying embedded If formulas
Now you know how to recognise a Jedi modeller. You’re in on the secret. Now when you see those old-fashioned old-hat regular If formulas you’ll be wondering: “What’s going on here? Haven’t they been modelling very long? Don’t they know they can reduce that regular If formula right down?”. You’re in on the secret though. In our quest for “shorter, neater, better” by shortening those formulas we’ve taken a step in the right direction.
But here we’ve stumbled across a way of radically simplifying horrible complex embedded If formulas. We’re not a fan of embedding/ nesting: putting one formula inside another. We’d rather you think harder about your formula choice, or take a few more lines in your model and break out the logic step by step. That’s clearer all round. But we don’t live in a fantasy world of perfect modelling. Embedding happens and if you find yourself forced into that situation with your If formulas, what we’ve got here is a way of radically simplifying them.
An example: Excel If formulas
Take a look at the example below. If you click on the picture you can download it. The first If formula is a regular one – horribly ugly and embedded. When we model like Jedi Knights in the next line down though, the logic is dramatically simplified.
But Jedi modellers should have more than one trick up their sleeves. We’d expect that wouldn’t we?
Max/ Min functions to shorten the If logic
Sometimes a delegate on a financial modelling course has seen Max/ Min functions at work. What’s going on there? Nothing that special really. Again, you’re just seeing the shallow footprints of a Jedi who treads lightly upon the Earth. Jedis have some choices (how cool is that?) and are always looking for the shortest neatest most elegant modelling solution. To shorten the regular If formula they can either multiply through by the TRUE/ FALSE check or use Max or Min functions. When you’re seeing those Max/ Min functions all you’re likely seeing is someone who wants to shorten the regular If formula.
Here’s an example showing (i) the regular old-fashioned If formula (which by now, you’ve probably pledged never to touch again for fear of looking like a total amateur), (ii) the version that multiplies through by the result of a TRUE/ FALSE check, and (iii) the Max/ Min option side all by side.
In the example we’re pretending that we’ve got a model that produces a forecast cash balance. Cash could be positive or negative. If cash is positive it needs to be allocated onto the assets side of the balance sheet. If balances are negative we need them appearing on the liabilities side e.g. under overdraft or a revolving credit facility.
Passing the Jedi test
Now you’re very clear on the hallmark of a Jedi modeller. When you see one of their models you can smile knowingly to yourself. You recognise the signs that it’s a real advanced modelling expert at work here. This is one of the chosen few who knows that you need never write out a full If formula again. Now that you’ve made sure you’re part of that special club you should happily feel a teany tiny bit smug. Pat yourself on the back. You deserve it.
Get a free financial modelling check up
Excel has hundreds of formulas on offer. That can present a challenge: you need to become fluent in the formulas that are going to be most helpful in financial modelling.
We’ll send you one email a day for a month.
Each email should take only around ten minutes a day to work through and you can unsubscribe at any time.
The course covers the Excel formulas our delegates find best for financial modelling.
About Financial Training Associates Ltd
At Financial Training Associates we’re all happy modelling geeks but, rest assured, we’re bigger fans of Excel than we are of Star Wars.
If you’re brave enough to admit that you could be made of the same kind of stuff, you’re a person who will likely benefit from a best-practice financial modelling course.
It might not be a religious experience, but it might be close. And it might see you starting to wander around the office mumbling “shorter, neater, better” to yourself.
You’re reading stories from Financial Training Associates Ltd: holding the hands of those on the path to Excel modelling enlightenment.