 So hello everybody and welcome to the financial modeling webinar series from D Brown consulting and David Brown I'll be taking you today on the webinar for seven steps to automating any debt Schedule so we're going to be talking about debt and hopefully you don't have any debts But that is not too bad. That can be good and that can be bad. Yeah, that can be bad. It depends on how you manage it, right? so Okay, let's see testing one or two things, okay So please feel free to chat in the chat and then also put your hand up if you want to talk and then we release your mic And we talk and let's try and make this as interactive as possible All right, so we'll be done before an hour and let's say this is just about 11 o'clock started. So let's move on So about us we do training consulting and payroll at the Brown consulting We do training in fact we train the analysts We just say we're the analyst trainers. So anybody that has any analysis or analyst role We did data analysts reporting analysts financial and a resales analyst HR analysts I don't think there's anyone that doesn't do some form of analysis or the other So we train analysts and if you go over to our office training hub.com that is our online platform You'll be able to Hi, Yusuf. Hi Yusuf. Everyone's saying hello. Hello. Good. Say hello on the chat Please everybody should say hello only come into the chat and please close the door behind you so that there's no noise in the room Yeah, anyway, so the payroll we also do payroll. We have an online payroll platform and we also do consulting So let's move on for me. I'm David Brown. I am the one taking me for this webinars I've been doing fun or building financial models now for almost 20 years and Modeling is quite a cool thing for me I mean, it's a nice thing about modeling is I'll give you some tidbits is you you need to build the model in your head first How many of you think about that? I know we have a couple of modellers here in the chat For me, I have to build the model in my head. It has every single thing about the model has to be built in my head Then I now translate it into Excel and then the key thing for me also is to understand What are the main outputs that I need to do in a model before I build it? So if you have any other ideas about how you build models, can you share in the chat and let's just keep chatting in the chat Well, we're listening to the webinar now our affiliates a deep round consulting. We affiliated the financial modeling Institute We are the training approved training providers for them back one of the first in the world to be registered as an approved training providers training provider and Financial modeling Institute is like your icon your ACCA your CFA but for modeling So there are three levels of exams and I'll be talking about that later. Very cool Microsoft data analytics partners to Microsoft and we are also members of ATD Association for talent development. So we kind of have the expertise for transferring knowledge We these are the courses we offer I will advise that you guys do the advanced financial modeler course That's the course with a certificate attached to it from the financial modeling Institute I think it's an excellent course because we have a special offer which I will show share with you later Where we're going to pay for the exams for you So if you stick around you see that offer later and we do business intelligence courses with the Rupal BI and Excel We do Excel courses. We have office 365 courses and financial analysis and accounting courses So these are our main courses. We also have our online platform on office training hub.com Right, so what are we going to talk about today? I'm going to give you an overview of debt and then what is a debt schedule or schedule and the types of debt components of debt importance of debt in a financial model and Seven steps to automating the debt calculation. So I'm going to give you seven steps and Go through the seven steps and then we're going to do a demo of those seven steps and how they work, right? So stick around and we're going to see how all this works out And I'll also tell you about the offer for the financial modeling certification that we have Yeah, next week is the exams for financial modeling Institute. That's April 28th for the Exams, but there's also another exam in October. So you could also do that So what exactly is debt? So debt is an amount of money borrowed by any party from or one party from from another party Often to make large purchases and all that kind of stuff But at the end of the day one party lend money to another party and the party that lends money Expect something back. Of course, you expect your money back, but you also expect interest So typically that's how it's done now in financing when you're financing a business You either finance a business with debt or equity now and so that's usually you get your money back and you get interest Equity when you invest, you're not expected to get your Investment back, but you will get returns on that investment. Equity is kind of more long-term And although it depends if you have private equity, so private equity firms What they do really is they invest in a company And they kind of fix the company up management wise and make the company much more efficient cash flows are more stable And then the exit the exit through the stock market or however else they exit So that kind of equity is not so long term per se Although there are some private equity firms that do more long term Now what the name we call that kind of equity is patient capital Patient capital is really one of the best capital because you just keep it there in the market Capital because you just keep it there in a business. You know, it's a long-term thing So if you're Warren Buffett, for example, you love patient capital because you're investing in companies for the long term So debt is just a means of financing and at the end of the day debt is always you invest You make a return and then return is called interest and there are different ways of slicing and dicing debt. We'll look at that right Toys R Us Who here knows Toys R Us? Please could you type in the chat? Do you know Toys R Us? How many of us know Toys R Us? Yes, I know it. I'm typing yes. So type yes or no in the chat if you know Toys R Us You should know Toys R Us Okay, so for Toys R Us Toys R Us are going through a kind of a mess right now So I'm going to play a short video just a two minutes or so video about Toys R Us. Let's just America's most iconic toy store chain full of everything from bikes to trains to video games plans to start closing all of its us stores You're going to be singing that all day long Toys R Us announced plans to liquidate overnight over 70 years in business It will close or sell at 735 us locations, including its babies are us stores The move means around 30 000 employees will lose their jobs CBS News business analyst Jill Sussinger is here to look at the ripple effect. This will create Jill Good morning This one I think really hurts because it was so fun as a kid to walk through Toys R Us stores looking at the aisles of Stuff that you can't get but then you would want to dream about and everybody wanted to be a Toys R Us kids That's so so they filed for bankruptcy in september take us through the timeline What happened that it didn't work out? Well, they were really hoping for a very strong holiday season and the holiday season was quite disappointing now remember The whole reason they went into bankruptcy was sort of a convergence of bad timing and bad decisions They did a deal in 2005 with a private equity firm It saddled the company with five billion dollars worth of debt at the same time online sales Growing and becoming a huge player in the toy market And also we should note that the actual tastes of kids started to shift away from physical toys To technology around their phone and you put it all together and it was a rough go All right, so that was Toys R Us although I cut the kind of cut it short sorry about that so I was asking how many of us know Toys R Us not many of them not many people said anything Yes, yes. Yes. Okay. Good. You know Toys R Us. I can see yes. No. No. No Okay, for those that don't know Toys R Us well the toys are us, right? So they have loads of toys and everything but now they're in debt some serious crisis really serious crisis Took a private equity firm and they didn't do their debt calculations properly And they cannot pay off their debts and so they're bankrupt So that's what happens when you play with debts and you don't really play with debt very well I mean you you you don't really do your detailed analysis their models. Maybe we're too optimistic they went to realistic And your company goes under so debt is dangerous. That can be very dangerous. You need to be careful and So what's a debt schedule? The debt schedule lays out all the of the debts a business has and How do you build a debt schedule? Really a debt schedule is very easy There's something if you've done our modeling courses we say base analysis Base analysis means there's b. There's a there's s and that's e So obviously b is beginning balance and the beginning balance of any schedule or schedule is always equal to the previous Ending balance. Can someone type what's a please in the chat? What exactly is a what's a what does a mean in base? Can you type in the chat? So what does a mean in base? Nobody's typing chat chat type in the chat. What's a what's the meaning of a for base? Joel Joel said addition. So Joel is completely right. A means addition Yes, exactly. A is addition or new issue. So for debt Addition is being okay. We've got new debt Yeah, we've got new debt and If a is new debt, what is s s is subtraction Subtraction from your schedule for debt is a repayment Now you have to understand that debt you have a principal portion is what we're looking at right now the principal portion So you're getting the new debt in and then you're repaying the debt obviously And then e is ending balance. And so this is your typical formula for base. You have beginning balance addition subtraction and Ending balance. So that's how debt works Right and the ending balance is beginning plus addition minus subtraction one best practice in modeling, right? Always use positive numbers in the whole model. Don't use negative numbers Always use positive numbers because if you say beginning balance, whatever may obviously going to be positive Addition is positive and you make subtraction negative It will cause problems down the line when you're doing some calculations and especially cash flow So the only places you have negative really are cash flow statements You have negatives in your cash flow statement, which is cash out And positive means cash in and then sometimes you make a loss in your p&l Your net profit is a net loss. Of course, it will be negative So but most places will be negative and sometimes you do a schedule or a schedule in your calculation sheet That is not a base schedule is more like a bce schedule Which is more like a beginning a change and an ending So that change can be a positive change or a negative change All right for those that have done our modeling courses, you know what i'm talking about Types of debt what are the kinds of debt we have? Well, the first type of debt is bullet So bullet debt Now obviously bullet debt it seems like you You enjoy for a while you get there alone. You are so happy Walking with alone paying your interest until the time it comes for you to repay And you need to repay the whole thing in a one lump sum. Can someone tell me on the chat What do you think is the most common way to repay debt? Just tell me where does the company get that money to do a bullet repayment one big repayment at the end Can someone type in the chat? Where do you think that money? Where does a debt repayment fund come from? Abbey has got it right. Abbey says more debt 100 percent If you look at the financials of a company in fact, i'm going to pull up Maybe financial we like to use cadbury. So i'll probably pull up cadbury and show you Most debt in a company is repaid by debt Can someone tell me why there's a there's a good reason why can someone tell me why You are paying off your debt with another debt. Can someone tell me why? There's a good reason for it actually Okay, tell me the best reason why You see some people want to get a gift No, just just out of the love of your heart for teaching people. Yeah Okay, tell me why would you repay debt with debt? Yes, it's called refinancing Chairman is here. Yeah, it's actually called refinancing. But why would you do that? Why why wouldn't you just why why would you use debt to pay debt? Okay, lower rate the manual is very right. Manus says there's a lower if you can get a lower rate Excellent, you should refinance your debt immediately if you can get a lower rate But then you also should check covenants There's some covenants that says if you refinance this debt before the term is over You're going to pay 2 percent penalty. So you have to factor that into your calculation That's why models are good to roll over your debt. Yes. So roll over debt is cheaper. Well, that's yes That is technically cheaper than equity. Although in Nigeria, that is pretty expensive. Yeah, but Yeah, so that is cheaper So anytime you get cheaper debt is good to refinance just refinance and pay off that debt and get another debt But let's assume everything is fine. Everything is okay. The one of the major reasons Don't want to hold down cash. Yeah, that's cool. If you don't want to hold down cash You get that but you get debt if it's cheaper, I guess But then also there's the debt to equity ratio, right? So debt to equity now the company and the modelers in the company would have done a calculation to find the Sweet spot that every company has like a sweet spot. And when I say a sweet spot, that's the spot Spot at which their debt to equity ratio is such in the right place that it gives them the cheapest cost of capital That's the most important thing. You need to get the cheapest cost of capital Let me just write that down. So cheapest cost of capital. What do I mean by that? If you when you build your model What you should do is do a sensitivity analysis of the debt to equity mix It's a very nice cool thing you could do Do a sensitivity analysis of the debt to equity mix and how you do that is this when you're When you of course you are refinancing your debt You will probably refinance it with a portion of equity or debt until your debt to equity mix is your debt to equity mix will Let's say 60 equity 40 percent debt you check how well you're doing profit. How is your profitability like 70 percent equity 30 percent debt was a profitability like 40 percent equity 60 percent debt was a profitability like So if you do a sensitivity, you would see which spot gives you the highest return Highest return on your capital that but not with the highest risk So your model is what will answer that question So your cheapest cost of capital would depend on that debt to equity mix and to maintain that debt to equity mix If you pay off a debt let's say a big debt like a billionaire And that's like 50 percent of your debt if you pay that off with equity That means you're increasing the equity proportion in your company which could be very expensive So you usually pay off debt with debt and that's one of the reasons to maintain that your optimal Capital structure, which is your debt to equity mix. So apart from bullet debt There's also amortized debt or amortizing debt now in Nigeria. We like this debt a lot This is what you usually get from your bank. So your bank says, hey, I'm going to give you a hundred thousand naira or a hundred thousand dollars or whatever And you're going to pay me back a certain payment of let's say one thousand dollars every single month In this case, we have five hundred and thirty six point eight two dollars every month and that Amount paid has a proportion of it as principal and a proportion of it as interest So there's a way you calculate that and then you keep paying the same amount every single month until the end of the debt And by the time you pay your last payment Maybe in five years time That proportion that same payment has a proportion that's principal and a proportion that's interest And typically your principal portion keeps increasing and your interest portion keeps reducing So that's amortized debt Yeah Now amortized debt is very kind of good for let's say private equity engagement where you're trying to sculpt your debt You're trying to make sure that the debt is not to you know Give you spikes and troughs in your in your repayments You don't want to have to go and start looking for one two billion to pay off a debt that's going to mature in two years So It depends which one you want you want a very smooth Capital structure where you have a nice debt profile Then maybe you need amortized debt bullet debt is also pretty good because you know you're going to refinance So obviously the repayment of it has been taken care of by refinancing So all these things you need to think about when you're building a model We're going to do a demo bullet debt, but there's also hybrid There's the hybrid debt. What is hybrid? Please can someone tell me can type in the chat. What exactly is hybrid hybrid? Quickly don't you give me an idea type in the chat. What do you think hybrid is what's hybrid debt? Why is something hybrid sure people are typing so it's probably coming in so something like preferred shares preferred shares is You're saying that this thing whatever this financing thing is has some characteristics of equity and some characteristics of debt Yes, say are they doing yes combination of two things? Yes, that's hybrid. I agree But so for debt when you have some something that has some characteristics of debt and characteristics of equity You call it hybrid. Some people call it mezanine So you have mezanine financing where someone says, okay, do you know what I'm going to restructure my debt I'm going to give you a loan of one million era And I expect five percent interest every year and in two years time. I'm going to convert that loan to equity I'm going to convert that one million era to equity. So it's convertible So it's like has some equity portion and some debt portion same thing with preferred shares or preference shares you have You're saying you're going to be getting a certain amount every year a small amount But you also share in dividends. So you're getting like the best of both worlds kind of So that's a hybrid So components of debt what are the components of that? Well, you need a maturity date So these are the things you need to be able to calculate that you need a maturity date for sure You also need issue date. When did it issue this debt? Yeah, you need interest rates You need interest type Is it a floating rate that is it a fixed rate that especially for bullet blood that fixed rate of floating You also need the loan amount So how much exactly was the the debt? Yeah, obviously that's obvious. Yeah Then importance of debt in a financial model. Why is it so important? Now debt is very very important because it's a big portion of what you use to finance the business, right? So there are three key financial statements. You have your p&l your balance sheet on your cash flow And that affects all of them. That's you see that's footprints everywhere So that's footprint in your p&l is in the interest expense portion of your p&l Of course the interest expense portion has debt Yeah, so that's that footprint now because it's it's below. I mean it's above net Taxes it it kind of has an effect on your taxes. So your effective Your effective debt cost is actually less but it reduced by the what it does in the form of taxes because It gives you a tax credit so to say Okay, some of your answers are just coming in late Okay, another part is debt also touches the balance sheet with your long-term debt in the schedule Although for IFRS guys, you also have the short-term portion of long-term debt When I build models, I just put everything in long-term debt I don't break it down into two unless of course I need to make it IFRS compliant Well, that's fine. It's easy to do if you want to split So obviously the balance is the liability side of the balance sheet has long-term debt And then your cash flow statements that affects two places You know your net income comes in your cash flow debt has already come out of that And then of course you are repaying and getting new debt and that comes from cash flow from financing So you have cash flow from operations which already has interest expense in there After already there before net income and you have cash flow from financing, which is your long-term debt and your New debt or repayment of debt So we've come to this stage of the seven steps to automate the debt calculations Now we are going to go through a demo to do this And to do this demo, let me see let me just show you Let me share my screen So seven steps now we're going to use bullet debt. So we're going to do a demo bullet debt And there's amortizing debt. So maybe in another webinar, we're going to talk about amortizing debt But mainly bullet debt is what I will talk about now. And how do you automate it? How do you build a schedule because sometimes you have a big company or modeling And you have like 20 debt schedules 20 how okay, let me ask you how do you currently even model debt? Yeah, let me let me just ask you that. How do you currently model debt? Let me Put up a poll So can you guys answer this question? How do you currently model debt to use separate your Manual manually enter debt schedules for each debt That means you type it manually all the debt or do you have separate debt schedules with dynamic links to an input cell Or do you do the full set of flags masks references and everything do you do that? Which one do you do? So can you answer the Small poll I've put up before we continue How do you currently? Measure or calculate debt in your models. All right, so most of us We have separate debt schedules with dynamic links to an input cell. Okay now um Dynamic links for most people really is every single debt schedule is on its own What I see most times every single debt schedule is on its own So you do this debt calculation you want to replicate it for another debt you copy paste and you now have to manually start changing The links the formulas are not correct. You need to change the reference blah, blah, blah So if you had like 20 debt schedules, you need to now almost physically calculate 20 separate debt schedules And then sum it up quite tedious So thanks for the poll i'm going to end the poll and let's um, let's get back to our slides So i'm sure you can see the slides. We have our first step. Our first step is set up your debt structure Template that's the first step set up your debt structure template. So if you look at my um Screen right now look at the screen right now. I'm sharing a template So usually we use this in our financial modeling training if you've done our five day financial modeling school How many of you have done that training? Yes. No, can we type? How many of you have done the financial modeling school or the advanced financial model course? Can you type into the chat? Which one you've done with us? Advanced financial model or don't financial modeling school. Okay, some of you have done abby has done abib. No, no ester. No Okay, non. She she may say okay. So I have a special offer for you at the end So let's see this typically how our school goes. This is I think they I'm correct. I think this day four of the school. So day four of the school. What happens is you um We download the full historical financial statements of the company. Unfortunately, I couldn't show you that Yeah Oh a lot of the years you've done this in abby inquest. Excellent. So We typically download the full financials and You now see once you show As typical of a model we say we should have your inputs your calculations and your outputs So these are our input sheets. This is an extract from the inputs Now if you go to any company's financial, especially large multinationals, they have Financials for they have the detailed debt schedule. So this is just a fictitious example I'm pulling out eight debt schedules or eight debt schedules. So if you want to call it And here we have your name of your debt. So this could be called oh libo with something something uh floating rate debts from Fidelity bank or something. I'm just giving an example, right? So different banks You have these are all the different banks are giving us debt, right? So issue year for this one is blank, which means what's issued on it Maybe let's just assume it was issued. Let's just say all our debt was issued in 2000 or 1995 Something like that. I'm just I'm just writing anything, right? So that was issued in 1995 Oh, wait a minute. I'm going to unshare this because I didn't share it properly. So I'm going to unshare this screen And then I'll share it again I'm supposed to share it to enable you see everything I'm doing So I'll share it again properly, right? So now you should be able to see schedules. You didn't see a schedule I was trying to do Yeah, now you can see it excellent. So I'm just making this a general format Now the skill sets you need when it comes to modeling One of the one very important one is referencing you to understand referencing So this is my data now the first step we saw who can remind me What was the first step for for building or automating debt? What was the first step can type into the chart if your memory is so sharp? What was step one? Who can remember I had a slide telling you step one. What's that one? Oh people can remember All right, so let's show you So this was step one Set up debt structure That's step one set up debt structure. So we're going to set up that structure template Yeah, and so set up debt structure template. So that's what we're going to do first So in doing that, let me quickly Do that So set up debt structure template I'm just showing you the inputs. These are all the inputs So I'm just going to calculation sheet and as you said if you remember debt structure template is simply beginning So that's beginning balance Let me just type that there and Beginning balance addition, which is a new debt And subtraction, which is debt repayment And then ending balance And then maybe somewhere here we have our interest expense Excellent. So if I come here right now, you see that these years are my historical years So typically in a model your last historical year, which is this one Your last historical year is good idea to put a link style That's my link style. So this is a link cell style. This link cell style. We mean it's going to be linked to my It's going to be linked to wherever the historical balance was So I now do an equals to this which is my Beginning balance is always equal to ending balance And then my ending balance is beginning balance plus addition minus subtraction And there we go. And then I can always like formatting as I build my model. So I'm going to put this double end line there, right? And I can highlight this And go to the right and do my contour, right? excellent so that's That's just typical So I I think I want to minimize all this stuff. I don't need all this space for now or let's leave it for now So anyway, so this is my financial calculations. Let's call this our debt summary So I'm going to call this debt summary And I'm going to make this Maybe subtitle So this is my debt summary, but I now need to calculate give a summary for all I create another schedule for the other debt for the real debt calculations individual debt calculations, right? This maybe we'll call this debt one for now, right? So this will be that one new issue and stuff So this is my step one done. I'm done with step one, right? Not bad. So what is step two? Any ideas what is step two? So if that's step one step two is Bring in that inputs. So we already showed you step two I showed you I jumped the step and you go bring in your debt input So I showed you what those debt inputs are we brought it in we've typed it into a table I would put it in a table form that's step two step three is Plug historical debt balance. Hmm. What's that? So one thing you have to understand is this when you're building your debt schedule and you have to build a very gigantic debt schedule for lots of debts Think about automation every time you build try and tell you tell yourself this Do something twice. I will never do something twice Try not to do anything twice and what I mean by this is we have about eight. Let's see how many debts we have we have We have about eight. So whatever it is you're doing you should work automatically for 20 debt 30 debt 40 debt You should work the same. So whatever we do for debt one, we should be able to just replicate it for the rest So bringing in this input this historical. Let's check that one. Where's that one? This is debt one debt ones um historical balance is obviously this one million and what about that uh The debt six now debt six Doesn't have a historical balance because this is new debt So debt six is new debt because see the debt came in in 2018 and if you look our forecast is starting at Okay, and again, I shared the screen wrongly. I keep doing that. Sorry about that. So let's uh, let me share the screen properly again okay So I'm sharing the screen the correct way No, all right. Super. So debts one debt two debt three debt four. We need to automate the debt. So how do we First do the first step which is plug your historical see this historical here What I need to do is this right type equals to go to the input sheet and select that one. Is it it and we lock f4 yes Can someone tell me if that's correct? I just did the manual link Correct or not correct so much to type in the chat Please I need your help Who thinks that's correct? See what I did If you want to see the formula for what I did, let me just do formula text. There's a function called formula texts And you see that I just did a manual link. This is Not the way to go absolutely not the way to go. So what I want to be able to do is Just say that too and it automatically changes So this won't work what we first need to do before you bring any calculations any links is you need to bring in your inputs When you do the best practices when you're building a schedule All the inputs that you need for that schedule should be brought into the Schedules you brought into the calculation sheet. So we're going to go to our input And all these guys here we need to kind of automate the way of bringing them in here Right. So I will need something like debt one or something debt and then I will say that debt one Let me make this bigger once I say that one this would change to the debt ones input that two that three something like that So what let's bring in the inputs To do that first thing is highlight this table and give it a name So I'm going to give this a name. I'm going to call it D So I've called it D. So if you check this drop down, I've already named it D I've named this D and I've named the headings h See, I named the headings h. So if you guys do our final excel trainings, you know, there's a methodology we call D k h methodology for extracting anything from anywhere D k h being data K being key h been headings. So here I'm going to use V look up. So I just need D and k I mean D and h. I don't need k. So Once we do that I come in here and I also need the headings. So I'm just going to take these headings like this Copy them and bring them into this calculation sheet here. I'm going to do paste special a special Maybe say values and I transpose So values and transpose I'm bringing them here. These are the headings Now there's something going to be wrong with these headings. I'll tell you very soon Now the serial number what I want is debt ones I'm just going to type one in here this debt one and I need to do a V look up So I need to do a V look up. I'm looking up this debt one Now every time you type a formula, you have to ask yourself what kind of reference should I use is a relative reference? Well, let's use a relative reference for now. So I'm saying hey look for debt one V. Look up this One in the table called D Okay Now best practice for me is usually you bring in this dialogue box. This dialogue box makes things easier So what am I looking for? I'm looking for this value I'm looking for it in table array and I press my f3 key and it shows me the names of the various tables. I select D I say, okay Then column index norm. What's my column index norm? my column index norm is I don't know what column do I need for name name of debt is in column two column three column four That's where you put a function you put a match function to do the automatic thing for your type match and open And close your bracket if click inside your formula bar Now for this technique, I'm going to go fast here if you want to see how this technique works Go to our youtube channel Go to our youtube go to youtube type d brown consulting and then search for V. Look up You see exactly what i'm doing here in detail And they will go very slow and show you how it's done. So here. I'm saying hey I want to match This name of debt. I want to match it in this array This array, you know the array is called h. I called it h You know the headings. I called it h and I want you to give me an exact match You type a zero So this match is going to give me an answer Can you see two here and that answer I go into my formula bar and select be look up It sends that answer to be look up and I tell hey look up I want an exact match which is zero or false I enter and it gets what I want But then look at what happened if I drag this down it's not going to work because This value here is not looking at one anymore because I didn't lock one So this one I'm supposed to lock it right so I do f4 And enter this reference to one then I can double click But then there's still another problem because Here let me just make everything general Right. There's still one more problem. Who can identify that problem? So The thing is whatever you build in this schedule, you need to be able to replicate this schedule at the end So I need to be able to automatically do that's two that three Dead four dead five like that and if I lock something Permanently for example this if I copy this down It's not going to work Because really Okay, well, I didn't copy the names. Let me copy the names. I copy the names down as well It's not going to work for me in the sense that if I come here and change this to two I expect to see that's two if I change it to five I expect to see that five is not working because I made this permanent lock Which is not right So but I had to make it permanent lock because it didn't work anyway all confusing So key thing in in and modeling right one of the very important things is referencing So if I click on this cell, this is called a relative reference f 23 if I press f4, this is called an absolute reference if I press f4 This is now locking only 23. This is a row constant. My row is constant If I press f4 my column is now constant So to make this work Yes, it had to be fixed for temporarily, but I need to change Each and everyone from being a permanent lock like this so a Instead of an absolute reference to a relative reference I need to do that almost one at a time But there's a faster way to change things What I can do is see what I'm doing. I'm changing dollar f dollar 17 to f 17 To do that quickly All I do is highlight the data and do control h control h is find and replace and I'm saying hey find dollar F dollar Oh, who can remember where it was 17? I think I want you to replace dollar f dollar 17 with just f 17 and then click replace all and it says is replaced for and now everything here is relative referencing, right So this works. So I changed it to everything seems to work. Excellent now to plug This we need to now plug Think about it this historical value here The only time you should have a historical value here is when the debt was issued a long time ago So if you look at this my issue year is 1995 Definitely this Debt of one million should be here because it's a bullet payment debt, right? But if I had debts, maybe let's say the debt eight That eight was issued in 2020 That debt should not come into your historical The historical line you shouldn't come in here That debt should come in up here in additions for 2020 you should have your four million here right But we we come to it we come to that uh four million not four hundred thousand So to do that you need what we call like a flag you need something to mask to check How do you check that we're only time you're going to have something there is when This financial year you have here Is either less than or equal to this issue year If that is less than or equal to your issue year Then we can get then that was so true or false. So here it shows true Oh, I've done the opposite. It's supposed to be the opposite not that So the only time you would have a historical debt is when this value is actually less than or equal to this So what is the opposite of less than or equal to is actually greater than so Opposite of less than or equal to is what I actually wanted to say is actually greater than or equal to not Not less than less than so if I say greater than or equal to is false I mean this debt eight is not historical But if I say that one That one is true That because look at it 1995 is definitely before 2017 Let me just out of interest if I type this 2017 very bad practice. Don't do that. It's still true right undo So when you get your mask the logic is this logic you multiply This logic by the money. So this is giving you a true or false. This is a logical formula Now logical formulas give you a true or false answer Then you multiply it by this and for anything you're going to link here because this is your data coming from your inputs And all this data is in column f We're going to lock column f although it will make a difference here But a good practice is we're locking column f. So this is a column constant So once this comes in you see the money comes in fine. Let me edit the format of this Right. So I will need to do a little bit. I need to rush because we only have about 10 minutes left So I'm going to go extra speed right now. So One million if I change this to eight, is it going to come in? Absolutely not It shouldn't come in because this is not new debt Yeah, this is not new debt. So it shouldn't come in What is our next step? So what I'm going to do guys? I'm going to go through all the steps I'll rush through all the I'm going to show you the steps in the slides And then I will now come and implement all of them. So I stop going back and forth Okay, so let me show you all the seven steps to take note of all the seven steps And then I'm going to do all of them in the I'm going to do all of them. So remember your step three was plug historical debt balance. Let me remind you step one was Set up debt structure template. We're doing that step two was Bring in debt inputs step three is Plug historical debt balance. We just did that step four is dynamic plug for new issue Repayments dynamic plug for new issue and repayment Once we do that step five will be build a dynamic interest rate selector Build a dynamic interest rate selector. Then step six will be interest rate expense calculation And I will leave step step. Well, I just have okay step seven is the magic step seven is replication and death summary magic We'll see how that works So currently guys, we are in step four So this we're going to dynamically plug put a dynamic plug for new new issue and repayment Let's see how that works. Let's quickly get to back to our slides Share screen Let's get back to um Yeah, so let's get back to excel. Okay. So now back in excel We plugged in our historical which is nice. That's good Now we need to do all the schedules all the different schedules and stuff. So Um, I need to come in maybe I'll come here and type We need to know what is our our new day when is this debt coming in For you to know when the debt is coming in you need to calculate something called an issue mask And to calculate when the debt leaves you also need a repayment mask You see what those are Many people like to use nested ifs in the model to me nested ifs is a sin Don't use nested ifs. Nobody could be able to audit your model properly. Your model is not going to be experienced and one of those are one of the best practices that um align this right so I don't need to Drag want to align right there we go. So what do I mean by issue mask? Issue mask means hey did I issue this debt in this year this year up here this 2018 I'm going to lock the row because I'm selecting this 2018. I'm locking the row. I'm saying did I issue this debt in this year If I check The answer should be no. I'm clicking on column f when I click on column f. It must be column lock So this is where the trick is this is a roll lock and this is a column lock Now this mask says hey false. You didn't issue the debt in 2018 So what if I copy right you see yes, I issue the debt in 2020. So if I change this debt to debt, um, let me see that six That six was issued when 2018 and you can see the true has come in here Perfect. So let's stick to debt one. What about repayment? When are we repaying this debt? I do equals to This year up here. Hey, did I repay this debt in 2018? That's the question I'm locking the row And I take equals to is the logic. So when you're doing logical calculation, it is something logic something That's how it sounds something logic Something when I say something the something can be a cell. It can be a name a reference to a name It can be even a figure you type 20. Are you equal to 30? Answer will always be false But that middle must be the logic and there are seven or should I say six logical calculations or logical operators In excel or in life in general, but let me now go there. Let's just say hey this year Are you equal to the repayment year which I need to lock the column? phase No Copy to the right now if you look at it the repayment was 2019 So when I drag to the right this 2019 should show me a true here. Let's see if it does Yeah, it does. So you can see that my issue here is all false because this debt was issued a long time ago My repayment year is here and it's transparent when I change my debt to debt for You can see that the true is still 2018 is we're paid in 2018 that's six Everything seems to work now that's six. There was no money in why? Let's leave our debts is because there's no historical So the debt issue the issue you now come and plug this in so if you look at where I am here This is your new issue line So your new issue line should now be equal to what are we issuing? This is the debt we're issuing this amount down here And as I said anytime you click something here you lock the column So we're locking this column and say hey this debt here. Let's multiply you Let's multiply you by the mask And that's the technique This debt minus times the mask and true in excel is one and false in excel is zero So what will happen is the money will come in Then you drag that right Now if I change this to let's say debt one obviously there's no debt issues it won't come in I change to debt seven you can see it came in in 2019 Change to debt eight is working perfectly. So let's leave it at that six for now Now what about repayment same logic equals to the amount of debt f4 f4 lock the column And you multiply that by your repayment Flag or mask And you enter and that's a relative reference you drag that to the right to the control r And it got in that year and came out that year. What kind of debt is this highly unfortunate debt? So let's go to check the inputs for example, you see the debt here It came in in 2018 and went out in 2018. Let's check it. Let's change it to going out in 2020 Because by changing the inputs and I go back to my calculations, you see that it's now leaving in 2020 Came in and that's it. So that was that step the next step We need to do is do that logic for interest calculation. So we need to understand your interest Interest type interest type What interest type is it? Is it a floating rate debt or a fixed rate debt? Now in floating rate debt if you have floating debts, that means your interest rate You must add a reference rate to your interest rate That's what floating rate means. So this floating rate this spread of 8.5 percent. You have to say 8.5 percent plus libel So let's let's use nibel also use nibel bank offer rate. So the Interest rate for this debt one is not seven percent. It's seven percent plus one percent Right and how do we do that in the model? You first of all have floating. Let me just say are you floating rate Are you floating rates? It's a question So I want to say true or false. Let's check equals to this cell f4 f4 Are you equal to I'm going to break a rule here. I'm going to type Just for speed sake. Are you equal to floating? So if I float So yes, it says true step. That's true Then if it's floating, let's bring in a libel or nibel. Let's just bring in nibel So now let's bring in the nibel rate. So nibel rate. Let's bring it Equals to let's go to nibel This is nibel up here now use relative reference absolute or what type in the chat What should I use relative reference absolute reference row constant column constant? What? I think I yeah Okay chat is not talking So what do you need to do is if you look at the formula bar here You need to use a row constant because all this I boys in one row Then different columns for one row. So you need to lock the row and this is your row constant I copy to the right. I do my shortcut for percentage, which is contra-shift five or contra yeah, and then How that I have those two the actual interest Interest rate on that is equal to This interest rate up here Which I need to kind of lock the column And then what I do you now add that interest rate so This nibel rate only when the debt is floating and how you do that is you multiply that this rate by the true See the logic your rate, which you don't know whether it's a rate or a spread Plus your spread, which is nibel multiplied by the fact of whether or not that debt is floating Then you enter and you drag to the right contra r I do my percentage style and this is my interest rate because that's my interest rate now. I can calculate Expense, which is the next step Interest expense and interest expense typically is going to be an average Of your balances your beginning and ending balance You close your bracket. You multiply that average by your rate That's how you calculate interest expense. I do a formula there and then you drag to the right So that's interest expense and really that's all that is for debt Now that is your debt schedule but the beauty of doing this technique to the what we call the magic of automation So I'm going to link this heading to this debt here And then let us all start with debt one Right, so I'm going to start with debt one And it's starting with debt one We can see that let me just minimize that so you see what's happening So you've built a debt schedule using best practices and the best practice mainly is those references It seems simple when you're just watching it on the screen, right? But it's not that easy. You need to understand referencing In detail once you do if you make one slight mistake in your reference It's going to mess up. So let's say with everything worked perfectly you come here to the edge You highlight all the way to the end and make sure you have an empty Row at the bottom just an empty row So you're highlighting debt one And you come to the edge here and you just replicate we have eight debts So I'm just going to replicate a lot just fly down all the way down. I don't know how far So you see some refs. Don't worry about the refs. Don't mean anything They just mean there's no debt there. So let's go to the beginning and see what we have So you can see that your debt one has created debt two at the bottom But look at debt two. What's happening with that two is floating is a 1995 debt and look at this So look at this formula. This formula says hey debt Um you amount k 17. Why is k 17? It's checking k 17. Are you equal to f 34? That's what this is checking k 17. I equal to f 34. Now. What is k 17? k 17 If you scroll up if I scroll up you see what k 17 is Uh-oh and scroll up scroll up scroll up Scroll up It's Stock Let me escape. Let me just go up. So you see what k 17 is k 17 doesn't make sense This is k 17. That's because the formula was wrongly entered in the beginning. So look at this k two k two is Up here. We need to lock. What we need to lock the row k 19 which is here We need to lock the column And then f we lock the column and now your formula is now correct for replication So you see when you make a mistake like that everything messes up So we corrected that mistake you can come back here and replicate again I can't replicate. I'm just replicating a lot. You know, I don't need up to that But by replicating all of that, let's come back up and see You come back up. You see that one has worked Which is fine. Look at that two. So that two look at how that two has worked that two has also worked Yeah Although we started with that Two, okay. We said that two three has worked See that three the loan quickly on 11 2022. It did that fine. That four is working fine as well Dead five it came in in 1995. It's going out in 2020. It's going out in 2020. Yeah Dead six is going out 2020 as well. It's working fine But this is in in 2018 And went out in 2020 and the interest rates and everything works and this is a floating rate deaths. Yeah Dead seven works fine. The eight works fine We don't have dead nine. So that's why you have these errors. We don't have dead nine So I'm going to delete all of this. I'm just going to wipe them out So you see how all your debts working perfectly now if anybody increases your debts Maybe tomorrow they come here and add new debts. Yeah, let me just let me do that So you add new debts. So I just come here and add new debt to my model. Yeah So I just add new debt So now I have debt up to debt Let's see Up to Let's just fill the series to this double click So I have 13 up to the 13 Right, and let me just change this this 2019 2020. Let's even say they're all the same. That's fine How do I bring this in? Let me just put one million one two three. There we go 100,001 million Okay We'll just leave it there So I brought in all this debt. If I come to my calculation sheet, what do I need to do to replicate it? What I advise is just go back to your debt Just go to your debt one Highlight your debt one again Drag it all sparse. Let's just go all the way down down down. I hope there's enough space. Let's see Where did we stop? So we have the 13 so we're going to look for a big replicate that up to the 13 Let's look up number one two that three that four seven Is that seven? I didn't Okay, we called it all debt seven so let's So what it is the name here if you come to calculations you see it's that 10 That's 11 That's 12 that they're Delete from all the way down. Now once we do that A debt summary schedule this new new be equal to this new debt debt one plus debt two's new debts Plus that these new debts all the way down But I'm a bit lazy. So I'm just going to try and Filter this color this column New the new debts in one place you debt Just say new debt. So now I see all the new debts in one place Then I can easily do this now some people use we look up for this or some ifs I kind of advise you should do the old-fashioned Linking like this. So all I'm doing is saying plus plus plus plus do you get it? I think it's on you see why very soon it kind of does help you to Easily replicate and then for audit purposes it helps people to audit Because once they're auditing they can see that something was wrong They don't need to audit one complex some if or the audit one complex we look up Well, we look up and even work is more like some if for some ifs I just did all the plus plus plus plus And then I can clear my filter Once the claim I fill this formula if I show you the formula formula text This is the formula. You can see the formula on the screen 1833 4863 sound is 93108 123138 153 6883 98 So because this is relative referencing all I need to do is copy this Highlight it like this Enter Then copy one of them come and put it in the historical Enter and everything just works and then in fact interest expense But if I copy it like this and take it to interest expense down here It will and that's because when I do F2 it's not picking interest interest expense is no more down here. It is somewhere Here how many steps further down? one Five six so that means seven one two three four five six Seven right so if I insert seven hopefully the relative size the relative space between Where interest is I think it's a bit too far, but let's see enter now. Let's check. What is this now? Is this now where interest is scroll? Yes, it is now once you've done that link this excess Uh space you don't need it again It's it's just a trick you can have interest expense And this is how you calculate a debt schedule from beginning To how you the process it's a very very cool technique And for this we're going to have this webinar is going to be a channel. So make sure you join our youtube channel And I have some goodies for your screen That you can use so let me so what we've just done here is we've done the seven steps for automating any debt report We did the interest rate expense calculation and then we did this replication magic you just saw So, how was that? Did you get that anybody in the comments? You got that. I know we've gone a bit over we've gone over over so the next three minutes will be done So what what that debt calculation just shows in a model you don't need to do things twice You don't really need to do things twice Now, uh, if you check your download i've given you guys a download a download to a brochure For our advanced financial modular course. So the exams are coming up in october for those that are not doing the exams Next week And what we've done is we've left the offer that we offered other people that we're going to pay for your exam Because we really want you to pass this so we're going to pay for your exam So i'm giving you This file so anyone can download it as a brochure for advanced financial modular certificate course So click on the download file link and you'll be able to download the brochure So just click on it So once you click on that we download the brochure Then what I would give you as well is just give you an offer to register for the financial modular course So you can register for the financial modular course. I'll leave that offer for you It's on your screen now. So once you register for the course for october You'll be able to um, we'll prepare you solidly for the exams And for those that are doing the exams in april as next week You we have a one day course is one day free free for all those that have attended our courses We're going to give you a free one day mock exam. That's this saturday You do a mock exam will you'll mark your mock and then we go through Answering the mock exam the way it should be answered in the exam So you you you come in and you do a three hour three and a half hour mock exam Because the exams themselves are four hours You do three and a half hour mock exam And then we go through the mock together and see where you made your mistakes And then hopefully next week you'll not make any mistakes and get a hundred percent You don't need a hundred percent to get a certificate, but it'll be cool All right. Any questions guys anybody have any questions? Any questions no questions. Did you like it? Okay, some people liked it. That's nice So how many of you really know about this? What's it called financial modeling institute? I'll put up a poll. How many of you know about this institute? Only a few people are answering how many How many how many know about it? How many of you know financial modeling institute can be all of you? I'm not okay. Well, maybe because I said so right in this in this webinar And please how many of you have joined our meet-up group because some of you here are not in our meet-up group I know that some of you are in our excel meet-up group, but we have a modeling meet-up group We have a financial modeling meet-up group. Should I share a link for you to join right now? There's a financial modeling meet-up group In fact, let me share A link for you to join the modeling meet-up. Let's try and answer how many of you know fmi Okay, see the numbers are reducing as people are answering. There are no fmi Yeah There's one very short video about debt. I think it's quite Interesting, it's by investopedia and it's about debt and one very important ratio in debt It's called a debt service coverage ratio Yeah, I know we've gone over time. Would you like to just it's only like two minutes Would you guys still want to watch that video right now about debt service coverage ratio? Just say yes or no and then we know if you're interested in watching that Okay One two people. Yes, nobody's saying no. Okay. So let me show you that video very quickly Watch this short video about debt service coverage ratio. Just understand What debt is The debt service coverage ratio Is a simple way to analyze whether a company can adequately manage its borrowing costs The ratio helps banks evaluate the credit worthiness of an organization that is applying for a loan It also tips off investors to companies carrying a debt level that could be destructive To find the ratio start with the company's net operating income Then divide this figure by its total debt service Which includes principal and interest payments as well as any contributions to a sinking fund created to retire Bonds ahead of schedule. The dsc are measures the cash flow available to service accompanies debt A number above one indicates a positive cash flow Abner's art supplies has a net operating income of $500,000 per year. However, it owes $450,000 in principal from existing loans and another $100,000 in interest Abner's art supplies has a debt service coverage ratio of $500,000 divided by $450,000 plus Equals 0.91 the retailer is actually taking in less money than it pays out to its creditors Unless it can turn these numbers around getting approved for new loans will likely be difficult One of the dsc are's weaknesses is that net income can fluctuate from one year to another However, its advantage over other ratios is that it looks at all the company's debt related payments And therefore provides a more comprehensive gauge of its borrowing all right, so guys that was um debt service coverage ratio pretty cool and Oh, we have to do in next week's exams. Yes, of course excellent Uh, are you coming? Um, so that's chimese chimese What you do is you call 0 700 training and see if you can come for saturday's Practice so saturday's mock practice so that I can get even better ready for the exams And by the way, the exams are venue for the exams are our offices. So we um kind of Instrumentally getting fmi to come to nigeria. And so we have also given our office as the exam center So next week your exam is going to host or we're hosting you guys And the exams are going to run in our offices in our moley phase one 70 a dm or capo That's where the exams are going to hold Right, so let me quickly finalize for you guys. I think we're almost done Just a one minute left and There we are. So your exams are going to happen next week And your mock is we're happy to host you for the mock So register for the september afm. I've sent you the brochure. I've given you the promo right now I have that offer still open for you You should click on the register for the course link so that you can register for the september course This is a Five day course plus a two week online plus the exams the exams are worth 175,000 naira That's the exams our course is typically normally 400,000 naira But if you pay that now you're going to get the exam free So you pay for your registration for the exam and you also get the online course as in there's a very detailed online Two week pre-course so that two week pre-course is on our office training hub And you can see it right here I'll just show you the pre-work the pre-work is very jam packed It's jam packed with all sorts of stuff and you can even buy the pre for 200 dollars alone Just the pre-work so it teaches you how to build a modeling tem How to work with math and rounding functions a lot of interesting things you've never heard about About math and rounding functions. It teaches you working text working with text the dates and time mastering lookup functions And performing scenario and sensitivity analysis Capex and depreciation calculations Time value of money There's an integrity and error checking link to somewhere else You do that and then there's also a link to the using logical formulas and functions in excel But we're going to load that very soon and then we have some bonus for you So it will take you two weeks to go through this pre-work And then you when you now come for the The five day full modeling Thank you everybody and I hope you enjoyed our webinars. This is dbran consulting and everybody else that also in dbran consulting brought this together I'm david and I'll see you guys on saturday coming and good luck to those doing the exams next week And please join our meetup group. Please. Please. Please join our meetup group financial modeling meetup groups share the link with your friends And share all the link to our meetup group with your friends In fact, let me just put up that meetup group right now for you to Join and I'll leave you with that meetup group link so that you could join the group And thank you very much guys and see you next month This webinar has happened every month third Thursday of the month from 11 o'clock. See you next month guys. Thank you very much Bye. Bye