 Right, so welcome. Today we're going to talk about cool tips for the Advanced Financial Modular exams. Now, what are we going to talk about? As I said, an overview of the AFM exam, I'll probably do that at the end. Sample questions and answers, and then we'll go straight into the demo. And we've picked out template, build, and print structure. How do you print? Because one of the things in the exams, you should be able to print your scripts. You have to be able to print the entire exam you did in Excel. So give you a very nice cool trick to make that very easy. How do you create scenarios? How do you do depreciation? How do you do deferred tax? How do you do cash sweeps? So those are the things we're going to talk about. So let's quickly move on. So the Financial Modeling Institute is a new institute, mainly, of course, for financial modeling and trying to enhance or trying to improve standardized financial modeling worldwide. So there are three levels. Advanced Financial Modeling is Level 1, Chattered Financial Modeling Level 2, and Master Financial Modeling Level 3. So the Level 1 exams are four hours, and you have to build a model from scratch. So the exams, as I said, are in two days' time. That is the 20th of October worldwide, and Nigeria is also a center. We can do the exams in Nigeria, which is cool. So check them out. Go to FMinstitute.com and check them out. I think this is an extremely cool innovation, because once you are certified by a body that you might financial model, it's easy for you to convince anyone that you're a model, including new potential employers. Great. So the four hour exam. So what does the four hour exam cover? Well, these are the key things you need to focus on when you're doing the exams, or when you want to try and write the exams. The financial model layout itself is very important. Then, of course, there's CVP analysis. CVP stands for Cost Volume Price Analysis. So this is where you'll be able to automate your revenue. How do you calculate sales? You calculate sales in many ways, really. You calculate sales by price times quantity. That's how sales is calculated, price times quantity. No matter what kind of complex question comes in the exams, you need to find the price. You need to find the quantity. Then the price you need to project the price going forward is either you have a fixed price, or you already know the price for the product you are selling, or your price is increasing by inflation. Really, there are no other two ways about it, except if you want to do a cost plus. A cost plus basically means you try and project your cost, and then maybe a 10% margin or 40% margin or whatever margin. That's another way to get price. So as I said, price, you either project it with inflation, or you have a fixed price. Somebody has given you the price already. Then quantity, what you need to do is understand that quantity is usually a very risky thing, and you can't predict it that well. Even price, even price for inflation, you can't really predict inflation. So how you know what scenarios to use is those things that are not too easy to predict, like price and like price inflation, like even cost inflation, and of course, like how much are we producing? How much are producing is dependent on demand, dependent on even your suppliers, is dependent on so many things, really even capacity utilization, how much capacity can your machine, if you're manufacturing something produce, right? So those are the CVP, very important, then intermediate Microsoft Excel skills you need to know. You need to know Excel pretty well, and then you need to know how to do scenario analysis. We'll talk about that. Accounting fundamentals. You need to know basics of accounting. You don't really need to know too much. You need to know what the piano is, the balance sheet is, cash flow, the linkages between all those three accounts. Then model schedules. You need to be able to build, fix, and variable cost schedule, and then working capital schedule, deferred tax schedule, debt schedule, all sorts of schedules, and then the ending of the schedule, the ending balance of the schedule is what goes to your balance sheet. Then you need to have the ability to generate plausible assumptions. So most times in the exams, they will not give you working capital assumptions. You have to generate those working capital assumptions yourself. Then speed and accuracy. That's the key. You need to be fast. What I advise people is when you're practicing, try and finish the mock exam that you're practicing in three hours. Don't do it in four. Try and finish in three. That's my advice. So sample questions. So for example, if you go to our course online, we have a course we've just developed just for the exam called AFM exam prep. If you go to officetraininghop.com, you'll see it. But what we did there is we took the case study exam that the AFM gives out. They have a case study exam they give out, exam script. Then if you see what you need to do, the very first thing you need to do in the exam is mark it up. Highlight the key things. Let me give you a secret. How to know what to highlight is simply this. Anywhere you see a number, highlight it. As far as I'm concerned, anywhere you see a number is usually an input. Anywhere you see a number, highlight it. Anywhere you see a number, highlight it. Key. Very key. So you can see I'm just highlighting different things. This is how you should do it in the exam. Take your pen or take a highlighter to exam. Take a highlighter to the exam and highlight key areas. That's one key step. Then this is just an example of a printed version of a solution for this Henderson manufacturing. Once you finish your exam, just do a control P. Do a control P and see if it is printable. And if it's printable, it should give you something like this. You can see the income statement on the page. This is the historical financials which you'll be given in the exams. You'll be given historical financials. Then this is a summary sheet. This is an example of a summary sheet. You have to have very good line items that make sense. Line items that add value. They have revenue, gross revenue and growth, EBITDA, EBITDA and growth and EBITDA margin, net profit, net profit amount, the growth rate and the net profit margin. DuPont analysis, which is return on equity, net profit margin asset. You don't need to have as much detail as this, by the way. I'm sure the first three will be fine. You don't need to go all the way. Hopefully, you'll have time at the end to do something nice like this. But you do have to put the summary based on the various scenarios. So you do a summary for the base case. You can see up here, I have the base case scenario. Then I have best case scenario. Then, if I go to the next page, I'll have worst case scenario. You must do those three scenarios. Best case, best case, worst case. Right. And then, of course, you have the remaining parts of your model. General assumptions, revenue assumptions, all the different assumption sections. So you can see right here at the top, these are inputs and assumptions sheet with all your assumptions, all nicely listed and clean. Then you have your scenario analysis sheet. We're going to build this. This is one of the demos we're going to do. We're going to see how to build this. Then you have your financial statements. This is your PNL or your income statement. This is your cash flow statement. And this is your balance sheet. So you can see how neat and tidy this is, right? Pretty neat. Schedules, pretty tidy. So how did we create this template? How is it so neat and tidy? Let's jump straight into that very quickly and see how to create a very simple template. I won't make it as nice as this, but let's just see how to quickly create a nice looking template. So the first thing we're going to do is template and print structure. How do we do a template and print structure? Let's see. So template and print structure. So I'm going to share my screen. All right, templates. So we're going to share my screen and then we're going to talk about how to create a template. So here we have a blank screen. So the first thing we're doing is creating a quick template and printing and ready to print. First thing I create three columns. Yeah, my first three columns, I make them tiny. Usually we use that for navigation. This D column is going to be where my major description is. I'm going to make it a little bigger. Then my E, my F. So this would be like description. This will be my units. This will probably be my, like a sum or total. I'll leave this blank. So I'm going to make this G small. Then my H, I, J, K, L, typically where I would want to put my, my figures, right? I want to put, like all the actual figures and the projected and stuff like that, right? So some people start from I, it depends. Maybe you just want to keep two columns in case you need these columns. You want to keep two blank, maybe just in case. Then you could say, okay, we're starting from I. So if I come to I, so I'll come somewhere like this. I'll come here and say actual because I'm going to have three years of actual. So I'm going to put actual all the way there. And then I'm going to have four, I mean, five years of forecast. So forecast, right? One, two, three, four, five. So I just put that there. Why am I presenting it in five? Well, just so that I can put other things up here and you'll see later. So here, actual forecast. Now let's assume that I had already put, I have a start date somewhere. I should have a start date maybe in my menu sheet. But I'm just going to type. Maybe I'm starting 2014, right? So I'm starting out 2014. I'm going to do a format general format. All right. So general format. Hope you can see that general format. So there we go. Let me just confirm that you can see my, yes, you can code. So general format. I'll make this a little bigger. So that's general format. I put a centralize it. Come here and say equals to this plus one. And then I can drag this all the way right. I can make this one's general format as well. And centralize as well. Right. So here I have that. Then you could do something like this actuals. You could go to a custom format and just make them put a zero here and a forward slash or the backslash was my backslash. Don't have the slash that appropriate slash a. Okay. It's not working. Come on. Where is this slash? Anyway, I'll just put a double code a. All right. So double code a double code. So that just shows that it's actual. Okay. And then here for this ones, you could just do a similar thing. Come to custom, put a zero here. And this time I'm going to put a double and an F just to show that it's the forecast, right? F. So I have my A's and F's. So here we have that. So as I said, up here this is actual. To me, this would be my units. And this could be something else maybe. Let me see what I put here. Inputs. Sometimes I need to have inputs directly, some single cell inputs. And then this is like a description and stuff. Right. So that's my actual. I come up here. I probably usually have this section, maybe this section here. I'm going to put a link to balance sheets. Check is okay. So eventually this is going to link. I can merge these two cells. Just going to merge this merge and center. So this is going to be linked to whether or not my balance sheet check is okay. I'm going to link it later. And then here I'll see scenario. Scenario is, let's just say my scenario is base case. Okay. So this is where I'm going to link to my scenario. Center on merge and center. Right. So this is my scenario. Now, why did I do that? So these two things are going to be linked. They're going to be linked eventually. So in fact, I can quickly put a board around it. Put a board around it. And yeah, that's fine. I probably split this one. By the way, to put a board around something, shortcut is contraship seven. Contraship seven. I should have just done that. That's much faster. Contraship seven just puts a board around something. Right. Right. Here we go. Then what else do I want to do here? I could put something like financial, probably concatenate equals to double quotes, financial forecast. And then I put the column and double quotes, 20. This is my forecast. This and I put an and forecast space to space. This guy here. Right. I could lock that. It's not necessary. I could lock lock. It's fine. So this should give me something sensible. Let's see. And then I could centralize this as well. I could just do control one alignment center across selection. And there we go. I could color that whatever color I like. Yeah. Then up here to the left, you put the name of the maybe Henderson. So this is Henderson. Henderson. Henderson. I think it's manufacturing. That's the name of the company. And then this could be my name of my sheet. Right. So this is your template. And you could highlight this and color it as I think color. For example, I like this and give it a color style. So I come to sell style. You would have done your style. So I have a banner style here. I have a banner style. And this one I could just remove the banner style from there. Maybe I would just say there's no banner style for that. Let's click on maybe number center or something. No, no banner style. Let me just click any style. I don't want a banner style there. It doesn't look nice. Okay. I should have done that before. This, this, this. Merge, merge, merge, center. Merge and center. Merge and center. Here we go. Country shift seven. Country shift seven. So this is my template. My template, my grid lines, view grid lines. I can come here and do a freeze pane. Usually, you come to this cell here and do a freeze pane from here. You do a freeze pane, freeze pane. And now I can scroll down and up. And this is my template. The next thing is for printing. The most important thing to print. How do we make this print nicely? Now this is very key cool steps. You just go straight to page layout. Once you've created this, go to page layout, then click on this button here, the corner here, right? Page setup. Click on this page. That's a button. This is your secret. You go to page here, escape. Change this to fit to and leave it as one, fit to one page wide. Leave it as one page wide and then delete this tall. Just delete it. Nothing here. Delete it. Then you go to margins. Your margins are fine. We could reduce the top and bottom margin if you like. Go to header and footers. And then under headers and footers, you go to custom footer. Under custom footer, you see this dialog box. You come to the bottom here, like I could put it, name is what? Sheep name is actually the name of the sheet, right? So that when we print, we see the name of the sheet is this. This tab here. Click on where is the tab. Come here. That's called a tab. Click it. That's a tab. That's the sheet name. I could do something like page in the middle here, page, page, space. And then if I say page, space, this one here is the page number. That's a page. And then page that of then space. And this one, second one here is the number of pages. So the same page, page of this. This is the same page one of two. Here we could say last updated. And I can put a date here. This is the date. Or last updated, maybe last printed or maybe printed on. And I have the date. So you could see, you could create your own custom footer. When I say, okay, look at it. That's a custom footer down here. Sheet name is template page one of one. So this is all. That's all you need to do. Now you could come to sheets. See this sheet. Under sheets, you see rows to repeat at the top. This is what you highlight this. And this is what you want to repeat at the top of every page. You click okay. And this is you've created a perfect template. This template. Now if I minimize a bit, you'll see that if I scroll down, you will see, I don't know if you could see it, you would see some lines here. This break. This means this is where page two will start. This will start. Now you could, if you decide as the ended here, and you want to actually put, you come in here to breaks and say insert click. So now you have a page break there. So anytime you can create your own page breaks. But if you don't want to create your own page breaks, you want it to naturally create its own. You click. So next thing I'm going to do is scenarios. Are you ready for scenarios, guys? So scenarios, how do we automate scenarios? Let's come to the scenario sheet. So if you've got a scenario sheet, first thing you should do is decide on what scenarios you want to do. But really even before deciding what scenarios you want to do, I could just decide on just a scenario one. It doesn't matter. It doesn't have to be, you will decide on what scenarios you want to do, right? And you just type them out. So I advise you just create a very simple scenario three. So typically I have three scenarios. And this is a structure I advise you to build. You will say scenario used. Let me minimize this. Good. The thing here is you say scenario used scenario one, scenario two, scenario three. You could decide whatever that is. And the trick here is you need to create a template for this scenario. It's a scenario template. So here you could say a base case, for example, base case, worst case, or let's say best case, and worst case. Once you, this is a template. Now on that template as well, right at the top here, this is going to be my link cell. All right. So this, or I can put it here, this is going to be my link cell. I'm just going to put one. One basically means this, I want us to use scenario one, which is base case. If I put two here, that means I want to use best case. If I put three, I want to use worst case. So I can highlight this to my contrast shift seven, shortcut for creating a border. I can put contrast shift seven here too, or make this one an input cell. And then now is to plug this and make it work. How do we make it work? You highlight this and I advise you give it a name. Let's call it, I don't know if I've used names before. Let me see. Maybe I have. Yes, I have. So I'm going to call this one l underscore sc for scenarios, l underscore sc, right? Make sure you call this l underscore sc. This is a list for my scenarios. And then this cell call it s underscore sc. So this is called s underscore sc. And this is called l underscore sc. Why did we do that? Because that is the trick for combo box. Once you list out your scenario and you have your switch, this is a switch, you come to your developer. You need to have your developer open. See this developer tab? You need to have it open. Now developer tab, once you get to developer, if you don't have developer, go to file, then go to options. Under options, you will see quick customize ribbon. And then under customize ribbon, you see developer. That's how you get developer. Just tick, tick and you're good. Right. So go to developer, then you go into insert. Then under insert, you see form control. Select the second one. Usually this is the combo box. Select it. Then you come in here and draw it. Just draw your combo box, drag and draw it. So this would create a combo box. So here we have our combo box, right? So what we do is you right click the combo box and you go to format control. Then you get this format control dialog box coming up now. Then under your input range, you just say l underscore sc. Remember we named it. You shouldn't highlight. Most people highlight, don't highlight. There's a reason for it. Then cell link is s underscore sc. Click okay. I click out anywhere and then click on the drop down. You now see your base case, best case and worst case. So this is your combo box, right? So this box is your combo box. Now, if you want to immediately click on this, then you can say best case, for example, say if this is best case, you can come in here and change your scenario used. So we can change it and say, what is the scenario we used? It's going to be an index of your l sc. So it's going to be an index of your l underscore sc comma your s underscore sc comma one. Now it looks strange, but look at it. The formula for pulling out what scenario was used is your list comma your switch comma one. But in fact, you don't even need the comma one. Just leave that. Just your list comma your switch. It's very simple. Also index your list comma your switch. You know we've named them and that's best case. And now if I change this to worst case, guess what? This is worst case. And then this should be replicated in every single sheet. Right? So there we go. So now that we have this, by the way, when we create your template, of course, all you need to do is replicate it. I hope you knew that you just need to replicate it. So you can hold your control key drag up. It's a shortcut for replicating or do your normal right click move or copy create a copy. So hold your control drag up and make copies of it. Once you make copies of it, you can rename the sheets. You can call this inputs and then you come and name the sheets. So that's what you do. All right. Back to scenario. So I've created this combo box. Now everything works. At least the combo box is working and it's all linked to this cell here, this switch cell here. Now the rest is easy. Why is it easy? Because in your scenario one, let's assume your scenario one was price inflation, right? Price inflation. All your scenarios are going to have three things. You're going to have used in the mod, or four things used in the model. Then what I advise you just link to this list. This is equal to let me just say D11, and I can make this F to F4. I'm just going to make it locked. So this using the model, this is going to be replicated for each and every one of these scenarios. Right. Then what you now do is you have to decide what scenarios you're using. It's you that decides, okay, what's my base case? What's my best case? What's my likely case? So I can decide, okay, 2% is my base case, same, secular reference. I need to come to form those. There's a secular reference in my model. Let me quickly delete that. So that doesn't mess us up. Let's go to input interest expenses. No secular reference. Where did I create a secular reference? Oh, that's strange. Okay. Okay, so this is 2%. So I'm going to make a percentage style. So I could just decide this 2%. This is, best case is inflation is at 1%. My worst case inflation is at 3%. Right. I'm just in contra lens. These are my inputs. So I go to my cell styles and make them an input style. These are my inputs. So what did we use in the model? That's the key thing. Now you could use the same assumptions, right? The same assumptions you could use everywhere. I could just copy this. Let me just do one. So now to say what was used is best case, isn't it? So how do you plug this in? So how do you plug it in? Best case means it needs to use 1%. So you click here and say equals to index again, index. You highlight this 3. Let me zoom into this so you can see this. Well, because that's just a trick equals to index. You highlight the 3. Let me remove this from the other way. And then comma, comma what? Comma, which one of the three are we using? We're using it based on the switch. It's the switch that will tell us. The SC, SC, the switch. And then how many columns are we moving? It's just one. Really close our bracket and enter. And then we make this a percentage. And then we drag this all the way right. Right. So this is it. So let me hide all of this so you can see better. Right, anyway. Right. So that's it. If you now come here and change to worst case, it's now 3%. Change this to base case, it's now 2%. Now you copy this whole thing, copy, take it down here. Copy, take it down here. And that's it. So your next scenario, decide what that is. Maybe this is cost inflation. And maybe this next scenario is price. We have a price of an item or something. So price, let's say price, we're just going to make it normal number format. So just a number format, for example, my price for base case could be $10 each or something. Price for worst cases, maybe eight. And price for, so best case would be a bit more, maybe 12. And worst case would be eight. And then of course, since this is units, you need to remember to put your units and say your units. So that you are done with your scenario. That's simple, not too complicated. So you could be your cell style. Cell style would be units, unit cell style. Let me quickly find out what is this circular reference issue we're having. And go to error checking, circular reference, handerson, oh, okay. So have a circular reference somewhere else. So let me stop that from messing us up. Okay, give me a second. I'll close all the other spreadsheets I'm using currently. So, okay. All right. Any questions? Any questions? Anybody with any questions? So why a combo box? Well, typically, a combo box is far more efficient. So combo box, the reason you're using a combo box is to make it easy for the user to change the scenarios, right? It's so much easier. Now, you could use a list box, for example, I could come to developer, insert and do a list box. So it's this one, but it's not as popular. So this is a list box. So I right click for my control and it's the same kind of controls as a combo box. So here, I have my L underscore SC and my S underscore SC. Right. So if I click okay, click out, you click any one of these, it works. So they're both working. So list box, combo box, you decide which one you want to use. Data validation. I won't advise you use data validation. I prefer this not as secure as a combo box. Combo box is more secure. And the reason we named it is so that when we copy the combo box, if you used L underscore SC and S underscore SC, you can go to another sheet and paste the combo box, right? And it will still work, right? So remember that this, I'm going to link this to the scenario, you know, we automated this. So you'd see that everything works. This combo box, if I say worst case, you see worst case up here. Yeah. So that works. I think our third thing is depreciation. So we're going to talk about depreciation. Let's go into depreciation. So I'll come into this template view. Let's go into KPEX and depreciation, right? So this is an issue a lot of people have with KPEX and depreciation. So hold on a second. See my mic. I hope my mic is showing a sign. Great. So for depreciation, we need to, first of all, you need to create a small template. So I mean, I'm not, you're doing a depreciation. You need to know what's the remaining asset life. What's the new assets? How much are the new assets? So they'll give you this in your assumption. So we're going to do straight line. So straight line, depreciation, you have your additions. The additions, I'm going to link it to my input sheet. Did I have an input sheet? I hope I did. Additions, yes. So I go to my inputs, you would have created or generated your inputs. So these are my additions. All right. So my additions. So what about my existing assets? Old assets, existing assets I had. Where are they? New assets, remaining assets. My existing assets, inputs. I have to go to my balance sheet to get that. Inputs, inputs, inputs. Okay. Usually, I think it's good to bring it in your input sheet. So I won't waste too much time looking for it. Let's just pretend, okay, let's pretend I had it. I'm going to put 39. It was 390 or something like that. All right. So those are my inputs brought in, right? Then you build a simple template. And for these additions, you need to bring them, these additions, you need to bring them here. So how you do that efficiently is you highlight and say equals to transpose equals to transpose. And then you highlight these. You close your bracket and you do control shift enter, control shift enter. This, you can't delete, this is now an array. You can't delete anything inside the array. And then now you go and type in your depreciation formula. Now, a lot of times people, there are many depreciation formulas you can type in. But really, what I see is sometimes your depreciation for the first year, what you have to ask yourself is, sorry, you have to ask yourself, sorry about that, you have to ask yourself, do we have depreciation? Did we buy the assets in January? When did we buy the assets in the first year? Is it because we're depreciating for a year? And we need to know, okay, when exactly did we buy the assets? So we could have bought the assets. We can assume that we bought the asset media. By the end of the day, your depreciation will need to know, okay, how many remaining useful life. So let's, I need to know how what's the life of the assets? What's the life of the assets? I go into inputs. This is my remaining life for the existing assets. And this is my remaining life for the new assets. Sorry about that. So if I have 30 years lifespan, technically, this asset is going to be equal to this guy, this amount divided by 30 years, isn't it? That is my depreciation, technically. That's my depreciation if it's 30 years. Yeah. Now, obviously, I can't do that. That's not too efficient. But I mean, it's still plausible. You could divide it by assets new life and drag it right. Oops, sorry. F2, this item. So this item, we need to lock the column. Yeah. And then this item, we can lock the whole thing. So this is my depreciation for the first year. I could highlight this. And this really is my depreciation. But then for the first year, although I'm going to make this look all nice, for the first year, we will need to divide it by two. So what we could do is we could do a multiply. I'm going to multiply by if this year up here, if this year up here, you can't see it now, but if that year, I'm going to lock my row, right? That year, are you equal to this year? I'm going to lock my column. So the locking rows and columns are very important. That's the trick. So here I'm saying, are you equal? And if you're equal, it's going to tell me a true, right? So this is the question. Are you equal? Oops, sorry. So at the end of this formula, it's going to tell me a true or a false. So what I want to do is that if it's true, if it's true that it's equal to the same year, that means we're going to do half of the depreciation. So if it's true, we're going to do half. Now I could decide to just say if I could put an if here, if, if this thing is true, and give me 0.5, which is half, else give me one. This is one way of doing depreciation. I have another way online. I will show you the link. So this is one way of doing it. What will happen when I say enter? You have to put a close bracket at the end. So what it has done is it's given me depreciation for, let me just zoom in so you can see this. So it's given me depreciation for the first year. Now there are many ways to do this. I can now drag this all the way right and all the way down. RD. This error messages every time. Okay, then manually you could now delete these ones that are not really the depreciation for those years, right? There's something up in my formula. Let me see. What is this looking at? Okay, same year. Okay, no. I think everything's fine. F2, F2, yeah, it's good. Everything seems fine. So that's one way of doing it. There are other ways of doing it, but key, another way of doing it is just to simply say equals to SLN. SLN means straight line depreciation, right? What does SLN do? It returns straight line depreciation. What's my cost? This is my cost, F4, lock. I'm just going to lock. Let me take this here. I'm going to lock the column. And comma, what's my salvage value? Well, I'll just put zero. We don't have any salvage value. And then what's my life is this, right? And I lock it. So this is just SLN, straight line depreciation. And it's the same thing. The only difference is you need to put that the straight line depreciation, I think, is very stress-free, straight line SLN. Can you see that? Now, if we are told that it's not straight line depreciation, now for the first year, if you want to, you could just say in your model tell, put an assumption and see an assumption for the examiners that all assets are depreciated fully in the year of purchase. You know, you're allowed to put assumptions, right? So you could put this assumption. If you put this assumption as part of your assumptions, you've told the examiner that, hey, I don't need to divide this first year by two, because this is what some companies do. Regardless of when the asset is bought, it's fully depreciated in the first year. So you could put that assumption. Now, if you're being told that it's double declining your user or declining balance method, not straight line, all you need to do is come in here and say equal to DB. DB returns the depreciation of an asset for a specified period using the fixed declining balance method. So that's a fixed declining balance. The formula is DB. What does DB require? Cost. So this is the cost. You do an F4, F4. Let me move this out. F4, F4, we're locking the column, right, comma. The next thing, it needs salvage. We don't have a salvage, zero, comma. What's the next thing it needs? It needs life. So this is the life here. Now you press an F4. We need to lock it. Now this is where the issue comes. The next one is period. Period means, okay, this is depreciation for what year? Is it for the first year, for the second year, for the third year, for the fourth year? What is it of? So here you have to be a little bit creative. For what period is the depreciation? Forget about month period. For this one, it's going to be the first year, right? Here will be the second year. So inside, we need to put a formula, a clever formula that tells us whether something is first year, second year, third year. How do I know it's the first year? If I was in 2019, for example, 2019, for this depreciation starting in 2017, in 2019, it will be the what? 2017 is the first year, 2018, that will be the third year. That will be 2019 minus 2017 plus one. So how you do this is you simply say for the period, you say equals to this cell up here, this year up here, right? And I lock the row minus this one here, and I lock the column, right? Plus one. Well, what this will give you, if I highlight this and I press F9, you see it gives you one. If I'm in 2019, it will be giving me three. So this is the trick. Once you do that now, the only issue with this trick is if it's negative, you could put a, to stop the negative, you could put a max, a max formula there to say it's max. So if you say max, has to be at least one, right? Max of zero or something, max of zero, one. Well, really, that's there. Max. So I'm just trying to automate the period. So if I close my bracket and enter, this will give me my depreciation. Sorry, this double declining, double declining balance. I have to now divide it by the useful life. So let me go into the formula. Quick, quick, quick FX so you can see it clearly. So here we have your cost returns the depreciation of an asset for a specified period using the fixed declining balance method. So your salvage value, this is your cost, your life, and then your period. Life is, life is the number of periods over which the asset is being depreciated. Oh, I've mixed them up. So this life, yeah, life is the number of periods over with the app is being depreciated, sometimes called useful life. Okay, good. And then this period is, period is the period for which you want to calculate the depreciation. Period must use the same units as life. All right. So period must use the same unit as life. So that's period is using, what are we using for life? Yeah, we're using one, we're using the total periods. Yeah, that's fine. And for month, let me see month. Month is the number of months in the, in the first year. If month is omitted, it is assumed to be 12. Okay, so this will solve our problem of six months, right? That's six months thing. That will solve our problem. Okay, that makes sense. So this double declining. Only thing there for us really is we still need to, okay, cover it into 30 and check. All right. If I drag this right, okay, where's one? Let me decline in balance. Where are we? Where are we? Where are we? Where's the useful life? Look at the life. Look at where we are. The life. Actually, this should be one divided by the life. That's what it should be. But that's an issue with the declining. I'm going to put double declining. Let's see if double declining doesn't have that issue. Double decline is cost, salvage, life, period. So that one should work. Let me see. Double decline is just DDB. It's just the formula that changes. D, this DDB, all I did was change DB to DDB. DDB is double declining balance, which obviously something is wrong with it. Double declining is just DDB. And that has exactly the same syntax. And that one works. At least that one works. Then there's something called sum of the year's digits. I think what they call it, sum of the year's digits is another way they could come. So sum of the year's digits. Sum of the year's digits. Trying to remember the name now. Let's see. Sum of the year's... What depreciation is that? SYD. That's what it's called. SYD. So you have your cost, your salvage, your life, and your period. If you do the same thing, it will work. Right? So this is how you do it. If it's double declining, you use DDM-DM, which it's not really working for some reason. DDM-SYD. And then you could just use straight line. What's straight line called? Straight line method SL, is it? Yes, SLN. So SLN. So if you don't want to do the complex calculations, use these formulas to do your depreciation. The declining doesn't seem to be working because I'm sure there's some nice bug in the formula. We'll try and fix that and let you know how that's done. Trick there is to get the periods. Use this construct of the year here minus the year there, plus one, and you'll be able to automatically get the periods. Right. So let me jump straight to cash sweep. That's another one we wanted to do. Cash sweep. And we'll go to cash sweep. For cash sweep, if I go to debt and interest, you're really calculating revolver. Is the revolver, you're trying to calculate. You're trying to see, okay, if we run out of cash, we need to get money from OD, overdraft or a revolver. And if we have excess cash, then we need to pay off our loans. So the first thing is for you to do a revolver cash sweep, you have to know exactly how much excess cash we have. Do we even have excess cash? So let me go up here and open up the cash flow statement. And then we can move from there. So excess cash, so let's say our beginning balance, beginning balance, cash generated, cash generated, and ending balance. So beginning balance, cash generated, and ending balance. I can just copy these three. So here, I just link this up to the beginning balance, whereas my cash beginning balance. Here we go. This is my cash beginning balance. And then this is my, let me just stop this message coming all the time. And then this is my cash generated. So I just scroll up to cash. What did we generate to generate at this? And then this is my ending balance. So this is cash now. All right. And change in cash position. Okay, we had, we had, and let me just click it there. We don't, the historical balance, I need to have a historical balance. So my, actually my beginning balance, let's just make it equal to my last year's ending balance. And maybe here. So this is my last year's ending balance. I'll just take that as my previous ending balance. Where are we? Ending balance. Yeah. Ending cash. So this would be the sum of this two. I think this is all the way right. R, what's happening? Contra R, Contra R. Okay. This is my ending balance, beginning balance. Let's see. Something's happening. Yeah. What's this? Change in position is 3.3. Okay. Yeah. So we don't have any negatives. I wanted to have some negatives. Okay. So now for your revolver, what you need to know is, okay, we need to first of all calculate cash available for debt service. So cash flow available for debt service. Some people call that CFADS. So we need to find out what is the cash flow available for debt service. That's how do you do that? You go to your cash flow statement, and for every, you pick out all the line items of cash, excluding, of course, this line item for revolver. So if you go to my cash flow statement, what was my operating cash flow, for example, what was my investing cash flow, investing cash flow, and then what were all the other line items of financing cash flow, excluding overdraft. So all these other line items, see these guys, this one, this one, these are all cash generating. I don't want to calculate this finance. So all the other things that we do with our cash before we pay the revolver guys. So these are all cash from operations, cash from investing, and then these are some of the cash from financing. You bring all of that cash in. So I can just copy that and paste this here. So these are all the cash flows coming in. This is relative referencing. Then if this is all the cash we generated before OD, before the overdraft, then this cash, we will need to add this cash to, okay, what cash did we have at the beginning? This is what we generated, right? So we generated this cash. We generated cash. I'm just not labeling it as well, just generated cash. So what about the beginning cash balance? If I come here and say, okay, equals to, what's my beginning cash balance? If I started my cash with this, this is my beginning cash balance, right? These are my beginning cash balances. Those are my beginning cash balances. Then how much do I have available? So this, what do I have available? What I have available, what should I say free cash flow? We could just call it free cash flow. Yeah. This, my free cash flow is now this two, this guy, this guy, right? And if this is my free cash flow, then we can now build out our own schedule for our debt. So we can build out our schedule that says, okay, we have your beginning balance. I do for our overdrafts. Beginning balance, you have, you know, either we are going to additional or OD additions, or what's happening in my spelling? Additions slash repayments on bracket. And then our ending balance. So beginning balance, additions, repayment, ending balance. So what is my ending balance, my previous ending balance, I go equals to, I go to my overdrafts. Where is my overdraft balance? It's in my balance sheet. So OD balance. I'm just going to cheat a bit. I'll just pick this figure. Yeah. Well, is it really in my balance sheet? All right. So, and then this will be my beginning balance. This would be equal to this, right? And then this obviously is going to be a summation of this too. So the trick about cash sweep is, okay, what's going to happen here? Do we need cash? Don't do we need cash? So if let me play with the figures a bit, make it a negative, let me try and make one a big negative. Let me use this and say minus 200 or something. It's not realistic, but yes. So I've made it a big, big negative. Let me just come there. Let me go up to cash. Let's do this messing up with our figures up here. So I'm saying that assets, we bought assets of 200, right. That's a lot, maybe 100, 50. I'm just trying to make some negatives. All right, that's good. So now we know we need cash. Good. So question here is, do we have an overdraft? Do we need an overdraft here? Do we need an overdrafts displaced? Do we need it or not? We don't need it because this is positive. So we need like the minimum, the minimum, right, of maybe, do we need, do we have a balance owed? If we have a balance owed, we'll take that balance, right? If they owe us money. So if they owe us money, let's assume they owe us five. If they owe us five, just example, if they owe us five and they have 12, of course, somebody should pay up. He owes you five. You have 12. You need to pay up. He needs to pay up. So he's either, he's going to give you, if I do the minimum, I'm sorry, minimum, just see how this logic works, the minimum of this and this, right? Of course, it's going to give you five. What about if this was minus 12? What's going to happen? And you see that you can't have minus 12 there because you don't have enough money. You have five. So if he was minus 12, he needs the loan, he needs 12. He needs an additional loan of 12. So obviously, we can't take that directly. So it has to be a negative. So if you have, if I have 12 here, yes, this would work. I'm going to get, I'm going to get paid back five, but this payback that I'm going to get has to be a negative, isn't it? So that's why you put a minus sign at the beginning. Put a minus sign at the beginning. You're saying, hey, you have 12, pay me back five and he does. Now, you don't have money. If I have negative 16, you're going to give me that loan as 16. So that's an additional overdraft. So that's how this very simple logic works. And that's what you will do if I copy this here. That's what you will do. And you drag that right. And that will become your overdraft. These are then the amount of overdrafts that you have. And then these are the repayments. So this balance at the end is what you calculate interest on. And that's how you plug in your cash sweep. So any last questions? We've overshot our time, guys. If you have any questions for me, just type it in the chat before I have to call it, take it or close the presentation. Any questions? Questions? You can type the questions in the chat. So what we did is template and print structure. We talked about that scenarios. We talked about that depreciation. We talked about that. But does that declining balance method didn't really work? Formula didn't seem to work. It was giving us 16. It didn't make sense. And then we have where do we have here? We have deferred tax. Oh, we didn't do deferred tax. Unfortunately, we didn't have time to do deferred tax. And we'll probably have to give you a video. If you want this, send an email. Send an email to training at dboundconsulting.net. Or in fact, you could get let me just give you this offer for those that haven't done this course. I would like recommend that you do this. And what I'll do is at the back end, I'm going to make this deferred tax video maybe free so that you could at least get that. So if you want deferred tax, send me a message, send me an email, send to training at dboundconsulting.net. I'll see if I can get you some help on deferred tax. But this is an offer you could use. And hopefully, yeah, hopefully get that offer, which is our training on this, a detailed training on how you write the exams. So deferred tax and then we had cash sweep, which you just quickly did a cash sweep on. So what are your next steps? You need to get yourself ready. In a scenario where your tax is negative, how do you set up the schedule because of the deferred tax? So your deferred tax, you think is if your tax, whatever your taxes, just leave it like that. So that's, that's fine. If your tax is negative, first of all, your tax can't be negative. It's a minimum tax. Every country has a minimum tax. So maybe you're saying if you have a profit, if you have profit and it's a loss, right? So if it's a loss, then your tax will be negative. In a scenario where your tax is negative, how do you set up the schedule because of the deferred tax? So the deferred tax is you're just saying that you're equating what the government will calculate as deferred tax. So whatever your profit is, whatever your profit before tax is, you add back, let's say you add 10 million. So even if you made a loss before tax, add back 10 million on that loss and calculate it. It's the same thing. You have to add back that amount that the government is claiming. Do you get? That's why it's called deferred tax. Between now and then practice taking the mock exam at least three times. For those that have the mock, your goal is to finish in three hours. As I said, try and finish in three hours, not four hours. And then don't forget to have a good night's sleep the day before the exam. Don't walk all night. Make sure you sleep because you need your energy on that day or four hours of that day. You need your full energy there. So it's not too late to purchase a course. If you want our course, we're giving you a 50 or 25% discount. If you want that course, quickly get that as well. All right. I think we have done, we're done really. So the sponsors of this webinar is a D Brown Consulting. We do training, consulting and payroll. You can check us out at dbrownconsulting.net. And we also have affiliates with Microsoft, affiliated to Microsoft Financial Modeling Institute and a number of other bodies including ATD. And these are some of the courses we offer. We do financial modeling courses mostly financial analysis, Excel, business intelligence, Power BI and the likes. So obviously, some of you that are taking next year, good for you. You have enough time to practice. So really that's us guys. I hope you enjoyed this webinar. Sorry for the few glitches, but go online, go to the meetup group, meetup.com slash financial modeling and let's start the chat there. You can also chat us up. You can chat me up on Twitter, dbrown analysts and any tips you want for the exams. You can chat me on Twitter. We can talk about it on Twitter or talk about it directly on meetup.com. All right guys. So we'll see you next month when we bring you another episode of financial modeling webinar. Thanks guys. Take care.