Part 2/9 - Learn Financial Modeling in Excel - Step by Step (By Ex-JP Morgan Analyst)

Hi all, welcome to this 9 step video series on financial modeling in excel step by step. If you have ever wondered how Anderstat JP Morgan or top firms like that create financial models that drive billion dollar decisions, you are at the right place. Over the next 9 videos, I walk you through every critical step from building the structure and analyzing historical data to projecting revenues, cost, cash flow analysis and even balancing the balance sheet. As a former JP Morgan analyst, I have built countless models and I know the importance of getting each step right. In this first video, we are laying the foundation of creating the structure of the financial model. Ready? Let's dive in. Hello friends, welcome to this step by step tutorial on financial modeling in excel by wall street mojo. I am Dheeraj Vaidya and I will be your guide as we build this financial model from scratch. So if you're learning financial modeling for the first time and you would like to figure out you know how it exactly works, this is the right place. So in this session, we're not just going to talk about theory as such, you know, we are going to take this whole financial modeling session in an excel template that I have prepared for you. So by the end of this tutorial, you will have a hands on experience on how to build this model completely in a fully integrated way. Right. So please note, this is a beginners model. And a lot of assumptions which we have taken here to create these models are very, very basic. But the idea here is that, you know, if you are new to financial modeling, this will give you a good idea on what exactly this is and how integrated three statement financial model is built. Right. So before we get started, you know, you will need two things. The first is that we have built a case study on financial modeling, which you will need as default, so that you know, when we go through this case study, step by step, you will know what to do. So this is the case study, which I am referring to, you've created this case study here, I'll come to the contents of this case study, but I would like you to first download this, right. So this is a case study about EPIC solution, an IT company, and you have to basically project the financials and create a financial model for this company, right. So we have this case study created. And you would need access to this in order to create the financial model, right. The other thing that you will also need is this financial modeling Excel template. So this is a without solution sheet. And I have already populated the income statement balance sheets for EPIC solution. As you can see here, the historical data is already available with you. And with the help of case study and the step by step instructions, they start creating the financial model, right. So once you complete your financial model, your financial model will look something like this, where your income statement will be fully complete with forecasts, right, the balance sheet will be fully complete, right. And the cash flows also and all the other aspects like the depreciation, working capital and debt schedule will also be fully complete. So that will be the end objective of creating a three statement financial model. But for now, we would need the without solution sheet where all the things are empty. So the idea here is that I'll provide step by step instructions and I'll do the same in front of you and I would expect you to do it along with me, right. So now, let's get back to the steps which are mentioned here for creating the financial model. So here, broadly, I've divided this into nine steps. First is creating the structure, then analyzing the historical financial statements, projecting revenues, costs, then depreciation, working capital, completing cash flow statement and projecting debt and then integrating everything. The important thing for you to note here is that this financial modeling is not a linear approach. A linear approach will be that you first forecast the income statement and then go to balance sheet and then forecast the cash flows. In fact, here, we will be moving all over the place. Like, for example, when you look at the step number three and four, we are projecting revenues and costs, right. And then we are also projecting depreciation, which is leading to some of the items in the balance sheet. So this is where, you know, we stop working on the income statement, right. And then we move directly to the working capital. Here again, instead of then working on the balance sheet, we directly move to the cash flow statement, as you can see here. And then finally, we come to debt, which is again a balance sheet item. And from the debt, we also get the interest expense, which is again an income statement item. So a lot of things are kind of integrated. So it's not a linear approach like the way it is shown here, but it will be divided into different steps. Okay, so you'll understand what I am saying in a moment as we discuss each of these steps. So let's get started with step number one, that is creating the structure for financial model. Okay, so we'll target this one. But before that, we'll start looking at the case study. And in that context, we'll solve each of these steps one by one. Okay, so let's get started here. Let's read this executive summary. The executive summary tells us that we are talking about EPEX solution, which is a leader in global technology sector. Okay. And there are three portfolios products for EPEX solution. The one is software, then the hardware and IT services. Now, what is kind of expected, you have more details about EPEX solution. But at the end of the day, you are going to project the financials of this company EPEX solutions, right. And broadly, we understood that it is divided into three different segments. So a lot of companies have different segments, or it could segments could be based on geographies as well. Here, it is clearly mentioned, one is software, the other one is hardware, and the third one is IT services. Okay. Now, we've been provided with more details about each of these segments, right? Like for example, software EPEX solution has robust software products designed to streamline business operations across various industries. And it could be enterprise softwares, cloud based solutions or customized software development. Hardwares are related to servers, networking equipment, computing devices and peripherals. And IT services are basically IT consulting, cloud services and cybersecurity solutions and managed IT services as well. So these three segments are there, right? Now, the thing is that we were creating the financial model are going to step into the shoes of equity research analyst at capital partners. Okay. So that's the objective, you are going to work as an equity research analyst at capital partners, and your job is basically to create a financial model for EPEX solutions. Okay. So here we have been provided with more details on what equity research from typically does. But imagine you are approached by your senior analyst, and he has asked you to create the financial model from scratch. Okay, so with that, you get started with the steps which I discussed in the nine steps, we do that one by one. So the first step is basically to gather and organize all the relevant financial data from EPEX solutions and reports, right? So when it comes to creating a financial model, what we do is that we start thinking of the financial data, where will it come from, the typical approach is that you go to the annual reports, SEC filings, and find the financial data like the income statement, balance sheets and cash flows. So this is where we have already provided this to you, income statement data, we have also provided the balance sheet data with you and the cash flows here we didn't need to simplify, we haven't provided those details with you, right? Now, coming to the discussion of step number one, that is the structure of a financial model, right? So once you have the financial data with you, you have to put that in a very systematic fashion in the Excel sheet, which I've already done for you. But the idea here is that you know, you have to have a well formatted financial model like the way it is here, right? So you may want three years or five years of projected data, I mean that historical data with you, so that, you know, you can do analysis on that data. So the structure would mean you have income statement, balance sheets, cash flow data with you in appropriate format. So as you can see here, you know, the format is analyst friendly format, which means that if you want to analyze this company, right, so you would like to understand what are the gross margins, what is earnings before interest taxes, depreciation and amortization, what is EBIT. So a lot of companies do not provide a very systematic representation of the income statement. So here, we have that systematic structure in place, right? So this is what I meant by creating the structure for your financial model. Another thing to note here is that when it comes to the structure, a lot of financial analysts have different tabs for each of these objectives, like income statement separate balance sheet separate cash flows is a separate thing, right? And then we have supporting schedules as well, we'll come to these schedules one by one. But for now, what I meant was, each sheet has a different objective. The other analyst which you know, I have seen very low in number, but what they also do is they create a full financial model in a single sheet. So a single sheet will have all the details of income statement, balance sheet, and cash flows and all the other details as well. So I feel that that's more complex, because, you know, you will struggle to find the objective of each of these areas within the single sheet. And, you know, it goes deep into a single sheet, like for example, you know, this may run into 800 900 rows, and it will be very difficult to debug as well because of that. So I would prefer that you create a different tab for each of these sheets, right? So that's about the structure. Another thing that you need to take care of here is the color coding or the formatting part. So when we look at this numbers here, these are constant numbers, right? These are actual factual data, historical data. So this has to be color coded in blue. So let me put it here. Blue is basically for constants. Okay. All right. The next thing that you need to understand is that there are formulas right written here, like for example, some formula, which is a sum total of these three formulas are usually black in color. So let me write this in black is basically for the formulas. Okay. So that's another important aspect of financial modeling, color coding. And there is third color coding as well. And that's basically green color coding and basically green, you can use or choose any type of green asset. This is basically the links to other sheets. So what do we mean by links to other sheets? I'll just give you an example here. Like for example, you know, if we have, let's say, depreciation number, okay, it's coming from this sheet depreciation and capex. Okay. So when we are linking it from here to let's say here, okay, something like this, then it has to be green color coded. Like for example, whatever, I'll put some number here, it should be green in color so that I know that this is coming from another sheet like this. Okay. So please understand that, you know, color codings are super important. And when it comes to color coding, this is kind of industry practice. Blue is for constant black is for the formulas and green is the links to other sheets. Okay, so we'll follow these nomenclatures and the standard practices in our model as well. Okay, so with that, I hope you understood the first step regarding the structure of the financial model. It's actually the hardest step because you will have to navigate different annual reports, copy and paste the data in this format, which is the analyst friendly format, color code them, put them in different sheets, right. So this is very challenging. And that's why for the first model, we have already given this thing for you. Okay.