Flags and conditional formats

Overview

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

-Three things fundamentally changed and improved my financial modeling skills. They’re the absolute must-knows for any analyst and I have to share them. The first one was sensitivities and scenarios, which we cover throughout the course. The second is control accounts, something new to record volumes of items over time, and we will cover that shortly. The third, and the topic of this lesson, is something called binary flags. Good try, Dan, but not that type of flag. In this lesson, we’re gonna to learn what flags are, their use within a financial model, and how to create them. And as you may imagine, given these flags are one of the most important items to learn as a financial modeler, you’ll get to do these many, many times throughout the course. And let’s start by recognizing that there are many situations in financial modeling that are binary in nature. This is when something occurs only during a limited set of circumstances, i.e. if something happens, or within a specific duration of time. And in the context of that it’s useful to remember that multiplying by zero will always yield a zero result. Let’s say I have a set of revenues for five years of $100 and expenses of 60. So my EBITDA is 100 minus 60 equals 40. And even though I show five years of cash flows, these cash flows only meant to last for three years and I’m gonna need to build a single formula that is consistent across the whole row. But I need to make the values in year four and five zero. Broadly speaking in plain English, or at least plain kiwi English, if we are passed year three, our values must be zero. If, being the clue, we can use an if statement. But rather than apply an if statement separately to both the revenue and the expense line, I’m gonna do something just a little bit different. I can say if the year is greater than three, give me a zero result, otherwise a one. I’m creating a result that’s binary. It doesn’t matter what the year is, it’s result can only be a one or a zero. Returning a one result in years one to three, and a zero result in years four and five. I’ve added this if statement calculation directly above the revenue line in green. And given multiplying by zero results in zero, if I multiply the revenue and expense line by this if formula, this set of ones and zeros, the financial projection would be cut off after year three and my values in year four and five are equal to zero. So what have I done? I’ve effectively replaced an if statement in both the revenue line and the expense line with a single binary if statement, and then multiplied that if statement line by revenue and expenses. This reduces the number of if statements in my model, replacing them instead with simple multiplication. The formula are therefore easier to review and as such, there’s arguably less risk in my model. This one zero if statement is called a binary flag. And before we go a step further, let’s recognize the different types of binary results that can live within a financial model. Within a project life cycle, we can either be in construction or operations, or we may have both historic and future cash flows in our model, and they don’t occur at the same stage. They are binary. Either you’re in a future period or you’re not. You could be in a period before or after our loan is repaid compare to the period within which you’re actually repaying your loan. Or finally, perhaps we are in compliance of our loan covenants or we’re not. We’re either breaching it or not. So knowing that flag simplify formula is certainly helpful but the real power of flags is in it’s visual representation. By replace the green bar from earlier, we can format a flag to show more clearly when the project or business was operating. With this formatting and row label saying Operating Period, the model tells me a story that I need to know, being cash flows should stop past year three. Can you see how a binary flag with a one or a zero result can really help the user understand a model? I can’t live without these now. They show me all of the items in a model that occur in a specific time-frame or that occurred due to a specific event.

Hey, I really liked what you did there, but how did you make that beautiful formatting? I’ve total fear of missing out. I want to learn how to do that too.

Great question, Dan. How do I create this formatting? First of all, the format needs to change based on the results of my if statement. It should be green with a white-colored one or a light grey with a dash if the value is zero. We could use conditional formatting in Excel to create this. We’ll create many flags during this course so let me demonstrate just this one.

Welcome into Excel. Here we’re going to create our flags and the beautiful format you saw a short while ago using the same example.

Our inputs are clearly separated from our calculations and our flag is shown on row 11. The calculation is the same, just saying that if the current year is greater than three years, then make the value zero, otherwise one. The revenue and the expenses simply take the input value times the flag, so we can see in year four and five, the values become zero.

So how do I create this format across row 11? First I highlight them and what we’ve done is we’ve created a style called Flags. I navigate across to Cell Styles and select Flag. What this does is it adds a gray shading to the cells and it’s changed the color of the font to gray and it’s also make the zeroes to dash, which we here at Pivotal180 like because it makes it clear and easy to see for the user. This hasn’t though, created the automatic formatting of green, where you would require. So I highlighted this whole row and again I go back up to Home into Condition Formatting and New Rule.

We’ll learn some of other formatting rules later and now we’re going to select “Format only cells that contain”. And what we want to do is format the cells that are equal to one, we want to make the cells that equal to one, green. So now I’ve selected cell value is equal to one, I select what format I want to see if the value is one. I’m going to start by making the font white. So, the Font menu, I select white. I’m going to add a border by selecting Outline and I’m going to go and add a Fill itself. Our standard color for flags is not a standard color of Excel so we select More Colors, Custom. I’m going to type in what’s called the RGB into the section. So 78, 172 and 150. And now you can see the green color down here.

I can press OK. I select OK, I’ve done all the formatting I want to see when the value is one, and I’m going to press OK. You now see my cells had popped out as green and you can now probably see why we made the ones white because it makes it clear to the user that there’s a value of one in those cells.

Again what is great about this is I can clearly see when my project is in operations and directly beneath it I see all of my calculations related to that timing. You can’t get any clearer than that.

The main benefit of flags is that they show you what to expect. So they make you aware of something such as “Have I breached the covenant?” or “Is my loans still outstanding?” The visual representation is really powerful.

Flags may also simplify your formula as you can multiply many things by a single flag instead of writing an if statement on many lines. And it really boils down to one thing, at Pivotal180, we’ve a single rule we like to use. When you have something that is only meant to happen over a specified time-frame, create a flag before you do any other calculations. Just seeing when something occurs is a really helpful starting point before you tackle the problem.

Now you have an overview of flags, you are ready to see the full benefits throughout the rest of the course.