Transcripts
1. Learn Financial Modeling in Excel: Hi, this is Ryan Hall and we are going to be discussing all about financial modelling. So the goal of this one is for you to create your own financial models from scratch. And we pretty much have three main modules over here. So the first module is the financial modelling curie. So we are going to be focusing on the theoretical side. So think of this as the foundation and we will cover the basics for you to understand what financial modeling is all about. Okay? So pretty much this is the main goal that we are going to be doing, right? But before we jump on to the fun part of implementing this one, right, we need to understand on what the best practices, what are the design principles when it comes to working with your model? Okay, So the next module, the second one, is all about XL core concepts. So we are going to be going through XL concepts that are crucial to financial modeling or what we are going to be using for most of the time. So if you're new to Excel them, this is perfect for you so that you can have the Excel building blocks so that you can learn it together with me. But if you're already familiar with Excel, then have a look at this module, right? We have a lot to cover different topics, right? And see which ones would be helpful to you as a quick refresher. Okay, So that's pretty much for the second module. And last but not the least, for our third module, which is the fun stuff, right, is all about creating your first financial model. So don't worry about the details on this one. I'll be going through step-by-step with you so that you can create it from scratch as well. So you get to create your first full-fledged financial model, ranging from revenue, expenses. And then we have profit and loss over here, and then we have cash flow aswell, right? So right now you can see over here they're all blacks, but don't worry, we will be creating this on the go, right? And then I'll help you understand the essence of each sheet so that you know on how we are going to create this and populate all of the values, okay, so we will go into detail, right, of implementing our model using the tools and principles that we are going to be discussing. And you will be able to see like on how everything falls into place, okay, so I hope you're excited and then I'm excited as well, and then see you on the next tutorial.
2. Beginner - What is a Financial Model: Now let's talk about the definition of what is a financial model, okay, So let me share to you my experience, my own personal experience when I first heard about financial modeling, their initial impression that I get is it's something complicated, right? I mean, yes, there are complicated models. However, I also want to show to you that a financial model can be as simple one and it does the job as well, okay? So right now, what we have over here, right, is different inputs and then we have different names calculations that are happening over here. So what I'm showing to you right now is the finished product of what we're aiming to do. Okay, So the biggest secret about the definition of financial modeling is it's just a spreadsheet, right? With inputs. Okay? So we have inputs and then we have calculations. Remember crunching, that's happening, right? And then we have the outputs, which is the very final output to show to the user. Okay? So, okay, let me repeat that. So we just have inputs, right? So this is your starting point. Do you input the assumptions that you're making? And then we have calculations. So this is where the magic happens, right? The number crunching happens. And then we have the output which is the final, like a report or the final output that you want to show to your user, okay, with that definition, right? If you think about it, it's not as complicated as it sounds. But let's also go over quickly on what is not a financial model. So for example, if you have a table of static data, like for example, you have a less of names, then that is not a financial model, right? Because there are no calculations being made, right? There's no dynamic outputs that are being created by the spreadsheet. So data is not a financial model. And I hope this simplifies things right on what a financial model is all about. So don't worry, right? Once we get into the details of creating this model from scratch, then I think it will become second nature to you to understand better on what a financial model is able to do. Okay? So now you can breathe a sigh of relief, right? Because it makes it less overwhelming. I mean, hearing about the definition of the financial model. So I just want to give you a quick overview because I'd believe that Excel, right, it's the best tool to create your financial model. The best suitable tool for this one. And as a financial model files a specific structure that you can easily create this in Excel S, I'll just go over it quickly, right? If I show you quickly the numbers that are happening over here, right? You could see that Excel makes it a very good fit to display and also to create in here, okay, so you have your charts as well, right? We will be going through all of this in detail throughout topics that we're going to be covering, right? So the idea is you put in the inputs, okay, over here. And then the outputs, would they magically change of pretty much everything here would change just by inputting all of the initial figures, initial numbers in your first spreadsheet. And that's what a financial model is capable of doing, and Excel is the best tool for that. Okay, so let's go over here. We have inputs, right? I just want to show you quickly once we put in the numbers over here. So don't worry about the details yet. And then what's going to happen is the revenue numbers will update automatically so that you know, like how much of the sales coming in. And then we calculate the expenses as well, right? How much are we spending? And then we calculate the cash flow. Do we have enough cash? And then we have the summary which is the profit and loss. Are we making money or not? Okay, So this is what a basic financial model will look like, and we will be creating this from scratch as well. Okay, so I hope this opens like a different view for you right now. Like a different level of appreciation on what a financial model is capable of doing.
3. Beginner - Financial Model Design: Let's talk about the importance of financial model design. So if you're like me, I would be itching to start implementing the model as soon as possible. Where like creating calculations, designing how it would look like, right? So that's pretty much the instinct, right? Because this is like getting things done. That's where you think that you're accomplishing things. But what I want to stress when it comes to learning the theory behind the financial modeling. Sometimes it comes off as boring, but I couldn't stress the importance of it. Because having the incorrect the sign for your financial model, right? If you didn't plan for it correctly, it will cost a lot in terms of time, money, and effort, okay, and we don't want that to happen. So we just why financial model design the planning phase at the very beginning is very important. Okay, so I'll just go with you the principles, so make sure to keep your eyes peeled because this is very crucial, okay, so a typical financial model would have pretty much the worksheets in this order. You have the inputs, right? So you can see over here. And then we have the calculations on what happens in between. And then we pretty much have the summary, which is the final output that you want to show to the user. So it's like an ordering that tells a story step-by-step, right? So as you can see over here, right? We have the inputs. After the inputs, then calculations are made right, step-by-step over here. And then at the very end you have your summary which puts everything together, right? So it tells a story how it got from the very beginning, right? And then until the end so that when your users starts to dive into the details, then they can also check the calculations to see on how it's being made, right? It tells a complete story. Off your numbers. Okay. So what we went through was the ordering, the sheets over here on how we can tell the story inside our financial model. Now we want to go through the foundational pieces when it comes to designing the model. So number 1, it's all about the end goal. So start with the big picture in mind. So you need to know what the goal of your financial model is. So for example, over here, right, if we go through the summary page, okay, what are we trying to achieve, right? So over here is we want to show to the user that, okay, are we making money in the business or are we losing money? So that's the main goal. That's pretty much if you want to sum your financial model, this financial model, One sentence, then that's pretty much it. So you need to know the end goal so that you can start working backwards. Because once you have this goal in mind, now you can think of like what components or what details do I need to be able to come up with this output or the summary page so you could start thinking off all I need to know what are the expenses and then you also want to know about the revenue numbers. And then once you start moving backwards and then start to break this down, and now you start thinking, okay, to get to my revenue numbers, what details do I need to get from the user that they would input from the very beginning. Then I could start making this calculations. Okay, so you could see the thought process, right? So don't worry if this terms are not familiar yet to you, but I want to show you that we start with the goal in mind, and then we start working backwards on how we can arrive to that goal. Okay, So that's number one. Number two is your users, okay? So you need to have a think about who your users are for the financial model and what are their needs. So want it, one tip that I want to share it is to get them involved early in the planning process. So for example, you're planning the input page, right? I want you to get them involved very early, okay? Even though it's still not complete. But if you get them involved in the planning process early, you can minimize surprises in the end. Okay, so just ask for their constant feedback and then even if it's still incomplete, right? Because it's going to be very crucial. Because when you talk with users, right? Sometimes when users think about things now, oh, okay, I want to, you'll need the price, you'll need the percentage, for example, they gave you this details. But once you have this inside a spreadsheet, right? Like address, you could think of it like version one or not even version 1, a pre draft version, right? Once you show them this one, it gives them a more concrete view and then they start thinking that, whoa, hold on. I think we need more details on. And then they give you more input, like assumptions over here. And that helps you a lot because I've had stories and experiences as well when you create the entire model, right? And then at the very end, you show them to you, the user, and they tell you that it's not something that they want. Okay, so we should SLI all right. We need to have them be involved at the very start, right? So get them to test your model. So when they start using it, they can get new ideas and then they didn't give you rapid feedback as well. Okay, so there are some of the common questions. For example, do they need all of the inputs with validation to guide them, right? Do you want to restrict the inputs so that you can hold their hands throughout the process. Do they need detailed documentation? Or how do they want the output to look like, right, for the summary page, for example. So those are just some of the questions that you need to take into account for your users. So that's number two. Number three is the time series of your model. So when we say time series, right, what are you using? Are you using your data for daily, right, for weekly, monthly, quarterly, or annually, right? So it really crucial to get the time series Correct. So for example, for example, I'll just show you the revenue. So you could see right at the top, you can see the months over here. So that would already tell you that our data is in monthly, the monthly calculations over here, right? So those are some of the things because for example, just imagine if you've already created your model monthly over here to show the monthly figures. And then all of a sudden the user would tell you, Oh, I don't need monthly, I want it to be shown quarterly. Right. Or let's say vice versa. You created quarterly, right. And then all of a sudden they tell you, Hey, show me it monthly, please. Okay. So that's going to be devastating because there's kinda be a lot of rework and extra headache for you if you don't get the time series stripe. Okay? So it's very crucial to get it right at the beginning, okay, so just make sure on that so that you can avoid are working most of the model just in case and j. So I would strongly encourage you to create a simple model as much as possible. So don't over complicate things as long as it gets the job done, then that's a very good financial model, okay? Because it will make everything easier for you and the people using our model as well would be easier for them in the long run.
4. Beginner - Scenario Analysis: Now let's talk about scenario analysis. So scenario analysis is also very crucial when it comes to creating our financial model. And you could think of it as a fancy way of giving the user a quick way to analyze like different possible scenarios. So for example, in over here, right, we have the worst-case, we have the best case, we have the best-case and K. Because think about this, right? When we have all of the numbers inputted over here, when we create the initial list of inputs for the user, you could think of the user thinking like, okay, making this assumptions just numbers, right? But what if, what about the worst-case scenario? What if, let's say, I'm anticipating that it's going to be like just half for the profit, for example, or something like that. And this is a really nice way inside Excel that you can create for your users. So that in just a single click, right, you could have the user immediately play out that specific scenario. So it's just a, okay, The main goal is for scenarios to play out, right? You want it to be a one-click approach for your users so that they can just jump from one scenario to another, okay, at first it sounds like magic, right? Butt. Going to be very crucial to have that for your analysis to be easier, okay, So that they can just play out with different scenarios and then there's not like extra work that they need to do when it comes to setting it up. Okay, so there are different ways to perform scenario analysis, but over here in Excel, I just want to share to you the easiest way to do scenario analysis because it's also easy for you to create that. And it's also easy for your user to use that. Okay, so it's going to be true drop-down this. So you can see over here, right? We have disrupted and this over here. And then the user can just switch or jump from one scenario to another just by Desk click. And then once you click, let's say a different one, right? All of the numbers would change across the calculations and also your summary page. Okay. Sounds like magic, but we will be going through in detail on how to create this. So don't worry about that. I just want you to understand that we have another layer right? Aside from the calculations that we are doing, we can also incorporate different scenarios inside our financial model so that it will be easy for our user to jump from one scenario to another.
5. Beginner - Documentation: Now let's talk about the best practices in documentation and k. So this is entirely optional to be honest, but I cannot stress the importance of this as well off making the lives of your users easier with proper documentation, okay, it is crucial to the success of your financial model because the best practice is to put sufficient documentation so that you can assist your users on how to use your mother, okay. Because sometimes, right. If your user is using this for the first time or maybe after a long time and he or she cannot remember on how to properly utilize this, then documentation would help guide them. Okay, so it doesn't add functionality to the model, right? Because it's just text or information, additional information to your financial model. But this will give them a helping hand to make the process easier. Okay, so there are multiple methods that we could use inside Excel to accomplish this. Okay? So the first one is using texts, okay, so this is the most basic one. You just type in text to provide additional information for a specific field. So for example, over here, right, we have website hosting costs, right? And then we just type in over here, this is paid once a year, so we just place in texts over here. It's just additional information for the user to be aware of, okay? And that's number one. Number two is text formulas, so we can make it a swell, a bit more dynamic. So let's create one quickly over here for the text formula. And then I'll just create a formula, right? Have this in here for the value and then let's say we just want to share dot, thus not include bullous. So I'll just close this, right and enter. And then you have the quick value over here, right? That you're just saying that This does not include the bonus. So for example, let's say I just want to add the dollar sign is slow. We could quickly add BEP and then add the ampersand. And we have this one, right? When you change the value later, let's say we just change this to 30 thousand, right? Then your texts documentation changes as well. So you could use a tax formula for that. That's number two. Number three is the use of comments, okay, so we can include comments in here to add more information about a specific field. So we can do that via review. Okay, and then there's that new comment over here. And let's say for two formulas book, Let's say we just want to see a comment that this is the bestseller. Okay, so I'll just click New Comment. And let's say this is our best center. Okay? So we could add a comments or the next time when somebody like hovers over here, right, then you could see that comment right away. And that's your way off documentation. And there's also that mark over here right on the upper right corner that you could see immediately that there's a common, okay. So that's number three. And then the last way of adding documentation, right, is comments via data validation. Okay, So if you do that over here, you could see, right, we have already added using this method, right? It's also elegant in my opinion because it doesn't like get into weight. And then only when you click on the field, then documentation shows up. So for example, over here, right, for the number of business days, it tells us that it counts all of the days in the month. So to be able to do this for the comments via data validation, we just go over to data validation. So I'll just go over to data and then jump over to data validation. All right, if I select this, then you could see on this tab the input message, right? And then you could tick the show input message when cell is selected. And then just type in a title and an input message so that this will show up, right? Whenever a person selects that specific cell. Okay, so you can see, right, this input message over here, it's exactly the same on what we're seeing on this specific cell. Okay, So this are pretty much the different ways of documenting. So I would strongly suggest, right, don't put it off whether you're creating your financial model because it doesn't just help the user, but it can also help you. Because for example, let's say you've already created this, right? And then after a couple of months, and then the request for you to make some additional changes or your client wants you to change or make updates to it, then having that documentation makes it easier for you to understand. Like, okay, why does this feel for how, like, what assumptions am I making over here, right? And then you could just rely on that documentation to give you a headstart.
6. Beginner - Excel - Cell References: Now let's talk about the Excel core concepts that are crucial for financial modelling. Okay, so we're going to be discussing about cell references and for cellular, for answers, they're pretty much two types of references, which is relative and absolute references. So the key difference between the relative and absolute cell and our friends, right? Is that the relative cell reference, the move when you copy them. But 4D absolute cell references, right? They do not move. Okay, So to be able to show you on how it works right now, I'll just quickly like create formulas over here and then apply the two types of references, right? So that you can understand on what's exactly happening. So what we have over here is a monthly budget, right? We have the types of bills over here. We have read the car below that, right, pet food and phone. And then we have pretty much three months over here off the different expenses or to budget. Okay? So what we want to happen is to get the total amount right off the three months over here. So what we will be doing is I'll just create a quick formula, OK, and then we'll just add the three of them together, right? So pretty much straightforward and we're here, okay, so we have 3,600 with the addition of all of them, okay? And then the question right now is how do I create the other formulas as well over here, right? So to be able to do DAP, right? Instead of redoing everything one by one, which will take quite a while, right? So I'll just copy, right-click copy. And then I'll just paste it over here for the rest to have the same formula because it's pretty much the same concept. I want to add April, May, and June for the total of the car over here. Okay. So I'll just go for pace, right? And then we have the different formulas as well. You could see that if I double-click on this, you can see that it's adding the three values together, n, pretty much the same as before to pet, right, food. And then for the fault, so you might be thinking, Okay, what exactly happened? How did it work? Okay. Because isn't it that the foreman over here is this tree cells, but how come when a copied it over here, right? All of a sudden it's now pertaining to the outer tree cell self de Carr role. Okay, So this is where the relative cell reference comes into play. Because this is a relative cell reference for all this tree over here, B3, C3, and D3. If you copy it over here, right? The references move a swell because technically if you think about it, since we have moved one, so down, okay. The reference themselves, okay. Also move together like one cell down the swell, right? Pretty handy, right. So they're able to move as well. So you can see b3, c3, D3 now became B4, C4, and D4 because we have jumped from row 3 to row 4, which is moving one rule down, okay? So it's going to be the same thing as well. If you move from column to column, then the same principle applies as well. And it will just update the cell reference dynamically again when you copy the formulas over. And now you might be wondering, okay, we're dusty absolute cell reference now take place. Okay, so let's just jump over here to percentage. Okay, so to be able to calculate the percentage of the total values over here, right? We need to calculate the totals as well for everything here. So to be able to do that, what I'll do is to add everything together, instead of creating the formula like adding them one by one, we can use the AutoSum function. So I'll just click AutoSum over here, right? So if I press Enter, there you go, right? Very straightforward where it just takes the entire range of values and then adds them all together. So I can do the same thing as well if I copy, right? And I'll just drag this over here and then right-click again and go for pace, right? You could see that it did the same thing as well to the other values. So it updated the formula. Okay, right, since they are relative cell references, right? You can see that jumping from column B to call them sea. It was also a bit it in our formula, right? If I jump over here, it adds a swell for the values of column D and then for the values of column E as well. Okay, so very handy when you're using relative cell references. Now this is where absolute cell reference comes into play. Okay, so to be able to calculate the percentage, let's just try this out. So it's going to be something like the total amount, right? And then we are going to be dividing this by dy, grand total of everything. Okay, so to have the person's age over here, okay, so I'll just change the formatting of this one to make it look better. I'll just click on the percent format over here, percentage format, so we have 61 percent, okay? But once we start copying this down, you're going to be not as seeing a different type of change. So another way activity off copying the formulas down is instead of right-clicking and copy pasting this over here. And you could just drag this down to have epidemic apply this well, okay. But here's the thing. I'll come. It's an error, a division by 0 error. So what exactly happened if I go click on this, right, you could see that it was able to get the total off car. Okay. That's good. That's the correct way. But all of a sudden, right? It's now dividing by nine, which is this black cell over here. Because what we want to happen is absolutely E4 divided by 8, which is this grand total. So if you think about it, right, this is e t divided by e, right? But once we jump over here, we want e3, right, to move over here, which is relative. And we want to stay in place even when we're copying formulas over. So which means this is where absolute cell references come in handy. Because now we can have E8, like stayed locked in place when we copy over to a different cells, okay, using the same formula. So to be able to do that, right, it's going to be using the dollar sign and we'll just add dollar signs over here. We're seeing that we want the column partion to be absolute, right, for the reference. And then we want the row portion as well of this cell reference to be absolute as well, which is why we have $2 signs. Okay, So another shortcut, the swell to be able to do that. So let me just undo that. Is you have this cell reference selected. You just press F4 as the keyboard shortcut and an Excel will just apply this for you to make it as an absolute cell reference. So I've already press F4 you, so you can see over here we have the dollar sign already applied in. It's now an absolute cell reference. If I press Enter, right, we still have the same result, 61%. Nothing change. But watch what will happen once we drag this down and apply the same formulas. Now we have everything correctly calculated now. Okay, so if I double-click on this, we have e4 and then we have EA, which has been locked in place. Okay, so if I jump over here, right, we have E5 and an EA to swell, right? And then we have E6, and then we have EA to swell. And then we have E7. And if we have EA as well. So just to double-check everything, I'll go for autosome 40 percentage. Let's have a quick look. And everything is correct. Okay. Because when you add it all up the person page, we have a 100 percent, which is what we expect. Okay? So this is how you use relative and absolute cell references.
7. Beginner - Excel - IF Formula: So now let's talk about the crucial Excel formulas that we're going to be using when it comes to financial modeling. So the first one is all about the IF formula. So this is a logical formula that is useful when you have like a condition to check. So for example, if the condition is met, then you show a specific value. But if not, if the condition is not met, then you show something else. Okay, so let's jump over to the example over here. So we have, if a sales rep has sold more than $3 thousand, then give them a bonus. So we have a list of sale strap over here, right? We have the sales figures. And then if it's more than 3 thousand, then we want to check, do you give them a bonus or not? So what we want to show here is if 3 thousand and Smith, then we show it just a text of bonus. And then if not, then we just show a tax off no bonus. Okay? So pretty straightforward, but the IF function, right, makes it possible. So I'll just type in if over here, right? And then we have the first parameter which is the logical test. So what are we checking for? So what we want to check is if the sales figure, right, the sales amount is greater than 3 thousand dollars. So I'll just select AS which is the sales figure or the sales amount. And then we want to check if it's greater than three tells him, okay, so that's our condition. And if this condition is met, right? So we have the next value, which is if the condition is met, what do you want to display? So I'll just type in the text of bonus because that's what I want to show. Right. And what if it's not met, okay, So if it's $3 thousand and below, then we just show that it's null bonus. Okay? So we're happy with this one. Okay, I'll just press Enter and then this formula over here, I'll just drag this down to have it applied to the entire column. Now if we have a quick look over here, right, you can see that this is greater than 3 thousand, so we have a bonus. If I jump over here, it's less than 3000, so no bonus. And then S for this one, this is greater than 3 thousand and we have a bonus. So that's pretty much it for the IF formula.
8. Beginner - Excel - AND Formula: Now let's discuss about the n formula. So 4D and formula, okay, you have multiple conditions that you want to check. And then if everything is satisfied, dent, a true value is returned by the n formula. But otherwise okay, if any, even one of those are not satisfied with the conditions, then a false value gets a turn. So simpler terms, right, is just saying that it's all or nothing, okay? Everything has to be met. Then that's the only time a true value guests return. If any one of them fails, then it's a false. Okay, So over here, right, we have this example, we have dates, and then we have sales. Okay? And then our condition that we're are checking is it has to be both January and sales should be greater than one tells him. So this is where an end formula would be useful. But to be able to do that, okay. To be able to check first if it's the month of January, we will be using an additional formula, which is called the month formula, right? So if I just select this cell, the date, right, it will give back to me. Write the month. For example, this is January, so it will return one to me, okay? So we will use that month formula. Okay, so now let's jump over to n. And then what is our first condition that we want to check? Okay. It's the date in the month of January. So I'll type in the month formula now, right? And then we have the date and then what we want to check if the month January. So I'll just have a quick check. Is it equal to one? Okay, so that's the first condition. The second condition is, is sales greater than 1, 10, 100. So what I can do right now is I'll just type in comma. And then now I can provide the second condition. So you could see right on the texts over here to help texts you have logical to, you have logical tree, a chatter, right? Which means you can have different conditions. So we have five conditions that you want to check, then you can use the formula for death as well. You could just add conditioned neck right next to each other. Okay. So for the next condition, I'll just type in right now, it has to be the sales and k should be greater than 100. Okay? So we have this two conditions. I'll just press enter. And we have true, because it is both January over here, right? We have greater than 140 says. So I'll just drag this down to have all of the formula's copied over. So you could see, right, for example, this one is false because even though it's January, but it doesn't meet the sales requirement, which is greater than 1 tau 0. So we have this one as well as true because it meets both January and also tree taus and over here, more than 100. This one is false because even though it meets sales greater than 100, it doesn't meet the requirement that it has to be on-demand. January. Okay. So this is 40 and formula.
9. Beginner - Excel - OR Formula: Now let's jump over to the OR formula. Okay, So 40, our formula, It's pretty much like, very similar to the n formula. The only difference is for the OR formula. You're also checking for multiple conditions, right? But what you're seeing here is as long as one of the conditions is met, then a true gets returned. And then if all of the conditions are not met, then that's the only time that false gets returned. So which means for or what you're after is as long as one of them is satisfied, then it's true. Okay? Otherwise, it's false. So in our example over here, we have the dates again, right? We have the sales as well. And then we have January or sales greater than 12 thousand. That's the condition that we are checking, which means we don't care, Right? As long as one of the conditions that gets met, you want a true result. Okay? So to be able to do debt that's used the OR formula, right. And then to get the month to check if it's January, right, we are going to be using the month formula and then that's just add the date over here and let's check if it's on-demand of January. So we're going to be using the comma and then we're going to be starting our next condition, which is sales. Is it greater than 100? Okay, so since we only have this two conditions over here, we'll close the formula and then I'll press Enter. And then I'll just drag this down over here to have Aberdeen populate it. So you could see, right? We have or over here, so we have January, right? And sales, this will, both of them are satisfied. But for as long as one of the conditions gets satisfied, then that's a true. Okay. Over here. Okay. It's an or so which means this is not January. That's fine. But this is greater than 100. So that's true. Okay, Over here we have January, although this one is less than 100. Okay? It's still true because it satisfies the month requirement. This one is a false okay. Because it wasn't able to satisfy both conditions. This is not January, and then this is not greater than 1 tau 146, okay? And then this one's true because January is satisfied and then the sales also got satisfied as well. Okay, so that's 40 or formula.
10. Beginner - Excel - Nested Formulas: Now let's have some fun and then start combining all of these formulas together. Okay, so we're going to be using nesting, right? Wherein we have a formula inside of another formula. So for this scenario, we have the sale strap over here, we have the regions, and then we have the sales amount as well. And the condition or the problem that we have, right? If a sales rep has sold more than 3 thousand and it's from the north region, then give them a bonus. Otherwise, we just display a no bonus texts. Okay, so to be able to do this, so we have two conditions that we need to satisfy. We have more than 3000 and from D naught Grecia, So pretty much do things. Okay, So the first thing that comes into mind is DNF formula to make this possible. But how do we show the bonus texts conditionally or a null Boehner's texts condition in the end that's going to be true. And if formula, so we're going to be combining the two of them to get it. So to be able to start, I'll just type in df formula. Okay? And then what is the condition? What is the test that we're going to be doing? Which is an n formula that would have to, okay, two conditions inside, which is sales being more than 3 thousand. So I'll do that right now, right? Greater than 300. And then the region should be not. So which means I'll say D region over here. Okay? And then it has to be Norte for the region. Now close debt and we have our formula complete, okay, So if this condition is satisfied, what do we do? Then we display the bonus texts. Okay, so I'll just type in bonus. And then now what if the condition is not satisfied, then what do we display? Then now we display no bonus. Okay, now let's close this. We have our formula, complete it. And let's see what's going to happen. I'll press Enter and then let me just copy this across the entire column. And now let's have a look. Okay, why is this marcus bonus? Okay, So let's check sales. Okay. That's good. Greater than 3 thousand and then the region it's not. Okay. How about this one? I'll come it's no bonus even though sales dot satisfied greater than 3000, but the region itself, so that's a null bonds over here. Now let's have a look over here that's saying Let's go to North for the first one, right? Because this is north. Okay. That's good. But the sales condition wasn't met. So which is less than okay, 3000 over here. So which is why it's a no bonus. Okay? So as you can see over here, we have the different formulas, but you can have them working together side-by-side. True, the combination or two to power off nesting formulas.
11. Beginner - Excel - Conditional Formatting: Now let's talk about conditional formatting. So for conditional formatting, it's very useful inside the financial model because you're able to place like dynamic formatting of your cells, like on how the data gets displayed so that it's easily to have specific values stand out as needed. Okay, So to be able to appreciate this more, Let's have a quick example in action. So over here we have sales amount sales figures. Over here we have the months, right? And then we have the year 2013, 2014, 2015. So pretty much straightforward for the table. But what we want to happen is we want to highlight the values that are bigger than whatever is specified over here. So right now we have 60 thousand over here. So which means greater than 60000 for all of the values over here, we want it to get highlighted. Okay, so how do we do that? And that's where conditional formatting comes in. Okay, So what we're going to be doing is this artist self that we want to get highlighted depending on this one. So highlight the entire range over here and let's jump over to home, right? And then we have conditional formatting. So let's select this and then we have the Highlight Cells rules over here, right? We have different conditions and then we have more rules as well. But for our scenario, we just want to use the greater than because we want to check that. Okay, now we have this one format cells that are greater than then a specific value. But we don't want to type in 60 tasks. And over here, what we want to happen, right? I'll just remove this instead of having a specific value hard-coded in here. And I'll just select this cell to make it dynamic. Okay? Now, what is the formatting that we want to happen? We have different colors over here. And let's say I'll just go for the yellow. We want it to be yellow fill with dark yellow texts. But if you want more, like further customizations, then you could do that as well with a custom format. But for now, let's stick to the yellow format over here, and I'll click Okay. Now what we have right now is tanks to conditional formatting, right? There was no need for me to update and specific cells over here just to match d condition that was specified. But right now you can see it right? All of the values greater than 60 thousand gut edit over here. And once we start changing the value, then the conditional formatting takes effect as well. So for example, let's say 10 thousand, okay? If it's 10 thousand, absolutely, all of the values over here are greater than 10 thousand, so everything got highlighted. Let's say, let's just change this to 30 thousand. So if it's 30000, right, the values less than or equal to 30000, they're not highlighted. Okay? So it's like magic, but this is what Conditional Formatting can do for you end, it's going to be crucial to make values inside your financial model stand out more.
12. Beginner - Excel - Data validation: Okay, now let's talk about data validation. So the concept of data validation is very crucial for our financial model, is because if you want to place specific rules on how your model like you want it to be used by the user, then data validation is very useful. So I'll just show you quickly on the different ways that we could use data validation for. So for the first one is a drop-down list. So for example, if you have a specific field that you want to restrict the user to only have multiple choices, that he just had, like three choices or four choices, then you could do that in Excel. So to be able to do that, Let's say we have this drop-down lists over here and then you just want them to provide or select, okay, from the costumer and lists that we have in this table and dropped down it is, would be able to do that for you. So to be able to accomplish this, and let's jump over to data and then select Data Validation. So you need to make sure first right, you have the self-selected that you want to place a drop-down list on. I'll select Data Validation, right? And then we have the different settings. So over here, I'll just select from the drop-down that a list, okay, should be placed over here. But the question right now is okay, we have an S, But what are the values off the list that the user can select from? So this is where we're going to be selecting the source. So I'll select the list of customers over here. Hello, let me just expand this again, right? So we have the source, okay, which I've selected as the costumer column. And if I go, okay, you could notice right now it's now a drop-down list. And if I select over here, we pretty much have the same less of customers, right? So let's say 1, 2, 3, warehousing, and let's change this to 456. Okay. And the cool thing with this one is it also gets updated, okay, based on the source list that you have provided. So now you can see it's 45, six warehousing a swell. And pretty much the user is restricted to whatever it over here. Okay, So that's pretty much for the less. Another way of performing data validation as well is, for example, making sure, Let's see On the sales over here called over here, right? That only numbers like gets inputted over here. Okay? So if you want to make that restriction, then the same thing. You can also do that inside Data Validation again. So I'll just go over to the entire sales column for example, right? Or let's say 40 years ago, you could do the same thing. Okay, so let's say let's just jump over to sales and then we'll do the year as well. So I'll go to Data Data Validation over here, right? We have the settings. And then we have, let's say you could see, right, there's a lot of different types of smell. But for our sales column, we'll just stick to decimal. Okay, so over here you can also specify and write what is the restriction for D decimal values of this column. So in our case in sales, okay, can be 0 and above, right? Are greater than or equal to 0, right? It cannot be negative. So let's place that restriction. So I'll just select greater than or equal to and the minimum would be 0 over here, right? And I'll click, Okay, so let's just try it out. So if I type in ABC, right, it's going to give me an error and it won't allow me to make that change. So for example, if I type in the negative number, okay, so I'll just type in negative 1, 2, 3, and then we would have the same air as well, show up. Okay? But if I type in, let's say different value, It's just try it out over here. And that's allowed because we have defined our rules inside the data validation. So let's do the same thing as both for the year. So I'll just highlight the year values over here. I'll go to data, data validation, okay? And if for this one, the type of value that we're allowing is a whole number this time. And then let's say we just want it to be from the year 2000 to the year 200989. Okay. And that's just define a range. But you could also change the condition on the restrictions of the data that can be placed in there. So we're happy with this one. All right, so let's change this to 2000. That's allowed. Let's say it's changed, it's to ABC. That's an error. Okay, Let's see, 1999. Now, not allowed, okay, and then let's say your treat often, that's not allowed us. Well, because we have defined the restrictions inside data validation.
13. Beginner - Model - Inputs: Now we're going to be
doing the fun part which is creating our
financial model from scratch. But before we start, let's have a quick
overview on what is the business that we are going to create our
financial model on, okay, so what we're
going to be working with is an online bookstore. So over here actually this
is a real online bookstore. So it's written by yours truly
and migrate coauthor John. So we have this toolbox
about macros and formulas. So for our scenario, for our hypothetical scenario is
we have an online bookstore. Given the sales numbers
and also the expenses, then we want to create
a report on how is our profitability and how is our cashflow so that
we can also know like, are we still able to do
business month in, month out. So this is an online
bookstore, right? And then the numbers
that we're using are purely for
hypothetical purposes. Okay, so let's jump over to our financial models
so that we can have a look on
where do we start? So the first portion
that we need to start is with our inputs. So pretty much this
**** over here is the one that you have, like a playground for your
user to play around with. So when we say playground, this is where you
start working with your hypothetical values so
that you can get a feel. For example, like, Okay, are we going to make money or what should I price this, right? Then you have your pretty
much assumptions as well based on historical data
that you can place in here. So what I want to go
through right now is you could see
this one's right. There's a specific
format that I used. So once very important
for the user and for yourself as well as
when it comes to inputs, use a consistent format
or a consistent style. In our case, what I used was, for example, this one, right? This one over here. If I go to Cell Styles, you could see this
input over here. So use that to you. It's up to you. It's not like specifically this format
that you need to use, but whatever's good for you, whatever you think is right, you can use that as long as it's consistent throughout
the entire sheet. So that when it
comes to the user, when they jumped in over here, at a first glance, they would immediately know on which cells they can
put values into, which sales that they
can change so that they can see the numbers update
dynamically as well. So with that out of the way, let's just go over d
values that we have. So since we're talking about the profitability of a business, what we need first is the number of business
days in the month. Because of course, we need to factor in how many
days are there in a month because that
would affect our expenses. That would effect on how
much sales are we making, the revenue we are making. So I placed in
here the number of business days from
January to December, which is pretty much actually
the days of the month. So for example, we have January, that's 31 days,
February 28, right? So pretty much we're not
counting the working days, but we're counting all
the days in the month. And the reason being since
we're an online bookstore, so which means the
business is open 247, then we have the
seasonality as well. So let's say we
just base this on industry data
because seasonality, that would pretty much say that given a specific
month, right? If your industry has specific strong months
and specific week, month, then your sales
would fluctuate as well. So for example, over here, that's a 100% of
what's expected. But let's say on the month
of June, it's a doubt month. It's usually just 90% and
that's going to factor in. I'll just jump over
here quickly is two, since we're talking
about a bookstore, the number of books
sold per day. So it's like when
we say for January, it's going to be ten per
day because it's a 100%, but for the month of June, it's gonna be 90 per cent. So that amounts to nine
books sold per day. And then let's say on the month of September
and November, it's 120 per cent, so that's gonna be 12
books salt per day. Okay, So seasonality is also
going to be a factor when it comes to the calculation of our figures, sales figures. So now we have our
revenue related numbers. So over here is, of course, what is the price that we
are selling the books? So we have two books, the formulas and democracy. Then we pretty much have the price listed out
over here, so forth. The formulas book, it's $20. And then for the macros book, it's being sold for 30 bucks. Then we also have the
percentage of being sold, which means in a
given day, how many, what is the percentage like
how many books are being sold as formulas, right? And then the person that is being sold for the macro book. So we have 70%, 30. Given that on the number of books sold per
day, that's ten, which means we are selling
sudden formulas book, which is 70 per cent of ten. And then we have tree macros
book being sold today, which is 30% of ten. So it's pretty much
straightforward from a revenue perspective because all we need are
the sales numbers, how many books are being sold, and then what is the
price of each book. So that's for revenue.
So we're good with that. Now, let's jump over
to expense related. So when we say expense related, we're gonna be thinking, how much does it cost
to print the book? So that's pretty much the
printing costs that we have. The cost of manufacturing or making are producing the books. For the formulas book, what we have over
here is $5, right? Therefore, the macros book, Let's say it's more expensive. We just placed it here at $20 for creating that book or
for printing out the book. Now we have for
the support staff. So over here our other
expenses as well. And this is where
what's really important is to be specific
on the time series, like what is the time duration
for this specific expense? So for example,
let's say we have a support staff and then cumulatively is $20
thousand per year. And we have stated it over here because it's going to
be crucial when it comes to calculating later in our expenses for the
time duration as well, then other expenses
that we have. So this is pretty much
fixed costs are costs that are dynamically typed depending on how many that we sell, okay? Then this is the fixed expenses. So other wants that we have website hosting because we need to have a website to host
our online store, right? So website hosting is
going to be $40 per month. That software licenses
the ones that we are using the different
software, right. And then that amounts to
around $300 per month. And then you could notice a
spell on the right side that we specify it's being
paid once a year, right? So this is the cost
per month, right? But the time that we need to pay is only paid once a year. So it's the same thing
as well for licenses. It's paid once a year. Okay, So this two
points are gonna be very important as well
when it comes to cashflow. And I'll explain that once we're going to create
our cash flow, then we have our income
tax rate as well, which is 20 per cent, right? So which means from the
profit that we're making, which is revenue minus expenses, whatever's left ASD profit, then 20% will be paid
out as income tax. Okay, another thing
to note off, right? This is paid out quarterly. So take note of that. That's going to factor in
a swell into cashflow. And then over here,
four scenarios. We're not going to be
touching this for now. Let's just leave this there. I'll explain this when we're going into the scenario section. Okay, so we're good with that. Pretty much this is the
input page that we have. And this is where we can
play around with our values.
14. Beginner - Model - Revenue: Okay, so now let's work on the revenues. So when we say revenue, what we're after is how much sales are we making per month? Okay, So it's pretty straightforward because if we tried to think, okay, how do we calculate sales? So the main goal here is to find out how many formulas book, okay? How many of the formulas book are we selling per month? And in harmony of the macros book, are we selling per month from that number, right? You can just multiply it by the price that we're setting and we would be able to find out how much are we making in terms of sales every month, okay, so to start things off, what we need is how many books are we selling per month? Okay? So for this one you can see you write a place that note swell include seasonality in your calculation. Okay, So if we have a look, what do we need? Number of books sold per month? If I jump over here to our inputs, we need this value, ok, number of books sold per day, right? And then we're going to be multiplying it with the number of business days. Because if it's ten books per day turned 21 days, we need to multiply them together, right? And then we need to factor in as well, the seasonality. So we just multiply it with this one to get the final number. Okay, so let's just try it out, right? This go for equals nets, create the calculation, right? Number of books sold per day, okay? And then the number of business days, and then let's multiply it with the seasonality for the month of January. Okay, let's press Enter. And we have talented tenth, right? That's 31 times 10 times a 100 percent. So the question right now is do we just simply drag and drop this and copy the formulas up to December, okay? But it's going to be wrong. Okay. We'll find out in a short while. Let's do that. Okay, That came out weird. That's 0, right? We have an error swell over here. So what went wrong? So if we have a quick look, right? It's C2, C3. And see, if we have a look. C2, right? That's February numbers, okay, see tree, right? Looking good. And C ten. Okay. So this is, we're okay. We made a mistake on, it's because we want this to remain at the same cell for the number of books sold per day. So which means, okay, Let's go back to the month of January, which means this one, right? Be 10. We need to make this an absolute cell reference because we don't want this to move when we're copying the formula, it's over, okay, so we need to take this into account, into the other formula step we're going to be creating over here. Okay, so I'll just press the keyboard shortcut F4, right? Okay, so now that's absolute. Pretty easy. Okay, I'll press Enter and let's try it out again. I'll just drag and drop this to December. And now we have everything being calculated correctly day. So if I jump over state to let's say let's have a look at the September. Okay. So 30 right. And then 120 percent and then ten a day. And for the month of September, that's 360 and j. So it's not true 3D because of the seasonality. We have more sales coming in for the number of books. Okay, now we are going to get the breakdown. How many formulas book are resetting per month, and how many macros book are we selling in a month? So we're going to get this from the percentage off being salt. So 70 percent goes to formulas, right? And then 30 percent, That's macro book. Now. And we're gonna do that over here, okay? Since we have the total number of books, and we're just going to multiply this by the percentage over here, right? Okay, looking good. But we need to make sure as well that this is an absolute cell reference, since it's only this cell that we want to use for all of the formulas. So I'll just press F4 right now. Go and press Enter, right, looking good. And then we're going to do the same for the macros book aswell, same thing, January numbers over here. And then I'll just multiply it with the percentage for the number of macro spoke being sold. Okay? So same thing, right? Don't forget to press F4 to make this absolute no k. Now we have to 17, 93. Quick map. If you add this together, right, It's 210, you can see as well over here, right? 310. So which means we're doing it correctly. Now, what I'll do right now is let's just have another over here to have a quick check, right? Let's just double-check that we're not doing anything incorrectly, right? So it's also good to play some checks right, from time to time inside your sheet. So don't get to have a quick look. If you made a mistake in your calculations, you can use this to double-check. So what I'll do, I'll just add this two over here, right? So we should have 310. And then you could just have a quick, quick check. Okay? Let's say if this one is equal to this, we could just leave this as blank, right? But if there are not equal, if they are not equal, then we just want to show that there's an error so that we can have a quick look if we're doing everything right. So it's back, so that's good, right? So what I'll do, if you wanted something like explicit, then you can just type in good as well, right? So you can have an actual feedback on your error checking, okay, so I'll just highlight this one's, I'll drag this all the way to December, right? And we have all of our numbers and our checking right now everything's good because they're exactly equal to one another. Okay, so that's looking good. Now, since we have the numbers off the book of a specific book per month, now we can now calculate the sales, which is simply multiplying the quantity of the book, right, with the price. So what I'll do is I'll take the formulas book over here, right? And then I'll grab the price off the formulas book over here. And same thing. Don't forget to make this absolute because this is just the cell that we want to use for all the cells from January to December. So I'll trust for its absolute press Enter, right? And that's do the same thing as well for the macros. Now, I'll select the quantity for the macros book, and then I'll jump over to the price. Same thing, make it absolute. Press I for. Okay, looking good. Now let's select this two cells over here and I'll drag all the way to December to have the formula's copied and it's looking good. Now, the last bit that we need to do, the SD, total monthly sales, okay, what's the total amount? And it's simply combining this two together. Okay? So which means we're going to add it up. That's just type in, okay? And you formula and then just add both of them together, okay? Now, we can just drag this all the way to the end. And we have all of our total monthly sales numbers from January to December.
15. Beginner - Model - Expenses: Now let's talk about expenses. So for expenses, we pretty much have to mean Section step we need to calculate. One is d, fixed costs, right? And then the other is the printing costs, okay? So the fixed cost, pretty much as the name implies, they're fix, okay? So we can just simply calculate them right now. So first thing is the support staff and then website hosting and then the licenses. So pretty much we have this tree values are already listed out inside our inputs shit over here. Okay, so first things first for the support staff, right? It's per year. Since this is specified as per year, what we need for dxmA NCIS is the monthly costs. So what we're going to do is just create our formula. Let's go for the support staff value over here, right? And then since this is per year, what we need to do is divide this by 12 to get the monthly amount. Okay? So of course, since this is just a single cell that we want to use, then press F4 again to make this absolute and k. So looking good. And then next is we have website hosting. So let's just jump over to website hosting. Pretty straightforward because the amount over here stated is already per month, right? So we can just get the value from here, okay? And of course, since this is going to be D cell reference, the only cell reference, and let's press F4 to make sure that it stays that way. Okay, same thing for software licenses. Okay, Let's have a look and watch specified over here is per month, okay? So which means there's no further calculation that we need to do. Just get the value, press F4, same thing, make it absolute. Okay, so let's highlight this one's drag this over until December. And now you have the fixed costs, right? Expenses from January to December. And they're the same, right? Because they're fixed costs as well. And we need to get the sum of all of them. So let's just add them up all together, right? That's just create a quick formula and then add them all up. Okay, so same thing. I'll just drag this all the way to the end and we have our fixed costs. Okay? So quick tip as well. If you have a long formula, this is pretty simple, right? But if I asked you go along right, when you're working with your model, let's say you have a long formula, right? And then you are not sure if it's correct or not. So what you can do is over here, I'll just type in Evaluate Formula and then you can evaluate this specific formula. So what you can see over here is B3, B4, and B5. If I click event on weight over here, right, you could see the values change one by one. So over here, right, you've got changed to the support staff value, right? If I click Evaluate again, it's now 40 as well. So we have the website hosting value. And then lastly, right, they got I had it together first. And then lastly, we have the $300 for the licenses. And then the final result, which is this one. Okay? So it could use this a valid formula to help you understand the formulas step-by-step. And it's a really handy way to check any errors that you have, okay. So that's pretty much it for the total fixed cost. Now the next question is, how much are we spending, okay, for printing the formula books and the Metro books, I'll jump over to inputs after starting our formula. Let's jump over here, right? So what we need from this one is the cost to print. It's $5 per book that we're printing, right? So now we have this one and then multiplied by how many books are reselling, how many formula books, right? Are we selling in a MET and we have that number ready, which is from our revenue sheet. And we're here, right? And pretty much how many books are reselling, which is this one. Okay? So we have 217 formulas books, okay, being solved in the month of January, right? And then of course, since this is the price, okay, We don't want this to move. Don't forget to make this absolute, okay, and press Enter, right? And this is what we're spending for the formulas book, right? For printing in that case, I'll just have a quick look, right? 185, if I compare here. Right, for the revenue for January for to form his book. Okay, Good. We're making money, right? Okay. So just a quick sanity check, right? And then let's do the same for the macros book. So how much? Okay, It's the printing costs of a macros book that's select this one, right? Don't forget to make this absolute. They'll press F4, right? And then let's multiply this by how many macros books, okay? Are we setting in a month? But for this one is actually for the month of January. So that's go over here and we have the number ready. Okay. So you could see, right? What we're doing is we're not hard coding values in our sheet because what do we want, right, is when we link between cells, right? Which means everything starts from the inputs page, right? Then once you make a change to the inputs, everything else changes. It's like magic because it's the way on how we have set up our financial model, okay? So I hope this starts to make sense to you, right? As we start building this, that by step. So I'll press Enter. We're good with this one, right? So we have our macros book printing costs, and I'll just select this one. Okay, I'll drag this all the way to December, right? And just like magic, right, we have the printing cost and you could notice that it changes month by month, okay. Because it's the enamine it's not a fixed cost because it depends where it's based on how many books are we selling in the month, okay, So our expenses for printing costs would also change. Now we need to add both of them together. Pretty straightforward. I'll just select this one and add this here, right? And then we have the total printing costs and I'll just drag this all the way to the very end, okay? Now we have printing costs, we have fixed costs. So to get the grand total, right, we just need to add the two subtotals together, okay? The total fixed costs and the total print costs. And we have our total costs, same formula. I'll drag them all the way to December. And now we have our X-Men says Done.
16. Beginner - Model - Profit and Loss: Okay, now let's talk about profit and loss. So this is pretty much the main page that we want to show to our users. So it's pretty straightforward to compute this, since we already calculated the revenue and the expenses. So pretty much revenue minus expenses, that's our profit, right? And then afterwards, we need to factor in the income texts. And then after that we have our final profit after taxes. Okay? So the good thing with this one, since we've broken it down into revenue, right? And then expenses, then it's a lot easier to create this, this profit and loss over here for the calculations, right? Because we have broken this step-by-step and it's also easy for the user to see later on, right? Let's say they want to have a more detailed view of revenue, then they can just jump over and then see how the values are calculated. So let's jump over to profit and loss, and then let's place in the values one by one. So first day for revenue, since we have this calculated already, we can just grab it from the revenue sheet over here and then I'll just select the month of January. Press Enter. Okay, So pretty straightforward. And then I'll just drag this at the very end and then you can just see them get copied, right? It's not exactly copied, but it's referencing, right? The values in the revenue sheet, okay, So whenever the values change, same thing, it will change as well over here. Now for discipline, for the expenses, what I decided was I'll just place in the total fixed costs as well and the total print cost to have a quick break down. So it's going to be up to you and what your user needs. If they don't need this, right, they don't need that detailed and you can have that the data as well. But for our purposes, it's very straightforward to do that. 40 total fixed costs for the expenses. We'll just copy this, right? You'll just grab this. Okay. That's better term. Let's just grab this from the expense of sheet, right? And then just select the total fixed costs. I'll press Enter. Okay, So that's for the total fixed costs and then for the print costs, you know the drill by now, okay, so I'll just press equal and then it's just grabbed the total print costs for the month of January, okay. Now we have total expenses, so we could just either add both of them together. But for me, I'll just grab this from over here because it's better practice to do that so that whenever there's something wrong, There's only one place that I need to fix. Okay. So which means I could just fix it inside expenses and then those fixes will propagate misspell to the profit and loss sheep that we have. Okay, so I'll press Enter. Okay, So we have the total expenses over here. And then there's just highlight this tree expense related South and drag them all the way to the month of December, okay, now we have this, we can now calculate the profit. So to be able to calculate the profit that's taken, the revenue and then the duck, okay, minus D expenses. Now we have that. We have profit. What about the income tax? I'm up. So to be able to calculate the texts over here, we need to take in, right, the amount of profit and then multiply it by, remember that in our inputs sheet over here, we have the income tax rate, which we could use with this one, okay? And then since this is the income tax rate, the single cell that we're going to be using, make sure to make it absolute aswell press F4 and there you go, right? And then we have the income tax, so that's 20% of the profit amount. So definitely, I want to remind you, we are not hardcoding values over here, right? Because for example, over here you could just type in 20 percent, right? And that list will work fine. But what if we need to change the value slider, then you would need to go through the sheet and then find the hard-coded values over here. So what we're doing Earth now is since we're linking it, right, this income tax amount, the spell, if you notice all of the formulas that we have, tieback or goes back to the specific sheet so that whenever you want to make changes to your assumptions, just go here and then everything would change throughout the rest of the calculations. Okay, so we have our income tax over here. So what I'll do is that's just drag this o the wake and copy the formulas until December. And it's looking good, right? If I double-click on any one of them, can have a look, right? This is the October revenue minus October expenses, okay? And then multiplied by D income tax rate, right? Okay? And then we have our income tax and profit after tax is calculated by profit and then you just subtract the income tax amount from it. And we have the profit after tax. Okay. I'm just dragging the formula down all the way to December and we have ever been calculated. Now we just have this extra column over here to be the total. You could either just add all the cells one by one until make from January to December. But this is going to take a long time. So what I'll do is let's just go to Home. And then we have the autosome functionality. If I press this, Excel is smart enough to create a sum formula for me. And it's just going to take all of the cells from January to December. Okay, so we're happy with this formula and let's press enter and then we have everything summed up together. So same thing as both for disorder cells. Okay, So we can just copy, right? And then just paste everything in here. And we would have the same formula, okay, a similar formula, right? But the cells would be different because they are relative cell references, right? So the change is small and you could see that the fixed costs, for example, from January to December, our sum together same thing as well for the other rows. Okay, so that's the beauty of debt. And let's just do the same thing as well. If you don't want to copy paste, do the same thing, just go for autosome, right? I'm just pressing it one by one and you can just grab everything. I'll getter. Okay. So what we have at the very end, right? What they did was since I press o some added them together, right? So let's say you just want to have a quick double-check. Let's say let's just add everything and have another value over here, right? We add all of the profit after tax values. Just have a quick double-check. Now you can see that two values are not the same, right? Because what we did over here, Wallace, we sum, okay, we be added, right, the profit and the income tax. So that's just an example of a mistake that you could do. So it's good to just perform some double-checking. Okay, so let's just update the formula. That's incorrect. That's just subtract this instead, right? And we have the same amount over here, okay, so our checklist done, I'll just delete this and now you have your profit and loss sheet.
17. Beginner - Model - Cashflow: Now let's talk about cash-flow. So cashflow is a bit different when compared to the profit and loss. So I'll be discussing that as well on one RD, specific differences between the two of them. Okay, So when we say cashflow, the main goal over here is to understand do we have enough cash to operate the business month in, month out, okay? Which means what we're after is the closing balance positive or not? Because if it's negative, then that indicates the business is in trouble because you don't have enough cash to pay the bills, okay? Right? So what we have for cash-flow, right? This is where for the inputs, assumptions will come in handy. They're going to be very useful in terms of cashflow. So what I'm pertaining to write for website hosting, it's paid once a year. Okay, So let's paste that at the very end of the year, right? And then we have software licenses as well at the end of the year and then we have income tax being paid out quarter d. Okay, so that's going to be every three months. Okay, so let's jump over to cashflow. What we need, right? We have the revenue over here, opening balance, I'll explain that in a short while. And then we have our total expense as a spell, right. And then we have the income tax and our closing balance for revenue, right? It's going to be very straightforward for revenue. Let's just jump over and get our revenue number right for the month of January from this revenue sheet. Okay, So looking good over here, we have our revenue. I'll just drag this over here to populate from January until December. Okay. So for expenses, it's not as straightforward because there's a slight difference, right? When it comes to cash-flow, if you recall, let's jump over back to the input sheep, right? We're saying that support staff, okay. It's going to be paid out every month, right. As salary. And then we have for website hosting once a year, once a year for delighters, this as well. And then for the income tax, it's going to be quartered. So that's going to factor in on how our cashflow would look like. So for the support staff, since it's every month, right? Which means we could just grab the values from this one, okay, that would suffice for our cash-flow because month in, month out, we need to pay our staff. Okay, so I'll just go for equals and then I'll just grab the expense value over here, okay, For the support staff looking good. I'll just drag this all the way to December and we have those numbers. Now forward a website hosting. What do we take in here? Right? Because over here, if you look right for website hosting, it's done monthly from an expense point of view. But for cashflow, the only time that we need to pay, right, is at the very end of the year because it's paid out. And Woody. So what I'll do in this nest, just use the sum formula, right? And I'll jump over to expenses, website hosting. I'll highlight the entire January to December range. Close to formula. Ok, and now we have $480. Okay? So pretty straightforward, right? But you're now seeing on how it affects our cash flow depending on the input assumptions that we have over here, it's very important to have your documentation in place so that it's easy for you to start creating this one by one. So let's jump over to software licenses. Same thing as well, right? It's going to be paid once a year, so it's do the same thing over here. Let's add it up because from our expenses sheet for the license says it's listed out monthly. So let's just add all of the values over here, right? So that's going to be 3600 and k. And then we have 4D printing costs, printing costs, right? It's going to be a monthly spent. Okay? So which means monthly expense. So which means we could just copy over the print costs, okay, from our expense a sheet. So over here, print costs, pretty straightforward. I'll just go for equals and then just link that over here, right? And then we have Homer print costs for the month of January and drag it all the way to December. Okay. Looking good. Now 40 total expenses. That's just add them all together, right. I'm just highlighting the range of expenses over here. Close it. Okay. And then we can just drag the same formula because he's going to add everything up. So you could just double-click and then just have a quick look. Okay. Do we have everything in order? Okay. It's just do hold on. I'll just press Escape. Right. That's just double check. Okay. Looking good. All right. And then let's just do a quick spot check Looking good. Okay. We have the total expenses, we have the revenue. How about four to income tax? So let's jump back to inputs. You could see that it's paid quarterly. So which means every three months. So if I jump over to cashflow, which means we're not paying on January, we're not paying on February, but it's going to be on March for the first payment. So what I'll do is create the sum formula again. Jump over to worse, our income tax. It's over here inside our profit and loss, right? So over here for income tax, since we're going to pay it at the end of each quarter, right? I'm selecting the months. January. Right. Too much. Okay. We're happy with this. Okay. I'll close this up. And then we have our income tax for the first quarter. Now, for the second quarter, that's going to be June, and then we have September, and then we have December. Okay. So you could do the same thing as well. You can just go for some right. As well and then select the months of April to June. But I have a better way, right? I'll just copy this, okay? Because this is the beauty of using relative cell references. If I just paste this over here, right? We wouldn't have, if I double-click right, we have eaten two g ten, okay, so double-check from this one, it'll be ten to the ten, which means it was able to continue. And jump over to column E until f and g and an add them together, you get stats, the power write offs, relative cell references. Okay, so I could just do the same thing. I'll just copy this and then paste it over here. And then paste it as well over here. Okay? So you could see the formulas dynamically change in copying them together. Okay. And we were able to calculate the income tax right, from July to September and then same thing as well for the income tax from October to December. Okay, so now we have our income tax, now we need to calculate our closing balance. So which means what is the amount of cash that we have at the end of the month? Okay, So assuming, okay, since we're starting out over here, our opening balance would be 0, okay? So to calculate the closing balance, we would now start adding the opening balance, okay. And then add it with the sales or revenue, right? And then we subtract the total expenses, and then we subtract the income tax as well. Okay, Now we have our closing balance and then what's going to happen next is on the month of February, right? We're going to be bringing this up because that's the starting amount now for the next month. And then it's going to be the same thing as well, which means the opening balance at it with the revenue and then subtract the total expenses and then subtract the income tax or swell. So which means our formula would be exactly the same as what we did for the month of January. So what I'll do is I'll just copy it over and then I'll just drag it until the very end. And pretty much our opening balance would be the same thing. So I'll just drag this all the way to the very end. So if I just double-check this quickly, the beauty of this one is if I double-click right, you could see that from the November closing balance. That's the December opening balance, right. If I jump over here, I'll just double-check right. For the month of July, the opening balance came from the closing balance off the month of June. And then if I just double-click on the closing balance right, you can see the calculation July. And then you add with the opening balance revenue, right. And then the depth D, expenses and the income tax. And if you have a quick look, and pretty much the cashflow is looking good because every month, okay, the cash flow is positive, which means we are able to operate as a business without running out of cash in any given month. Okay? This is the beauty of numbers, right? Because if we have a look at the very end over here, right? So 800079, $0.320, right? If I jump over to the summary, okay. It's the exact same number for the final total. Okay. Although the numbers in MSL would differ, okay, because it's different from a cash-flow perspective, right? What you're telling here is a continuous story, okay? Because what happens in the month of January, okay? And gets carried to swell to February and the number would have an effect. Okay. For death mother swell and just continues all the way to the month of December. But for our profit and loss, right, each month is independent from each other, right? Because we have the profit for the month of January, but there's no effect on the profit numbers right? Off the month of February. Right. And then it's just same thing for the rest of the months, okay? So the cashflow itself, right? It shows a continuous picture or story of your cash balance. But for the profit and loss, right? It's an individual story, month in, month out. But the beauty of numbers, right? You can see at the very end, you could just quickly check if we did everything correctly. It's the same for the grand total, right? For this two statements altogether.
18. Beginner - Model - Adding Scenarios: Okay, so now that we have completed our financial model, right, we have all of our calculations in place. The next question is, what can we do to enhance our financial model further? And one of them is by adding scenarios to the model. So if you recall, we've discussed about scenario analysis wherein we can have multiple scenarios so that the user can just select a scenario and then everything would get updated right away. Okay, so over here on this table, right? This is the one that we are going to use. We have three cases specified in here. We have the worst-case, base case and the best case scenario. So I'm pretty much Fordist scenarios. We have two fields that we are interested in. Number of books sold per day, and then we have the expense of the software licenses per month. Okay, So with this tree cases, right, what we want to happen is we're going to be adding a drop down because that's the easiest way to create a scenario analysis in here, right? So the dropdown would have this three cases. The user would select them and the numbers will dynamically change based on the selection. So first things first, we need to populate this table with the values. So for the base case, it's like the normal case scenario. So for the normal case, we'll just have a quick look on the original values that we have in here, right, for the number of books sold per day, right? It's 10, so we'll just type in 10 over here. And then for the expense off the software licenses per month, what we have right now is $300, so we'll just add entry box here. Okay, so that's where it a base case. So let's have a look at the worst-case. What's the worst-case scenario? So which means for the number of books, It's going to be less number of books, right? So which mean is, let's just give it the value of five, which means five lessor books being sold per day. So which means less sales. And then for the software licenses, what's the worst-case scenario? So let's say from $300, our expenses increased to $400, which means that's the worst-case scenario. This values are not set instance, so you can change them as well later. And then for the best-case scenarios, let's say from ten, it has increased for our sales, 250 books sold per day. And then for the software licenses per month, right? We're able to cut costs, were able to find better deals. And then our expenses decreased to a $100 per day. So that's our best-case scenario. Okay, so now we are going to create the drop-down, right? So let's just highlight these two rolls over here. I'll right-click and then I'll select insert. Now over here, let's just type in selected scenario and then we'll be adding our drop-down list shortly. So let's place it over here, right on this cell. And to be able to do that, very easy to do. So I'll just go to data, right, and then select Data Validation. So just make sure that you have the cell selected on where you want your drop-down list to appear. So I'll just select this. And then for our choices, we have right, a left because we want that to happen to have a specific list. And then that's the only values that the user can select. And for our source, right, let me just drag this up over here. And our resource would be this tree values over here, worst base, and best case. Okay, so that's looking good. I'll expand this again and I'll click Okay. And there you have our drop-down list. Okay, So pretty cool, pretty easy to do, right? Okay, So the question right now is, what is our next step? Once we have one selected over here, what would be the effect or which means, for example, if we have best-case, right, we want this specific field, right? Instead of 10, it would get the best-case value from this table, right? So that should be 15. And the very nice thing with how we have set up our model. Yes, since all of the values are linked to one another, right? Everything's coming from the input sheep over here, right? This is our starting point, which means any change from this value. Okay, so if this changes from 10 to 15, all of the values over here, they automatically, which means any change that happens here would propagate to all of the shoots. It's because we have done the linking, we have done our proper design. And this is where the design of the financial model like really shines, okay? If you have a good foundation, then making additional enhancements are changes over here would be very easy to propagate through the entire financial model. Okay? So which means if we were going to have a quick demo later, okay, so you can see, hey, everything is definitely being propagated throughout the rest of the sheets. So the question right now is how do we make this dynamic? Which means based on the selection of the dropdown, then the value of this one would change. So the best one is the IF formula. So I'll start typing in if. So, the main goal of this one is based on the drop-down value, okay, get the corresponding value from this scenario table. So let's say if this drop-down value, right, is equal to worst-case, worst-case now, so what value do we want to return? So that would be this, right? The worst-case scenario for the number of books sold per day. Next one if it's not the worst-case. So we will be adding another IF formula, right? Let's do another check for the dropdown value. But it's not the worst-case then if it's the best case, right, what will be returned? So it's going to be 10, this one over here. Okay? And then if it's not the best case, so which means it's not the worst case. It's not the base case, right? Then we could infer the only remaining case would be the best-case scenario. So let's select this value over here, okay? So once we're done, let's close that. Let's close this, the outer IF formula and press Enter. And now you have your formula and you can see you write, since we have best-case elected over here, then we have retrieved 15 from the table. Okay, Really cool. So this is for the number of books sold per day. We're good that this right? So let's jump over to software licenses. Okay, so we want to make this dynamic as well. Same thing based on the drop down selection. This needs to change and get the value from this scenario table over here. So we could do the same thing, right? The F formula, the one that we did a while ago as well over here. So what I want to do for the IF formula is to live it to you as an exercise, try it out the swell to create the F formula for the software licenses. What I want to show to you is an additional tip. Okay, So aside from F formula, another formula that's very well suited for this type, if the switch formula. So let's try it out quickly, right, so that I can show you how it works. So afford a such formula. What you are going to give here is what is the expression or the cell that we want to evaluate. So in this case, it's going to be the cell, the drop-down. We are going to be giving value resolve pairings, right? So if it matches a specific value, then give this an assault. And then afterwards if it matches this value, then give this result. So which means it's just going to keep on checking specific values that we have placed. And then we'll be returning the corresponding result, okay, If there is a matched and return that specific result. So to be able to do that, what is the first value that we want to check? So for example, let's go for the worst-case scenario, okay? I'll type in comma. What is the result, okay? If it's the worst-case, if there's a match with the dropdown, then return this value over here. Okay, Now we can just continue adding cases over here. So the next value that we want to check, right, would be the base case. And if it's the base case, okay, I'll press comma again, turn this value. Okay, so you're getting the pattern now and then the last one, right? We're checking a pace comma and then we're going to check for the best case value over here. And if that's a match, then return to a $100. Okay, Once we're done, right, I'll close it and then press Enter. And now you have $200 because we have selected the best case scenario. So let's try it out, right? Yes, select worst-case. Now you've seen this change to five, this one has changed to 400. And if I go back to the base case scenario, then we have a 10, right? And there we have $300. Okay, So that's really cool. We have made it dynamic. And then what I'll do in a short while, and it's showing you the rest of the sheet so that you can see calculating and updating dynamically. But before we do that, right, we need to make some formatting changes. Since this one is now dynamic, write this two fields are now dynamic. We don't want our user to input values over here, which means we don't want them to just go in here and then type in a different number. We want them to make the change to the drop-down value or to the scenario table, okay? Which means this one. We need to change the formatting to let the user know that, Hey, you cannot touch this anymore, okay? Because this is a dynamic formula. Now what we will do is I'll just go for home and then I'll select any one of the white cells over here. I'll select Format Painter and highlight this so that this is now white collar, so that it signifies to the user that this is not right. And input field, so same thing as well for this one, right? So what I'll do is I'll just do it the old-fashioned way nor feel right. Make it white, and then let's make the font black as well. Okay, so there you go for this one. But we need to make a change as well for this, because this is now an input field, right? So we need to change that to. So what I'll do is damages. Format Painter does not form a painter. I'll just press Escape. And it's better to just do it over here. Let's highlight that drop-down. Let's go to self-doubts. And let's select the inputs now, okay? Now for this one, let's just make a minor change and I'll just copy this format over here to make it stand out more. I'll paste it here and you have your format ready. Okay, So which means we have this one. So it signifies to the user you can change the case, the scenario right over here by the drop-down. And you can also play around with the scenario values, okay? And then this two fields will just update automatically. Now the next question is okay, we want to see the rest of the sheets, what's happening when we're changing the scenario. And one cool tip that I want to share with you is the use of view and then new window. So let's select that and then I'll explain what has just happened. Okay, So what I did was I pressed new window please them side-by-side. So you can see that there's the one over here and then there's a two over here. It pertains that we are playing around with this same workbook. So for example, let's go to the general field over here. I'll just type in a random number. And you could see on the right side, right, it also got updated because we are working with the same shit. But what it allows us to do is I can just jump over here to the revenue and they'll have a leg of good view of both sheets from the same workbook at the same time. Okay, So which means if I change the scenario, we can see what's going to happen to our revenue numbers. So let's change that to the worst case scenario. Okay? So you can see it change, right? Okay, And it's happening to the rest of the sheet. So what I'll do is let me just jump over to profit and loss, right? You could see that since it's the worst-case scenario right now, we are getting losses over here, right? We're getting negative numbers right now, and we're not even making money after an entire year, okay? But let's change this. Remember this number, negative one then five. Let's change this to the best case scenario. And now we're making 41 taus. And over here, everything change, okay? Because it's deriving all of the inputs, the number, the initial numbers from the specific sheet. So you could play around with this one. That's the very nice thing that's in our analysis. So let's go back to the worst-case, for example. So let's say what is the worst-case? You can play around with this, like what is the worst-case scenario, and I can still start making money. So let's say what if we increased the number of books sold per day from five to say seven, would that make a difference? If I press Enter, now, we're making money. Okay, we're making a profit after 7,816. So which means just increasing it by two books or per day makes a big difference. And now you can start making your analysis. You can start understanding more on what you need to do for what's best for the business, okay, So use scenario analysis to your advantage and you can do that quickly inside Excel.
19. Beginner - Model - Charts: So another way to enhance our model is through the use of charts to supplement it. So one really good way to visualize the numbers in your sheet is the use of charts. So over here in Thailand are revenue worksheet. So the question right now is, what can we use here, right, to trade the good chart? So one way is to look at the book numbers over here. And then we just want to show inside a chart just to have a quick bird's-eye view, right? The high level view that which one's self smart. So what we'll do is first things first, let's select the data that we want to show in the truck. So over here I'll just highlight the book sales per month, right? For both books over here. And then I'll hold the control key because to be able to highlight the months at the top, because we also need to provide the time. So I'll hold Control to be able to highlight this row as well. Okay, so which means what we're providing the chart is the data themselves, right? And then also the time duration. So what I'll do is I'll go to Insert over here, right, and then ness select Recommended Charts and then see what Excel will come up with. So there's quite a number of recommendations, right? And then what looks good over here S this specific chart. So we have a stack Harlem, right? You can have a quick look on which one is selling more. Okay, Let's go four, okay? Now that we have our chart over here, we can just move this down. Okay, Then I'll just make this smaller so that you can see the entire chart. Okay, So which means this is a really nice chart that updates automatically based on the data. Okay, so over here we have, you could see quickly that there's a bigger portion. We're in the formulas book, right? This being sold over here. Okay. So you could change the paddle, right? You could just double-click here and then just type in the title, right? And you can also jump over to Teams, right? Over here. There's a lot of styles that you could select under chart tools design, right? You can just change the style on whatever you fancy, okay, There's a lot of cool stars over here to make your chart stand out more. Okay, So that's for the revenue. Okay, Let's have another example. Let's jump over to our summary sheet over here for the profit and loss. So what we can do is have a quick view, let's say for the revenue numbers, right? And the expenses so that we can just compare and let me just close this, compare and see how are we doing every month. So we're going to do the same thing. Let's highlight revenue first, right? That's how our data. And then I'll hold the control key. I'm just going to hold this and then highlight the expenses as well. Okay. And then still holding the Control key, I'll highlight the months as well because we need that for the dates. Okay, we have this tree roles selected right now, let's go to Insert, right? You could either go for Recommended Charts, right? Or you could also select a specific chart from any of this once. But let's take four recommended charts with that, we can just have a look on what are the suggestions that Excel will give us. Okay, So this one looks good, right? We have a line chart that shows both revenue and dandy expensive. So you just have a quick look on how are we performing ESA Business. So let's go for okay, over here. All right. And then you could see that revenue is above the total expenses, right? So, which means So far so good. Okay? But if for example, let's go for D inputs, right? Let's go for a base case or less, just go to worst-case. Let's see our worst-case now it's four. Let's play around with this. In our graph would change, right? You could see that expenses right now are above the revenue, which means we're not making money and we most probably are losing money in, money out to could see that the chart has changed automatically as well. So let me just make this smaller so that you can see the entire right, entire table, data table and chart in one go. So we can see it right? It changes along with your data. So a combined with the scenarios when you play around with this, then that would change. So let's see what if it's the best-case scenario. Let's go back to our chart over here and you can see that revenue is on top again, and same thing, right? I have this selected. Let's go to the sign and you can change these down as well to a different format. So this one looks really cool, stands out, right? And then change the title. You can make changes to your chart and K and make customizations. So use charts to your advantage and it's a great supplement to your financial model.
20. Beginner - Model - Conditional Formatting: Now let's talk about conditional formatting. So conditional formatting is a really nice way for you to be able to change the formatting depending on the c values of our specific conditions that you specify. So for example, we're inside our profit and loss sheet right now. And then let's say you have a target of making sure that profit after tax is one hundred five hundred at least, at least one hundred five hundred per month. Sought to be able to show that visually over here, what I'll do is over here I'll just highlight all of the numbers. And then what we're gonna do is create API conditional formatting rule that if it's greater than or equal to one hundred and five hundred, then let's have it colored green. And then if not, then let's have it colored has spread. Okay, pretty straightforward, but it's going to be cool when we started applying it here. So let's go to home. Okay, make sure everything's hydatid, okay? And then select Conditional Formatting and there's quite a number, okay? But in our scenario, let's focus first on highlighting the cells have score for D greater than over here. And then we can just change this accordingly, right? If it's greater than or equal, we want to make this greater than or equal to, right? But right now the only choice is greater than, but, let's just keep this. Let's make some changes later. So one hundred, five hundred over here and then we want it to be colored green. Okay? So that's going to be colored green, right? But it's greater than, right? But we want it to be greater than or equal. So we can make changes as well as go to conditional formatting. And then let's go for Manage Rules over here, right? You can see it's greater than one hundred and five hundred. That's not what we want. Let's select that girl and then select Edit Rule over here, right? And we can make a change. Let's go for greater than or equal to o over here, right? And then you could also make further customizations to your formatting AND gate. So what I want to show you is you are preset rules that you could select right from the selection I'll click Okay, over here. There are preset. You can just select this one's over here, but you can also manage them afterwards, after you have created them, select, Edit, and then make further customizations over here. Okay, so now we have our first rule, right? This one's good. Let's create another rule over here. Okay? So same thing, right? Since we have this selected as well, we can have another rule for D less than one hundred and five hundred. So I'll go to Conditional Formatting again, highlight cells. Let's select less than over here. Okay, now is one hundred and five hundred, and we want it to be color red. Okay? So this one looks good. Let's go OK. And you can see the rule. Next. Two rules actually we have created have already been applied over here. And you can see the ones in red that stands out there. Less than one hundred, five hundred and the rest, right? There are no color green. Okay, so there's the really nice thing with conditional formatting because if we go back to inputs, let's say, let's change our scenario. It's not the base case anymore. Let's select the worst-case scenario. Go back to summary, and everything has changed to red, okay. Because they're now below one hundred and five hundred. Okay, let's go back again here. Let's select the best case scenario. And again there to go to Summary. And everything's in the green because now they're all above one hundred and five hundred and at least, okay, so that's a really nice thing with conditional formatting. So what I'll do is let me show you another way that we could use conditional formatting. And let's just jump over to expenses. So let's go over to total print costs over here and let's have some fun with it. So I'll highlight all of the values over here to go to conditional formatting. And then let's say, let's just try out the data bars. So the cool thing with data bars over here. So I'll just select, let's say let's go for desk higher. Okay, So the nice thing with data bars, right? It just shows you a visceral way, right? You could see the largest value over here, right? One 530. So you can see that it has most of the data Barfield, right? And it gets shorter for the smaller values. Okay, so we could use data smallness, a visceral representation quickly write on a specific set of values. And there's a lot more that he can play around with conditional formatting.
21. Beginner - Model - Protect Workbook: Now let's talk about protecting
the spreadsheet, okay, so there are a couple of
ways that we can use that to protect specific
parts of our workbook. Okay, so the first one
that I want to share with you is you can lock
the entire workbook. Okay. So the password is required when it comes
to opening the workbook. So it's very easy to do
so and just go over to File and then go
to info over here. And then there's selections
under Protect Workbook. And what I want to focus over here is the encrypt
with password. So when you select this, what's going to happen
is it's going to ask a password from you. And then you could just use debt to be able to open the workbook. Okay, so that's the first
one. Let's go back here. Okay? And then the other
ways that you could do is protecting the workbook. So over here, if I go to
the Review tab over here, right, there's a selection
for protecting off workbook. If I select this, you could also see the description others from making structural changes
to your workbook, such as moving, deleting
or adding sheets. So before I protect
the workbook, right, you could just have a
look that I could just add a new worksheet over here. I could also right-click
and then there's the insert options
over here, right? Because there's no protection
being done at the moment. But if I go for
Protect Workbook, for example, you can type
in a password as well. This is optional, so
I'll just click, Okay. If I right-click right now, you can see that the options for adding sheets were leading, renaming, moving as well,
Darrow and disabled. So this protects the
structure of your workbook. And if I remove this, I could just click
on this again since I don't have a password, right? You can just right-click again and everything is now enabled. Okay, so that's pretty much
it for Protect Workbook. What I want to focus
is the protect sheets. So which means for
protect sheep, you could do a lot
of things over here. I want to focus on prohibiting
or restricting the user to up the only specific cells inside this input worksheets. So when I say restricting, which means we only want them to change the values listed
out over here, right? The ones that you
can just touch and play around and
change the values. So let's try it out. Let's go for Protect
Sheet over here, right? You can also type in a password. But for now let's just
leave this as black. Let's go okay, over here. Alright. So when I try to make a change, I'll press over here. Kenneth, make a change. That's good, that's good, right? So that they cannot change
the values over here. I'll just type again,
can't make the change. But the question right now is, even in this one, if I
want to change the price, I can change it as
well because we have protected
everything in here. But the cool thing with Excel is we can make this selective, which means for specific cells, we're allowing the
user to make a change, but for the rest, we're not allowing
them to change it. So what I'll do is
I'll just select this and unprotected
sheath again. Alright, so now we
can make changes, right? I can just type in. But right now there's one additional step
that we need to do. And it's making sure
that the cells, this one's over here
can be changed. To be able to do that, let's highlight all of the cells first that we want to
allow to be changed. So I'll hold the Control key. And then I'll select
all of the cells over here that we're allowing
the user to change. So let's select all of them. Hold the control
key all the way. Now we have all
of them selected. I'll just right-click and then I'll select Format
cells over here. And now just go to
protection over here. And then you could see
that he could take loved. If I untick this, which means even if I
protect the worksheet, they won't be loved. Which means the
user has complete control on making
changes to these cells. Really cool, right? So go for okay, right, so we have applied
to change right now. And then if I select protect
ships over here, right? Let's just go for okay. Now if I try to make a change, let's say let's just change
the label over here. No, you're not allowed. The sheet is protected. But if I jump over
here from awhile ago, let's change this to $50. You can do that
because we have done that additional step which allowed all of the
cells over here, right, to be able
to be changed by the user even though the
sheath has been protected. But for the rest, if I tried to change this, no, you're not allowed. But if I change this,
you're good to go. Okay? So that's for protecting
the sheet over here. The last step is the
use of drop-downs. So what we have seen inside
the scenario analysis, you can just create
a quick drop down through the data
validation over here, right, for data, and then
select Data Validation, you can make changes. So which means if you added a drop-down list and you're
setting it up so that the user can only have specific values that
they can choose from. Which means you're guiding them on what are the possible
choices that they can make. So you can apply
the same principle of using the drop-down list on any field that you want to guide or restrict the
user on making inputs. So this are the different
ways that you can use for protecting your
financial model workbook.
22. Intermediate - Modules in the Course: Hi, this is Ryan Hall and we're going to be discussing about the intermediate module, financial modeling. Okay, so I just want to walk you through on what are the modules that we are going to be covering inside this course. Okay, so for the first one is about the introduction, is just about the design principles like what are the best practices that we need to do when it comes to creating our financial model. Okay, so we're going to be focusing on that. Next module is about the Excel core concepts. Okay, So we want to discuss about the foundational like concepts that you need to have in Excel so that it's very easy when you start working with your financial model. So you can see over here what I have is I have open right now for the car concepts. So we have a couple of Excel formulas that we want to cover and then we're going to be jumping over to forecasting. Alright, mixed references, right? How do we handle this type of cell reference named ranges and then external links. So that's pretty much for D XL car concepts. After this module, we're going to be jumping over to financial modelling skills and techniques. So when you say techniques, like for example, error checking, how do we do that inside our model? And then we're going to be using gold sick, right? Then we're going to be computing a couple of financial concepts as well, like the cost of capital, the NPV and IRR. So don't worry, some of this day might sound foreign to you, right? But on the explaining, each one of these, what do they represent and how do we calculate this numbers? Okay, So that's pretty much for D, financial modelling skills and techniques. And then for the last module, right, this is where the fun part begins. So this is where we work on our business case, right? How do we evaluate a given project? Is it profitable? Right? Is it worth our time? So that's the answer that we want to get when we start working with this financial models. So if you've taken the beginners course, right? When it comes to financial modelling, we'll be building on top of that, right? And taking it to another level, making it the full-fledged business and then seeing if what are the numbers that will come out of it. And that will tell us if this is a good project to invest in. Okay, so stay tuned and we'll be jumping straight to financial modeling.
23. Intermediate - Design Principles: Okay, so now let's discuss about the design principles when it comes to creating our financial model. So we want to discuss about the best practices so that we have a financial model that's robust, right? And then it's very easy to maintain in the long run. Okay, so what I have open over here is our completed financial model, right? So don't stress over the details, over what you're seeing right now. Because we are going to be creating this step-by-step later in a future module. But what I want to show you is true this completed model, right? I want to show to you what are the design principles that are being? Demonstrate it inside here. So I want to touch over four important points, right? The best practices. And the first one, I'll just jump over here. The first one is using one formula per row or per column. So which means if we have a specific rural, we want to make sure that it is serving one consistent purpose, same thing as well, for a column as well, you could make it consistent across the entire table, then that's even better, okay, That makes it even easier to read. So for example, what we have over here, right? If you just jump from one cell to another, right? If you have a look at the formula, don't try to interpret the meaning or muddied is trying to do, but just have a feel off the structure, right? If I jump from one cell to another, right, you could see the structure looks fairly consistent, right? For the formula, right? You haven't, if you have to end right? There are some inflation rate over here and then you have power over here, right? It's very consistent across this one. And this is what I'm trying to demonstrate over here. Pretty much this entire table is using one single formula, okay? So when she is very easy to read and very easy to maintain, because if you want to make changes in the future, right, then you could just make change in one and then apply to the entire table, okay, So this is less prone to errors and maintenance. Very easy, lot easier. This also demonstrates the Excel concept of mixed referencing, right? You can see the dollar signs over here, but don't worry, we'll be discussing mixed referencing in detail as part of our XL core concepts so that you can use that to achieve consistent formulas. Okay, so that's the first principle. The second one is leveraged using multiple worksheets according to the intended purpose. So for example, you can see over here we have multiple worksheets in our financial model, we have inputs, we have revenue, staff, expenses, marketing analysis, and then our summary page. Okay? So when it comes to working with financial models, it's possible right, to just put everything inside a single worksheet. Because the reason, the reason for doing that is you don't need to jump from one worksheet to another when referencing different values, okay, when you need to get one value from another. So for example, let's say on the summary page, I need to grab a value from the revenue. And sometimes we get lazy in doing that, and I'm guilty of that as well. So the easy way to do that, it's just placed everything in one single worksheet, right? So that you could just refer to the cell inside the same worksheet, right? That's easy, that's easy to do. But in the long run, okay, if you don't segregate it according to purpose, right? Then you will have to restructure and it takes a bigger amount of effort in the low crime. Okay? So you need to divide them logically and keep it in an organized way. And I'll be showing that on how we can do that inside our model. Number three is about documentation. So for documentation, it's applicable to all the worksheets inside your model. But I cannot stress the importance inside your inputs worksheet, right? Because as inputs, right? This is where all of your assumptions or basicity starting point of data that your user is going to place values in. And you need to document very clearly inside your inputs worksheet, okay, so the main idea is to leave no stone unturned, right? You want to make sure that everything is very clear. You want to ensure that your users will understand everything on how to use this model, okay, so for example, over here, right, we have specific data documentation placed over here based on 2019 data. So did you have a background on where your data is coming from or what do you want to be placed inside this field? Those things, okay, you just want to make it very clear for the user and very easy for them to understand that. Okay, where did I get this? How did you calculate this right? Or this one, for example, is also used in revenue so that you know that, okay, it's not just expense related, but it's also double like use h, right? There's a WASH. when it comes to revenue calculations. Okay. So make sure that you have documentation in place. If let's say there's some points that you think might be confusing to the user. Didn't type it out over here and explain it clearly. Okay, So that's number three. And then number four formulas should refer two cells, right to its left and above only. Okay, that sounds a bit convoluted, but let me just demonstrated quickly over here, right? Let's say, let's pick this specific field. If I double-click on this, you can see that the formula, right, it's referring to cells above it, right? So the concept over here or different design principle is you want to create formulas, right? None are referring to cells above it or to the left of it. Okay? So which means you cannot, let say this specific formula over here cannot refer, so make downwards or to the right, okay? Because what this will do for you, right? This will make it like reading formulas more logical and also forces you to design your table in a way that would enforce this. And okay, if you have a look at this table, what will happen now? Because all of my formulas, right, I referring here to the left or to the above cells. You can now clearly read this specific table from left to right and then from upwards to downwards. Okay, so you can read it in this direction over here. And gate because we have designed our formulas display, which means from a logical standpoint, when you try to read this and then try to understand this, then it becomes in this clean and structured manner. Okay, So it pretty much applies to all of the sheets that we're doing a swell over here. And you will see that in action when we start creating our model in the future module. Okay, So just to reiterate the four best practices, the design principles, number 1, you want to use one formula per row or column, or even better, 4D and tar table. Number two, you want to leverage using multiple worksheets according to their purpose. S what you can see over here at the bottom. And then number 3, documentation. Documentation. I cannot stress that enough. Okay. And then number four, yes, formula should refer to the cells to its left and above.
24. Int - Excel - Aggregate Functions: Okay, So when working now with aggregate functions, this is one of the crucial concepts in Excel that we're going to be using a lot when it comes to our financial model, okay? So when we say aggregate functions, what I want to focus on just two functions over here. You have the COUNTIF formula and then we have the SUMIFS formula. Okay, So for COUNTIF, what it's going to do is it's going to be counting how many values that will meet your specific criteria. And I just want to show you on how flexible write this formula is because we have a couple of scenarios that we want to work with. So for the first one over here, we want to count how many values are greater than two. So if we just have a look over here, right, greater than two, that's going to be 1, 2, and 3. Okay? So we are going to be expecting tree values over here. So it'll be able to do that. And that's just type in COUNTIF over here, right? Then it's going to ask for the range fan. You step, you want to check. So which means I'll just highlight this specific range over here of four cells. And then what's the criteria? What we want to check, right, is greater than two. Okay? So the cool thing over here is you can start writing expressions are specific criteria that you want to check by the COUNTIF formula. So I'll just press enter. Alright, and we have three over here. Okay, so that's correct. Next one is I want to show to you that you can also check for specific values. Let's say given this range, this list of values over here, how many, right, have the value of yellow, which would be this and this. So that's going to be two. Okay, So let's try it out. Let's type in COUNTIF over here, right? Same thing for the range. I'll just highlight this for cells over here and to be able to type in yellow. Okay? Yeah, little over here. Okay. And also don't forget to use the quotation marks, that open and close quotation mark. And then after that press Enter and we have 22 values off yellow. Okay? Now there's going to be a cool way to use the COUNTIF formula. So over here we want to check how many values over here, how many names, right, starts with the letter J and K. So we have one, john, jenny, and it jumps, so that's going to be three. Now, let's give it a shot. Let's type in COUNTIF, okay? And then the range same, okay, I'll just highlight the four cells over here. And how do we write the criteria? So for here, right, we want it to start with the letter J we can use, right? It's typing the letter J first, and then let's use the asterisk. This functions as a wildcard character. It's like what we're saying is, okay, Just check first it does it start with j, and then after that, whatever follows to the right, it could be anything. Okay, So what you're telling Excel is, or the COUNTIF formula, just look for the values that start with the letter J, right? And then what follows after that? I don't care anything good. Okay. If I press Enter and we get treat a slow, okay. So if I add another one that's a, that's a jail over here, and then now we have four values, okay? So it's able to continue working even after changing our values. Okay, So that's pretty much for the COUNTIF formula. Now, let's jump over to sumifs. So if over here sum is in a way very similar as well to COUNTIF. So some, if it allows you to sum the values in the range that you specify that meet a specific criteria. So for example, over here, if you want to sum right, the sales, all of the sales values that are above $300. Okay, So this is the best function or formula to use for a specific scenario. So let's say over here, right, That's type in sum if over here, okay? And then we have this specific range net V1 to check for the venues that are above 3000. And then what we're telling Excel is okay, get those values and then sum them altogether. Okay, So let's say for D criteria, if you are now familiar with the COUNTIF, it's very similar as well and how we write the criteria. So when we say above 3000, we're going to be typing in greater than 3 thousand over here, okay? And then we have this value right away. So let's do a manual check. Let's say an S just add the values that are above 3000 over here, ok, so that we can just verify, does it work? Does it really work right? On how we are using the SUM IF formula? Okay, so I had a manual check over here and there are just three values, this tree values over here that are above 3000, that's first enter, add them altogether, okay, somehow the formatting is different, so I'll just go for Format Painter and then paste this over here, right? And you could see that given this specific formula, okay, we have the same result, which means our sum if it's working perfectly fine. Okay, so that's it for COUNTIF and the sum if Excel formulas.
25. Int - Excel - Lookup Functions: Okay, so we're going to be discussing the different lookup functions in Excel. And it's a very important concept that you need to master when it comes to working with financial models are pretty much in Excel as a whole because we're going to be using this functions like a lot. Okay? We have pretty much VLookup, HLookup, and then lookup formulas. And we're going to be discussing one by one on what are the differences between them and how you can maximize them. There's also actually the newer extra lookup, right? But for the purposes of when it comes to working with our financial model, I want to use this tree formulas because it applies to all versions are a lot of versions for the majority of Excel, X lookup is a newer formative, although it's more flexible as well. If you want, you can also check it out on how you can use that if it's supported in your Excel version. But for now, let's focus on this tree. Okay, so when we talk about VLookup, this is like the most, one of the most used functions or formulas in Excel. But it can also be tricky to understand. So when we say V, V is for vertical. So which means this is best if you see that the table is in the vertical orientation. So for example, over here, right, you can see our table over here, right? The columns, the column header on the first row over here, right? That's very much applicable for the VLookup. So what does it do? So over here we have our sample, right? We have, Get me the price of a laptop and tablet from the stop lists. So over here we have a price list over here, right? We have the prized off the different items and we have the costs values as well as the different items. But what we need is the price. So which means based on the value over here, the laptop, right? We want to get from this table and search for the laptop value and then returned to price. So which means we want $185 to show up over here. Okay, so how do we do that via a VLookup. So I'll just walk you through nets, get into the action. And then first things first is the lookup value. What are we searching for? Okay, what is the value that we want to use to search in our table? So we're going to be selecting this cell over here. So that's the value that we want to check. And then let's select what's next table array. So which means this is the table that we want to search and then get the resulting value from. And what is the table, right? This is going to be our table over here, okay? So you could select either this one or you could also select this one because these are just the values that we want to work with. Okay, so let's say what's going to happen next. Notice that I made a mistake over here. Let's just update the formula because somehow the lookup value was overridden. So I'll just select the lookup value first. And then let's do again the table array over here. So what VLookup will do is it will, let's say get this value laptop. And then there's a limitation that you need to be aware when working with VLookup. It will just match the value always on the first column. So which means it will start looking over here and then match the laptop value with this first column over here, okay, based on your table array. Now, what is d column index? So when we say column index. Where do we get the value to be returned as a result, okay? So over here, what we want is the price. So when we want the price to be returned, we check which column are we getting the value from. So it's going to be column number one, column number two, column number three. So which means counting is from 12 tree over here. And the price is in the second column. So you can see I've labeled this for you so that it's easier to visualize, which means, let's type in number two. We want to get the second column value, which means search for the laptop value and then return the second column that comes with that specific match. Now for the last one for the range lookup, this is also very important because VLookup is able to do approximate matches or exact matches. Okay, so when we say exact match, we want laptop to be the exact same value and then return that specific column that we had selected. So which means in our scenario we want it to be false because we want it to be an exact match. If I press Enter, right, and then we get 1 eight to $5, which is this one. Okay, so let's do that again for the tablet that's tried it out, right? The same principle. You want to search for the tablet value, right? And then the array. This will work as well if we do that for this specific table. And then which column that we want to return, okay, which means the price. So that's column number two, and then it's going to be an exact match. We want the exact word tablet to be matching this table and we're going to have it as false. If I press Enter, right, we have 245 as well. So which means I'll reveal lookup is working perfectly fine. So now let's jump over to HLookup. So when we talk about HLookup, think of it as a VLookup. They're very similar to one another. The only difference is instead of working with vertical tables, as the name implies, it starts with the letter H. So you get to do the same thing with horizontal table. So over here we have a horizontal table that we want to search, which means when it comes to matching values, it's going to be searching for the value in the first rule. Okay? So if you recall for the VLookup over here, right, we are searching for the value in the first column that we have specified for our table right over here in HLookup, it's going to be searching for that value in the first row because we're saying that our table is in a horizontal orientation. Okay, so let's try it out over here. What is the price AFI television. So which means given this one, we want to search for television, right? And then we want to get the price over here. Okay? So let's try it out. Let's say HLookup, right? And then lookup value, right? Let's type in television. Ok, we want to search for that term. What is the table array, okay? So you can either highlight this one or it could also highlight this one, right? So it's up to you on how you want to specify your table, okay? So let's say for us, we want this entire table to be highlighted, Okay? And then the row index number, what is the value that you want to return? So if you think about it, right, HLookup also has a similar limitation with VLookup were in the matching always occurs on the first row. Okay, So given your table over here, it's going to look for television over the first row over here. Okay? Now, how do we return the row index? Which value do we want? We want the price. So it's the same numbering, a swell similar with VLookup. We're going to be counting from one to n tree, okay, 123. So what do we want to return? We want to return the price which is row number two. So we're going to be typing in 20 over here. And then same thing. We want it to be an exact match because we want it to search for deck sec, we're off television. So let's go for false over here, right? And press Enter. And if we have a quick check, is this the price of the television, right? That's $150. Okay, so now let's work on the second example. What is the cost of a tablet? So it's going to be this one tablet over here. And then we want to look forward to cost which is $90. So that's C is the HLookup right now. It should look a formula for the lookup value. It's type in tablet, right? Then for the table that array, and let's just select the entire thing, all right? And then for the row index number, so what's it gonna do is it's going to search for the tablet value from the first row, right? And then what do we want to return? We want to return cost, which is row number 123, right? Cost is row number three. So I'll type in tree. And then we want it to be an exact match because we want it to search for the exact term of the tablet. It's press Enter. And we now have $90 over here. So that's for the HLookup and VLookup, you could think of them as very similar. The only difference is how is like the orientation off your table? Is it vertical or horizontal? Now, let's jump over to lookup because lookup also serves a different use case scenario over here, okay, it's useful for other things. So for the lookup, right? This looks up a value from a table RA or a one row, one column range. Okay, I'll demonstrate that quickly. Okay? The limitation of the lookup formula is it's always an approximate match. If you notice in VLookup, in HLookup, you were given the option to specify the wanted to be an exact match or an approximate match for lookup formulas, it's always approximate. And I'll show you on how useful, right, how you can use that to our advantage over here. Okay? But the advantage of the lookup formula as compared with the, compared with the VLookup or HTML got Bragg is, you are not limited to just matching in the first row, if you recall. Okay, first row or first column, if you recall, right, for lookup for the VLookup over here, right? You're always limited. You're matching would be on the first column. If it's on the HLookup, you're always limited to matching on the first row. But the lookup, right? It doesn't have that limitation. So let's try it up. And one of the very crucial assumptions, right, is for this table over here, if using lookup, your data right here, data to be sorted in ascending order and they'll show that to you quickly on why that's crucial. And k, So let's say you want to use this specific table. So we have a tax rate table. So if your income is greater than or equal to a specific bracket, a specific range, then that's your tax rate. So for example, if it's 10 times 100 over here, then if your income is greater than or equal to this specific amount, then you need to return that specific tax rate. So let's start working on our formula is type in the lookup over here. So what do we need? We need the lookup value. So that's going to be this one. This is what we want to use when it comes to looking for the specific tax rate. Now for the lookup vector, which range of values that you're going to be checking against. So it's gotta be this one. Okay? And then next one is the result vector, right? What the specific value that you want to return base on searching cross this specific range for the lookup vector. So if I select this one right before the tax rate, That's what we want to return. But somehow my formula and write it's not correct again. And then we just update that quickly. So we have the lookup value of 10 thousand over here, right? And then we have the lookup vector, which is this range of values because we want to check for 10 thousand against this range of values. Okay? So which is why it's very important to have the lookup vector in ascending order. If you've not astride, the values over here are in ascending order and now afford a result vector, which is this one. You want to return the specific tax rate. So that's just complete the formula. Press Enter, right, and you get 18%. So let's just analyze on what lookup has done for you. So with the 10 thousand, right, It was able to determine that it would fit in this range, right? Between 8,456 and then 15,874, and it would return this specific value of 18 percent. So that's an approximate match because it doesn't need for the value to be exactly 10 thousand over here, right. And then he was able to determine that this is the tax rate for you. Okay. So that's really cool. This is where it comes in handy when it comes to the lookup formula. And then another thing to notice is you were able to specify two range, like two ranges of values over here. One range is the lookup, right? The lookup range over here, okay? And then next is the result, which means it's not similar to the VLookup because for the VLookup or a shuttlecock, right, you were forced to select the entire table and then the matching always happens on the first row. But right, for the VLOOKUP formula, you are able to specify them, separate these. So which means even if our lookup vector right is located over here, then you could do that. And then let's say your tax rate is located over here, right? You can do that. It's not limited to the first column over here. Okay, so that's the cool thing with lookup. And one thing to note, approximate matches. They are pretty useful when it comes to working with commission bonus rates or income tax rates wherein you have a range or a specific brackets that you want to search for. So you can use the VLOOKUP formula for this specific purpose. Okay, so that's pretty much it for the lookup functions, the VLookup, the HLookup and then the lookup. So you're going to be seeing a lot of this ones when it comes to working with our financial model.
26. Int - Excel - Forecasting: Now let's talk about forecasting in Excel, right? We can also use formulas to forecast specific values in the future like you're making predictions. Because when it comes to working with financial models, we don't have complete data all the time. So for example, over here you can see we have a list of dates over here, right? And then we have the total sales about that after July. We don't have values over here, so we can use forecasting to do this for us, okay, to predict values in the future or to fill up specific values inside our table. Okay, And that's really handy because we're going to be using that as well for our model data. Okay? So for example, in forecasting to be able to visualize this better, okay, what I'll do is given this table of values, right, we have sales across different months. I'll just go to Insert and then let's say let's just go for Recommended Charts and let's just jump over to a line chart. Okay, So I just want to show you visually on how this would look like if we were to plot our sales values right inside a chart. So you can see over here and Forecasting pretty much what it will do is I'll just click the plus button over here, and then I'll select the trend line. So which means based on the specific set of values that we have right now, then Lot Excel will do, it, will predict on Where's it going, What's the trend? And then it will start predicting values over here. Okay, so we have this one, right? This is, our goal would be, but the question right now is how do we get this values over here? And the cool thing is, Excel has the formula for death. So this one, the forecast, ETS, so EDS, Okay, it just stands for exponential triple smoothing. So it's just the methodology on how Excel is using to be able to predict the values based on historical data or the values that you have provided. Okay? So the target date over here would be this one, okay, That's for the first parameter, okay? And then the values, what are the values that you want to use Excel as a basis for forecasting. So afford to values it would be the previous values over here, right? And then I'll just press F4 to make this consistent because for the rest of the cells over here, pretty much our range of values would be the same. And then for the timeline, the dates, the matching dates for the values. We'll just select this range over here. So this is very important, right? The vagus that the place right, and the range for the time nine they should match because it's like what Excel we will be doing is, okay, this is the first cell, okay, for the timeline and the value second cell, okay? But if you do this incorrectly, then you would also get an incorrect result. Okay, so for the purposes of this demo, we're going to be focusing on this tree parameters, okay? And then it's just close this and you will now see that Excel was able to compute on what it could potentially look like when it comes to. The future value. So what I'll do is I'll just drag this one down because we're gonna be using the same formulas across everything over here, right? So if we have a look, okay, somehow it's still the same over here, so let's have a look. What did we do wrong? Okay, So what we did wrong if we jump over here, right, we did not fix or make absolute on this specific timeline over here, ticket see once it jumps over here, right, you have our venues for Qian war to July, but somehow our timeline is from February to August over here. Okay. So that's the mistake that we did that I did. So what I'll do right now is this just change this quickly, right? Let's make this absolute 4D timeline for both values, right? And then let's just drag this down again all the way to the bottom. And let's have a quick check on any one of these formulas over here. So let's say this one, right? Looking good. We have the February over here as our target date. We have the values for the historical data and then we have the timeline as well. And it was able to predict at this specific point in time, this is the potential value given the trend of your historical data. And if you have a look right now on your chart, the cool thing is our values now exactly match the trend line. Okay, So which means we were able to create the formula representation of how Excel would be able to create the trend line, okay, for the forecasts that values. So that's pretty much for D forecast formula over here, right? I have pretty much the same, exact same table over here because I want to show you another capability inside Excel. This is the forecast sheet functionality and this is another way for you to customize your formatting. So what I'll do is given this table, I'll just jump over to data, right? You can see over here under the forecast group, you have the forecast sheet. If I select this, it's able to select the table, right? And pretty much what we're doing, it's very similar to what we have done over here. The only difference is you have a way to viscerally play with the different settings, which is really cool over here we have the options for seasonality, right? If you're dealing with seasonal data, then you can select or set it over here. You can start with the forecast start. And then for pests n, Let's say you want it to be a longer date range and you can set it over here, 40 forecasting. Now we have the confidence interval, right? And then what are the different methods that you want to use when it comes to forecasting or data, okay, so that's really handy when it comes to working in Excel, you have different options. If you want the formula option, you can do that. That's what we did a while ago. And then you can also use the create forecast, right? Functionality with this forecast sheet bottom over here.
27. Int - Excel - Mixed References and Named Ranges: Okay, so now let's talk about mixed references and name bridge and mixed references. This is a very crucial concept in Excel that we're going to be using for our model. So if there's one topic that I want you to master for short, It's going to be this because it's going to simplify your formulas by a lot as they're going to save you a lot of time when it comes to creating them because you have this flexibility, artist skill, okay? So if there's any exercise that I want you to do and like and follow along with me, it's going to be desolate. So for example, it's a very simple example of calculating the interest amount. So we have the amount over here, right? The investment amount for example, and then we have the interest rate, so we just want it to multiply over here. So very simple, right? And then let's say we want to multiply one by 3%, okay? And we want to multiply this again, right? By 3.5%. And that's going to take us forever to calculate everything. But okay, let's say I just want to make it easier to say I have this formula over here and let's just try to copy, okay, and then I'll just highlight the entire table. And then let's select pace over here, right? But you would notice right away that we're doing something incorrectly, because if I select this one over here, okay, It's not 100 times 3.5%. It's this cell over here, right? Pipes 3.5 per cent. So the question right now is how do we do that correctly? So I'll just click Undo over here. Okay, That's just delete these two formulas. So how who we worked with a single formula that we can just copy paste across the entire table and it will still work correctly. So here comes mixed references. So what I want you to think, right? Let's see, Let's work first with the first cell, a2, okay, I'll just delete the B14 now, a2, which would be this specific value in this entire table, okay, for the entire table, what are the values that we want to work with? So it's going to be 100 and this one until 5000 over here. Okay? So which means, which means what you're seeing is a, the column me would be constant. Okay. So what would be moving? Okay, we wouldn't be jumping from a2, A3, A4, A5, A6, which means a would be constant across all of them. Okay, So if I press F4, then we would be able to specify like rotate between which ones do we want to be fixed or absolute? So I'll just do this quickly. If I press F4, right? He could see the column and row being absolute or fixed, right? If I press F4 again, and now we have just two, right? Which is fixed, which is our row. If I press F4 again, then we just have the column is fixed and this is what we want. Because what we're seeing here is we're just going to jump from row 2, row 3, row 4 or 5, or 6, right? Butt. Keep the column constant. Always keep it at column a. Okay, so I hope you are getting this one. Let's jump over to the next cell. What we want to happen right now is get the interest rate. So this one over here. Okay? So what did we want to be fixed? If we have a think about, we always want to get the values to interest rates right? From this specific row, okay? It's always rule number one, always one, right? It's going to be b1, c1, d1, et cetera, until g one. So I'll press F4, okay, until we get to the one that we want to happen, press F4 again. And now we have the dollar sign attached to B1, which means it's going to be fixed, okay? It's going to be the absolute for the specific row in that is what we want. Okay, so now we have our complete neck forming over here. And this is where the magic will happen if we now press Enter, okay, so looks good when 1000 times 2.5. Yep. But now, what if we copy, right? And then we start pasting that exact same formula across the entire table. And now let's say, let's just jump over here. You could see, right? It's now 5000, multiply 25 percent because it was able to retain the specific absolute references, right? We specified the absolute reference on a over here. And then we wanted the one aswell to row 1 over here to be constant. Okay, so that's the really cool thing when working with mixed references. So if you like, you find this confusing, what you can do is when you work with formulas, you can just have a quick look over here. Let's say I'm working with this one. What's constant between my values, the values that I'm working on. It's called them a. So which means we want column a to be fixed. And then for this one, for the interest rates, what's constant between the interest rates? And that's rule number 1. Which means for our formula, we want the dollar sign to be right next to row number one. Okay? So if you are confused neck, okay, is it the column or set the road and you can work with this technique. I'm showing you right now. And you can just use the F4 keyboard shortcuts so that you can just rotate right now you don't need to type in the dollar sign manually to your references. Okay, so that's for mixed references and we're going to be using that a lot for Excel formulas. Now let's jump over to name range, so named branch. Okay, we're going to be showing this quickly to you, is, let's say we want to calculate unit salt, right? And then the total amount over here, so which means 10 times 20 dollar over here, 30 times 20 dollar. So let's say, let's work first with mixed reference. So it's going to be easy for us to do, right? Or it's just an absolute reference. So it's going to be this one, right? Multiplied by this. Okay? Let, let's have a thing for 83. What's constant between the value set that we're working with, That's column a. Okay? So I'll make this camp pressing F4 right now, okay? We're making column a constant. And if, How about this one? It's always be one, right? For all of the values because it's this 30 times 20, 50 times 20. So which means this is absolute. So I'll press F4. And now we have the absolute reference to cell B1, which is $20 the price of the book. If I press Enter, right, and I just drag everything down or just copy paste the same formula, then we have the correct value. Let's double-check this one, right? 100 times $20. So looking good. But there's another way. If this one, let's say we want it to be more readable for our formulas, then we can give this a name. So let's say for this one, let's type in, let's select this cell B1, okay, and then let's say price. You can give it a name over here and then let's just delete everything. Okay, Let's just did everything over here. And let's create the formula again. Okay, so I'll just change this again, right? We want column a to be absolute and then multiply with this specific cell in. Once I select this, we have our named range right now, look price, and that's really cool with Excel. Okay? And then let's say you can also type it in and you would get book price if I just select this right, and then you have that specific name inside your formula. And if I just drag this all the way down, right, then you would see everything would refer to that same book price as well. So that's the really cold day with name branch and named ranges, right? It's not just to a specific cell that, but also select a range of cells and then give it a name so that when you refer it to your formulas, it becomes a lot more readable because over here you can see that, okay, I'm multiplying it by the price of the book. And that's really easy to understand and to be able to find out because of course I see your worksheet or workbook grows. You could have more named ranges and to be able to search for or check all of the names that you have. It just go over two formulas, right? And then we have the Define Names and the Name Manager if you select this, yeah, and you could see that, okay, this is still one that we have just created right now. And you can just edit, you can delete or just create a new one. Okay, so that's pretty much for name rages and mixed references.
28. Int - Excel - External Links: Now let's talk about external links. So external links actually are very useful when it comes to your financial model expanding. And it's an important concept that you need to understand because for a lot of people, right, There's assumption that external links are evil or dangerous, complicate the teeth are dangerous, lead to lot of mistakes so people stay away from it. If you mentioned about external dataset in the, could just list a bunch of reasons right? On who I, external links should be avoided. Okay, so what I want to clear out this external links are very good and right, when it comes to updating and keeping your data up to date. And it only becomes cumbersome or troublesome if it is used in correctly. So what I want to show you is explain what external links are all about and also what are the best practices that you can do to make sure that your external links, like do not get out of control. Okay, So over here what we have is the concept of external links. It's very simple, is just linking, right? Or getting a cell reference from a different workbook. So you can see over here we have a bunch of values. So we have salesperson ID over here, right? We have the FirstName, LastName, and nationality. So just for the sake of discussion on what I did over here was this values over here, right? They just came from a different workbook. Okay, so it's not from this workbook. You can see over here, there's the path, Okay, excel was able to store that. We have the exercise files of links and then it's under this specific workbook, write sales data to and then this specific worksheet inside that workbook and that's the cell. Okay. But what I did was the sales data to, I've moved it to a different location. Okay, So which means Excel now won't be able to find the latest value from the workbook anymore, okay, because the external link has moved somewhere else. So if I press Enter on this one, okay, what Excel will tell me, right? It's going to ask for that specific workbook like worse it, okay? Because you're using this external link and it's not valid anymore. Give me that specific workbook. Okay, so to be able to fix that, you can do that and search for the workbook where have you moved it or if you've renamed it as well, you can point it to the updated workbook. Okay. So which means excellent is smart enough, okay? But what it's doing right now is it's also smart enough to retain the current set of values that it has stored over here. Okay? But it just the drawback, right? Because if I move the file to somewhere else or if I renamed it, right, or if I renamed the worksheet for example, then it won't be able to get the updated value anymore. Okay, So one quick way to be able to do that for dead links, because that's a dead link. Okay, we have all of this dead links over here, is you can go straight to data, right? And then you have the edit links over here. And you could also have a quick look, right? Okay, This is something that needs to be fixed as saying that okay, sales data to over here, this is the location that what it knew from before. Okay, then what do you want to do with it? Okay, You can change the source, you can update values, okay, you can try to open the source, or you can also break the link. Let's say you are not happy with this external link anymore because it just move somewhere else, then you're just happy to retain the values. Then you can just select break link over here. Okay? So it's saying that it's going to permanently convert formulas and external references to their existing values and it cannot be undone. Okay, So do you want to do that? Let's say let's just break the links. Let's go close over here and you could see right off the values are stored as it is. And now you don't have your external things. But the drawback of that one, right, of this approach is you're going to lose the automatic updates, right? If the source file changes to external file changes, then you won't be able to get the values anymore. So that's pretty much the main benefit of using external links numbers as they have values, they will automatically update from that target file that you're using. And it will save you a lot of time. Okay, So that's for external links. And what I shown you right now is a way to deal with if the external link somehow became a dead link. Okay? So what I want to show you right now is a specific example in action. So we have this specific file over here, right? We have the financial model that we used in our beginners course before. But there's, if you haven't seen that, if you haven't understood completely on how this was made, no worries, because I just want to show a specific use case that is really beneficial to use external links. So for example, we have this specific financial model, just very basic revenue expenses, cashflow and summary. And then we have our inputs page at the very beginning. So let's say we are going to be creating multiple financial models. We're going to expand this more, right? And then we want to use our inputs worksheet over here, right? As a central inputs worksheet, which means across the different workbooks, right? They're all going to be referring to a global like inputs or assumptions or work. Okay, So this is one of the really cool use cases when it comes to using external links. Because if you have like an expanding multiple models and you want to use a single, single inputs worksheet to make it easier for the user, right, to maintain. And let's say for specific values, you're going to be using the same set of values across the different models, then external links are the way to go. Because for example, if you have this specific set of assumptions that you want to use to a number model don't under model, right? It's going to be impractical to just copy paste this and then create a new workbook and had copy paste this again. And then once the user needs to update, then they would need to go through every single input worksheet and then make the same change over and over again. And that's going to resolve to a lot of errors because let's say if you forgot to update one of the input worksheets, then. You're going to have incorrect values and incorrect results in an adhesive said it's just like it's gonna be a costly mistake. It's just a simple human error, but a lot could be avoided if you let say, create the global inputs. Okay, workbook. So before we start, I just want to point out a couple of precautions that are very useful or best-practices, right, when working with external links. So you want to avoid moving files and whenever you move a file and then you always need to go to Data and then edit links to make sure that you're reflecting the current location or the correct location. Next one is to use named ranges as much as possible to minimize the number of fixes. We'll be doing that as well inside extract sample so that I can show you on how useful that named range could be. And the name range, right? You should refer to the external files. Next is don't change the foundings right next day and the file name off the workbook and the worksheet names as well unless necessary, because otherwise you will have to update a spell your external links. So let's jump over to our example so that you can see this happen in action. So what we want to do right now is move out this assumptions are inputs worksheet to a new workbook. Okay? And then we're going to be using external links because this one, this financial model over here are values. They are pointing. You can see over here, right? There are pointing to values to our inputs worksheet. Okay? So let's see what's going to happen. Let's go over here and let's select the right-click on inputs and S go for move. So over here what we want to do, right? Let's say we want to create a new book because this is going to be our global inputs workbook, so as go. Okay? Right? So now we're inside a new book over here. You could see, right, four inputs. And then over here as well inside revenue, if you have a quick look over here, right? The formulas updated automatically because they're now converted to using external links. You can see over here right, of oligo book one wasn't there. But because our inputs worksheet was moved to a new book called Book 1. Okay, now we're using that external link. So if I go back here, two inputs over here, right? And then what we can do right now is let's say let's just close this for now. Okay, so see changes. Okay, we're good with that. That's saved that. Okay, let's just go okay over here, right? And let's just open this. So what's going to happen? This, we have our inputs and then what if we change one of the roles? Okay? If we change one of the rows over here, and let's go over here first and, and protect the worksheet over here because I have this protected. And now let's say, let's just delete this specific rule. Okay, Just, just one rule. It's a harmless change. Let's say I just want to make this more compact. I'll just delete this. But what we're doing right now is the formulas right off D financial model that are referring to it would get affected because they are, let's say a while ago. So I'll just undo this. And let's say one of the formulas is referring to sell before, right? If I delete this right now, That's not before anymore and that's now B tree. So even just this specific layout change would like have a lot of impact to the rest, right? To the rest of your financial model or whatever files that are referring to this. So you need to be careful as well when making layout changes. Because what I do right now, right, That's just go over to the files again. Let's open this up, right? If I open this now, you can see all of a sudden they're all zeros, okay, because they're referring to the layout, the old cells, and they are now incorrect. So you could see here it's just it's just blank right now because I don't know what happened, but everything is just different. So what I'll do is let me just undo my changes so I'll if I insert again, right? So which means we are now in the same layout and the same cells. So once I open this right, I'll, I won't save this, okay, because this is now incorrect. What happens? So I'll just close this. And since I've reverted to the original layout, so what I'll do is let me just open this again. Okay, and then let's have a look since it's now processing the same layout us awhile ago, then we have our correct values now. Okay, So what we'll do next is, okay, so you might be thinking, what can I do then? What can I do with this external linking, right? What can I do to change the layout, but still make sure that it's still working over here. Okay, so here's what we'll do. Let's go over to expenses. So this one for your expenses, right. It's just referring to the support staff over here. The support staff salary, okay, In our inputs. So just remember this j 13, okay, fine, Go here is going to go to j through 13, which is this one. Okay? So here's where neighboring just come in. So let's say, let's give this the name range. Let's give it a name of support, salary, Okay? Okay, so now we have this one. And if I go back to our financial model, okay, So I'll, what I'll do is let me just update this out. I can just show you that even if we change the layout, as long as we use the name range, right, It's smart enough to be able to reflect that. Okay, so let's say, let's change this right now, inputs. And then that's changed this right now. Okay, so instead of that value, we want to use. So I'm just selecting this right? You want to use, I'm just maximize this again. You want to use the support salary, okay? So it's smart enough to be able to know that, okay, There's a named range, so I'll use that instead. So what I'll do is just copy this over to the entire row. So now we don't have that specific cell. We have the named range. So what will happen? Let's save this first, right? Okay, so let's go for save and close this one. Okay, Now let's try that experiment and let's go for delete. Okay? So we changed the layout, okay? And then let's say, let's open this again. Let's see what's going to happen that if it's still working or not. So you can see over here, right? So part salary, was he able to dynamically reflect that because what it's looking for right now is the name branch inside that workbook, that external link. Okay, so which means it's not hard-coded to a specific cell that our cell range because we have that name range in case if I jump over to revenue, we're going to have that same issue again, because we change a day out and everything falls apart. But since we have that good design principle that okay, I am using the name range inside the external link right at the global inputs workbook, then it must able to dynamically reflect that. Okay, so that's why when working with this principles that I mentioned a while ago, It's very crucial to practice that with external links because if you make that the sign mistake, then it's going to be hard for you to maintain that in the long run. But if you are able to do that correctly, then the benefits would pay for itself in the long run. Okay, so that's with external links.
29. Int - Skills - Error Checks: Okay, so when it comes to your financial model, you want to constantly use error checking across your model because let's say if you make an incorrect calculation or if something goes wrong like the USA just not correct, right, then your error checks will be there to immediately signal you, okay, net pay, there's something wrong and you need to fix it asap. Okay, so I just want to show a quick example over here to demonstrate the different ways on how we can represent error checks over here. So inside this table, so we have a list of sales ride the sales reps over here, and then the sales amounts. Let's say we have the question. The goal of this example is to get the sum of sales above 3000. And this is done quickly by using the SUM IF formula, right? And then this is the range of values that we want to check out. And then what is the criteria? And then we want to check for 3 thousand, okay, greater than treat house and over here, right? So let's say, let's say we want to do some quick error checks so we can also get right, and under some F over here, okay? And then the range of values, then we want to do the exact opposite, which is less than or equal to 3000, because we want to sum it all together and let's see what's going to happen. So I'll just type in less than or equal to 3000. Over here, close the formula, and then we have this amount. Now if we add up altogether, so it's just total this together. You can add this manually over here, right? And then let's say we want to get the swell, the total off the table, the foot, let's say, let's use the sum formula, right? And then let's just total the entire sales, call them. Now you can see that again, we're doing everything correctly because you can see that the total of this to some IF formulas, right? It's exactly the same as the total off this one. Now how do we do our error checks? So since we have these two values, right, One way is to use subtraction. This is the easiest one and also the most straightforward to checkout. So for example, I'll just do this and then subtract that with the other value, which we expect both of them to be equal, right? And then we can just change the formatting. Okay? So let's say it's just go for this one, the formatting of the number over here, right? And then you could also color it red to make sure that it stands out if something changes if there's incorrect value. So for example, I'll just type in a different value over here, right? Then you could immediately see it. Hold on, there's something wrong. Error checks, so I need to backtrack and then check my formulas on where it got broken. Okay, so that's really handy to do that. I'll just undo that right now. Okay, and then you can also try out the equals error checks. So to do that, you just go over here, right? And then just paste the equal sign and then check on the value that you want to check and get. Okay? And then if it's true, right, then they're just saying, but it depends on how you want to do this. Let's say this is not equal to swell. If you want to have that check instead, then you can do that as well. Okay, So it's up to you to define on Do you want to represent, okay, if it's true, what does that exactly mean if it's false? Well, the stack except the Min, okay, So for us we can just keep this as we want everything to be true, which means our error check is working fine. Okay? And then the last one is we can also use an IF formula, right? And then let's say if this one is equal to this, okay? And then you can just say that, okay, I'll just place black, right? And then if it's false, which means they are not equal to each other, which means that there's something wrong, then you can just return to word error, for example. All right, and let's just close this, right? And everything's working fine. So this is black. But let's say this changes again to a different value. Then you would see that there's an error value over here with our IF formula. Okay? So there are different ways. So the easiest is just uses subtraction so that if there's a value popping up, immediately, you see that there's an error. Okay, So what I'll do is let's say hi, That's still the rule. Let's go to equal 2, okay? And then it's going to be error over here, right? And then this is going to be the color. So that once error right, shows up with the value over here, then it will change the formatting to light red, fill with dark red text. Okay? Just to make it more obvious, okay? Just to make it more obvious to the user that something is wrong. Okay? So we're good with that. So the error checks, right? Su, work with your model. You add more error checks across the model, across multiple worksheets. It could get convoluted because there's going to be multiple spots in your worksheet, right? That there's going to be error checks and another one over Derrida and another one over there. And it's going to be hard for you to just jump from one place to another. To be able to find a pig, is everything working fine or not? So one best practice, right, is to create an error checking page that links to all of the errors. So what I have over here, right? I have an error summary sheet, right? You could just hide this as well from the user so that when it comes to checking on what went wrong, then you could just open this up and then you have a quick summary on what S happened so far. So what's going to happen? Usually write what you can do over here is you just have a simple table and then you could just link. So I'll just link, let's say to the result of this error over here, right? And then let's say, let's find the error checks. That's a distance. Another error checks. I'm just linking to all of the air checks over here so that you can see all of the results. So let's say if there's another one didn't, I'll have to go to the other worksheet for example, and then link to that specific error check aswell so that you have, you have all of the error checks in one central location. And then of course if you see that, okay, some of them return an error value to just double-check over here and you immediately know the location where it went wrong, okay, and then you can work backwards and then solve Right, the issue. So it's really handy. So one thing to note off, to take note, right? You can see over here, it's because I've used different methods. I've used if subtraction method equals error check the IF formula error check right? So it looks kind of like not pleasing to the eyes. It's best to use one error detection methods across your entire financial model. So let's say if you're more comfortable with using the subtraction error, check metadata and use that across your entire model. Okay, so once you have your error summary page, they would all look the same if everything's working fine and you just have the dash over there. Okay. You just have one consistent look, then it's a lot easier to maintain a swell. And of course, if you try to understand, sometimes you get confused, okay, which metadata I use. What is the result again? What does that mean? Because if it's not consistent, if we use different methods, right, then it's going to be hard to maintain in the long run. So you need to decide right from the very beginning which air checkmated do you want to use and then make it consistent across your entire model. So once you have everything in here, so let's say, let me just remove this too. Okay? That's just say everyday it's the subtraction model. And what you can do is you can have another cell and then just use the COUNTIF formula, right? And then just search for the error values, right? And then you could just have a quick count. Because if you see that the count if result is 0, immediately know, right, everything's okay. There's no errors. But if it's a non-zero value, then you immediately know you get signal that there's an error somewhere here. And then you can now start scrolling across your table so that you can find the problem area, okay? Because if you don't have that COUNTIF, right, if you don't have that aggregate function that would summarize the result for you, then you will still need to scroll down. Let's say there's a lot of error checks right now in your model, you need to scroll down every single time, right? To look for that specific error or just to check if everything is okay. But if you have an aggregate function, then that summarize the results right away for you. Okay, so take note off the air summary so that you can have all of your error checks in one central location.
30. Int - Skills - Goal Seek: So this is one of my favorite features in Excel and it's going to be very useful for you. And this is called goal sick, okay? So to be able to understand bidder on what goals it can do, I'll just show you quickly this example. So we have a very simple example of the revenue numbers that we have right, for the first three quarters. And then we have the profit margin. Let's say for quarter one, the first quarter it's told percent, 40 percent, 50 percent. And then for the fourth quarter. Okay. It's 20 percent, but we don't have the revenue numbers yet. Okay, so how do we calculate that, the net profit, very simple, right? You just multiply the numbers together and then we get our profit numbers. Okay? So we have the profit numbers are ready for the first three quarters, but for the fourth one, it's still a question mark. Okay, so let's say we're doing some planning and then what we want to achieve, right, for an entire year is a net profit total of 200 thousand. So the question right now is, what is the revenue amount, okay, that we are going to be targeting so that once we get that net profit for the fourth quarter, then the total would be 200 thousand. Okay? So we can reverse calculate that it can perform working, wants that back wars right to get the amount or you can just go for trial and errors. Well, let's say it is. I'm just making it up, right. Very close. Okay. It rounded 50 thousand really closer. Okay. I could just try again and it's going to take quite a while. So Goalseek is very crucial because what it will do for you is given that specific cell that you want to change, it can target your goal. You can find out the value that you need to place it over here, right? And then so that your goal will reach the desired amount that you want to happen. Okay, So one of the important things for the Gold sich toward your target value, which is 212. And in our case, right, should be a formula, okay? Should this specific cell should be a formula for the Goal Seek to work. Okay? So what we will do right now is let's go to Gold sich feature, okay, So to be able to access Goal Seek, Let's just go to data forecast and then what if analysis over here. Okay, so let's select Goal Seek. Let's have a look. So set cell, okay, so this has to be a dynamic formula that it should change, right? So the target cell, F5, so that's good. Let me just change that, this one, okay? And then to value, what is the value that you want to target? So we want to target 200000 over here, okay? By changing which cell you're tending Goalseek, okay, you can play around with the cell final dic sat value that you need to place it over here so that we get an amount of 200 thousand inside the cell F5, which is this one. Okay. So changing cell this one, Let's select this and let's go OK. And you can see Goal Seek, working its magic. Okay. It was able to determine the excess amount over here, 437,730. All right. It just had the exact amount so that once everything gets updated then adequately, then you have two hundred, ten hundred. So that's the really cool thing with Goalseek because it just takes the guesswork out. Okay, If you're trying to find a specific value or just playing with different potential scenarios, then Goal Seek will be able to help you out. Okay, so if you're happy with this one, you want to retain the values. You can just click Okay, right, and then you have the amount that Goal Seek place in here. But if you're not happy, just did it, right, or just click Cancel from awhile ago, then you have the original values. But that's the really cool thing with Goalseek. So that's one way of using Goal Seek, what I want to show you for discipline to swell if we have another simple example wherein you can also use Goalseek for break even analysis. So for example, if your target is break-even at 0, then you would be able to know what is the minimum goal that you need to target, okay, to be able to break even. So when you say breakeven, it just means you're not making a profit, you're not also losing money, okay? So we're just 0, okay? So for this one very simple example, we have book units sold, right? And then this the book price that we're selling. So which means the revenue is just simple multiplication between the two values. One tau multiply to 30, okay? And let's say our profit margin is at 12 per cent and then we have expenses, $50 thousand, right? So to be able to calculate our net profit or loss, we just multiply the revenue by 12 percent, right? The profit margin, that's our profit. And then we need to divide up the expenses, right? We just minus 50 thousand over here. Okay? So right now we're losing money, but with Goal Seek, okay, So let's see. We're just running a couple of scenarios inside our head. You can play around and see what the book units sold or the book price. Because for example, let's say, okay, given this settings right now, what is the minimum number of books that I need to sell? So that will just break even. That's 0, which means I'm not making money, but I'm also not losing money because right now, given this numbers, I would be losing a lot of money. So let's play around it. So let's go over here. What-if Analysis, Goal Seek, right? And this is the cell that we want to change, right? So make sure you write, this is a formula, so this is something that's dynamic, right? So, so far so good. And let's say we want to set B7, this one, the net profit or loss value to 0. Okay, So what do we want to change in our specific scenario right now, we're running in our head, right? You want to know what is the minimum number of books that I need to sell so that net profit or loss is 0. So let's select this. Let's go. Okay? And let's see, Goalseek work its magic. So which means now you're not able to find out, okay, you need to sell at least 13,880 books or 89 books, right? So that you would break even. Okay, that's 0 over here. And that's radical because the Goal Seek is just able to give you that exact amount okay, to match your target scenario. So let's go for cancer. So that's one way, right? You're making like finding out what is the exact number of book units. But let's say, let's say, let's say that, Okay, No, my target is just selling 11000 books and that's it. So what's not a way that I could just play around with this using caustic. Let's say I want to find the book price instead. So in our scenario right now, I'm pretty sure I just want to sell 100 copies of the books, right. But why is the price that I need to set so that I wouldn't have break-even, right? So which means Let's do the same thing. Go to Data, What-if Analysis, right? What we want to change is the net profit or loss value to 0. And then what is the cell that we want to change that we want Goal Seek to find out, which is our book price. Okay? Go okay. And then now call sick will now change the book price and value so that we will break-even. And we have found out that by selling at 100 books, then the book price needs to be 416.67 so that you would break even. Okay, so you can play around with Goal Seek, with your different scenarios. And we will use this as well inside our financial model.
31. Int - Skills - WACC: Okay, so now let's talk about the important financial concepts that we will be using for our financial model. So when it comes to evaluating projects, Let's see a corporate project or even your personal project. Then this numbers, okay, that we will be computing will be very important so that you can evaluate if it's a good investment or not. So the three concepts that I want to talk about is the walk over here, right? The cost of capital. And then we have the NPV and IRR. Okay, so let's get over them one by 1 first just to have a proper understanding on what these terms are. So when we say IRR, this is the internal rate of return. So pretty much it's the amount expected to be earned on the capital that you have invested in the specific project. So it's just telling you a percentage, right? That okay, this is the rate of return that you're getting from your investment, okay? So you have an idea on how much you're going to make because of this project. So that's IRR. And then on the other hand, right, there's the cost of capital. So what stands for weighted average cost of capital? Okay, don't worry about the formula. I'll walk you through that. But what it signifies the, what the cost of capital, It's saying that the corporate capital always comes at a cost, okay? Because you're investing something, right? So which means either it's funded by debt or equity, and both of them, right, has an inherent costs. So we'll understand more what we mean by that. Sv go through the numbers and the details off the formula in a short while. So that's where cost of capital, so the weighted cost of capital is a person page. So which means this is what's costing you because of your investment. Okay, so that's for whack. And then last one is the NPV. So when we say NPV, this is the net present value. So if the IRR exceeds the walk, so you could think of it like whack, and that's the cost of investing. And the IRR, on the other hand, is the return of your investment, right? So you want your IRR to be greater than WACC because that tells you, okay, you're making money over time. And the monetary value is represented in NPV, okay? So the net present value will show you, right? If it's positive, which means you have made money with the project, then that also signifies that IRR would be greater than your cost of capital. We'll see that in a short while on how this relationship works out. Okay? So hopefully this three terms are more clear right now. Okay? So if interest rates would rise, for example, in your debt, for example, in borrowing, it starts to rise, then the cost of capital, the WACC would also rice. So if WACC would rise, then your NPV right, would be reduced. Because if it's more expensive to borrow money, it's more expensive to invest, then your return on your investment will also be lower. So there's that inverse relationship between the two. Okay? So first things first, let's focus on computing WACC over here to cost of capital. Because. When you tie up your capital, say on a project or investment, right? There's always a cost. So think of it as a missed opportunity because for example, this dead off placing your investment in stocks and bonds, or maybe just placing it in the bank to earn interest, right? That's a missed opportunity because instead of placing it in those instruments, right, you're placing your money or your investment right now in a project, okay? So which means if you put your money here, then we need to compute. So if saying put your money here in the project, right, then we need to compute the cost of capital so that we can check if the project is worthwhile or not. Okay? So let's compute this right now. So pretty much what cost of capital stands for, right? It's just a combination of the cost of equity, right, which we will be computing and the cost of the debt. So debt capital, typically it's about interest expense, right? Because you're gonna be paying map, the burrowing interest rate for your debt. And then for the equity capital, it bears the opportunity costs of foregone capital gains to outside investor. So which means since you place your money here, could be earning more elsewhere, right? And that's the missed opportunity. So we need to take that into account as well so that we can combine them together and then we wouldn't have a final number of the cost of capital. So this is a very simplified example because what we did was pretty much our debt numbers and the equity numbers. It's just a single them out, right? There's a single cost percentage for each for equity and debt and then there's a single amount for the equity and debt as well because normally you would have different sources, right? You have different calculations. But I just want to show you this simplified example so that you can understand the essence right, on the cost of capital so that it becomes clear to you. So what we will do right now write this formula. It's a bit complicated to look at, but if you understand the essence, I'll walk you through right now, then it becomes very easy to understand. So what we need to do first, right? We have over here the amount toward equity, right? The amount for the depth that's magnitude tells him cost percentage over here for equity it costs felt percent and then for the debt it's 6%. So to calculate the cost amount, it's very easy. We just grab this one and then multiply by the 12 percent, right? And then we have the cost amount, same thing as fo for death. We just copy paste it because it's going to be using the same formula. Which means this is what it is costing us right now. And then after dampness, just crab the total amount for D equity and debt is just a quick sum over here, right? And then let's just add both of them to getter, okay? And then for the debt, what you need to calculate, if you can see here on the formula is we also take into account the tax rate because what do we want to calculate for our cost would be after taxes. So to be able to do that, right, we take this amount right now, the cost amount for the depth b, multiply that and to remove the tax is going to be 100% or one minus the tax rate over here, 80% right now if the cost amount for your debt, okay, so once we have net, what we will do next is we can just copy paste actually, the same formula over here because for equity, right, Since tax rate is 0, we don't take that into account over here, but we can just copy paste so that our formula would be consistent, right? So it's just dragging this down 180 thousand over here. Okay? So looking good. Now what we will do now is add them together. So we have now our combined costs right now. And then ultimately, the cost of capital is just the percentage of the total amounts. So it's like saying costs over the total amount. Okay? So once we have this over here, we have our presentation, which means your cost of capital is 9.3%, okay, So this is what's costing you off placing this investment right inside the project. So it's a really nice way because right now it's not exactly clear because inequity is 20 percent and then we have your debt at 6%. This formula combines them together and then gives you a single number so that we can use that in calculating our NPV and IRR and making comparisons and making an analysis based on the resulting numbers.
32. Int - Skills - NPV and IRR: Okay, so now it's time to calculate NPV and IRR. So let's just have a quick recap. So what we did awhile ago was we calculated the WACC, which is the cost of capital. But since Excel doesn't have a ready-made formula for work, we had to do that manually for the calculations. But thankfully, for NPV and IRR, do are ready formulas for us to use an Excel, okay, so it's going to be straightforward for us as to how to calculate this together, okay, so for NPV, right? This is the net present value. So if IRR is greater than your cost of capital, then your NPV would be a positive amount, which means you will have monitored like a positive monetary amount after you invest in this project, okay? And therefore, IRR, the internal rate of return, that's the rate that you are going to be getting after investing in this specific project. This is what you're going to be making and your goal, at the very least, right, is to at least be greater than the cost of capital because otherwise it wouldn't make sense to invest in this specific project. Okay? But one thing to note, we'll see later, right? It really depends on the numbers and then you can make your decision whether this is a good investment or not. And what we have over here on top right, it's the cashflows, the projected cash flows for the next five years, okay, for five years. And then we can use that to calculate right now in Excel, let's say for the NPV, let's start with that. So we have the NPV formula and it's asking for a rate. Okay? So for District, we're going to be using the cost of capital, okay? Over here, right? And then we are going to be providing the values, which would be the range of values from our cashflow. So I'll just highlight the five values right now. And what NPV will do is, okay, given this cashflows and then this is the amount that's the rate that's costing you write with your capital, I can now discount the cash flow values so that you have a single amount. And this is whether you're losing or you're making after your investment. So let's close that and then let's have a look. We have Florida 30 to tau sub k. So not bad, okay, but it's up to you to ultimately lead the side. Is this worth your time? Is this worth your investment? Because this is the amount that you will be making right? After considering the cost of capital. Taking that into account, this is the amount that you will be making, okay, After your investment, okay, so for NPV, okay, positive is good, right? But it's not the ultimate goal because it's still up to you to decide. Is this a math word? It because you'll be spending time, you'll be spending your investment, right? So it's up to you to base your judgment on death. Now for the IRR, it's also very straightforward, right? For this one, for the Excel formula for IRR. And then watts is asking for is the list of values. So we'll just highlight again the five values over here and it will return to you the IRR. So since we have seen that NPV is positive, right? It's foreign, it's 30 thousand. Then IRR would be greater than your cost of capital. So which means we're expecting a number over here that's greater than. 9.3 per cent. So let's do that right now. And we have 30 and 25 percent, okay? So that's the rate of return. But there's one caveat, right? When it comes to using NPV and IRR D Specific Excel formulas, right? Because for example, for NPV, what it does is it happens, it assumes that the cashflow numbers, like for example, this specific cashflow, it happens at the end of the year. And if you think about it logically, it doesn't happen because what we're seeing over here, say for the year 2024, It's like what we're seeing at the very end of 2024. All of a sudden there's $1,000,150 thousand that comes in. And that's not reasonable because what really happens is this amount is earned right, throughout the course of the year, could like portion in January or February, etc. And it's not just everything in December. Okay. So that would affect our calculations because the time right. The time on when you receive specific amounts of the money would factor in into your calculation for the NPV, for the IR. So that would skew the numbers by a bit, right? And then that would also affect your decision. So to be able to do that, right, There's also another function in Excel that takes time into account. So for example, let's say given your projected cash flows, you have more detail. And you know, like specific dates on the cash-flow, right? You can see that for 2021, right? We have more detail on the projected cash flows. Okay, For 2022, we have approximately the day the swell in February and then for 2023, et cetera. So there's more detail when it comes to the timeline of the cashflows. And we can take that into account into the calculation using the NPV and then the IRR functions. Okay, So if we do that over here and let's try it up. And then let's see if there's a difference in our results. So just type in x NPV, right? And then for the rate, we're going to be using the cost of capital again that we calculated a while ago. And then for the values, okay, so same thing as what we did from a while ago with NPV. We have had added the values. And the last one is we need the corresponding date, so I'll just highlight the dates over here. Now, let's have a look. Dx NPV, it's 364 tau. Okay? So it shifted by a bet, right? Because we have more reflective numbers taken into account right now. And then for x IRR, same thing as well. If you've use IRR, then this will be very easy to utilize. So we have the values over here, right? We are going to be highlighting the range of values and then what it's asking for next, the dates. Okay, so same thing as well. That's what we did for NPV. Okay, So since it's a positive amount for x NPV, then we're expecting IRR to be greater than the cost of capital. So we have 12.31%, okay? So given this numbers right, then you can now start to decide, okay, give me this project. Is this beneficial to me or not? Is this a good investment or not? Right? Or if you have multiple projects, then you can now compare the, given the different rates or different NPVs. Then you can compare them side-by-side to have a better idea on which one to take. Okay, so there's a cool relationship, right? If you've noticed that the cost of capital, the IRR and NPV, all of them are intertwined together. Okay? So right now, if NPV is 0, okay? If it is 0, then what do we expect for the IRR and the cost of capital? What would they look like? They would be equal to each other, okay? They would be perfectly the same for those two values. So let's just play around with it. Okay, let's see if this relationship is really true or not. Or maybe I'm just making this up. So to be able to do this, okay, We're going to be using Goal Seek. So I'm going to show you the power of gold thick swell on how we can use this so that we can play around with the numbers and then see that if NPV is 0, then we have IRR equals the cost of capital. So what we will do right now is let's go to Data goal sick, right? We have NPV selected right now we want it to be, okay, Let's set the cell to 0, okay? Which means that's our goal, right? And then what do you want to change? We can just change any of the values over here, okay, in our cashflow, let's say, let's just change the projected cashflow for 2025, okay? We're just allowing Goalseek to do that. If we go okay. Goal Seek, we'll adjust that. It's now 3.5 million. And as a result, right, NPV is now 0. Since we have the formulas right, for the IRR, for the calculation of that, all of a sudden, IRR is now exactly the same as the cost of capital. Really cool, right? Okay, So which means you can just see here on how the relationships of this tree values are connected together.
33. Int - Model - Introduction: Okay, so now to the fun part, we are going to start creating our financial model, right? So if you've checked out the beginners financial model that we have created before, right? This is the end result, but no worries if you haven't gone through that. But I just wanted to bring this up to give you the context of where we're coming from. And because the beginners financial model that we worked with is we already have an existing online bookstore, right? And the way this is just me working on the books, right, for example. And then I'm just creating them and then I'm just selling them through my online bookstore. And then what I did was I created a financial model just to have an idea on how much am I making and then how it's my expenses, how are they going right now? Do we need to improve in those numbers as well? So that's the goal of that financial model. And then right now Let's say, okay, I'm happy with the online bookstore, but I want to take it a step further. And that leads us to our financial model that we will be working right? We will be expanding our online bookstore, right? To have staff, for example, we're going to be employing a full-blown team to work on creating books, to work on the marketing, to work on designing it, right? It's not just kinda be a one man team anymore. And then what we're after a swell is we will be doing some objections as well for our revenue because this is a project which means this is not existing at the moment. But what we want to do is analyze and do some projections, right? And then given the numbers we can evaluate, is it worth investing time? Is it worth investing money for the online bookstore to go like full-fledged project, okay, and then our main goal, right? Our main goal is at the very end, we want to be able to calculate the NPV, the IRR, the payback periods so that we can evaluate, okay, is this a good investment or not? Or if we're also considering other projects, other investments, then we can put the numbers that we will be calculating from here. And then we can determine, okay, which one's a better project. Okay? So that's for deist financial models. So we have inputs over here, we have revenue, we have staff expenses, we have the market analysis and the summary, the profit and loss we vote summarize everything in here, okay, so I hope you're excited with our financial model as we will be creating this from scratch.
34. Int - Model - Staff Expenses: Okay, so afford the starting point of our financial model, we will be working first with expenses. And the most complicated part of our expenses is the staff expenses. So we want to find out right over time, how much would it cost us to employ our team? So we have different roles, different salaries, different Start years and different, and years. Okay? So which means we're making some predictions and assumptions over here, right? And then we want to find out how much is it costing us each year. And one thing to take note, right, is we also want to consider inflation because expenses, right? Because of inflation, they will grow over time. So we need to take that into account over here and hits kinda be really cool on how we will be building this from scratch with the use of formulas, with the use of mixed references. Okay? If you're not very familiar yet with mixed references, I suggest you go to that lesson and then make sure you have master deck because we will be using that a lot here. Okay? So a lot of blank fields right now that we need to fill up. And then over here, right, we're going to be making a quick summary based on the role over here. And if we want to count how many editors, How many designers, right? And then et cetera, and then the amounts right off their salaries as well. Okay. So pretty good. So what we will do first is we need to populate the salary. So for the salaries, right, what I did was already set up the salaries over here. I just play some assumptions on what are the salaries that I would think would be for each role. So you feel not a so swell. It's very important to be specific because over here, what we placed over here, it's per year so that it becomes clear because that would factor in into our calculations as well. Like okay, what's the duration of the Saturday, right? S what the place. So this is n naught. So that's pretty straightforward. So we have more fields right now. But what I did was pretty much I separated them into two main sections, revenue related, which we will be using later, and then the expense related for right now, we have the staff salary in here that we will be using. Okay, so as best-practice, you need to use name ranges a lot, especially in psi d inputs worksheet or the assumptions. So what we will do right now is just make this table over here. This, give it a name. Let's say staff salary over here. Press enter. Okay, so we have this table right now, staff salary. And then for the inflation rate, since we will be using that for staff expenses less, just say it's 4%. Okay, Let's make that assumption right now. And then let's also set, right, the name range over here as inflation rates so that we can refer to that later while we start working with our formulas. Okay, so looking good, this will be the main assumptions that we will be using for our staff expenses. So the question right now, let's jump over here. How do we populate the salary? So one thing you could do this, Let's say equals. And then you would just go to editor over here, right? Press Enter. And then you will go for the designer, and then you will look for that insight inputs, right? You could do that. That will work fine, but it's not the most efficient way to do that. Because it's going to take you quite a while to be able to populate everything. What if you have 20 rolls? What if you have 30 staff over here, right? And that's going to be a long time. So what I'll do, let me just delete that. And one thing you can do is to use a lookup function, a lookup formula. So one thing we could do over here is use VLookup because what we have for our staff salary table, if it's a vertical table. So we can use debt. So lookup value, we want to search for this one, right? Okay? But since we will be using this formula for the entire column, so we need to take into account, is there any mixed reference that we need to do here for a tree? And we do right, because column a is constant, so we need to press F4 and make sure that codomain is constant over here. Table the very right, we already set it up. Staff, salary, okay, so that's really cool, is already over here. And then what is the column index that you want to return? So if, if you recall in here, right, column number one, column number two. So which means once we are able to find that say editor, okay, return column number two, which is the salary value. So which means that would be let me just remove that and that would be column number two. Okay. For the salary. And then for the match we want it to be an exact match, which should be false. Okay, now that's 200 thousand. And then we could just drag the formula all the way down and let's do a quick spot check. So for example, in marketing is it really won 20000. Okay, Let's have a look and let's check the support staff. Is it 100 thousand to have a look? And it's also 100 thousand. Okay, so VLookup is working perfectly fine. Now, let's talk about how do we populate the timeline over here. So which means on 2021, right? Based on the start and the end year, we want to populate the staff salary over here, SR expense. So which means for editor, we want to populate that for all of the years over here. And then for a designer that will only be from 2021 until 2025 as stated in the Start Here and DNR and so forth. So, okay, try to have a think about this and then try to approach this. You can pause the video, try it out. Try to create the formula, a single formula for the entire table. That would do this for you. Okay? So for this one, it seems complicated at the beginning, right? And then how do you make this formula step-by-step? So the very easiest way that I could suggest is when you starting to work with long and complicated formulas, it's best to do this step-by-step so that with each step that we create, we are able to test it out and then make changes as necessary. Corrections as necessary, so step-by-step. So first things first, we have to start your endure, but let's not worry ourselves with both of them. Let's focus first on the start here. Let's start small with our formula. So if we're going to be considering that, okay, if it's inside or if it's greater than or equal to start year dead show up. The salary are displayed a Saturday, okay? So which means if this year is greater than or equal to the start year, okay, what do we want to happen? We want to return the value of the salary over here, right? And if not, then we just return 0. Okay? So that's just taking into account the start year. But let's have a look. But for the formula, okay, mixed references, we need to take that into account. So for E2, are we doing that correctly? Because 40 years, what we are considering is row number two is going to be fixed, so we need to press F4 and set that over there, okay? For S3 over here, for to start here, we're always considering the start year over here. So what's fixed? It's going to be column C. So let's press F4, right? We have that fixed for column C for the salary. Okay? What's going to be fixed if they're going to be a fixed row or column, yes, there is. It's going to be always in column B. So which means let's set that up slow, okay, press F4 and we have that formula. Okay, So we have 200 thousand. And then let's just copy everything over here, right? And it's copy all the way down. And let's do a quick spot check. Okay, so what we're doing right now is we get the initial formula that we have, right? We are only considering the start year, so we haven't taken into account the end year. So let's ignore that. So for example, let's say for the designer, okay, it starts at 2021, okay, So pretty much if the Earth, Okay, looks good. Let's jump over to the writer in 2023. Let's have a look. Okay, this is blank. Blank. Okay. And then for 2023 we have 150 thousand all the way till the very end, Looking good. How about this one? 2022. And it did write it started on 2022 with the correct amount, let's say for this one, 2025. So you have this one starting on 2025. Same amount. Okay. Looking good. So which means for our start date portion or to start your portion, we have that nailed perfectly. Okay. Because we have done our spot checks and then we can now move on to the next step. So what is our next step? Our next step right now is to add in the logic that would take into account the end year. Okay? So how do we do that? Right now we have here, right, this is the start date to start gear over here. So we now need to calculate or include in the logic di and year. So which means we will be using the formula because now we have two conditions that we need to take into account. So we have the start here, that's fine. Now how do we consider the end year? So which means for the end year, right? This one, okay, the current year that we're evaluating needs to be less than or equal d and year, okay? So which means we're defining a range over here, right? If you're greater than or equal to the start your, or your less than or equal to the endure. So basically you are saying it should be in-between, right? The start year and the year. Okay, So let's quickly evaluate that. Are we doing that correctly for E2, okay, for E2 over here, right? What's going to fix this row to 40 years. So let's do that F4. Alright? Okay, You'd have that fixed and it's same thing as well for D and year. It's gotta be fixed for column D. Okay, so let's do that as well. Press F4. And now here's the moment of truth. Let's copy and paste this formula. Let's just drag this down again, okay, and then let's see if we have the correct logic. Okay, so now it has changed because we've added the year logic into account. And if you notice right, 21 over here, 2025. So I just have a look. Okay. So it did stop, right. Which means come 2026. Everything. Okay. Because it's only from 2021 to 2025. Now let's have a look. Let's say how about this one, 2020 to write until 2028? And that's really cool. And I think the best part or the most fulfilling part is we were able to achieve this using a single formula, okay? Okay, just one formula and just copied it across the entire table. It was able to reflect that logic that we need. And it's all thanks to the different Excel formulas and the mixed referencing that we are using. So saying goes over here for the support staff 2025 until 2013. So it's reflected as well over here. Okay. So looking good. So the missing part right now is we need to take into account the inflation because costs would increase over the years depending on the inflation rate. And what we have in our assumptions in our inputs worksheet is that inflation is 4, 4% percent. So we need to take that into account over here that every year it needs to increase by 4% after 2021. So how do we do that? So what we need to do is we need to include the rate in here. We need to count how many years have passed. And then based on that number of years, then that's the number of times that we need to increase or multiplied by the rate. So to be able to multiply by the rate, what we normally do is we need to multiply that with a specific amount over here, right? So that's going to be one plus the inflation rate, right? So we already have that named range, so we can just copy that over here, right? So that's going to be, if you put it in absolute terms, the numerical terms, that's going to be a 104%, okay? Given the a 104%, how do we take into account the number of years that had passed? So we can use this symbol, right? The caret symbol. And then this is going to symbolize right, the power or the exponent of the exponential power. How many times would you need to multiply the inflation rate over and over again, the compounding effect of that. Okay, so to be able to do that, right, That's add the calculation for the year. Okay. So to be able to calculate that this is going to be the year, right? And then base on the start year that we have, right? It's going to be minus S1 and the swell to itself. Okay? Because what we're seeing right, once we copy the formula over it, we want it to be 20, 22 minus 2021. And then once we copy the formula over here, it's going to be 2020, it's three minus 2021. So what we're seeing is okay, two years have past three years had past four years have passed. So you need to reflect that in the exponent, okay? I'll fewer inflation rate or the power off your inflation rate. Okay? So we need to make a change over here and what's going to be fixed and what's not going to be fixed. So what we have this one, right? Yes, we want row tube to be fixed because we want to use 2021, etcetera all the years over here. So let's set that, okay, for row two to be fixed. And then this one is going to be absolute because always minus 2021, which is the first year that we're taking into account. So let's press Enter, okay? So for the first year, right, there's not gonna be any inflation, okay, for the first year because 2021 minus 2021 is a power of 0, so nothing yet. But let's just drag this out and then let's do the same and do this all the way down. So let's have a look. So one way for me to quickly check, right, to quickly check is to have a calculator by your side. Because if you want to do a quick sense check if you've done your calculations correctly because it's pretty hard to evaluate. Am I doing this correctly or not? Let's put this in here too, on a 1000, right? So I'm just typing it up. That's type it for the first year, 4% over here. And indeed 280, looking good, right? Let's do that again for the second year. Okay? Multiplying by 1.2 or four and you have 216,320. Okay, Looking good. So we can do that and just continue and then try it out the swell on the others, right, Just to have an idea, if is it working for everything. So for example, over here, right, for the support staff, that's 100 thousand. And then what's, what's next? Wanted four by 4% compounded with an under 4%, right? And it just grows and it's looking good. And if you notice a swell inflation still kicks in even though the staff was hard at that later year, right? So you want to reflect the real inflation over here and over time. Okay, so that applies to everything. So that's looking good and right for the staff expenses and we're done with that one. So the cool thing again, It's a single formula that we have used.
35. Int - Model - Staff Expenses Summary: Okay, so right now let's work on the summary table of our staff expenses. So we just need the staff count right. For the specific role that's listed over here. And then we need to get the salary totals for this specific role as well for each year. Okay, So this is going to be true Excel formulas. So for the first one staff count, what we want to do is get this role and then have a look over here and count how many roles are in here, right, for this specific one. So it's going to be true the COUNTIF formula. So let's do that. Okay, So the range over here would be this one. Okay? And what we can do is let's just make this absolute because it's always going to be this specific range that we will be checking on the specific role over here. Okay, so let's close this one. We're happy with the editor and then we can just drag this all the way down. And let's have a quick look. Okay, so afford to center is looking good with one over here, okay, and then two writers to motivating and then tree support staff. So which means our formula's working fine. Okay. So next one is for the summary for the salary expenses. Okay, so what do we need to do is we need to sum values from here together, right, that have the editor role. Okay, so for this one, right? So for this one and then for the supporting staff, for example, then we need to sum the values over here that have a role of support staff, okay? So to be able to do that instead of using count if, right, There's another formula that's very similar. And thus the job well, which is sum if, then what is the range that we want to get the basis of our values from? So the range would be the list of roles over here. And that's going to be fixed, right? So let's just make this absolute. So no problem there because we're always going to be basing it over here. Okay, How about for the criteria? What are we matching it against? Okay? So we're matching it against over here for D editor role. So which means we're saying, okay, grab the editor and then match it against here. If it matches the criteria, then we're going to be getting some values. Okay, so that looks good. But let's have a think about this one. Mostly go to be fixed with the rolls over here for our criteria is always going to be in column a. So let's make that fixed for column a. And then the range of values that we are going to be getting from would be this range of values. But let's have a thing again. What we're going to be constant about this specific range is always going to be in rows three until 11. So let's make that fix a swell with the keyboard shortcut F4. So now we have debt. Okay, let's try it out. Let's see if it's working fine. Okay, So for this one there's only one editor, so that's going to be 200 thousand. Okay, so that's looking good. Let's just drag everything all the way down. And let's try out this, do some spot checks if we have set this up correctly. So let's say for support staff, okay, over here, did we do it correctly? Okay. So that's looking good. Now at first I was looking at this doing I was like, no, this is not. But the good thing is upon checking, we have three support staff once a total for 2021. It's wanted to tell him. Okay. It's shelf over here. We have this tree values 104 thousand, okay? And then how about, let's see, and it's jumped to this year. And gate we need 24 4 supports that it's going to be DIFS amount. So if we have a look over here and it's 224,973 and you have the correct amount. Okay. So that's really cool. If you job, let's say, let's just jump to one of the writers. So for the writers, let's say for 2028, did we get the correct amount? So for the writers, okay. We have 18739. Okay. That's the correct amount. And then for 2020 74 to writers, That's 379, 596, which would be this two values over here. And that's the exact same amount. Okay? So as you can see with the combination of the right Excel formula and the right combination of swell of the mixed references that you would be using. You could just use one single Excel formula and then we just copy everything over here. Another important concept to take note off is the use of error checks. So over here we have the total, right? We haven't created the total yet, so I'll just create a quick sum formula over here, right? And that's just told all the entire column together. I'll just copy, copy this, and paste this all the way to the end. And then same thing as volunteers, just create a quick sum formula over here, right? And then let's just copy as well, all the way to the end. Okay, so let me just highlight that or I can just drag this as well. That would work fine. So let's have a quick look, right? Looking good, just do a quick spot check to your formulas, right, so that you can make sure that Excel did not do anything in correctly. So that's looking good. Okay? And again, we did a correct calculation for everything because he could see at a glance, right? That everything is exactly the same for the total of this table and then the total of this table at the bottom. And then you can create a quick spot check. So let's say over here, right? Let's just go for minus deaths. And then you can just drag this all the way to the end so that you can have a quick look. And there Let's see color red and let's just give this the formatting of the number, okay? So that if there's anything wrong, if one of the formulas, like we made an update and we made an incorrect update, okay, Then we would immediately see it inside the error check over here. Okay. So always be on the lookout for opportunities for error checks if he could just place it across the sheath, right, so that it makes it more stable and easier to maintain in the long run. Okay. So that's pretty much dead force that expenses on how we were able to calculate it from scratch and also take into account the inflation rate every year.
36. Int - Model - Revenue: Now let's talk about forecasting revenue because revenue is a bit more challenging to project our forecasts. Because as compared to expenses as what we did, there, easier to calculate because we have an idea of who to hire, for example, what's the expected salary, etc. And we can just plot it out with our expected values. But for revenue, right, you need to be forecasting and you need to determine on where to get those financial numbers from. It's predicting, for example, how many costumers we will get, right? Or how many customers will convert or will become paying costumers, right? So there's, there's quite a number of layers that we need to calculate over here. So it involves research and also involves educated guesses. So for our revenue, what we're thinking is getting at the website, visit our starting at that because we have a website and then we want to convert them into a paying customer of our online bookstore and k. So let's say we have a blood or we have some free articles and free website out there that we want to sell them, the books that we are making for our potential project, okay, so the question right now is, how do we convert them? And then after wars, after the conversion, how much will we make from the sale soft disc costumers? So for the assumptions, for the inputs that we are going to be working with, we're working with the population numbers, okay, so there's a big assumption that we're making over here, right, for the website visitors in 2020, let's just assume that we're starting with 300 thousand, okay? And then to be able to determine because one of the questions that we have, right? Okay, if it's starting with 300 thousand and what will happen to the rest of the years moving forward. So one thing we can work with, all right, it's working with the population numbers from the census. So another assumption that I'm making is let us assume that our business is US specific or like targeted towards the US population, that's our target customers, right? So we're going to base our numbers on the US population. So I just took this from the census, right? So let's say from 2021, this are the numbers that I got it. So this is the research that I did beforehand. You can just do a quick Google search and then get the numbers from there. But one of the banks, right? One of the banks with numbers or research, right? We don't always get the full picture. So as with this one, we only have numbers from 2021 to 2026. So which means this for years right there, black. So it's not a complete picture. So what we can do is we can use the forecasts formula, right? We can fill in the four remaining years with the forecast function of Excel. And then we can do that quickly over here. Okay, so that's the cool thing with using Excel. So for the target date, right, this is going to be our target date. And then the values that we are going to be basing it on would be the historical values. Since it's always going to be the same historical values, let's press F4 and make this absolute. Right, and then for the timeline of smell is going to be this six years and let's make it absolute S. Well, okay, so that looks good. And k We're now predicting, write the population numbers for the next four years. So I'll just drag this all the way to the end. Let's have a quick spot check. Looking good, right? And then our forecasting looks to be working through rate. Okay, So once we have this over here, we haven't the population numbers. We can now calculate, right? The growth rate. But before I jump to that, okay, just to make sure that we're following best practice. Because as you can see over here, right, this our inputs that we placed in replace those numbers from the census website, right? We place them for D assumptions, but this one's this was our formula. So which means this is not taken from the website. So we need to change the style of this one. We need to make sure that there's a distinction, right? So let me just change that to numbers over here, right? And then let's put this way. Okay, so we need to make that distinction at a, this numbers are not I inputted from source or taken from a source or assumed. Hey, this our formula. So we need to change their formatting to make it clear to the user, okay, that this, our formulas, this are automatically calculated. Okay? So we're making that distinction over here. Okay, so now we need to calculate the growth rate. How do we work with this one? So to be able to calculate the growth rate, okay, because this is going to be useful later when it comes to working with our website visitors. So we can take the growth rate, right, and then make that assumption that our website visitors over the years, we'll also joy that same growth rate. Okay, So we're assuming we're placing the assumption here that our market share remains constant. So the growth rate of the population will also apply to our website is thirst. So to be able to calculate that, right, we just need to subtract right, over this one, the value of the population from the current year versus the previous year over here. And then what we will do, right, That's just divide it by the past year value so that we can calculate the growth rate over here, okay, so that's 0.70%. And then what we will do is just drag this same formula all the way to the n. So let's have a quick look. So for example, this one, right? Okay, we're just deducting the difference and then getting the person pays off that one. Okay, so that's looking good. That's for the growth rates of the population. Okay, so let's jump over here for the website visitors, okay, let's just assume it's 300000. So before we start using this assumptions over here, one best practice that you can do is start making named ranges for this. Values over here. So for example, the 300000, that's type in website visitors. Let's create that named range so that we can use that later in our formulas. So to be able to show you quickly, right, that's used this now, let's jump over to our revenue for a website visitors, right? So our starting point will be the website visitors, okay. That's going to be our initial amount and that's 300 thousand. Okay, so that's pretty easy. One quick tip is while you're typing or if you're unsure on what the name is, you can use the keyboard shortcut F2, f3, right? And you can just select from the names that you have already created. So I'll just click Okay. And we have three endotoxin in here. Now, how would we populate the rest of the numbers? We'll just grab it, right. Multiply the previous amount or number of visitors, and then multiply it against the growth rate that we have just calculated. Okay, so let's just press Enter. Okay. So somehow I made that incorrect one because I just multiplied it against the presentation. I need to update this formula, then add with D 100 per cent. Okay? And once we have this, let's just drag this all the way to the end. So if we have a quick check, for example, this one is the previous value, right? And then input D6, if we have a look at these six DCIC is this one. So this is the growth rate for 2023. Okay, so we have our website visitor numbers looking good at the moment. Okay, so what do we need to calculate next? But before we jump to debt, okay, Same principle, that's a while ago, we made a different behavior over here, right? We made the use of an Excel formula, S4 duress, its input numbers. Same thing, a smell over here. This formula, right? It's a simple formula, but it's different from the rest over here. So one thing we need to do is to make that distinction as well. You can also use a border, for example. So what I'll do is I'll just create a quick border over here. Or I can just also use draw border or lifestyle over here. I'll just pick this one. Okay, and then I'll just draw this line over here, okay? So we have that distinction that Hey, press Escape right now, okay? And you can see that distinction that hey, this is using a different formula then for duress right there using that same formula. So it's up to you to make that specific format consistent across your entire financial model. But just make sure that there's that distinction to make it easier to understand for your user. That's looking good. Now let's start working with are converted costumers. So we now are you going to be going back to our inputs worksheet and less work with the rest of the assumptions over here. So what we have, right, our costumer conversion rate. So let's just assume is 4.5%. So which means How many customers are we converting into paying customers? And then we also have the attrition rate. So attrition rate, pretty much we're seeing that, okay, per year, how many of our paying customers are we losing? Okay, and then we have customer revenue. How much are we making per customer? And then there are payments, right? There's a payment processing costs of 8%, so we need to deduct that from our revenue. Okay, so to be able to start using this one, best practice is to keep on using named ranges. So for example, over here, let's just type in conversion rate, okay, and then this one, let's say that's give it a name of accretion rate, right? And then this one cost the revenue, okay? Right? And then this one is payment costs. Okay, so let's just give it those names over here so that when we start working with our formulas, we can just refer to them very quickly. Okay, so that's our assumptions and now let's start calculating for this one. Write it. Very straightforward to calculate that. So we have this one, then multiply B by D conversion rate that we have. Okay, So I didn't complete the name over here right now. I'll just press tab and we can just complete this out. And let's just drag this all the way to the end. And we have our converted costumers at the end of the year and k, So that's looking good as 4.5%. All right, and then for the total converted costumers, Because if you think about it right, this is the acquired customers that we are able to convert, true our marketing efforts for depth year, for that specific year. And then a new batch, right? We were able to convert more customers the next year. So we need to have a sort of count, right? That we are adding costumers on top of our existing customers. So we need to have that cumulative customer count and a k. So to be able to try that out, Let's just try it here first on 2022, right? So we want to sum the values together and then k. But Let's have a think about it. If we're going to be jumping over here, we want to add this tree together. So which means we need to make this fixed over here. So let me just change this to be right, column B being fixed so that it will always start from here and then we can just continue adding until the very end. So let's just try this. Let's copy this one. Or let's just drag this all the way to the end. And then we can also copy this formula and then paste it back here at the very beginning. So let's have a look. This one looking good, right? Let's jump over here. Looking good, right? We're adding everything from 2021 until 2025. So that's the cumulative total of our converted costumers. Now, if you think about it, if we were to calculate our numbers, it's not exactly logical, right? That we would assume that, okay, this numbers over here would be the total number of costumers are paying customers right away from date one, because this is at the end of the year. So to be able to have a more reasonable amount right off revenue numbers, we can get the average of the two years, right. And that's the converted costumers we will be using for our sales numbers. Okay? So let's say for this one, let's just work with the load since there's no previous year, right? We can just divide it by two. It's like averaging between 0 starting out, and then we have this 13,500. Okay, so same thing as well over here. Let's say we can just go for D average with this two years over here. And what I'll do is I'll just drag this all the way to the end. And let's do a quick spot check over here, right? You can just see that it's the average of this two years and it just applied to everything. And same thing as what we did a while ago, since this formula is different from the rest, same thing. We could just do the same formatting over here. So I'll just add the border. Okay, I'll just do this again and add this border to signify death. Hey, this is a different formula from the rest. Okay? So this is what we did right? To have more sensible financial numbers, we did the average to reflect the sales numbers better. So now we need to calculate our attrition rate. How many customers are we losing per year? Because we cannot expect them to stay like for the next ten years, right? We would lose a specific portion of our costumers year per year. So to be able to calculate that, It's up to you actually butter if you want to base it on the average, right, or the total converted costumers, but for example, ness base it on the average right off the previous year. So for the first year is going to be 0 because we're not losing any customers yet. So let's just paste that in. And then for this one, okay, Makes sure it's negative to signify the last costumers, and then it's going to be the previous year, right? And then multiply the attrition rate that we have defined. So I'll just press Tab and we have 675 because we placed in 10 percent for our attrition rate. So let me just drag it all the way to the end. So we have the last costumers over here. So now we need to calculate what is our net, right? Converted costumers. That's very easy to do. So we can just add both of them together, right? The last costumers end the average and then I'll just drag this all the way to dn. And we have our customer numbers, okay? And now we're getting close, we're getting close. Okay, So now we're going to be calculating what is our gross sales? So which means based on the revenue numbers, how much revenue is each customer paying costumer bringing to the company? So now or to our business. So now we have 35 dollars over here. So we'll just multiply this over here, right? By the costumer revenue, okay? But we're not done yet because what we will consider a swell is since we are talking about revenue, about pricing, we need to take into account inflation as well. Just that's going to kick in Year Bye every year. Okay? So which means our price would increase, okay, to follow inflation. So we need to take that into account as well over here. So right now, this is pretty simple, but we need to include inflation as well. So which means if we were to include the inflation, so that's going to be one plus inflation rate, right? And then we're gonna raise that to the power. Okay, so it's going to be how do we calculate the power? So it's going to be this minus this the year, minus the initial year. So we want this to be, let's say this is going to be fixed all press F4 because it's always going to be the start year and key so that we can count the number of years or number of times that we are going to apply inflation, okay? But for this one, for example, this is going to be the year always in rural 2. So let's make row to fix. So there you go. Over here. Okay, so let's just drag this all the way to dn. And then we have our sales numbers right now and k. So let's do a quick check over here. Let's get our trusty calculator, right? And then let's say, let's calculate 19,622, right? And then multiplied by our customer revenue, there's 35, then our inflation rate, if we recall, that's 4%. So let's drag that again over here, multiplied by 4 percent. And if we have a look at our revenue, okay, let's drag our calculator again. And it's not a release going to be like exactly the same over here, right? But you want to see that it's very close, okay? Because what we're not seeing is the actual decimal places as well. So we don't have that taken into account. If we were to expand this right, then we would have a different number. Okay. But at least we were able to do a quick spot, check that our calculations are working correctly. Okay, so you can do the same thing as well, okay, over here, if you have complicated formulas, just do a quick manual check just to have that confidence that everything is in place. Okay, so lastly, we need to take into account the payment processing for our net sales. So we need to deduct that from here. So this one is going to be one minus the payment processing, which is fairly straight forward, right? That's 8%. So let me just deduct that from here and then drag the same formula until the very end. And there you have it for our net sales numbers.
37. Int - Model - Market Analysis: Okay, so given our numbers, right, we can perform a quick market than that is, is so that we can have a look on how good are we when it comes to converting our blog readers, our website visitors, into paying customers? And are we doing well in terms of market share? So let's start off first with the US population numbers and that's just grab that from our inputs worksheet over here. Okay, so either I can just drag this right now all the way to the end, or I can also just create the complete set of formulas for this column. Uh, I can just copied them all in one go. Okay. So next thing is website visitors. As just grab the website visitor numbers, we've already calculated that over here. And that converted costumers. Pretty easy. We have that ready as just scrap it from here. Okay, So website penetration, this just grab it as a calculation and quick calculation off the person page. And in case of deaths divided by the website for the Persian teach right off the website visitors. And then for the market penetration rate, right, it's going to be this divided by the entire US population. Now we have that ready and let's just drag this ONE the way to dn. Okay, so once we have copied over all of our formulas, right, you can now perform a quick analysis. And the conclusion that we can get from here is we're actually doing great when it comes to website conversion, right? The numbers are pretty impressive because we're accumulating customers right from our website. But 4D market penetration, It's barely moving, right? So which means we need to focus on the website visitor numbers to grow our business. So we need to bring in more people into our website.
38. Int - Model - Profit and Loss: Okay, so now let's work on our profit and loss and we're nearing the completion of our financial model. So it's taking shape. But before we start here, right, That's just jump over to our assumptions and there's a value that we need to use, which is the overhead cost per customer. So this is pretty much nice support costs, right? Or when you use software systems or systems that adds to the cost per customer. So for our assumption that say that's $2, right? For each costumer, for the overhead costs. So as usual, right? That's plays a name range over here. Okay, so that we can refer to that inside our formulas later. Now, let's start first with the total revenue, okay, for each year and we already have that. Okay, So let's just jump over to the revenue worksheet and that's just copy that over. Okay, so we have the revenue and that's looking good. That's pretty straightforward and that's just get it straight from the source. Staff costs, staff expenses, we already have that. So let's just jump over to our staff and expenses, right? There's pretty much two sources over here, so we could just pick one of them. I'll just pick this one okay, as looking good. And let's just drag this. And we have all of our staff expenses now for the costumer overhead costs. So this formula is going to be the net converted costumers times d costs per customer. It rarely have that from a while ago, that's $2. And okay, so there's also actually another worksheet, if you notice, there's also the net converted costumers that we have over here. So one thing to take note off is the best practice when you want to get make from a different worksheet for your value, always get it from the source. Because over here, right, for market inadequacies, it's just getting it from revenue, which is from this specific cell over here. So it's better to get it straight from the source. Because if for example, let's see, instead of getting it from the revenue worksheet, I took it from here, right? It will be difficult to maintain it in the long run because we're having chaining right now. We're introducing chaining over here. It's like from revenue and to market, then additives and then to the summary page. Okay, so we want it to get like values straight from the source. So instead of getting it from here, okay, I want to stress that Let's go straight to the source and get it from here instead. Okay, so that's looking good. Hold on. Let me just go back here and then let's update our formula, multiply it with the overhead costs. Okay, So we have that for the overhead costs over here. Let's just drag this all the way to the end. Now we have another type of expense, okay. So one thing to take note off, right? Is you can create separate expenses page because this are still expensive, right? But since we only have a couple of additional Roles on top off the staff costs of the staff expenses. Then we can put it straight here, right inside the summary page, our profit and loss, just to simplify the number of worksheets, but that's up to you. That's your design. Like, how do you want to design your model? But in our case, we just place everything over here TO seeing goes as both for D, F, B adds marketing costs. So one thing over here, right? Yes, let's just go back to our assumptions, to our inputs worksheet. We have our FB ads marketing budget. So let's say, let's say we're planning to spend 200 thousand, okay, on the first year. And then we have the assumption that the marketing, right, the budget for debt 1, will decrease 40 percent each year because our goal is to decrease the marketing costs, right? And then shift to organic traffic, to natural traffic. Okay? We want that to happen. So it's just fairly easy to do that, to calculate that, right? And then we can just multiply that since we have that assumption listed out over here and we can just multiply it by 60 percent, okay? And then drag the formula oh, the way to the end. So same thing, right? Since we are using different like a formula over here and then this one is the input assumption. So we need to change the formatting of this one to show that, hey, this is different from the year 2021. So let's change to sell style to normal, since this is a formula right now and then just change this to a dollar value. Okay, so now we have our marketing budget laid out over here. So let's go back to the summary and we could just copy the marketing budget or the marketing costs over here. Let's just drag that as well, okay, looking good, okay, For the total expenses, Let's just add everything together over here, okay? And then just drag this just to have a field, okay? And that's looking good. And then we have our profit and loss. So what is going to be our total amount? Let's just do a quick adoption, revenue minus expenses, right? So we have our profit and loss values over here, okay? So the last one is to calculate the cash flow. So to be able to calculate the cash flow, we need to have a sum, right? So for example, from here, it's just this in 2022 is going to be the total of this in 2020, the tree, it's going to be the total of this tree values over here. Okay, so let's just try over here and let's do a quick sum, right? And there's going to be this until this, okay? So how do we create the sum? So which means be 10, right? Is always going to be fixed because that's going to be the first cell. So I'll just press F4 on that month, make that absolute and foresee ten, That's going to be relative until the very end. So let's just copy this one, drag this all the way to the end, and we can do a quick spot check. I'll just copy this as well and paste that same formula over here. So let's check it out, looking good, right? And then this one is the sum total. This one, okay? Hello, this one, looking good. Okay, Let's just jump over here. Okay. Is the total from 2021 until 2027. And then at the very end, it's a total of everything. So that's our profit and loss worksheet.
39. Int - Model - NPV IRR: Okay, so now it's the moment of truth. So we are going to calculate the crucial parameters right now for the NPV and IRR so that we can make the decision, right? If this is going to be a worthy investment or not, okay, so we have the five-year NPV, we have the five-year IRR. And then for a longer term view, since we have 10 years worth of project the data, we can also calculate the 10-year NPV and the 10-year IRR. And then separately, we'll be calculating the payback period as well. Okay, so before we start things off, what do we need to do first is we need to define our cost of capital. So I'll just jump over to our inputs worksheet. And we have here is our cost of capital. And we will be using a constant number here so that for all of the projects in our company or that we are going to be working with. We can compare apples to apples when it comes to potential projects. So let's just put in 14% over here and S with our best-practice, that's just type in a name for the cost of capital over here. Okay, so that's looking good. Let's jump over to our summary and then 45-year NPV. So the question is how much money is left over after taking into account the cost of capital. So that's fine out soon. So for this one, right, we need the rate, so that's going to be our cost of capital. Okay, and then for the five-year values, Let's jump over to our profit and loss so that we can use the money coming in and out. And k is going to be negative for the first few years, right? But let's have a look. What's going to be our NPV. So it's going to be negative right over here. So which means for the first five years, okay, not looking good. But let's have a look as well for the IRR, Okay, So since the NPV is negative, right? And our cost of capital, okay, So let me just do a quick recap. We placed in 14%, right? So let me delete that and I just paste that in so that we can quickly see since NPV is negative, so which means our IRR would be less than the cost of capital, okay, So that's going to be below 14%. So let's calculate that for the values. Let's grab the first five years over here, right? And then we have negative 8%, okay? So that's 45-year IRR. Now let's do the same thing as well for the 10-year NPV so that we can have a longer term view on how it would look like. So let's type in the formula, cost of capital for the rate, right? And then let's grab the 10 year profit and loss values over here, ok, so that we can take all of the cash into account then, okay, we have 4 million, okay? So it has a positive value right now and in ten years, okay, It's looking good. Okay, so let's do the same thing for IRR swell. So IRR, it's grabbed the 10 year values over here, right? And then we have our IRR off 50% Okay, so you can see that in the short-term or medium term, if you define that for your five-years, it's a bit painful, right? But in the long run, it's going to make up pretty quickly. Okay? So that's pretty much for the NPV and IRR. So it's very easy to compute this because we have our foundation set in place in our financial model. So you can see that all of our hard work has paid off at the very end. Now let's have a quick fun activity. Okay, So let's say you want to explore that, okay? How do we get our five-year NPV to be 0, okay, So which means we're at these, at 0. We're not losing money, but we're not making money eater. Okay? So which means we want to set that goal, that minimum goal, okay, four or five years to be NPV 0. Okay? So we need to select a number that should change and k we can use Goal Seek for this scenario. And then let's say, let's have a think about what do we want to play around with when it comes to our values over here? Okay, So let's see, let's pray out customer revenue so that what if we increase the price but to what amount? Okay, there's a question mark at the moment. When we increase it to a specific amount that our NPV for five years will be 0 so that we can explore that possibility if that's reasonable for that new price. So let's try out our goal seek experiments. So what I'll do right now is let's jump over to data, okay, forecasts, what-if and that it is, and that it's target, our NPV over here. That's go for Goal Seek. Okay, so set the cell, the five-year NPV to 0 by changing cell. Let's jump over here and then customer revenue. Okay, let's see the magic. Now. You could see NPV is now 0. Pretty cool. Let's jump over. And it's said this too. Okay, Let's jump over to our customer revenue. And the amount is 39.8 is three, so that's around $40. So which means just by increasing the revenue per customer by $5. Okay, you can now quickly assess, is this possible or not. But if we are able to accomplish that, right, our five-year NPV is now 0, okay, so which mean is just with this simple change, just one change, okay? If we're able to improve that, okay, our financial model just recomputes everything automatically. And all of a sudden you can see here, well, cool, right? The IRR now is now positive. The five-year is at 14 percent end, if you notice, right, since our NPV is 0, that would imply that our IRR would be exactly the same as our cost of capital. Okay, So we have 14%. If you jump over here, our cost of capital is 14 percent. So we can see the relationship of this numbers at work, which is really cool. Okay? So right now you can use goal seek to play around with the other input parameters that you have over here so that you can see that, okay, which one can we tweak, which is more feasible to tweak? And then is the new number, is that feasible as well so that you can play around with your targets and possible scenarios as well. Okay, so for the next one, what we will do next is we will calculate the payback period.
40. Int - Model - Payback Period: Okay, so now let's get the payback periods. So how do we define payback period? So pretty much what we want to know is, which year did our cashflow start to become positive, okay? Because over here, right, you can see negative numbers over here and then we start getting positive. But when we think from a payback perspective, we need to calculate using the cash flow because over here, right, we're losing money over the first three years and by 2023 we're losing by a total of 1,000,200 thousand and we need to make up for that. And over here, right, we start calculating, and pretty much you can see that the payback took effect on the year 2026. So which means we need to reflect that over here. You could either reflect the year, right, dynamically or we could also have it as six years because we just counted from here. And that's the time that it started to become positive for our cashflow. Okay, So this is where the cashflow would come in and we've already calculated death from a previous tutorial, right, for this one. So to be able to start off, we need to think about, okay, so how do we grab the first positive value from this? So which means we need to use one of our lookup functions, right? And it has to be an approximate match because I don't think we'll be able to find the 0 value because that's the time it starts becoming positive, right? So we're going to be using VLookup, HLookup or lookup. But in our scenario, it's going to be look-up because the value is in question. We need to look for the first positive value here, right? And then we need to get the year from the very top. And since We're rows are pretty separate from each other, then lookup is perfect for that. And since we are also looking for an approximate match, then lookup is the perfect match again. Okay, so let's try it out. Let's see what's going to happen. Let's go for lookup, right? And then the lookup value, okay, since we're talking about positive, Let's go for 0, okay? And then forward the lookup vector, okay, which is the list of values that we want to check, right? And then the result vector would be the years. So okay, so what we're expecting is going to be 2026 over here, but There's going to be a surprise for you over here. It's going to be 2025 because what lookup does, if you recall from our example from before, it's going to give you this one over here, okay? Not this result is always one behind its wants but behind. So which means we need to add this manually by one, okay, So that would give you 2026. And the cool thing with this one is since this is a formula, even if the numbers change, right, then we would have our payback period year over here dynamically. So the next question right now it's okay, I don't want 2026. I want to reflect six years instead. Okay, so to be able to do that, we're gonna do some math over here. So to be able to do that, let's just subtract this from the start of the year. So we have five years, right? But if we were to count it, it's not exactly five years, 1, 2, 3, 4, 5, 6. Okay. So from the subtraction, you need to add one again to consider the duration from 2020 to 2021. So let's add one here, right? So we have six now, okay? And then after six, so let's say we want to make it look better so we can just add the CONCAT over here, right? And let's just add the texts of years. Okay? So now it would look like six years over here. So we were able to add the US texts using the CONCAT formula. So it's a pretty long formula, but I've just shown you, right when it comes to designing long formulas, just go step-by-step so that you can evaluate first is working fine for the first portion. Okay, looking good. Let's jump over to the next end. Any keep on doing that until you get to your desired result. Okay, So that's pretty much for our financial model. And we will jump over to scenario analysis so that we can play with multiple scenarios.
41. Int - Model - Scenario Analysis: Okay, now let's talk about scenario analysis. So scenario analysis is just putting functionality in your financial model. So that at a single click, it's going to be easy for us to jump from one potential scenario to another instead of changing the assumptions one by one, then we can define a base case scenario, best-case and worst-case scenario. And then it's up to you on what values that you want to tweak between your specific scenario. So what I have here on top right is just a blank cell right now, the selected scenario, and then we'll be setting this up in a short while. And at the bottom, okay, what I have over here is our scenarios table, okay, so I have a section over here for our scenarios. And just to have fun, right? We want to play around with the costumer revenue number, right? And the attrition rate. Because let's say this two parameters are the ones that we see as the most crucial when it comes to driving our numbers. So let's populate, right? There's going to be tree, but you can add more depending on your needs. Strike. But in our case, let's have the base case that's had the best-case and worst-case scenario. So let's just populate this values over here, right? We have thirty-five dollars, that's our base case, and then we have our attrition rate off 10 percent. Best case. Let's say we were able to increase it to $40. And for the worst case, let's say it went down to 30 volts. Now, for our attrition rate, let's say for the worst-case, right? Attrition, we're losing customers at the higher rate. And then over here for the best-case, Let's see, Let's go for eight per cent, okay, So we have our table. But right now how do we make this neck take defect to the entire financial model. So to be able to do that first nets have this, let's set up a drop-down, right? That would show this tree choices over here, okay, the worst based and best-case. So that's pretty easy to do that. Let's go for data, make sure that's selected. The cell is selected. Data, data tools and data validation. Now over here, and let's select a list because we want to have a list that you are enforcing the user to select, Okay, and our source of values. Now let's just go over here and then select this tree values over here. Let's go okay, looking good, right? And then you can select a base case, best-case, and worst-case scenario. Now, the next question is, how do we ensure that based on the scenario you have selected, right, the values would get selected from our table over here, okay, so we need to update this values over here. So let's work first with costumer revenue. How do we make sure that when you change the drop down selection, then this value changes as well. So which means we need to make this dynamic. So let's say what formulary when I use for this one. Since our scenarios table, if you have a look at the orientation is horizontal. So which means the lookup formula would be perfect for debt, okay? First things first. So what will be our lookup value? So our lookup value will be from the drop down selection. Okay? So looking good. And then our table array and K, We can just select the table over here, right? And then, okay, if you have a think about, it's going to look for the case value over here, and then what will be the row index number? So if we were to count the rows 123, and since we are talking about customer revenue is going to be wrong. Number 2. And then for the matching, it's going to be false because we want the exact match to be returned. Okay, so now we have 35 dollars over here still looking good. Let's just try it out quickly as change this to the worst-case. And you can see that it has changed to Turkey dollars, which is our worst-case scenario. So we're going to do the same thing, a smooth for our attrition rate over here. Let's change this to HLookup, same pattern, okay, lookup value, Let's go up, Let's grab it from the drop-down value over here, our table, okay? And that's just grab the entire table for our scenarios over here, right? And then row index num, okay? Which row? It's 40, attrition, 1, 2, 3. So it's going to be row number three, okay? And then for the matching, it's going to be full for exact match over here. Now we have 12 percent because what we have selected at the moment is the worst-case scenario. Now, since we have everything setup, our scenario analysis setup is complete, but as best practice, since this is now dynamic, okay, this is not manually inputting anymore by dy user, okay? We need to change the formatting of this. We need to make sure that this is formatted as normal format over here so that for the user, okay, we're telling the user, okay, they'll change this because this is dynamic, but let's fix the formatting first. Two percentage singles for the customer revenue. That's go to Cell Styles. Let's remove the formatting for our inputs, right? And then for this one that's changed this to a dollar value over here, so that we can keep the best practice and tell the user that, hey, this is not the place that you need to change the values. This is dynamic. If you want to change it, do it over here inside this a nurse table for customer revenue and the attrition rate. And then if you want to switch from one case to another, then you need to do that via the drop-down over here. Okay, so now we have our scenarios. Let's have a look on how like how our numbers affected. If we have a look at our summary page, right, our five-year IRR now drops to negative 39 percent with our worst-case scenario. And it's a negative 1 million over here, but do notice as well, right? Payback period now increased from six years to seven years. But let's say, let's go for the best case scenario. Let's see what's going to happen. Okay? So five-year NPV is a positive five-year IRR 75 percent and then payback period is five years. So there's a lot of days that you can do with this model. You can add more scenarios as needed, and then you can just make it easy for your end users to be able to switch from one specific scenario to another inside your model.
42. Thank you and Congratulations!: Thank you and congratulations
on completing this course. So I hope that this has
helped you in learning the financial modelling
basics from scratch in Excel. So you are also able
to leave a review on this course simply by clicking
on the link over here. And once you click on the link, you're able to leave a review through the leave Review
button over here. So please, please leave an honest review as I would
love to hear your feedback. You can also check out my
profile by clicking on my name, this link over here, or more Microsoft
related courses. And once you have
clicked on the link, it brings you to my profile
page and you can continue your learning journey by going through my top
microsoft courses.
Bash (BizTech Matters), Online Teaching Excel Expert
