 Hello everybody and welcome to another financial modeling webinar series from Debron Consulting. This is the September 2018 episode and we're talking about top five uses of timing in models and financial models. What we're going to talk about, the importance of timing in a financial model, Excel tools used to manage timing, and then our various techniques who have some interesting techniques that are very efficient and very scalable that you can use for timing. So how five demos we're going to do? Identify in a single time period, identify in a time block, identify in an intermittent time period, and then identify in a cascading time period, and then manage in two time periodicities in a model. So you have periodicity in a model is when a model is a quarterly model, is it a monthly model, is it an annual model, let's assume you do a monthly model, that's the periodicity of the model is monthly, and you want to show an annual output. So you have two different periodicities, monthly and annual, how do you manage that? And then we'll talk about the certification and the exams for next month. So the importance of timing in a model, why is that so important? Why is timing such a critical thing in a model? Well, one of the biggest challenges really is identifying a particular time period. And then once you identify that time period, try and make it flexible so that when someone changes, let's assume someone says equity is going to be contributed on the 25th of October, next year. Now you're not 100% sure that equity will be contributed at that time. So what you do as a modeler is you make it dynamic. So someone can go and type in another date and the equity will be contributed on that precise date. Or you could say, oh, it's going to be contributed the social month or social quarter. So those are timing, especially for project finance models, you have a lot of timing things. Like construction starting, when is construction ending, timing is very critical. As a modeler, you must build flexibility so you don't need to save 10 versions of the model. It just works. So I've given you already examples, but here are some more. An equity contribution that may come in in year three of the model or year four. How do you time it? How do you automate that so that it just comes in at the right time? Or debt, when does debt come in? When does debt start? What kind of debt is it in the first place? What kind of debt is it and when is the money coming in? When are the repayments expected? Those are the kind of things we're looking at. So depreciation, for example, is also a timing thing. How many years are we depreciating this asset? We're going to start depreciation on the day we buy the asset and then we're going to depreciate for six years. Then what about if someone changes it to four years? Is there a sale in your model that you just change from six to four and then it automatically works? That's what we're talking about timing. You should be able to build that into your model to make it dynamic. Then of course, periodicity, a monthly model and you want to show annual reports from a monthly model that you have two periodicities. How do you do that? So timing is critical. You need to be able to be in control, be the master of timing in your model. That's where you'll be able to build a lot of flexibilities into your model. And I think when you learn how to build a normal three statement model and you want to now progress into project finance modeling, you need to be a master of timing. You really need to be a master of timing because there's more precision in a project finance model. So Excel tools used to manage timing in the model. What are the tools we have available to us? So Boolean, I think it's Boolean. They call it as in true and false. You need to be able to understand how to build, how I call it, it's almost like a fact sheet or a fact row, a fact column. I mean, trying to identify is this thing true or false? Do you meet the timing or you don't meet the timing? So every single time blocking your model and your time blocks in your model are columns because times move across a row. So you have column one could be a year one, column two, year two, column three, year four, year three, year four like that. So you need to be able to identify this thing, this transaction. Is it valid for this time? Is it true? Yes, it's valid or false? No, it's not valid. You need, that's the beginning bits of using timing in models. So how do you go about that? Key thing is logic. You need to understand logical calculations and logical operators in a model. What are the logical operators in a model and how do we use those logical operators to carry out logical calculations? Right? So there are six logical operators in any model. You have the equal to that means something is equal to something less than something is less than something greater, less than or equal to. That's how we write it in the model, the third one, less than or equal to. Then you have greater than and then you have greater than or equal to and then you have not equal to. So not equal to is less than and greater than together. That's how you write not equal to. So these are the six logical operators in any financial model or any calculation you want and how do you implement it by simply saying something, maybe sell A5, are you greater than sell A6? That's how you use logical operators in your financial model to test conditions and then it will give you a true or a false. Now there are some logical functions as well in Excel. So logical functions and the ones you use quite a lot would be and or if. And now there are ifs as a function called ifs that allows you to do more than one condition because your if says if this condition is true, do this. If it's not true, do that or your ifs is multiple conditions. And then for those that know Power BI, Power BI introduced a function called switch that's the DAX, DAX is data analysis expressions. It's a new language for Power Pivot in Excel and Power BI and SSAS. So switch is a really cool logical operator. It gives a test and then various conditions after the test. But really I won't be talking much about switches. Just check it out in Excel. It's an excellent new function called switch. In fact, there's another one called XOR, which has something to do with even and odd numbers and whether an odd number is true or even number is even condition is true, something interesting and complex. But you really don't need that for most financial models. So that's XOR, I didn't list that here. So you have and or and if, really that's the most you will use. Ifs, switch, XOR and the other fancy ones, not really. And frankly speaking, the technique we're going to use will not really use much of this function. So I'm going to show you a different technique. So yes, another handy tool is MOD. The MOD function, MOD is what when you do a calculation like a number divided by another number, you get a remainder. That remainder is what MOD uses. But I think we'll probably use this. I use this a lot when I want to identify time periods like quarters, every quarter, semi-annual time periods and stuff like that. So we'll see how that works. Right. So finally, the big tools you need when you want to build your model and put in timing is counters, masks and flags. We're going to look at them throughout this demo. Counters, masks and flags are your secret weapon when it comes to building models with excellent timing built in, super timing built in. And timing that you can easily explain to anyone and not use what people call nested ifs. You don't use all those nested ifs. You use counters, masks and flags. That's what you should use. And of course, for you to use that well, you need to know how to use cell referencing very well. You need to be very good at using cell referencing. Is it A$2 or is it A$2 or is it A$2 or is it just A$2? You see those referring to a cell reference is very important. There are four kinds of references. We'll look at that as well. Right. So we've seen masks, we've seen flags, all these funny things I'm saying. What can we do? I mean, what do I mean by all this? Can we jump into understanding how all these things work? Right. So we'll start off with a demo. Let me share my screen to see the demo side. So our first demo, the first thing we're going to do in the demo is identify a single-time period. We'll start simple. All right. We're going to just identify a single-time period. And here I have all the demos I want to go through with you, to you, the guys. Let's just do that. Here I have my demo. So identify a single-time period. That's our first demo. Let's look at that. Right. So see this simple. We have this here flag. 2022 is when we want to bring in equity of 100, and let me just put 100,000. I want to bring in equity of 100,000. This equity of $100,000. We want to bring it in at a certain time period. Right. Well, let me put an IR, actually. 100,000 IR. Right. So that means here I want to be zero. This is what I want to see. 0, 0, 0, 100, 1, 2, 3, 0, 0. Right. That's the answer. This is the manual approach, obviously. What we're saying is we shouldn't be using the manual approach, should we? We should use the automated approach. So if we change this to 2020, unfortunately, you have to manually come to your model and copy this, take it to 2020 and make this zero. Right. That's how you manually do it. Now, that's not efficient. Now, some people say, well, we could use an IF, IF, blah, blah, blah, blah, blah, blah, blah. Yeah, that would work. But really, what I want you guys to always avoid in your model is using IFs. What we need is a flag. What we need to do is, first of all, identify where, when exactly, where to flag the year that equity will come in. And to do that, you simply say equals to. My current year period up here. Are you equal to this one here? Now, if I leave this this way, this is going to be an error. It's nearly not going to be correct because we need to lock. This is going to be a relative reference. E9 is going to be a relative reference. Now, this is a single cell that controls everything here. And the clue for you is this. Once you select a single cell input, just know that you need to make it. Sorry, let me end your poll. Just know that once you select a single cell input, you need to make it an absolute reference. So see this D7. We need to make it an absolute reference. And to do that in Excel, you press F4. You just keep pressing F4. It keeps changing the referencing. So I have E9 is equal to $D7. I enter and then I go right. You see that it's going to give us true and false. It's in the correct place. My true is in the right place here. This is my true in the right place. Let me see if I have styles. I already said I could do a flag style. But anyway, typically I would like to do a style and conditional format that says if this true, then give me a nice style. But what we're looking for is true. False, true, true, false, true. False, false, false, false, false. If I change this to 2022, you'll see that our true goes down here. Now, when you talk about Boolean logic, Boolean means false means zero. In Excel, false is zero and true is one. So if you say this flag multiplied by the money, and again, this is an input. So I need to lock it fully F4. Then you get zero. So zero false times this is zero. Now I drag this to the right, and you'll see that true times this. If I double click this formula, you see that it's true times this is not zero. It's correct. So let me think this is complaining something. Ignore, ignore error. I don't like these error messages. Sometimes they're smart. Sometimes they're not. Ignore error. We're done. Good. So this true times 100,000 is 100,000. False times 100,000 is 100,000. Now, this is the more efficient way to do it because if I come here and change this to 2020, now the true comes here and then we get 100,000 moving correctly. Yeah? 221, I mean 2-0-2-1. So all your user needs to do is type in when equity comes in, and equity will come into your model at the correct time. That is how you do that. What's the next one? The next one is identify a time block. So identify a time block. That's what we're going to do next. Now look at this demo. Let's see. You have all these. Let's just, we're going to use an amortizing loan as an example. But I have this loan amount. So this loan amount here, I have this loan amount 200. Let's call it 200 million. Let's say everything is in millions. The loan tenor, that means how many years? This is going to be a five-year loan. Five-year loan. And then our interest rate is going to be 15%. And then the loan is going to start in period one. I want to be able to change this to period three, for example, and the loan starts in period three. So currently the loan starts in period one, right? So if you look at this, so we have this start period of one. If I change it to two, you want it to start in period two, period one. So let's look at this. If you look at my timing, I have done where is my first, my model, historical, this is in 2017 was the last historical, and 2018 is where my projections are supposed to start. So if you think about it, this is my debt calculation here. And this is an amortizing loan, which means that this 200 is the principal, and my interest is 15%, but I want to be paying the same amount every month or every year till I finish paying. So that means there's going to be a payment that includes principal and interest. Now in Excel, if you want to calculate that payment, there's a function called PMT, PMT function. So let's just have a look at how this function works. So the PMT function says, what is the rate? And this is the rate 15%. How many periods is this loan? For what period is it? Five years. So this is an annual interest rate. These are the number of years. And then my present value is this 200, which is the loan amount that will now calculate how much you need to be paying every month, every year for the next five years. So if we scroll down, this is a simple schedule we have. We have our beginning balance, which is last year's ending balance. We have our new debt, which is this 200 million. Then we have our repayment, which is 29.7. How did I get the repayment as 29.7? We'll check that this is the principal repayment. And then you have your ending balance. Now if you pay this principal repayment every year for the next five years, you will get zero. So this is year five. Scroll up. You see that this is year five. So this is when the loan gets fully paid. But you can see that this continues the calculation because we have no masks or flags. This is actually the correct schedule. Right? This one here, the correct schedule. How did you calculate that? Well, this is the payment. You know, we already had the payments. The payment worked fine. These are the payments. 559.7. But then your interest payment is simply, let me do that here. Interest payment is simply equal to this loan, right? Or this balance of loan. Interest payment, if I sum this two up, if I sum my beginning and my new debt, so this is the actual loan. So this loan, right? If I multiply this loan by interest rate, which is up here, so this interest rate right at the top, this is what will give me my interest of 30. So if I drag this to the right, you see that then I have my interest of this. This is the same as this, isn't it? So if my interest payment portion is 25.625.6, then my payments, you know, this is my payments. That means the difference between my payments and my interest should be my repayment, which is principal repayment. So principal repayment is your total payment minus your interest payment. Okay, so that's how all this math works at the bottom. What's the problem with this math? The problem is we are over depreciating, we are not depreciating when we need to, I mean, not depreciating, sorry. We're not calculating the debt dynamically. So if I come here and say, do you know what, my loan doesn't start in year one, it starts in year three. Nothing is happening here. I mean, I need this 200 to come in in year three, like here. Not here, right? So it's all messed up. Nothing's working. So for us to do this automation, the first thing we need is a counter. We need to know which period are we currently in. Like this is period one, this is period two, this is period three. So we need a counter. So counter is going to be equal to the left, which is blank, plus one. This is how you do a simple counter. You do that, you drag it right. Control R, that's your simple counter. Then loan start flag. When is the loan starting? We already knew when the loan was starting. You remember, we did that in the previous example. So loan start period is three. So loan starts on period three. So all we need to do is identify, hey, this period, are you equal to this? And F4, we did this before. We did that in the first example. So this identifies when my loan started, right? You can see that my loan is starting in period three. If I change this to one, my loan starts in the correct time. Next step, we need to do loan period mask. When exactly is this loan period? So let me change this to three, for example. If I change this to three, when is my loan period? Is my loan active here? No. Is my loan active here? No. Is my loan active here? Yes. Is my loan active here? Yes, it is. Is my loan active here? Yes, it is. Because my loan starts here and it lasts for five months, or five years. So this is supposed to be my active period for my loan. One, two, three, four, five. So it's to see true all the way there. And then I see false, false, false. So that my loan, when my loan starts, well, I know from this true mask. This is when my loan starts. But then I also need to know how long my loan lasts. So I can know when my loan starts, but I also need to know how long does it last. So there seems to be two conditions here. We are checking that, hey, this period that I have here, it needs to either be greater than or equal to this cell here. Do we agree? So this period here, the only time your loan starts, well, it has to be greater than or equal to this. Now, since I'm clicking on this, I need to lock it, right? F4. Let's do, this is the first condition. Let's just check that. Let's go right, check that. Okay. So this one finds out when did the loan start. I see that the loan started in three. So three is greater than or equal to three. But then it continues as true, true, true. Let's assume that the loan period is only for three years. Okay. Or should I say four years? And then make it in there four years, four. So here I'm saying, up here, I'm saying that the loan tender is for four years. And up here, the loan start is three. So my loan start is three. And I need it to last only four years. Look at it. It's true, true, true correctly. But this true shouldn't be true. It should be false. What I need is these periods should be false. So that means I need to trap when it ends. So this condition conditions here. But another one. So I'm going to write the other one down here. I'm going to say equals to your loan should really end. When should it end? It should end. Yeah. We are testing whether this, right? And I'm testing whether this one, are you less than? Are you less than what? When should the loan end? The loan should end after four years. But four years after number three, after three, right? So if you start in in period three and ending four years after period three, that means it's ending in what? Period three, four, five, six. So six. So that's going to be this period. I'm going to say it's going to end in when it starts. Therefore, plus the number of periods of the loan. Yeah. Minus one. I want to think through that a little bit. Yeah. So the loan is going to end at when it starts plus how long it lasts minus one. Because if it starts at three, starting at three. So three. If your fingers, you can say three, four, five, six. Three, four, five, six. So it's ending in period six. So how do I get six? So that's three plus four. That's four is seven, seven minus one. So it's going to end. It has to be less than actually or equal to that. This thing, which is six. So I say in the period one, are you less than or equal to six? Of course it will be for this one. But if I scroll right all the way to the right, I less than or equal to period six is when it's going to end, right? Period six. So yes, you can see true stops here and then false, false, false. So this one tells us when it stops correctly. This one tells us when it starts. So we need to combine these two calculations together. When does this stop and when does it start? And that is what, how you do an identifying a time block. When you identify a time block, identifying the starting and the ending, and they're combining the starting and ending together. So this is my starting calculation. We're saying, hey, when should I start this loan? Oh, I should start at three. So this is identifying the starting. This one is identifying the stopping. And we need to combine both. So all I need to do is copy this copy onto this one. And since I have two conditions, we can use the and function. So the and function, I open that and function. This is my first logical calculation. Then I put a comma and I control V, paste that second logic. And I close my bracket. So this is your construct. This one says, hey, period. This time period I'm currently in in my model. Are you greater than or equal to the time period that they said that the loan should start? So the answer here is no, I'm not greater. No, I'm not greater. Yes, I'm greater than or equal to the time period that the loan supposed to start. That's a three. And then the next condition is, hey, time period. Are you less than or equal to when they said that the loan should end? When you combine the two, I just delete this. You don't need this. And you drag it to the right. That's when you now get the exact time that the loan should start and the loan should end. And if I come here and change the loan ten or two years, you see that your true isn't just the three years that is correct, which is loan starts in period three, ends in period four. See, loan starts in period three, and it lasts for only two periods. So this is your loan period mask. Very key. Once you get your loan period mask in business, you now know how long your loan is going to last. And that way you are now going to program it into your model to say, OK, when should the money come in? When should the money go out? That kind of stuff. So that's what we're going to put in here now. So this year, when is it going to come in? We're just going to say equal to. New debt is going to come in when the flag sees so. So when this flag will multiply this flag by the money, this money, and that is how the money will come in. We already did this in the first example. So you guys, it's not so difficult now. See, this money is coming in at the right time. Next step is OK. Now my repayment is simply equal to this minus this. So we're going to do all the calculations for payments here. Your payments. This actual payments. This is this payment. You remember, it's just a payment calculation. We're saying this payments should only be active when the loan is active. And look at it. The loan will be active when this mask is active. So this payment, which is equal to this cell for payment, should only be active, multiplied when my loan is active, which is what this loan mask does. So we enter and I drag right onto our right. So now my loan is coming in at the right time. Can you see that in another right time? Because I've used this mask. Now my repayment, my interest payments is simple matter of some in my beginning and my new loan times my interest rate. So there's nothing special here. Interest rate comes in. So this is my total payment. This is my interest payments. And then this is my principal payment, which would be this minus this. And you can see how perfect it is. It's working perfectly. My loan came in in this period. And then I paid back principal. Then my balance is now this. And then I paid that principal. And these are my interest payments. And now I can come here and I can change this to, okay, this loan is going to last four years this time. And just by saying four here, you can see that the loan is now lasting four years. And then if I say, okay, no, do you know, I'm going to start this loan in period one. The loan in period one can, it's just as if it's automatically shifting, perfectly shifting. So this is a very, very cool way of using your masks and flags to make your loan calculations very nicely dynamic module. But before, before I go to the next one, those that are doing financial modeling, those that want to do the exams, the financial modeling exams, let me play you a video that we just recorded from the very first advanced financial modeler in Nigeria. Her name is Adi Tutu. Adi Tutu Oludare. And let me just play that video for you. Just play how she did it. So just watch this short video. So I'd like to welcome a special guest today. Her name is Adi Tutu Oludare. And she's the very first advanced financial modeler issued and certified by the Financial Modelling Institute. So we did the exams in April 2018, and she is the first qualified advanced financial modeler. So Adi Tutu, you're welcome. Thank you. Yes. And just like to share your experiences as in how, why advanced financial modeling? What's the fascination with modeling? Okay. Why advanced financial modeling? Early this year, when I was to go and training and then I was to write a training plan. I just decided I wanted something different from the usual. I work in internet control. So I didn't just want to do anything related to control. We knew all that is not already. So I just wanted something different, something that would help my career as an accountant. So I looked through all the training calendar that was sent and then I discovered Deep Brown. And then I saw a few of the trainings they had and then National Modelling resonated with me because I used to do a few modeling for people, not the professional way. So I just felt it was a good time to learn how to do it professionally and then just be best at it. And then I decided to involve. Yeah, but there are many financial modeling courses. You don't need to do an exam and do that regal of an exam. Why did you go all the way? All the way. Okay. So when I chose the course, I didn't know you had an exam. So I just chose it to learn. And then voila, when I got there, I saw there was an exam. And I really don't like to give up a thing. So I told myself I was going to do the exam. So I went for it. And where do you work? What's your small background to your career? Okay. I work with Stambic High B2C. I work in the international control department. I'm an accountant. I'm a chartered accountant. I have this CA. So I started working with Stambic three years ago. And it's been a very interesting journey working for Stambic High B2C. It's a great institution. They paid for this exam. Yeah. So you're officially known in the institution as an advanced Spanish model. Okay. So when I got the results, I sent it to my Helen D. That's learning and development. Head of learning and development. I sent it to them to say, okay, thank you for paying for those costs. And I've done you proud by passing. Identifying an intermittent time period. So here we're going to identify what about if they say something like, we're going to contribute equity every first quarter for the next two years. Something like that in your model. How do you identify that? And then someone else changes. No, no, no. We're going to contribute equity every second quarter in the next two years. So let's, let's, let's see that. Okay. Let's see that in the model. Now we're going to talk about identifying an intermittent time period. So let's click on plus sign. So let's have a look at this. So let me hide this. I don't think we need this and hide that. So here we have amount of equity. Let's assume it's 600 million. And they need to contribute this every single year. So every year this, this amount needs to be contributed every year. But this is a quarterly model. So if you look at the periodicity of this model, it's a quarterly model. So we have first of January, 2019 to that first of March, 2019, April, 2019 to June, July to September. So it's a quarterly model. Right. Now we want to identify which of the quarters are we contributing this 600 million. Is it every first quarter we contributed? Is it every second quarter we contributed? Is it every third quarter we contributed? Or is it every fourth quarter we contributed? And how long is this contribution going to last? We're going to start this contribution first of January, 2019. Or is we starting it on in July? When are we starting this contribution? And when are we ending this contribution? Right. So quite a lot of asks here. When is the contribution? Which quarter is it coming in? And how long is this contribution going to last? So here we do our normal counter. Usually what's our counter is equal to the previous cell plus one. This is a simple counter. Right. So let's just highlight that. Control R. Okay. It's a longer model for us. Control R. Right. Then quarter identifier. We need to be able to identify, is this quarter one? Is this quarter two? Is this quarter three? Is this quarter four? Now there are many ways to do this. Plenty different formulas to do this. But let me give you the coolest one I like. The one I like is the mod function to identify what quarter we're in. So if I say the mod or mode, I don't know how to pronounce it. Mod mode. So the mod function or mode function, what does it say? Returns the remainder after a number is divided by a divisor. That's a mouthful. Returns the remainder after a number is divided by divisor. Now, if you think about it, this month is March. First of all, we need to find out what month we are in. I'm going to find the month of this cell, this ending period. What is the month? So this is month three. If I scroll right, this would be month three, month six, month nine and month 12. We know that month three is quarter one. Month six is quarter two. Month nine is quarter three. Month 12 is quarter four. So mathematically speaking, if we take this and divide it by three, right? In fact, you will get one. If you divide six by three, you'll get two. If you divide 12 by three, you get one, two, three, four. So really, that's all you need to do to identify quarters. It's one, two, three or four. So month three divided by three is one. Month six divided by three. This is a quick way of doing that. For the mod function, if I had used the mod function, I would simply say, hey, mod, what month is this, right? What month is that? Comma three. And if I say comma three, the remainder would be zero, right? There's no remainder. So it's really like the opposite of mod is what we're doing. The opposite of mod for those that like function a lot is quotient. So the quotient is returns the integer portion of a divisor. So quotient enter will be one. For all that story, we can just do the simple division ourselves. Since we know that you always divide by three. So this is how to identify our quarter period. I'm just going to highlight that to the right. Come on, highlight to the right. Highlighting manually. I don't really like this. What I do in my models is I put a, I just put like a blocker here, something like that. I just type one or something. I'll make sure it's not visible so that when I want to drag something, I do control shift to right, then it stops at the blocker. And then I go left one step and control R. Just some tricks, right? Okay, so here we have our quarter one, quarter one, quarter two, quarter three, quarter four, quarter five. No issues, that's fine. So let's quickly rush. You have so many, so much more to do. Start mask. When is this, first of all, if this is your quarter identifier, let's find out when the contribution should come in. So let me put it before the start mask. Let me say contribution, money contribution or something. Country or quarter identifier, something. Quarter identifier or quarter flag. We're going to flag the quarter. So there's going to flag all the different times that the money is supposed to come in. So the money is supposed to come in when this is equal to this guy up here, isn't it? R4. So we have our falls. Oh, our falls. So the contour R here, yeah, contour R. True is going to come in always in quarter four. True here, true here, and the like. Now true can come in there, that's nice and good, but we need to know that this loan is still active. So we need a mask for, we need like the second, so example like everywhere, is this loan active? So what's the start mask? What's the end mask? So, and then the loan, I mean, I mean, the, not the loan now, the, when is this parameter active? You know, it's going to be active only between 1st of January and 31st of December, right? So how, it's like, almost like when is it active? Let's calculate the start. Is this start and end date? Is this date here in between these two dates? Is this somewhere inside here? Is this 1st of January, is you in here? So we're saying, hey, this 1st of January, right? We're actually testing two conditions. So we can start with an and straight away and say and, right? This 1st of January should either be greater than or equal to this and also less than or equal to this. That's when we know it's in there. So 1st of January, are you greater than or equal to the starting period? And are you also, are you also less than or equal to the ending period? So we're testing whether this is within this. So that's 1. We test that. And then also we're testing the ending period. Ending period, right? Is it also, is it ending period less, kind of less than or equal to this 2? So to say, but let's, let's just do the starting period first. So if I come in here and I say something like 31st of June, 2020, right? 2020. I'm going to get a false here, which is correct. Right? So I'm going to take this all the way, right? So this just identifies. Oh, you can see good, good, good. So you can see that. It's not greater than. So this guy 1st of January has to be, has to be at least less than because we want to know whether or not the start period. So this start period, are we less than this? So start period is actually prior to this. And then the end period. And when we're supplying the start and the end periods together. So your start period 1st of January, 2019, we need to know, has this loan already started? So frankly speaking, we should actually take it this loan. Are you less than, are you less than, less than this, this 1st of January? Have you really, really started? So we don't even need this is a good thing that we got this. I did this too. All I want to know is that this loan has started. So are you, are you here? This guy, are you less than or equal to? You're less than or equal to my model start period, actually. So you see, if I control R this, you see that, yes, this loan has started that already started. So the start period is within this time frame. If I change this to June or so, then we know that it hasn't started here. It's started here. So 1st of June, it already started. So 1st of June, ha, now look at it. 1st of June is already started in here. Can you see that? It's already started in there. So how do we capture this? See, 1st of June is in here somewhere. So even though it's, it's not, it's actually greater, is less than or equal to this. That means this start period, look at, let's use this. See the start period. Yes, this is after this, but then it's within this period. It's within 1st of April and within 30th of June. So June is in here somewhere. June is in here somewhere. So what we should be using to check it really is probably the ending period. So the ending period, are you less than this? So you're either less than this or you're greater than this. Any one of them that's true should be fine. It's either that you're greater than this or you're less than this, less than this period. But we're now checking the starting period of our model to know whether or not this loan is still active. Right? So let's modify that. We check this out. So, okay. Are you and blah, blah, blah. Are you less than or equal to I-49? And are you greater than, so this guy, if I put the or here, as the loan 1st of June is in between. So 1st of June is in between those dates. So the model, the loan is still active because it started off at the time period, this time period here. If I check this time period here, it is, this 1st of June is still within that time period. So it's definitely going to be greater than, as you'll see, this 46th, yeah. It's going to be greater than or equal to this guy. This is a starting point for our model. It's greater than that. And it hasn't ended. And it's definitely greater than that, but it should be less than or equal to this one here. So that's this same guy, yeah, F4, should be less than or equal to. If I close it, so that's for this one here. Oops, sorry, I didn't close all the brackets correctly. So less than or equal to this close this and this comma. Oh, I didn't type and properly and and and and B and yeah, there we go. So this works. Right. If I copy this formula all the way to the end, let's just test it now. So the 1st of June is true here is within this and is still on start mass still on, but then the end mask. When will it end? We're seeing that the end mass saying that, okay, this loan is not active at all. This start max. Yes, it's active active, but then the end mass is actually checking whether this the loan has ended with this 31st of December 2022. When is this loan going to end? Let's make this easier for us to check. Let's say 2021. All right. So if this loan is starting at 1st of June, that means it's starting here 1st of June. And it should end. Where should it end? 2021 December should end somewhere here. So we should see true only up to here. We should see true only up to here. And then this should start being false. All of this should start being false. Make that fall. How do we what do we ask Excel to be sure that look this loan has ended. Shouldn't shouldn't occur here. Shouldn't be here. Right. So if you look at it, we're looking at 31 December. So I'm saying yes, all this start is fine. But the end we need to stop it. This and that means this ending dates should be this date should be less than or equal to this for you to say that it is really ended. So if I come here and I say, hey, you this ending dates, right? Are you are you less than if it's ending? If it's ending is the only time that it should still be within is when it is before this date. Anything after that anything after this date. Well, it's really ended. This is it. It has ended. So are you before less than or equal to this? No, no, no. Are you greater than? Why am I mixing two things up? So if it's if this date is if this date, sorry, this one here is less than or equal to this date. We lock this. Let's drag that all the way right, blah, blah, blah, blah, all the way right, all the way right, all the way right. Okay. So has the loan ended? I mean, has the period ended? Nope. So that's why we say it's true. End date is still active. You're still active. You're still active. You're still active. You're still active. You're active all the way till when you're not active here. So it's not active here at all. It's active here and here, but it's not active here. So it's not active here. That means it's already ended. Right. It's ended. So that's one way of doing it. And then you can now multiply this two together this times this. And then you get it's true. Both of them are true. So the only time that they're going to be active is when and then that's when you'll see one. You know, I said this is one, one, one. This means it's active, active, active, active, active, active. And then it's not active, not active, not active. Right. So that's one way of doing this. There are quite a few ways of doing it. So this is telling us whether it's active or not. We just use this as help as this loan is active at this period. I mean, this not loan as in the period is active. This timing is active right now. So the timing is not active when we, this is when the quarter, when we're actually getting the contribution in that particular quarter. What quarter is it? Every fourth quarter, if you look up here, every fourth quarter, we're getting our contribution. So that is contribution is coming in in this fourth quarter through then another true, another fourth quarter through. Right. So we're saying that when are we supposed to get a contribution? And are we still active? So we're supposed to get the contribution here. Right. And the question is, yes, we get the contribution there, but is this loan even active for us to, I mean, is this timing active for us to get that contribution? Yes. So true and one, you know, one and true are the same thing. So what about if I scroll right next? True. See, true and true and one, true and one. But then here we have true as a problem, we have true, but we don't have one. So we don't have one wet, which means that it's not active. So this times this will not give us anything. So that means, yes, we know that this is quarter four, but our active, the timing is not active currently. So that's the active timing. And then this is the saying that when the money is supposed to come in as a contribution. So for those two, what we now do is now come here and say when should the money come in, which quarter should it come in? And then we multiply that by, are we active? Is this time period active? And then we multiply that one by, so we're multiplying this by this by the money. So let's me say that the money is here. Let me put some money in here, right? Equity payments. Let's just say equity payments is in there. Oh no, this is the equity payments up here. All here, this 600 million is there. So I lock that and say enter. So the money didn't come in. Money didn't come in here. The money didn't come in here, but the money came in here. And money didn't come in here. Didn't come in here. Didn't come in here, but guess what came in here? So I just dragged this formula all the way, right? And you'll see that it works for all the periods. So the money is coming in at the right times. It's coming in here. Coming in here. It's coming in here. It's coming in there, but it's not coming in here. That's because we've said that this is the end. This is October 22, December 2022. And if you look at the condition here, we're saying that look, it's not active anymore. It has ended. So if I change this to 2023 for some reason, 2023, that means we're still contributing every quarter all the way to 2023. You'll see that it continues to contribute. See, it's contributing. Now it's contributing because it hasn't ended. It's not 2023 yet. Every quarter is still contributing here, but it will stop contributing after 2023. And here we can also say, do you know what the contributions should come in every second quarter, every quarter, every second quarter. Now it's coming in every second quarter, every second quarter. All right. It looks complex, but what we've just done is translated this into a single row, a single row of calculations that just automatically works. Right. So we spent some time there. We just need another eight minutes of time so that we can go through the last two. So we're just going to take an extra time from you guys. Let's see the next two. What are the next two? The next two timing flags or timing calculations that you can use in a model. Let's quickly check that. So what we just did now is we did the identify an intermittent time period. So intermittent time period is what we did. Then here I want to identify a cascading time period. What does that mean? Cascading time period. Any idea? What's cascading time period? Well, mostly cascading time periods are usually like a waterfall, like a time period waterfall. And for that is usually depreciation. It's depreciation that usually has that. So let's do the demo of that. How do you calculate your depreciation calculations? There are so many ways to do that, but let's look at one. So identifying a cascading time period. Let's click on that. So here I have depreciation. I have like a depreciation schedule. Now this is just a simple schedule. You have your assets. So look at this. I have my Kpex and buying 10 million, 10 million, 20 million, 20 million, 30 million, 30 million, 40, blah, blah, blah, blah, blah. These are the assets I'm contributing in these various years of my model. And I would like to depreciate these assets. This 10 million is going to be depreciated over four years. So you build something like this. Just build a simple schedule like this. You list out these assets. You list them out here. And the simplest way to kind of list all of these things here is to use the transpose function. Then you list out all these years as well. You list them out down here as well. And then what you're saying in this cell, for example, is in 2019, I want to calculate the depreciation for the assets I bought in 2019. Then here in 2019, I'm calculating the depreciation for the assets I bought in 2021. That doesn't exist. 2021 didn't exist in 2019. So if I come here, for example, to 2022, here I'm saying 2021, for example, 2021, I want to calculate the depreciation of the assets I bought in 2019. In 2021 here, I want to calculate the asset depreciation for the asset I bought in 2020. So the first thing is, should there be depreciation for this 2019 asset in 2020? Yes. Because this 2019 asset is going to be depreciated over what? Four years. That's one year, two years, three years, four years. So I expect to see true all the way here. I expect to see true, true, true, true, true. It's going to depreciate. And then there should be a false here. It says, no, I'm not depreciating. False here. It says, no, I'm not depreciating. Then what about this 2020? I bought the asset in 2020, which means for sure there shouldn't be any depreciation here. The asset doesn't exist in 2029. It only exists in 2020. Then I'm going to have true, true, true, right? And then true here. So for four years and then false. So now if I change this input here to let's say three, what I now expect to see is true, true, true, and then this one would be false. And then this asset that I bought in 2022 would be true, true, true, and then false. All this manual stuff I'm doing, I'm doing manual stuff because it's supposed to be automated, right? So that's what we're going to do. The first thing we're going to do is to identify the timing flag as in when exactly should the depreciation be active and when exactly shouldn't it be active? So I'm going to use this. I will do the calculation here so it makes sense. So if this is three years, first thing you're going to ask yourself is this, hey, this asset I bought in 2020, am I supposed to be depreciating it in 2022? Right? That's one question. I mean, is it supposed to be depreciated? Should I have a true or a false here? That's the question. So I want you to think about it. What should the formula be inside itself? The clue is this. You need to find the start. It's almost like the timing thing we calculated where we had to find the start and the end together. You want to find out when is it active? So for example, the depreciation I'm calculating here, must start in 2020. So it has to start. So for it to start, you're saying that this year, are you less than this? I mean, less than or equal to this. For you to know whether it should start. It should start whenever, or should I say, this year is greater than, sorry, greater than or equal to this. So that's when you know it will start. Right? As you know, the starting point. So either that this is less than or equal to this, or this is greater than or equal to this. So that's when you know the start. So let me click this. So I say, hey, you thought I'm locking. I'm locking the column. So this is the column lock. It's a column constant. Are you, for example, are you less than or equal to this one here? And this has to be a row lock. So this is identifying the starting points. So do we, this is the starting. I come to the left here and come continue coming here and then come here. You will see that this is false. This is false. Why is this false? Because you can't start depreciation of an asset you bought in 2020. You can start it in 2019. You must start it in 2020. So this identifies the start. The problem is it doesn't identify when it should stop. It identifies when it should start. Now we need to formula to identify when it should stop. So if I have the start, which is fine, what about the stop? When should it stop? Well, it should stop. If it's starting here, it should stop 2020 plus three. It should stop 2020, 2021, 2022. It should stop in 2022. So we need to add three to this. And then now ask ourselves again that whether or not that year. So we have our start. Let's do our stop. Let's just do our stop. So when should it stop? Really, it should stop by saying that this year, this year must be, yeah, for it to stop less than this year, which is this one, right? F4, F4 lock in the column. Here we're supposed to have locked the row. So here we're locking the column. And then we're adding this three up here. So in 2020 plus three is 2023. This must be less than it. Or we could say this must be less than or equal to this plus this minus one. All right. So when we enter, this actually identifies when it should stop. Look at it. It doesn't identify start. Only identifies as stop. It should stop here. 2022. Why? 2020, 2021, 2022. If I change this, for example, to two years, two, it's only depreciating for two years. You can see that, yes, it will stop here. It's not supposed to depreciate here. It's only supposed to be this year. So now we have our start and we have our stop. We need to put an and. We need to put an and function. We put an and function. I can look at the formula by up here. Put an and function. The and function will check and see, let me just zoom in so you can see this clearly. Let me zoom in a little bit more. Okay. So here we're going to say and this is identified our stop. If you remember, how did we identify our start? We said that this cell, which is hiding to the left. And are you less than or equal to this cell up here? And then we locked our row. Right. That's the start. Then comma, this was the stop. So I have my start and stop. If I copy this all the way to the entire table, this entire large table, all the way down. Okay. I don't know what does it end? Okay. Up here. Enter. So you're going to see your truths and false is all around the place, truths and false is. Let me make this conditional format a little better so we can see what we're doing. All I need to identify is the true. So it's not a very smart condition of format. Edit. So now this makes more sense. So if you look at it, you can see that. Cheat over two years. Cheat over two years. Cheat over two years. And if I change this to three, there's depreciation over three years. If I change this to four, you can see it's working. Now this is just identifying when to depreciate. Yeah. You say one year, which makes sense was one year, depreciation two years. I want to identify when to depreciate. Then you all you now need to do because you understand Boolean logic. You multiply that amount by the actual asset. The asset. You lock the column divided by the useful life. So that will now be your depreciation of value, which you can now R D and you now have your depreciation calculating perfectly. And there. Right. And this is the formula. So that's one way of calculating depreciation. And this is how you do a cascading calculation. Pretty neat. So if I change this to four years, you see the depreciation is four years, changes to three years, depreciation three years. Right. Let's quickly jump to the last, last thing. And then we're done. So if we're taking more of your time, and this is cascading, which is your depreciation calculation just to recap. That's the formula in there. There are many ways to calculate this other people use an if and stuff. I prefer to just use a simple and no ifs. And there we go. Now, lastly, if I jump back to our slides before we close. So the last thing we're going to do, we've done the identifying a cascading time period. The next and last thing is managing two time periodicities in the model to two time periodicities in the model. What does that mean? That means we have maybe a monthly model and we require annual reports in a monthly model. So that's very easy. So it's not going to take us much time at all. So let's check that out. Managing two periodicities in a model. So here we have managing two periodicities in a model. Let me give you the example we have. Click on the plus sign. All right. So let's pretend this is our model to the right. We have our financial model. This is the days and monthly model is the ending dates for the monthly model. And these are the figures in the model. So we have our revenue, our operating costs, our bidder, our depreciation. Yep. And this is our model. So what we want to do is give a summary and annual summary of this. An annual summary for 2019 will be all of this all the way to this cell. Yep. So let's just minimize that. So I'll just sum up all of this. This is 2019 revenue. And then first scroll, right? This will be 2020 revenue. So how you do the annual model is you let's create a small template. So this is your annual. So what we need to do is now just calculate the revenue, operating costs, a bidder and depreciation from this monthly model. So we're going to have two periodicities. This would be in another sheet, but I'll leave that for you here. So how do we know total 2019? The first thing you should do is come and calculate what year this is here. So if I'm doing monthly to yearly, I can say this is equal to what? What is this? What year is this? So I say it's equal to year of the cell. So this tells me the year. And I can drag that all the way right. Drag that all the way right. Right, right, right, right, right, right, right, right. That's quite a long model. Wow. Okay. All right. Monthly model. So this just identifies the year. That's the very first thing you do. Once you identify the year, you now come here and do an equals to some if, some if, the range criteria and some range. So what's the some if? Let me open this up. So the range and criteria. So the criteria, I'll start with the criteria. So what do you really want in this cell? It's everything that is 2019. That's what you want. That's your criteria. Everything, when I come to this cell, it's everything that's 2020. So 2019. But because I'm dragging right and dragging down, I need to lock the row. So I'm locking the row. And my range for checking this 2019 is this one from here, all the way to the right. Shift R. All right. All the way to the right. And for this one, you're going to lock, go to do a constant column, column lock. I'm locking the L. And I'm locking the BS. So I'm locking the color so they don't shift. In fact, I can lock everything really. I don't need to lock on the column five. So also lock everything because this whole period is supposed to be static. Doesn't shift at all. Because I don't actually don't want it to go down either. I don't want it to go left and right. And I don't want it to go down. I'm locking it fully. So I'm saying this range here. Go and look for 2019 in this range. Then some, which range? We're now summing this since I'm in revenue here. I'm summing revenue all the way to the right. So I'm summing my revenue for this one. Obviously, I will need to lock the column so that I can drag this. This formula, when I drag my formula down, it should move down. So here I'm locking the column. So I'm saying go and look for all 2019 revenue and sum it for me and say, okay, then you get this figure. And then once you get this figure, now drag that, copy this, click, click, copy. Place it all the way here. And copy, paste it all the way here. Copy and paste it all the way here. And let me just use 2021 as an example. If I sum, do a sum for 2021. So 2021, let's go and sum the revenue for 2021. I highlight from 2021 January all the way to December, right. So we agree that this is 2021's revenue. We enter and you see that the figure doesn't look the same, does it? So 2021, let's F2. Let's check. Did I highlight 2021? All the way to, oh, can you see that? I added 2022. So it's supposed to stop here, right? 2021, enter. And you see it's exactly the same as this. So this formula works. So look at the formula again. Just zoom in so you can see it. This is your formula that does that small magic. Sum if the periodicity you're looking for, the role containing the periodicity you're looking for, and then the actual periodicity, particular value, and then the sum where you want to sum. And that's how you get it, right? And that's it. So that's how you use different periodicities. You take the lower granularity, your model must always be the lowest granularity. So monthly, monthly, and then you can sum it up to annual, you can sum it up to quarter.