Excel Tips for Financial Modeling

Hello everyone, I welcome you to the second session of financial modeling. If you haven't seen the first session yet, then I would highly recommend that you watch the first session from here and then start this session. In this particular session, you understand that we have to do the whole work on Excel, right? So Excel should be in our absolute control. We will learn in this session and in the coming one or two sessions that how you will bring Excel in your favor. Usually you must have seen that you are worried with Excel and when it comes to financial modeling you need to understand you will be working with a large amount of data so it is very necessary that excel should be in your absolute control we will only be understanding and learning the most relevant excel functionalities and excel setup for us this particular video is divided into two parts First, we'll understand what are the settings of Excel that we have to do by default only once. And in the second part, we'll understand that what are the functions of Excel that we'll be using in multiple places in our financial modeling. So it becomes very important for you to understand every function very well. And if you'll see in the description, we'll be keeping the exercise sheets for you. Who's Smith? proper dia hua hai ki what is the problem statement, aapko kya kya steps follow karne hai, toh mera request aapse ye hai ki once you watch this video till the end, just stop it, open the excel sheet and start doing it simultaneously, matlab ek bar shanti se vatke pura video dekho kuchh mat karo this dhauran, don't open the excel sheet, don't do anything, pehle ye samajh lo ki mai kya batana chaara ho, then open the excel sheet, download the excel sheet, open it exercises are written in it, you should do that thing and perform it again. If you during that re-performance or during that practice if you have any problem then go to my video, go to this particular video and see that how I have redo it. It is very necessary for you to do the at most practice. If you just watch this session and you know you won't practice it on your own, then nothing is going to happen. So let's start. Yes. So let's start with the Excel function. We will start with the Excel setting. What all settings we have to do in Excel. Don't worry, I have given all these things here. This file is also available. But if you have to do this by reading, then you will learn financial modeling by reading books. So be with me till the very end. And it is very crucial for you to attend each and every session and see it one by one. And do the exercises as per the exercise sheet in the description below. First of all, my intention with you is that the less you can use the mouse, the better it will be for you. I will tell you a very simple hack to eliminate the mouse. First of all, if you get very impressed by someone else's Excel speed, that he does Excel very well. Or you are seeing that someone is very pro in using shortcuts. So that thing is not going to happen to you overnight, that is for sure. but if you are conscious about this thing that if I want to increase the width of the column or reduce the row of the column or reduce the height of the column or add a new column or row, then how can I do it with an Excel shortcut? and if you are very conscious that I don't want to use the mouse and I have to do this through the shortcut, then over a period of time you will use that shortcut 3-4 times and it will be very easy for you. So now what we have to do is we have some settings in Excel. which will optimize our excel for financial modeling. We will do that first. So what we will do is, I am using the mouse for 2 minutes now, but I will tell you how to go over there. So what I want you to do is, I want you to go to files, I want you to go to more, and if there is no more, then you will see the options directly and we will come to the options. By doing this, an option window will open in front of you. Just in case if you don't want to use the mouse, it's very simple, you will press alt, the moment you will press alt, you can see that if you want to open a file, you can press F, H, N, P for page layout and what not. Then without pressing Alt, I have pressed Alt just once. Without pressing Alt, I will press F00 for more and I come to option. Or you can do Alt F. Alt F and T So, this will also come directly to the option window. This is written very easily. Now, here there are multiple channels over here. First of all, we go to general. Generally, what we have to do is we will be focusing on here that when creating a new workbook, that whenever I create a new workbook, then what happens, then we will not do the default setting again and again, that sometimes new Romans is coming. See, my intention is that you make a very professional financial model, which actually makes the investment bankers who follow the standards and conventions. Okay, so I want to teach you to that level and here only on YouTube. So what we will do is we will set the default font on Calibri. Calibri, okay. You don't have to take any other variant of Calibri. Just don't take any other variant of Calibri like light, dark, etc. We will select plain Calibri. We will select font size 11. We will select default view for new sheet as normal view. And include this many sheets. This means that whenever you are opening a new excel file, what you want is that how many sheets are open. Earlier it used to happen in old excel that 3 sheets are open. Now we don't want that one sheet is fine. If I want more sheets, I will add it on my own. Not an issue. Right. So, when you do it here, it will give you a prompt. It is not giving me because I have not changed my font. But say for example, if I change my font size, I will go with Alt F T. And I change my font to say for example, this one. Berlin. Okay. Berlin sense FB. And as soon as I press it, it will give me a... prompt data please close and restart excel so that the font changes can take place why it didn't give me before because my font was already calibrry and I have just changed it to calibrry only so I will change it to calibrry again and I will click on ok once that is been done we will just again open it and after that we will come to personalize your copy of Microsoft Office so here you can put your username that you know whatever you want, I have put evaluation school here, you can put your name, it will be really good if you name comes in author if you want to decorate the background of the office then you can decorate it at the back, it is not very necessary like if I am a circuit then I can see the circuit at the back, if I do calligraphy then I can see the calligraphy, whatever it is I will leave it on circuit as it is default setting. And that's it. Here one more thing is there. This show the start screen when this application starts. This last option should be unticked. If it is ticked then what will happen? I will tell you something. Say for example it is ticked. Okay. So what will happen? Whenever you open a new excel then it will not open a spreadsheet. Rather it will open a window like this in front of you. That this is the start window. Now you need to go and open a blank workbook. By yourself. Now what happens is it looks very easy to listen. That we can do this ourselves. But when you are doing. When you are working on excel for day in day out. You want to optimize it so well. That it works in your favor. And your time is saved. If even one minute is saved. Then I will try to save that one minute. Okay. Now what we will do is. We will go to the formula tab. Again. I will try. And I will do it again and again so that you know it will be easy for you. Alt F T. Option opened. After opening the option, I went to formulas. When you come to the formula, then see one thing. In the formula, first of all you have to come to calculation options. In calculation options, the first function that you see here, this setting is of workbook calculation. In workbook calculation, it is automatic. Automatic except for data tables and manual. Look, I had two options. Either I keep this video very short so that the views come more. And I tell you quickly that this is what you have to do. But it is my responsibility to tell you that if I don't want to do it, why not? So say for example, I keep it manual for one minute. And I write here 100. I write 100 below. And I add the sum function of both of them. 200 came. Now what I did? I do it 10. So you are seeing something is happening. I did 10 so 200 and 110 did not change. Why did not change? Because I have said my functionality in formula that brother you do not calculate any formula automatically. You calculate it manually. Okay. Don't do any automatic calculation. Automatic except for data table is when you are working with some data which is or with some financial model which is very heavy with data tables. Today, next session we will learn how to work on data tables. But if you work with data tables, then it takes a lot of time to refresh it again and again. Excel takes a lot of time. So it says that you automate all the formulas but leave the data table manually. I will have to update it on my own. And the best option is automatic. We will keep it on automatic. When you put it on automatic, it automatically entered 100. I am doing it again 100, so it is bringing it to 200. Okay. I hope this is very much clear. Now, again I will go to... Formula. In formula, you will see here, enable iterative calculation. Understand this very carefully. Most students don't understand this. So, please try to gain at most attention as possible. We have to keep this unticked. I will tell you what is its function. First of all, if I keep it unticked, what will happen? See one thing. I have given an example here for iterative calculation. This is my iterative calculation. Now, you need to understand. For example, these are the sales of my three different companies. These are the sales of my three different companies. One is Tata Motors, one is Maruti and one is Aishare. These are all hypothetical figures. Tata Motors has a sales of 1000, Maruti has a sales of 4000, Aishare has a sales of 500. And the total sales of the entire market, I am saying that there are only three companies in India that make cars and no other company. So this 5500 is the total market size. For a minute, understand this way. The total sales of the market. So what is happening now? Now this normal function is applied. Normal function means I have summed all three. And this total is in my total sales. I will make it total sales. So, it is in total sales. But now you have put a condition that the sale of Tata Motor, that is one third of the total sale of all three companies. You have put a condition like this. So, what did you do? You see, understand my point. These three things are input and this is output. Now input is different, output is different. Input is in absolute numbers. Output is their calculation. But if you accidentally link input with output, then it is called circular referencing. What did you do? I divided the sale of Tata Motors from total sale by 3. When you do this, then it will give you an error prompt. That you are doing circular referencing. Don't do this. There are one or more circular references where formula refers to its own cell either directly or indirectly. This could cause them to calculate incorrectly. Do you know why I am getting this prompt? I am getting this prompt because I have not enabled iterative calculation. Okay. Just once I will bring it back to normal. And now I will enable iterative calculation. I have enabled iterative calculation. I have enabled it. And I have done OK. Okay. Now it is normal. Input is different or output is different. I link input with output. Divided by 3. Now when I will interpress. So what will Excel do? Excel will bring a figure of total sales. Which will be exactly one third over TAMO. Means in Tata Motors. Means it will do LHS equals to RHS automatically. See this. So Excel says. If Tata's sale was 2250. or 2250 and just a minute I will make it normal for you if Tata's sales were 2250 Maruti's sales were 4000 and Aishar's sales were 500 then your total sales would be 6750 and 6750 will be exactly Tata's motors sales would be exactly 1 third of 6750 are you getting my point we always have to be careful Iterative calculations To disable Okay Second one is When you go to Working with formulas In the formula tab So here It will write R1 C1 Referencing style We have to keep this Unticked What does R1 C1 mean Basic Very simple function Wait a minute Its function is When Yeah Now you are seeing A1 B1 B2 So what it means is It is doing columns A, B, C, D Alphabetically And rows In numbers 1, 2, 3, 4, 5, 6, 7, 8 This is its referencing style To reference any cell So if I am on this cell Then I will say I am on E2 Simple as that Alt F T Went to formulas Now if you do this R1, C1 And do it ok Then see this Now what happened He made columns and rows alphabetical. Now, how will I call this? I will call it C5R2. This becomes very complicated. Why? Industry also doesn't use this. We are doing financial modeling to remove complexity. And not just because we want to make things very complicated. Now, you see here very carefully. I don't want to waste more time over here. But if you come to the formula at the end, then you take the reference of this sheet and untick all these things. Like inconsistent calculated formal iron table. What does it mean? If you want to understand it, then understand it. It shows an error. I will do this for a minute and then tell you. There is no other problem. Like what I did here, I did, just a minute. So what I did here, I wrote a hundred number and now I am making a table of 100. I am making a table of 100. 100, 4, I will explain all this one by one. 100, 1, there. 2, 4, 5, 6. You can see that there is a formula here. From what is written in K25, you multiply 1, 2, 3, 4. Do you know what I do in the third one? I directly do 100 into 3. When you do this, it will prompt you to do an error and a green screen will appear here. I have unticked all the options, maybe that's why it is happening. But you can do it really easy and it's a very simple thing to do. So just don't waste much of your time. And you can see this, take a screenshot from here. This is in the excel file. So you can just do it like this. I will just remove this. Now, we will move to save. So when you open save, When you open your excel option and go to save, then you have to do save my workbook within every 1 minute. What will happen is that excel will auto save it every minute. Now this is 1 minute, if it was 30 seconds or 5 seconds then every 5 seconds I would have asked excel to save. And keep the last auto recovered version, leave this as default. You don't have to do anything in this. Don't worry. You don't need to do anything on this. Then we will come to the advanced function. In advanced function, you have to keep this as unticked. Its basic funda is that whenever I enter something after writing something in a column, then where does my cursor go? Okay, I'll just show it to you right now. I'll tell you in a very easy way, so there won't be any problem. I went to advanced and after pressing move down. If I press move down, And I have saved it. Okay. Now see. Now as I press enter, my cursor has come down. If you want, if you are comfortable with it, then keep it. I am not comfortable. I want when I press enter, my cursor should remain there. It should not move from there. That is why I do it normally. I keep it unticked. But that is completely optional. If you want to do it, you can do it. If you don't want, don't do it. Okay, and third is my add-ins. What you have to do in add-ins is that you have to keep one thing in mind. That in your add-in, analysis tool pack should be in active state. Because when we will learn to do regression, then only analysis tool pack will be very useful to us. How can you do this? You can go to manage admin. I will just show it to you right now. You come to add-ins. First of all, you have to check if the Analysis Toolpack is active in the application add-in. If it is active, then no worries. If it is not active, then you have to go to the Go tab and tick OK. I am not talking about the VBA, Analysis Toolpack. So, you have to add it in and click OK. It will automatically come in the application. This is my complete Excel settings. Now we will move to the next stage where we will understand such functions of excel which will help you a lot in making the complete financial modelling. In this particular part, we will learn some basic setup, advanced setup, basic formatting And then some functions without which you can't make a financial model. So we will start with basic convention. Convention is that which is followed by the whole industry. Along with following, if you are following it then you are following the industry. If you are not following then you will feel that some amateur has made a model. My objective for this session is that I should tell you all the things that are part of a professional financial model. Okay, so let's start quickly. For example, number one. We don't have to start the first column of the spreadsheet from there. The width of that will be smaller than the rest of the columns. It will be a margin column. For example, if I have to set its width. Assume this is the width. I have to set the width of this, so I have to do two ways, either I right click on the column and set the width of the column. Again, the point is that we will use the mouse less. So what I did is, I am here for example and I have to select row A. So I will press control and space together. So my row A will be selected. Then I will press alt, so I will press H to go to home. You can see that it is for home. If format is O, then I will press O and press W for column width. I will tell you again, Alt H O W, how. And here I will set 1.15 in column width. So my column width is set at once. What is its use, what is necessary to do, it is very important. It has so much important use, I will tell you in this session. Then in making financial model, first of all we will write currency on the top. Whatever my currency is, it will be written on the top. So that you should know in which currency I am talking. Then from here my years will be plotted. From here I will plot years. So 2020 is my actual year. Then 2021-22 will be like this. I have two options that either I do hardcode. and so on blah blah blah blah blah 4 5 and 20 26. Okay, here I will do hard coding like this. One thing to keep in mind is that hard coding should be minimum in financial model. You need to understand this, okay. If you want to put any input of hard coding then that input should go only once, not again and again. So what I will do is, I will say in 2020, do plus 1. And I will pull it till 2026. So what happened is that if I am doing 2019, then automatically everything is changing. Okay. Now the second thing that I get is that our financial year is not over yet. The financial year 2022 has not been ended. So the actual financial statements that I have will be here. 2021 and from 2022 to 2026, my estimate will be 5 years. Forecasting will be done. So, I have to show this somewhere to the other person. How will he know? If you are looking at a financial model after 2 years, then how will you know, till which year is the actual estimate? There are two ways of doing this. Either you do actual on all of them. Write estimate here. Now again this is a very rough way of doing it. We have to work professionally. So what we will do is, we will select these two actually. Press control 1. When you press control 1, the format cell will open in front of you. The format cell will open. You will go to numbers. Go to numbers and go to custom. Go to custom and go to the first option below general. where it is written as 0, you can see it on the screen. After that, you will go to the type bar, you will open double quote and you will type A in capital and you will close double quote. When you are doing this, you can understand how the sample is telling in it. If you type small a, if you close double quote, it will tell small a. We will type big A, capital A and we will press OK. When I have pressed OK, you can see it as 2020. Now, the rest of the data is estimated. We need to repeat the same activity. All the estimated years, the forecasted years, go up there and select them all. Press Ctrl 1, come to format cell, come to numbers, come to custom, select the first one, select the first one, double quote open, E double quote close.. So it will automatically put E in front of 2022. Just in case if you want to use F instead of E for forecasting, you can use F. You have options. There are different things in industry but you can understand what E and F mean. Now if you see there is some problem in it. The problem is that column width of D is different, column width of E is different, column width of F is different. You don't have to do all this. It doesn't work like this. What I will do is, I'll go over here. I want to be in the D column. If I press Ctrl and spacebar, then my D will be selected. If I press shift and right arrow, then all my columns are pressed. I press Alt H. So I went to home, O formatting and if I do I, then it will become auto width column. I don't want to do auto width column, I want all my widths to be same. So I will do W and I will do it 10 here. By doing 10, all the widths, especially this one, are set. And these two widths where we will write everything here, I will make these two widths 12. H O W 12 So it has become a little bigger than them. Now, Once I have done that right and guys please you need to understand up Jitra come mouse use caroana up kill it on a better now Hum carengi ab hum age omara header a yeah, hamesha visible on a chair immediately visible on a chair So my control be car don't know it's called bold karuna. Fair man home page on go My bucket may John guy and I will choose some dark color. I could take green also I could take orange also may be Kelly blue. Let's on you a la blue I think a second last Romney last voila blue Lelia and And I will make the font color white so that it is immediately visible. Now what will happen? Now I have done this. Now when you will go down working. And you have come here for example. So now you don't know which year we are talking about. So for this I have to do free span. How to use free span? Understand that. I will understand it in one go. I selected the 7th row. When you select the 7th row, it means that if you apply the free span function, then the area above selection will be freezed. So for free span function, you will go to view, free span and you will free span. You can use shortcut also, I will use shortcut. Like if you want to change font color for this, then do alt H F C and I will select white color. I came down, I pressed shift and space bar so whole row is selected. And in alt, W, F for freeze pane and again once more F. So now this selection, everything above it has been freeze. When I come down for my work, just a minute. When I come down, it didn't happen. I just need to do it. it once more alt w f and f okay so what happened now this is completely freeze when i come down for doing any kind of work then everything above will freeze i have put this logo of valuation school you want whatever company you are valuing you can put this logo okay now what we do now we start doing basic plot of income statement So I have typed here income statement and I will just do it. It will be first of all top comes revenue then comes my cost of goods sold. So I get gross profit. From gross profit I will reduce selling and admin expenses. My rate will be EBITDA. From EBITDA, I will first minus depreciation then interest. Then I will get EBIT, I am telling you a very basic statement. Then we will talk about taxes, then I will get net profit. Or net income, whatever you want to say. Whatever you want to say, say net profit or net income. Okay, let's say net income to maintain the consistency. It is very important to maintain consistency. This what I have done now. you will feel that I have done it fast I already know all these useless things don't be over confident in a minute he will give you a lot of money in the interview you will get this exercise file here I have guided you step by step what all things you have to perform you just have to run those things and see we have already given the exercise file in the description if you are facing any problem while doing it then what you will do is go to the next tab you will see the same thing or very similar to the previous one almost same the only difference is that we have started one dot below so I am adding a line yup Now this is exactly same I will come to the basic convention part 2 In basic convention part 2 I have to tell you some things which is very important See when you make financial model you have a structure and you input a data in it That data can be given to you by client that these are my actuals you can take it Or you can take that data from internet I have given time because purpose is not to tell you about data but to teach you formatting and basic convention So I have given basic data here I have given a sample data in this data. Basically, I have taken any random data in this. So, what I have to do is I have to plot this data here. The best way of this is You go here and do equal to the revenue of 2022. Come to basic data and merge it with this. When you do this, then you can plot it till the bottom. It happened quickly. Do you know why it happened quickly? Because all these line items and all these line items are same. But it is possible that the data given by the client is different from the line items of your model. So in that case, you have to go and plot the data individually. I will directly copy the data from here. OK. I'll just copy the entire data from here time being and I'll go over here delete it for a minute and now I have to paste the data here When I need to paste the data, I have two options. Here I will do Ctrl V, it will be directly pasted. But what will it bring along? It will bring value, format, formula. Whatever is there, it will paste as it is. I just need values. When you just need values, then for that you have to paste special. Ctrl Alt V. Rather than pressing Ctrl and V, you need to press Ctrl Alt V. When you will do this, the window of paste special will open in front of you. This is very important window. We will use it again and again. You will go to values and click on ok. When you go to values and click on ok, then this data has come in front of you in a very good way. Now, Now there is a problem in this data. The problem is that some of these are in decimals, some of these are in absolute numbers. We have to maintain consistency and make a thumb rule. The thumb rule is that whatever numbers I have, they will be on one decimal and whatever percentage will be, decimal and comma will be on 3 3 distance which is US convention. So I will do control 1, please understand this a little bit. I have selected all the numbers first, I did control 1 then format was settled, I came to custom. Now pay attention, this one is visible to you, stop the screen and see once. From here to here, this semicolon divides both the numbers. There is a semicolon in between. I am not sure if you are able to see it or not. On the left of the semicolon, this is the formatting of the positive data. On the right of the semicolon, formatting of negative data. So I want the formatting of positive data to be like this that 3 numbers, put a comma on 3 numbers and with that dot I will make it 0. So I want formatting on 1 decimal. See it is visible above. And in negative number I don't want that negative sign should come. I want that negative number should come in bracket. So I will delete the negative sign. I will delete the negative sign and take the negative number in bracket. Dot 0 because negative number also need 1 decimal and I will click on OK. When this is happening then you see all the numbers are constant and in case if I put any negative here then it is automatically coming in bracket. You don't need to do that thing again and again. This is very important and we will maintain this consistency throughout That is number one Number two, now you will see that everything is hard coded See this, we have copied and pasted everything Can I put formulas in this? Yes, you can Like I can make a formula for gross profit, EBITDA, EBIT and net income So we will make it quickly minus this copy it here then EBITDA is nothing but gross profit minus selling expenses, copy it then EBT, pay attention here pay attention here it's nothing but EBITDA minus sum of always say sum of the individually you minus, please don't do this And I copied and pasted both of them. I came to last net income EBITDA minus taxes. Okay, both of them are equal to me. Now what happened is that there are some formulas and some hardcoded numbers. One very important convention is hardcoding color convention. throughout follow throughout follow so this means that all the formulas will be in black and all the hardcored numbers will be in blue and which should be blue? good blue So don't take this blue, take this blue which is coming here. What you will do is, you will go in more colors, standard, go to standard and take this blue, this blue, from here third, in second row third blue, take this, this is standard color which is used in the whole industry. And you will, Now since you have used the last function and not used any other function So you will select the hardcode numbers and press F4 So it will automatically repeat the last function I want you to be doing very smart work not you know donkey work I will tell you that you have to do laborious work one by one So now the picture is clear in front of me That boss the blue color are constants My hardcoded numbers are and the black are formula numbers I hope you all understood very well These were my basic conventions Now I will come to basic formatting Basic forecasting So now You must have seen that We have You are facing problem in doing this. Come to basic forecasting and you will get this done. Now assume you have given you assumption drivers and you have to plot numbers from them. How will you do it? We will learn it in professional way. First of all, we will plot actual numbers. It is very easy to do here. Revenue growth can't be a plot of 2020 Because 2019 number is not available One more thing to keep in mind This column of particulars It will be aligned right And all other columns will be aligned right right sorry your this column will be aligned left and rest of the columns will be aligned right keep this in mind ok so now if you see here it is left aligned it has to be right aligned I will select all at once alt h a r alt h You use this only, right? You use mouse, I used shortcut, simple, no other thing. Now, revenue growth, what is the growth in 2021 compared to 2020? So, 2021 divided by 2020 minus 1. When you do this, then you will get this figure that there is a growth of 0.125, means there is a growth of 12.5%. I have to convert this into percentage. To convert into percentage, there is a very easy option. Alt H and I will press P. So it has converted into percentage. But we had talked that we will keep percentage on 2 decimals. Now to increase the decimal, So what do you usually do? You go to home page and increase the decimal from here. Okay, don't do this. Okay, don't do this. We will save as much time as possible. Alt H 9, oh sorry, Alt H 0. Alt H 0. I repeat it once more and it is done. I hope this is very clear. Should I do it in blue color? Not at all, it is not constant, it is linked to formula. So, we don't have to do it in blue color, it will remain black. Cost of goods sold percentage to revenue. It goes up. Cost of goods sold divided by Revenue copy paste here I am taking constant in SNG expenditure, it is above SNG expenditure is constant, so I am taking constant Formatting, at last I will correct it completely Depreciation, percentage of sales So your depreciation is divided by percentage of revenue 4% is coming out Interest I am taking constant which is above interest You have to add according to the data available in front of you. Did I write here interest as a percentage of sale? If not, then you have to take constant. Tax is 30%. This will be our tax. Did I hardcode it? Yes, I did. I did hardcode it. So, it is simple. First of all, I will... First of all, we have to blue it. Don't leave the time for later. I will tell you how to audit the model. Now, once that is been done, I can understand that there are some percentage signs which are not proper. So I will go here and copy it. I will select these and I will select the one below. And I will press Ctrl, Alt and V. Now I just have to press the format of this cell on selected cells. So I will go to the format and press OK. And I will go to the format and press OK. If you want to do a shortcut, then Ctrl-Alt-V. And you can see that there is an underscore under T in the format. So, you press T and enter. It's very simple. If anyone is not able to understand, then slow down the video and watch it. Repeat it again and watch it. If anyone is able to understand, then take the video to 1x, 2x. But don't ignore it. Because if you feel that you already know all these things, then don't worry. I am... I am... I am committed to give you the value and I will give you some new perspective on this. Now I have to plot the numbers for the future. I will tell you what numbers to plot for the future. Revenue growth is for 10%. We will learn to forecast. How to forecast in actual. I am telling you this according to formatting. If you already have forecasting. Then how to plot numbers professionally. So 10% revenue growth will be ahead of us. Our cost of goods sold will also be 40%. My S&G expenditure will be 2500. My depreciation to sales will be 5%. My interest expenditure will be 5% in the future. 250 and taxes will be 30% Did I hard code all this? Yes, I did. First thing I have to do is I have to blue this. Before that I will set their formatting. So I copied All my percentages, I selected them Ctrl V and I pasted the format Then I copied it Here and here I have pasted the format I hope this is ok, now all these numbers are hard coded So first of all I have to go to the page and without using the mouse I have to come down I have to do this, without using the mouse I have to come down Which is my recent color and select it Now I have two options, sir let's do one thing Since everything is going to be same, then let's copy it and paste it from here. This is the wrong way of doing it. What are you doing? You are inputting hardcoded words more than once. We discussed in the starting that hardcoded number will come only once. It should not be added again and again. You understand what the problem is. Now it is on the same sheet. But if you have the habit of hardcoding again and again and it is on another sheet or you have hardcoded the same figure at 10 places and that figure has changed. Later you will know that the figure has changed So you will not even remember at which 10 places you did the hard coding So I have to link it with this That link with the last one What is the advantage of this? Formatting will be spoiled? No problem, we will correct it in a minute What is the advantage of this? The advantage of this is If in future I am told to take 10% or 12% Then you don't need to change anything Or if it is said that from 2024 onwards 15% will be there Then you just hardcode it in 15% And do formatting again If you do formatting then it is done Now let's correct its formatting Let's copy this format Let's paste all this. Ctrl-Alt-T. Enter. Come down. And press F4. It takes 1 minute to do formatting. How much time will it take? And all this speed. Just don't underestimate yourself. That I am not getting it. It will come slowly. You have to practice. Practice it. That's why they did it. See yaar if you don't practice then it's all vain Don't waste time, go and watch other videos Entertain yourself Don't stay here, don't learn I don't care I am doing this for one person who will watch this video till the end and learn from this video. I am making this video for that. Now I have to plot the numbers. Here I need to plot. I have to plot. It is simple. I will go here. I will press equal to sign. Growth rate means 10% on that. So we will select this. I will select my last year's sales. Multiply by bracket open 1 plus growth rate. 1 plus growth rate, the growth rate has come down. bracket close, enter. Don't take the problem of formatting. This is the growth rate, my plot is complete. Formatting is done in 1 minute, what is the matter of formatting? You know how to do this. Nothing, normal, okay, do we have to keep it blue or not? There is a formula, so I will go to the black one, I will copy, I will come here, select it completely, Ctrl-Alt-V, for format T. Enter, formatting is done. Cost of goods sold is the percentage of sales. So sales into, directly percentage will be applied. 40% of the sales. Not 40% growth on sale. Copy this. Paste. From above, pick up the formula. Copy it. Ctrl-Alt-V T for format. Formatting is done. This is running constantly. The gross profit formula is pasted below Now the topic of selling and general expenditure It is hard coded, you have to directly link it There is no formula but I have linked it I have not hard coded it You have to keep all these things in mind This will differentiate you Excel operator is different Financial modeling expert is different You have to become financial modeling expert Not a plain excel operator Now Abida, the formula of Abida is directly applied I will do it quickly. Depreciation is the percentage of sales. I took sale into I put the percentage of depreciation. We will do formatting at last. Don't take any problem. This is direct, absolute is coming. I took this. I copied it. I put the formula of EBIT. Taxes, EBIT is 30%. So, EBIT is 30%. EBT into 30% Net income formula is there, press it and add the formula Press Ctrl V and add the formula Now my formatting, this is the correct formatting, copy it Press Ctrl Alt V, T for format, press it and your formatting is done This is your basic formatting I have also told you how to plot with assumption driver. Now we will go to advanced setup. When you go to advanced setup, you will get this already done. Before this, I have to tell you one more thing. Now you see. I will make this a little more beautiful. So what I did to make it beautiful. I have to immediately highlight this income statement. So I selected this. I went to home. And I will select the color in the bucket. I can do it with the mouse if I want to but I don't want to do it with the mouse. To tell you that you can do anything without the mouse. You have selected it completely. Alt H and H. Press it once more. H is written in front of the bucket. Press H once more. See, Alt. H is written on the home. So, we have to go to H. If I press H once more, the bucket will open. And I will select this color. Done. Now I come down, I go over here and I just press F4. So this is my more professional look in the model. I want to tell you one more thing which we have to do immediately. What happens is that we will keep making many different tables below this. So, the model should not look very clumsy, that's why it has grouping. You can call it grouping of rows. Now, what I have to do is, I have to put a function here, that it should collapse completely. Collapse means, it should be deleted. No, it should collapse only visually. So, it is called grouping. I will press shift and space, so my row is selected. I will press shift and downward arrow, so I have selected this whole. Done. Now, I have to put row of all the particulars of income statement. You have to select the whole row. You don't need such selection. This selection will not work. You have to select the whole row at once. When you do this, then you will go to Alt H. Sorry. Go to data and group it. When you group it, you will see something here. When you close this, you will see a sheet like this. Should I include this as well? If not, then how will I know about the income statement? Now I know that the income statement is here but it is clubbed. It is grouped. Open it. Sir, can we do this grouping manually through data? I mean, through mouse? Yes, we can do it. How can we do it? I will tell you in a minute. You have selected this. Whatever you want to group or club, you have selected. You shift, alt and right arrow. Shift plus alt plus right arrow. If you do this, it will be grouped. If you do shift plus alt plus left arrow, it will be ungrouped. I have to group. I have grouped. Then I came down in Assumption Drivers. I pressed shift and space. So the whole row was selected, I came down by pressing shift and came till here Then I pressed shift, alt and right arrow So this grouping is done Now you also clip this and this too When you do this and go away, you will see that the income statement is also clubbed. This is also clubbed. I hope you understood till here. Now we will move ahead in the advanced setup. When you go to the advanced setup, there is one more thing in the basic formatting, it's okay, it's a very good thing. The more I tell, the less. Now I have to highlight some things in it. First of all, I will highlight the top and bottom row. Control B. And control the one below also. First of all, the top line and bottom line will be highlighted with bold. Then what are the most important things in which I need immediate attention? Gross profit, EBITDA, EBIT. These are the three things. So what I will do is, I will select the gross profit row only till there without selecting the whole. And now I have to put the border. I want to put the border only on top. Usually, you go to home, go to border and put the top border. No problem, put the border. This is a little unprofessional way. Don't use it. It is the right way but you have better way. We just selected gross profit and pressed control 1. So, format cell opened in front of me. After format cell opened, I went to border. What I have to do after going to border? First, I have to see what style of border I have to take. Do you want to put dotted border or plain line? What will be its width? If the line is wide or you want to add a very fine line, then I have to take this line. Now, the selection you have done of the row, of this cell, In this, you have to put the border on the top, on the left side, on the right side, or in all four places. I just have to put it on the top. So I will select it and click on the top here. And I will click on OK. So you can see that it is highlighted that there is some calculation up to here and this is the result of that. I have to do the same on EBITDA. Now I don't need to do it again, I just have to press F4. It's done. I just have to press F4. The last column, the last row, sorry, the last row is my result. It's the end of the statement, the statement is ending there. For that, what I have to do is, I won't be able to work with one single border. I select it, press Ctrl 1, on the top, I have I put single row, single border and below I have to put double border. So I put double border below and I have to click OK. When you click OK, then this model is now very professional looking model. One thing you must be thinking that where are the grids in this? And what we will do about grid? So go to view and always keep grid line off. Now see how clumsy model is looking. Now how clumsy it is looking. It is not looking very neat and clean. See, there is only visual representation. So you have to off grid lines You have to off grid lines It will become neat and clean model It will see directly what you want to see Are you getting my point? Now finally we will be moving on to advanced setup In advanced setup we will learn few things That is Number one, we will understand that we will make a common size statement and change analysis statement. I have to teach you the purpose of making these statements. Anchoring is of two types, one is absolute anchoring and the other is partial anchoring. And we will understand what is the use of both. Before that, we understand partial anchoring. Before that, I have to see one more thing. I have to see if I have done any flaw in convention. If I have done any flaw in convention, it means that the model which I have made till now, if any convention has been missed in that. means formula was there but it was blue, constant was there but it was black, I couldn't make it blue, how will you change it? how will you check it? so the easiest way is, you stay anywhere randomly, press control G, then go to will come when you press control G, it will prompt your window called go to and you have to go to special, can you see special? you have to go to special, if you want you can press S also, you will go to special, I pressed S, not here Ctrl G and S Alt S Press Alt S and you will go there Now I Select constant here And press OK Now what happened in this All the things are selected automatically Which is constant and hard coded So you will get to know which cells are there Where formula is not used and it is hard coded So you just have to ensure By looking at it that is there any black cell Ok Let's see, as you can see, here is NA but black, there is no problem. It's absolutely fine because it is not a number. But I have hard-coated this 3030 but I have forgotten to make it blue. Okay, so I will immediately, you understand how the model is audited. With these tools, they will audit your model. Whenever you review. You need to have surety yourself. What I am doing is I am doing it correctly. I have done it. Now I have to do one more check. That my formulas, Those cells are not black. So I will go to go to, special. And again I will go to formulas. So my formula, formula is selected. I hope you are still understanding this. Now we have to make a common size statement. Common size statement means that you define every parameter of income statement in percentage of revenue. Percentage of revenue Like Depreciation as a percentage Of sales I took But I kept interest Standard Static I didn't link it With sales But if I Common size statement Purpose is To see All All items revenue percentage pair a ticket to a behemoth carrying a move per jayang a hum pura pura income statement was if copy carrying a or men Nietzsche as it is based a mean he karuna I will not be pasting is control we karuna the cookie car I think I have a formatting it the Gandhi or EOS key which in a karna I only want the values and not the formatting or the formulas So this became my revenue statement. It became my full income statement without any formatting. Now, how much percentage of revenue is there? It is 100%. So I went up. I went up. So revenue will be divided by revenue. D9 divided by D9. Okay. So this 1 has come. 1 means 100%. So first of all, we will correct its formatting. I did alt H and P. So it converted to percentage. Now I have to increase 2 decimals more. So alt H. Alt H. And 9. Again alt. H9 It happened immediately You will take the mouse in your hand You will open home You will go here Okay, if you keep this open Then you will see You will be prompt to use the mouse So always unpin this I never pin this I always need neat and clean Excel The thing on which I have to focus That thing should be in front of me Now Revenue, now what I will do is drag it down. See what problem is happening. I have done control V. When you are dragging down, it is doing D10 by D10. Then D11 by D11. I don't want to do this. I want now that cost of goods sold is how much percentage of revenue. So I want the revenue column to be constant. Revenue column is constant, so here I will use partial anchoring. I will explain what is partial anchoring. Cost of goods sold divided by sales. Divided by revenue. Now understand. Now pay attention please. This D9, I have selected this D9. I haven't pressed enter yet. I will press F4 once. When you press F4 once, then sign of dollar before D and sign of dollar before 9. This means. Column D is locked and row 9 is also locked. Means this is absolute anchoring. If you do anything, then it is locked. But I don't want this and I will tell you why. Okay. Let's do it for 1 minute. Let's do it with absolute anchoring. Okay. So 40% is coming. It is coming absolutely right. I copied it and it went down. Cool. Now see, is EBITDA 50% of sale? Let's check once. EBITDA is 10,000, revenue is 20,000, 50% of sale. So you can do one more thing, then copy it and I will paste it like this. No, it's wrong. Figures are looking good but what problem is there? You have done absolute anchoring above. What is happening because of absolute anchoring? Focus here. For next year also, COGS of next year. Comparing with last year's revenue. This means I don't want absolute anchoring here. I want partial anchoring. Do you understand? I will press Ctrl Z and go back to 40. Now this is absolute anchoring. So I want that you anchor 9th row but don't anchor column D means when I come here then it becomes E9 and here it becomes F9 so either remove dollar sign manually or when you anchor If you are anchoring, then if you press F4, then both are dollar. If you press F4 again, then only row is anchored. If you press F4 again, then only column is anchored. If you press F4 again, then nothing is anchored. So I will do anchoring once and then do anchoring again. So this is my partial anchoring. I have freeze 9th row and not columns. And I have done OK. Now when I have done OK, now you see. Now see what happened. Ctrl V Now My next year EBITDA is also 50% Let's check. Is it working properly? It is working perfectly. Let's check randomly. Is it working properly? Yes it is working properly. It is including the depreciation of that year with the revenue of that year. And here there was no requirement of anchoring. If you want then do it. It will be good. This is the concept of partial anchoring. Now I have to group this too. So what I will do is, select the whole row by pressing shift. How to select? By pressing shift and space bar. That is how the row is selected. How to select the column? By pressing ctrl and space bar. That is how the column is selected. Shift and space bar. I selected all the rows and I Shift Alt and Right Shift Alt and Right Right arrow, so this is my grouping Grouping is done, how easy it is to work Now I want to do a change analysis. The main purpose of this change analysis is to teach you absolute anchoring. What we will do is, we will copy the entire statement as it is and paste it below. We don't have to paste the values, we just need the entire statement with formatting. In change analysis, I want this. In change analysis, I want this. Just a minute. I copied it completely. Ctrl-C And I pasted it. In change analysis, I want this. If someone tells me that if you increase 10% of every line item, then how much impact will it have on your P&L? Now I have to do that. First of all, I have hard-coded 10. I want to give it blue color first. I have given blue color. I got rid of it. Okay. Now. So I want. That. This 2020 revenue. If it increases by 10%. Then how much will it increase? Gross profit will also increase by 10%. Increase Now I'll just copy it and I paste it across. Something happened? Something happened? What happened? As it is formatting. So what? I had to paste formatting. I just have to paste formula. So I press Ctrl-Alt-V. And for formula, you will use F button. And OK. See. All formula is pasted. Do you... The difference between absolute and partial anchoring is that the more you practice, the more your hand will be lost. Don't worry. One thing is wrong. We will understand that in a minute. I have opened all the statements. Now my income statement is almost ready. My assumption drivers are ready. My common size investment is ready. My change analysis statement is ready. Now I have to apply the final check, the convention check. I have done Ctrl G, I have gone to go to and special. First of all, check the constants. Check if there is any constant which is missing in black. Let's check. Is there any selection that is left in black? No, there is no selection that is left in black. Then, press Ctrl-G. Let's go to special. Let's check formulas. Is there any formula that is left in black? That is left in blue by mistake. Sorry. Yes, I got it. Can you see this? This is left in blue. Don't do anything. Don't do anything right now. Everything is selected. Go to single one shot and make it black. If you haven't seen it by mistake and it will be left, then that thing, see the one above also selected, that's why it was left black. So it will automatically become black. Now, what is the benefit of making this change statement? I am saying that if you want to do hard coding once, if someone says that if it changes from 50%, then what impact will it have? Then I can give the result immediately. If it is 5%, then what impact will it have? If it is 100%, then what impact will it have? Everything will be doubled. I hope you understood till here. Now, in advance setup 2, it is the exact same thing. First, how you have to go to, number 1. Number 2, we will learn one more thing in it. Okay, which is advance setup, but I will do it over here. I want to perform it over here. Number 1. Now, there is an income statement. Here, you assume for a minute that the income statement you are making is for Tata Steel. Assume for a minute. So, you have to input the name of Tata Steel at one place. Keep in mind. You will not do this. Income statement, Tata Steel. Then come down again. Assumption drivers, Tata Steel. I told you that the less hard coding, the better. So how will it be hard coding? Means how will we formulate it? Because what will happen tomorrow? Will you make another model instead of Tata Steel? Will you change the name of 20 places? It does not take that much time. Here you have to assign equal to in its starting. Then you have to lock it in double quote. Lock it in double quote. Then you have to put the AND sign. Which is above 7. Shift plus 7. Okay. Then you have to open double quote. Give a space bar. Put a hyphen sign. Then give a space bar. Then close double quote. Then you have to put the AND sign. And you have to link it with this cell. Now see. Did you understand what happened? It is written exactly the same thing But suppose this Tata is instead of steel If this was a Godrej model And I would have put Godrej here once Then automatically Godrej would have been done The more you practice this The better it is for you This is what I have told in Advanced Setup 2 I will do it once more and show you Understand it carefully Assumption drivers are I went to formula bar I put equal to sign first Assumption drivers should be in double quote Whatever is in double quote, it will be as it is I have taken it in double quote Then press and Then open double quote again give a space because I want hyphen between driver so I gave space between double quotes then I put hyphen then I want hyphen between Tata Steel so I gave space and closed double quotes and then I put end to my dynamic cell and linked it so assumption driver is Tata Steel then came down in common size statement and this happens in very fast don't worry so you are feeling like this I will directly type b5 Common size statement attached here See, we have to do change statement It is so easy to do I have taken it in double quote and double quote space hyphen space close and b5 Change statement at I Steel. Now what is happening because of this, I will tell you by ungrouping for a minute. You will understand immediately that why I have told you this thing. I will group this also. I want to group this also. So I have selected the whole row. If you want, you can select it like this also. Shift space and I came down. Now shift, alt and right arrow. You did that and this whole grouping happened. What is happening is, if you assume that you are making it for ITC in the future, then ITC will happen. Whatever you want, you can do. We keep it as Tata Steel for the time being. I hope this thing is clear. Now there is one small aspect that we have to learn immediately before we move on to functions. That aspect is this. conditional formatting if you know then well and good if not then also understand and if you know then also see once atleast in conditional formatting what i want is two types of conditional formatting by the way there are multiple ways but what is the meaning of conditional formatting if my condition meet then you change this in formatting i explain this to excel So here I want that whatever is less than 10% It should be highlighted in grey Light grey instead of black And it should be italic too Say for example and bold or whatever it is Whatever you want to do So first of all where you want to apply conditional formatting in the cells you will select them You will press alt once If you just press alt and leave it then you will see this Then you will type H and go to home Then under conditional formatting you will see L So you will type L and you will come to conditional formatting. You will come to highlight cell rules. What is my rule? If it is less than 10% then it is less than. So for less than I will press L again. He is saying. Format cells that are less than 10%. We have to put percentage sign. What formatting we have to do in them? So formatting we have to go to custom format. When we go to custom format, when we go to font, then first of all I have to make its color light gray. Number one. If you want, then select italic from here. And. We will click OK when you see something happening. And click OK. Whatever numbers were less than 10% became grey and became italic. This is the use of conditional formatting. The second use of conditional formatting is that wherever EBITDA, say for example, if EBITDA is less than 15000, So make them red or if it is more than 15000 then make them green So what I did is I selected EBITDA Alt H L Then if you want to highlight then H Greater than then G Whatever word is there in front you have to press it, it is simple 15000 Highlight this with green Custom format fill green Let's take light green Okay If you want to remove all the custom formatting without selecting it, Custom Formatting, Clear Rules, it will ask you that you have to remove the rule on the sheet or on the whole workbook. It is written here, Clear from selected cell and from entire sheet. So it depends. If you want to remove it from the entire sheet, then it will be removed from the sheet. You have to do this exercise again and again. The more you do it, the stronger your grip will be. My personal experience is that I have seen that people who are very vintage eat gacha in it. Now we are coming up for some functions. The functions that I want to tell you. First of all, I want to emphasize on the time period functions. This function will be used whenever I will be doing present value calculation. Learn this once, it will not happen again and again. I have hardcoded a date here. That date is 15th of August 2022. Now I want 15th of August 2022. What is the month end of the year? Because your financials will be at the month end If you are doing monthly projection, then it will be at the month end If you are doing quarterly, then it will be at the quarter end Usually it doesn't happen at the month end but I am telling you that it happens at the end of the year So if you want the month end of anything Then there is a function for that That function is EOMONTH End of the month End of the month, EO month. You have written this. As soon as you open the bracket, it will say start date. So, my start date is 15th of August 2022. This is my start date. So, after 15th of August 2022, which month should I want? Now, here I have the option to make it 0. Write 0 with hand and close bracket and enter. So what did it do? It told me the end of 15th August, 31st August. Again what did I do in this? I hard-coded 0. This is the deadliest thing that you are hard-coding something in formula. I won't even know. Because you can't catch this thing with goto function. You see, I do constant and it won't select this. Whereas you have hard-coded in this. This is a limitation of this boss. You have to always keep this in mind. This is a very deadly thing. You will not remember that you have done 0. When you work on a model for 4-5-6 hours, multiple sheets are made, then you don't remember it. So what we have to do for that, we have plotted numbers from 0 to 6. Do the plotting, now go in the beautiful type. You have to go on efficiency, the model should be more efficient. Right, not very beautiful. Why you didn't make it blue? Because this is not my input variable. If you want you can do it. If you want you can just do it input. If you do it, then it will be hard coding. It depends on you. It's not very hard and fast tool. EO month. My start date is this. Do I have to do absolute anchoring for start date? Yes, I have to do it. My start date will always be same. My start date can never change. So for absolute anchoring, I press F4 once, put comma and my month is given here. It is written 0, 1, 2, 3. So I will select that and I will click OK. Entered it. So, you will see this is my monthly period date. The result is the same. It's all about efficiency. You have to learn that. You make a model that if there is any error in it, you can't even catch the error. Does not make any sense. Model should be in your control. Not that you are in the control of the model. You should not be the slave of your model. Rather the model should be your slave. I just dragged it. And it automatically happened. Now understand the meaning of this formula. The formula which is here. It is saying. Tell me the end of the month. Will tell. Which end of the month? This is the start date and after this, tell me the end of the second month. So, August is zero. Sorry, tell me the end of the first month or the end of the second month. Here, tell me the end of the second month. So, the start date is August. So, in the first month, zero month will end there. Next first month Which will be September And second month October I hope this is ok Ok Can I do this Like this That I took this And I added 365 To it Do it No problem Do it No problem But by doing this If I want annual data, I am talking about it. So by doing this, are you reaching the end of the year? Not at all. You are not reaching the end of the year. So for this, we use the formula of year. I will explain you how the formula of year is. Sorry, it is the formula of date. First of all, I will put date. In date, it is asking year. First of all, tell me which year you want to set. So I will use the formula of year. Year again and I will use this serial number of year. I did F4 plus do this in that. So this is my year. Which year do I want? So I want year 2022. Which month do you want? If you are running from calendar year then I want 12. If you are running from financial year then you need 4. And day 30. Do 31st or 30 whatever it is. Do 31st. Date 31st is here. I have done complete formatting only for this. I have done this much for you. Hajjat Baaji has done it for this. So that it becomes very easy for you. If you do this, then automatically everything is coming in front of you. Don't worry if you don't understand it at once. Rewind the video a little. Think calmly. Everything cannot happen quickly in life. You cannot produce a baby in a one month by making nine women pregnant. This is the quote by Warren Buffett, few things just take its own time. So understand things slowly. Now, I want to know how much gap I have between 2 months and 2 years. If you see this, it is very easy for you to say that if this happens, we will do this, if this happens, we will do that. Don't worry. What does this mean? Between 15th August and 31st August, I am telling you by looking at the type, there is a gap of 15 days. I am not talking about in the days terms I am talking about year terms So for this a function is used Our year frac I use this function Year frac What is the start date? 15th August I have to do absolute anchoring It will not move, start date will be same And what is the end date? Monthly data is this So this is the difference of 0.04 years between 15th August and 31st August 2022. I will drag this and drag it till here and press Ctrl V. Similarly, I want the annual period. Do you know where you use this annual period? Present value factor. At that point of time you need this. You apply its power. If you know the formula. And if you don't know then don't attend this class. I am taking this presumption that you know basic accounting and basic finance. I am teaching you financial modeling over here. Okay. So I will apply a formula here. Year FRAC again. Start date. I need to freeze my start date. I pressed F4. End date. This is my date. Copy it And I have put the formula of ear frag I hope this is clear Where this will be used I will explain you Don't worry Okay, there is one thing In starting Do you know what we were doing in starting We had said this thing in starting That we will leave this column This is a very important column. Why is it important? I will tell you the importance of it now. Whenever you make a new table, then on the side of its header, this column A8, you will input a hash. I will explain the reason for inputting that hash. Understand it very calmly. I am not in a hurry. If you are in a hurry, then you can leave the video. Okay. Those who want to learn, they will learn. Now, Say for example you are here And now you have to go to common size statement Here you have to go to change analysis So you have only one option You go down and change analysis Now you have come early But suppose below this 15-20 table are there And you have to go to last table So either you will go one by one Or you will have to pick the mouse I don't want to pick the mouse I don't want to pick the mouse So what I did is I put it here What is the advantage of this? The advantage is that you can pick the mouse I want to go to common size and I don't want to use my mouse. I want to go through keyboard. So what I did, I came here once. Then I pressed control and left arrow and came here. Then I pressed arrow and came here. And now I will press control and down arrow. See, I came to common size statement. It helps a lot in navigation. It is really helpful in the navigation. Now, function 1, we have seen how to work on time period. Now we will understand few very basic, I know you will be over smart that we know everything but I am telling you again that many people get into this. Don't worry. What we have done is we have done costing analysis. For costing analysis, we have done, we have taken all the above income statements. I will take them, leave them, then you will tell later. I made a table of costing analysis and what I did was that I copied the cost portion from here Ctrl C I went down and I need only values. So all these costing values are here. I will expense this as SNG for now in short. Now I have to link from here. Hard code is not a mistake. First complete the first row of linking. It will be quick and won't take much time. Once it has been accessed, it has come here. Now I went here and I have completely copy-pasted it. And here I have brought total. Total is in total. I pressed alt and equal to, so the sum's automatic formula was applied. And above this, I want a border, so control 1. This border in the border and above if you want above. Done. This is your beautiful saree. Now, Now I assign some weights to it. I give 0.10 as its weight. And you can see that I have already done blue formatting. If it was black, I would have changed it to blue because this is a hardcoded number. I give 0.2 to this. So 30% is done. I give 0.20 to this. So 50% is done. I give 0.3 to this and 0.20 to this. And I have also done its total. So 1 is there. Ctrl C and I will paste the format here. I will paste it completely. So the format will also be automatically pasted. Now, what I have to do is, First of all I have to calculate the average. The formula to calculate the average is very simple. You must know it. You will put the formula of average here. There is no formula of mean, there is a formula of average. And from where to where the average is. I want from here to here. This is the formula of average and I will press control V and paste it. If I want to apply the formula of weighted average then understand that there is no formula of weighted average for that. For that formula is applied as sum product. When you apply the formula of sum product then it will say array 1. That means tell me from whom to multiply. So I want you to from this row Array 1, then multiply it with array 2. That is, multiply 0.1 with 8000, 0.2 with 2000. Keep doing this and tell me their sum. Do product as well and tell me the sum at the end. This is my weighted average formula. I am committed to make you One thing to keep in mind By the end end of this course, if you watch all the sessions properly, do all the exercises properly, then you can make the whole financial model by yourself. Don't worry. I guarantee you this. But you have to give the commitment that yes, I can do this much. And I copied this. One thing you should understand, and I will try to paste it but there will be a mistake. You hold it and tell me what is the mistake. I have not locked weights. So what happened is, here it took the correct error but here it multiplied both of them and told me the total product. These mistakes should not happen. So I will press F4 once and I will paste this formula again. I hope this is absolutely clear. Now we'll come to median. We'll go to median. The formula of median is very straightforward. It should be around 2000. 2000-2500 It means that in all these numbers In these 5-6 numbers There are equal numbers above 2000 and below 2000 And in between 2000 If you want you can manually check This is 2000, there are 2 numbers smaller than 2000 Yes there are 2 numbers, there are 2 numbers bigger than 2000 And 2000 is in between So this is median Now, there is a formula of minimum, which is the smallest number. I have put the min formula and I went up. If I say min function, min formula, then it will tell me which is the smallest number in all the numbers. That smallest number is the number of interest. Similarly, there is a formula of max. Equal to max and I have selected it. So, it has told me the maximum. Now, one more formula is small and large. Which is the smallest number among all these? We will know that from the min. But which is the second smallest number? Which is the third smallest number? I am not telling you all these functions in the air. We will use them again and again. I am saying that only. That's why I am devoted so much time on this. You need to understand boss. Okay. Now. So, for this, I have run a small function. So, it is saying array. Which array do you want to find the number in? So, I want to find in this array. And the K is, That K is asking for sequence from you. If you write 2 here, Then it will give you the second smallest number. I will write 1. See, it will be equal to min. 200 only came, right? It will be equal to min. I want second smallest. So, ideally 800 should come. 800 came. So, should I hard code in this? Should I hard-code 2 in formula? Not at all. I will put it here sir. Let's take it as third. Let's put 3 3. And I will link it with this. Leave it independently. Now if I want to apply small formula, I did small. Array means in which you have to find the small number. In this, you tell me the small number. Sir, in which sequence you want the small number? So tell me the third smallest number. Either third smallest or whatever is written in this column. And I will, I will, absolute anchoring bhi karunga 2000 aagaya dekho third smallest 2000 hai smallest is 200 800 2000 The more dynamic you keep the model, the better it is for you. Large is also same number. Tell me the largest number. In which array do you want to tell? So, tell the largest number in this array. Sir, in which sequence do you want? So, whatever sequence is written in the box, and anchoring it, absolute anchoring. In that sequence, tell the third largest number. 2000 should come in it. The smallest is 200. Third largest should come. 2000 should come. See, 2000 has come. Okay, there is no formatting, so copy it from above Go down, Ctrl-Alt-V, go to Format, from T It has done formatting, now if you type 2 here, it will tell 2nd largest If you type 3, it will tell you the third largest, if you type 2, it will tell you the smallest. I hope you understand this thing. You have to practice this again and again. This sheet is for you. I have given you this sheet for your practice. If you don't practice, you will take the knowledge home and forget it in 2 days. Now, I will come to a function. That is if statement You must have used it repeatedly But we will use it in a different way I will tell you how to use it So I want that If this particular year is full year So write full here Or write stub Stub means less than full year So I will put if condition here Equal to If This is basic if condition If This annual period if it is less than 1 This is my logical test So what value should come So you write here if it is less than 1 Means it is smaller than 1 and less than 1 year So in double comma stub That it is less than 1 Less than 1 is called stub not half year Because half year is 6 months If it is 8 months or 11 months then we will call it stub If it is not stub, then what to write? Means if it is less than 1, then write stub, else write full here And double quote close and bracket also close Enter, stub came This is your basic function which you use if statement But suppose here I want If the total is less than 17500, then write it here. If the total expense is less than 17500, then write it here. If not, then don't write. So let's see this. If this equal to F, bracket open. This is less than 17,500. Then put this. Else, put 0. It's coming. Now, I'll keep it all. I'll keep it all. For you. See. This above statement is small than 17000. Did he write something smaller than 17500? No, he didn't write. It's absolutely correct. But, here a problem is happening. You have again done hard coding in formula. You don't have to do hard coding in formula. You should write 17500 here. You do hard code it. Do hard code it. You have to do it only once. I will link it. I don't want to do it twice. I won't hard code one thing twice in the financial model. I am explaining it again and again. How important it will be, you think about it. I am saying the same thing 8 times in the entire session. Now, I will put the ifs condition again. If my total expenditure is less than this cell, then put this. So, tell me the expenditure. Else, if this value, this logical test fails, then you write 0 for me. The result is still the same, you have to understand. But now, the dynamism is more. How is the dynamism more? I have... absolute anchoring how is dynamism more? dynamism is more that if I want to add 18000 then I will add 18000 if this is smaller than 9000 then nothing will come I have added 9000 but there is a problem problem is here is 17500 then it is fine but if I will add 18000 then function is working fine is this giving a problem? No problem, let's fix this also. Before this, I put equal to sign. If and this I brought here. Ok. And one I put gap after this. And I linked this with cell. It's done. Now what's happening? Now I will do 16000 here. So this function is also working with it and this function is also working with it. So it will be 16000. Similarly do the same for the lower one. See how to do it. Remove 17500. Equal to. Bring the absolute text in double quotes. Leave a gap. Leave space and then close double quotes. If you don't leave space then what will happen? See. You can see that you have not left any space in between. So, you leave a space in between. Sorry. You close the double quote and C46. What is happening? You can see that leaving a space is very important if you want a proper format. Are you getting my point? Are you understanding this? Let's run this statement once. 17,500. Sir, I have to put 17,500 in one place and the rest of the change is happening automatically. This is simple to see. It is simple to execute. You understand. You are automating your financial model. Sir, I am applying VBI, Power Query, Power BI. You don't have to install it because it is not installed in the industry also. The simple model is in your control. If you automate a lot of things in this, then you will become its slave. If there is a little problem in the model, then the whole night gets spoiled. Believe me, I am working on a very small level. Gradually, we will go to a very advanced level. Now, total. Okay, what I have done with this? Now, if I total expenditure is more than or equal to what to this then put my expenditure l0 zero again b again b So, this has to do absolute anchoring. This one is anchor, nothing will change in this. See, it is exactly equal. All these expenses are less than 17000 and all these expenses are either 17000 or more than 17000. Okay, 17500 or more than 17500. I will do total of these. I will total these. Now, does error checking also happen with if? Absolutely. See how error checking happens. Equal to if this is equal to this. If this is equal to the above one, if this is happening, then OK. Write it down. Otherwise, put an error. Do you understand? This means that if you change anything here For example, if something is wrong Then it will post an error immediately If you want to go more advanced Then you can go Okay, one more function is equal to this equal to this. So it will say true, true or false. I will add any number here. See, it has told error and it has told false. You practice this very well. Practice this very well. We will bring the next session very soon. Don't worry. We will immediately. I am going to increase the frequency. Either there will be an alternate day session or it can come in a very fast speed. Before the next session, I want you to download the sheet. Do it properly. It is very important for you to watch, start watching the session as in when it comes. If you are watching two sessions in a week, then it is very good. If you are not watching it daily, then you will feel burden. You have to learn this with interest. People charge a lot of money in the market. 20, 25, 30, 30. You can read the comments. Go to the first session and see the comments. You will understand what I am saying. Why it is a gem. Okay. Now, one more thing. If you want to get in touch with me, go on LinkedIn, search my name, Parth Verma. Parth Verma. I will just write it over here. I'll be available on LinkedIn. You can connect with me. If you want to ask anything, you can ask me over there. Meanwhile, if you come to the next session and you get to know, for that, you subscribe and press the notification bell icon. Till then, this is Parth Verma. Thank you so much. Bye-bye.

So Excel should be in our absolute control. We will learn in this session and in the coming one or two sessions that how you will bring Excel in your favor. Usually you must have seen that you are worried with Excel and when it comes to financial modeling you need to understand you will be working with a large amount of data so it is very necessary that excel should be in your absolute control we will only be understanding and learning the most relevant excel functionalities and excel setup for us this particular video is divided into two parts First, we'll understand what are the settings of Excel that we have to do by default only once.

And in the second part, we'll understand that what are the functions of Excel that we'll be using in multiple places in our financial modeling. So it becomes very important for you to understand every function very well. And if you'll see in the description, we'll be keeping the exercise sheets for you. Who's Smith? proper dia hua hai ki what is the problem statement, aapko kya kya steps follow karne hai, toh mera request aapse ye hai ki once you watch this video till the end, just stop it, open the excel sheet and start doing it simultaneously, matlab ek bar shanti se vatke pura video dekho kuchh mat karo this dhauran, don't open the excel sheet, don't do anything, pehle ye samajh lo ki mai kya batana chaara ho, then open the excel sheet, download the excel sheet, open it exercises are written in it, you should do that thing and perform it again.

If you during that re-performance or during that practice if you have any problem then go to my video, go to this particular video and see that how I have redo it. It is very necessary for you to do the at most practice. If you just watch this session and you know you won't practice it on your own, then nothing is going to happen.

So let's start. Yes. So let's start with the Excel function. We will start with the Excel setting. What all settings we have to do in Excel.

Don't worry, I have given all these things here. This file is also available. But if you have to do this by reading, then you will learn financial modeling by reading books.

So be with me till the very end. And it is very crucial for you to attend each and every session and see it one by one. And do the exercises as per the exercise sheet in the description below. First of all, my intention with you is that the less you can use the mouse, the better it will be for you. I will tell you a very simple hack to eliminate the mouse.

First of all, if you get very impressed by someone else's Excel speed, that he does Excel very well. Or you are seeing that someone is very pro in using shortcuts. So that thing is not going to happen to you overnight, that is for sure.

but if you are conscious about this thing that if I want to increase the width of the column or reduce the row of the column or reduce the height of the column or add a new column or row, then how can I do it with an Excel shortcut? and if you are very conscious that I don't want to use the mouse and I have to do this through the shortcut, then over a period of time you will use that shortcut 3-4 times and it will be very easy for you. So now what we have to do is we have some settings in Excel. which will optimize our excel for financial modeling.

We will do that first. So what we will do is, I am using the mouse for 2 minutes now, but I will tell you how to go over there. So what I want you to do is, I want you to go to files, I want you to go to more, and if there is no more, then you will see the options directly and we will come to the options. By doing this, an option window will open in front of you.

Just in case if you don't want to use the mouse, it's very simple, you will press alt, the moment you will press alt, you can see that if you want to open a file, you can press F, H, N, P for page layout and what not. Then without pressing Alt, I have pressed Alt just once. Without pressing Alt, I will press F00 for more and I come to option.

Or you can do Alt F. Alt F and T So, this will also come directly to the option window. This is written very easily. Now, here there are multiple channels over here. First of all, we go to general. Generally, what we have to do is we will be focusing on here that when creating a new workbook, that whenever I create a new workbook, then what happens, then we will not do the default setting again and again, that sometimes new Romans is coming.

See, my intention is that you make a very professional financial model, which actually makes the investment bankers who follow the standards and conventions. Okay, so I want to teach you to that level and here only on YouTube. So what we will do is we will set the default font on Calibri. Calibri, okay.

You don't have to take any other variant of Calibri. Just don't take any other variant of Calibri like light, dark, etc. We will select plain Calibri.

We will select font size 11. We will select default view for new sheet as normal view. And include this many sheets. This means that whenever you are opening a new excel file, what you want is that how many sheets are open.

Earlier it used to happen in old excel that 3 sheets are open. Now we don't want that one sheet is fine. If I want more sheets, I will add it on my own. Not an issue.

Right. So, when you do it here, it will give you a prompt. It is not giving me because I have not changed my font.

But say for example, if I change my font size, I will go with Alt F T. And I change my font to say for example, this one. Berlin. Okay. Berlin sense FB.

And as soon as I press it, it will give me a... prompt data please close and restart excel so that the font changes can take place why it didn't give me before because my font was already calibrry and I have just changed it to calibrry only so I will change it to calibrry again and I will click on ok once that is been done we will just again open it and after that we will come to personalize your copy of Microsoft Office so here you can put your username that you know whatever you want, I have put evaluation school here, you can put your name, it will be really good if you name comes in author if you want to decorate the background of the office then you can decorate it at the back, it is not very necessary like if I am a circuit then I can see the circuit at the back, if I do calligraphy then I can see the calligraphy, whatever it is I will leave it on circuit as it is default setting. And that's it.

Here one more thing is there. This show the start screen when this application starts. This last option should be unticked. If it is ticked then what will happen?

I will tell you something. Say for example it is ticked. Okay.

So what will happen? Whenever you open a new excel then it will not open a spreadsheet. Rather it will open a window like this in front of you.

That this is the start window. Now you need to go and open a blank workbook. By yourself.

Now what happens is it looks very easy to listen. That we can do this ourselves. But when you are doing. When you are working on excel for day in day out. You want to optimize it so well.

That it works in your favor. And your time is saved. If even one minute is saved. Then I will try to save that one minute.

Okay. Now what we will do is. We will go to the formula tab. Again. I will try.

And I will do it again and again so that you know it will be easy for you. Alt F T. Option opened. After opening the option, I went to formulas.

When you come to the formula, then see one thing. In the formula, first of all you have to come to calculation options. In calculation options, the first function that you see here, this setting is of workbook calculation.

In workbook calculation, it is automatic. Automatic except for data tables and manual. Look, I had two options.

Either I keep this video very short so that the views come more. And I tell you quickly that this is what you have to do. But it is my responsibility to tell you that if I don't want to do it, why not? So say for example, I keep it manual for one minute. And I write here 100. I write 100 below.

And I add the sum function of both of them. 200 came. Now what I did? I do it 10. So you are seeing something is happening.

I did 10 so 200 and 110 did not change. Why did not change? Because I have said my functionality in formula that brother you do not calculate any formula automatically.

You calculate it manually. Okay. Don't do any automatic calculation. Automatic except for data table is when you are working with some data which is or with some financial model which is very heavy with data tables.

Today, next session we will learn how to work on data tables. But if you work with data tables, then it takes a lot of time to refresh it again and again. Excel takes a lot of time. So it says that you automate all the formulas but leave the data table manually.

I will have to update it on my own. And the best option is automatic. We will keep it on automatic. When you put it on automatic, it automatically entered 100. I am doing it again 100, so it is bringing it to 200. Okay.

I hope this is very much clear. Now, again I will go to... Formula. In formula, you will see here, enable iterative calculation.

Understand this very carefully. Most students don't understand this. So, please try to gain at most attention as possible.

We have to keep this unticked. I will tell you what is its function. First of all, if I keep it unticked, what will happen? See one thing. I have given an example here for iterative calculation.

This is my iterative calculation. Now, you need to understand. For example, these are the sales of my three different companies.

These are the sales of my three different companies. One is Tata Motors, one is Maruti and one is Aishare. These are all hypothetical figures. Tata Motors has a sales of 1000, Maruti has a sales of 4000, Aishare has a sales of 500. And the total sales of the entire market, I am saying that there are only three companies in India that make cars and no other company.

So this 5500 is the total market size. For a minute, understand this way. The total sales of the market. So what is happening now? Now this normal function is applied.

Normal function means I have summed all three. And this total is in my total sales. I will make it total sales.

So, it is in total sales. But now you have put a condition that the sale of Tata Motor, that is one third of the total sale of all three companies. You have put a condition like this. So, what did you do?

You see, understand my point. These three things are input and this is output. Now input is different, output is different. Input is in absolute numbers.

Output is their calculation. But if you accidentally link input with output, then it is called circular referencing. What did you do?

I divided the sale of Tata Motors from total sale by 3. When you do this, then it will give you an error prompt. That you are doing circular referencing. Don't do this.

There are one or more circular references where formula refers to its own cell either directly or indirectly. This could cause them to calculate incorrectly. Do you know why I am getting this prompt?

I am getting this prompt because I have not enabled iterative calculation. Okay. Just once I will bring it back to normal.

And now I will enable iterative calculation. I have enabled iterative calculation. I have enabled it. And I have done OK.

Okay. Now it is normal. Input is different or output is different.

I link input with output. Divided by 3. Now when I will interpress. So what will Excel do? Excel will bring a figure of total sales.

Which will be exactly one third over TAMO. Means in Tata Motors. Means it will do LHS equals to RHS automatically. See this.

So Excel says. If Tata's sale was 2250. or 2250 and just a minute I will make it normal for you if Tata's sales were 2250 Maruti's sales were 4000 and Aishar's sales were 500 then your total sales would be 6750 and 6750 will be exactly Tata's motors sales would be exactly 1 third of 6750 are you getting my point we always have to be careful Iterative calculations To disable Okay Second one is When you go to Working with formulas In the formula tab So here It will write R1 C1 Referencing style We have to keep this Unticked What does R1 C1 mean Basic Very simple function Wait a minute Its function is When Yeah Now you are seeing A1 B1 B2 So what it means is It is doing columns A, B, C, D Alphabetically And rows In numbers 1, 2, 3, 4, 5, 6, 7, 8 This is its referencing style To reference any cell So if I am on this cell Then I will say I am on E2 Simple as that Alt F T Went to formulas Now if you do this R1, C1 And do it ok Then see this Now what happened He made columns and rows alphabetical. Now, how will I call this?

I will call it C5R2. This becomes very complicated. Why?

Industry also doesn't use this. We are doing financial modeling to remove complexity. And not just because we want to make things very complicated. Now, you see here very carefully. I don't want to waste more time over here.

But if you come to the formula at the end, then you take the reference of this sheet and untick all these things. Like inconsistent calculated formal iron table. What does it mean? If you want to understand it, then understand it. It shows an error.

I will do this for a minute and then tell you. There is no other problem. Like what I did here, I did, just a minute.

So what I did here, I wrote a hundred number and now I am making a table of 100. I am making a table of 100. 100, 4, I will explain all this one by one. 100, 1, there. 2, 4, 5, 6. You can see that there is a formula here. From what is written in K25, you multiply 1, 2, 3, 4. Do you know what I do in the third one?

I directly do 100 into 3. When you do this, it will prompt you to do an error and a green screen will appear here. I have unticked all the options, maybe that's why it is happening. But you can do it really easy and it's a very simple thing to do. So just don't waste much of your time.

And you can see this, take a screenshot from here. This is in the excel file. So you can just do it like this. I will just remove this. Now, we will move to save.

So when you open save, When you open your excel option and go to save, then you have to do save my workbook within every 1 minute. What will happen is that excel will auto save it every minute. Now this is 1 minute, if it was 30 seconds or 5 seconds then every 5 seconds I would have asked excel to save.

And keep the last auto recovered version, leave this as default. You don't have to do anything in this. Don't worry. You don't need to do anything on this.

Then we will come to the advanced function. In advanced function, you have to keep this as unticked. Its basic funda is that whenever I enter something after writing something in a column, then where does my cursor go? Okay, I'll just show it to you right now.

I'll tell you in a very easy way, so there won't be any problem. I went to advanced and after pressing move down. If I press move down, And I have saved it. Okay. Now see.

Now as I press enter, my cursor has come down. If you want, if you are comfortable with it, then keep it. I am not comfortable.

I want when I press enter, my cursor should remain there. It should not move from there. That is why I do it normally. I keep it unticked.

But that is completely optional. If you want to do it, you can do it. If you don't want, don't do it. Okay, and third is my add-ins. What you have to do in add-ins is that you have to keep one thing in mind.

That in your add-in, analysis tool pack should be in active state. Because when we will learn to do regression, then only analysis tool pack will be very useful to us. How can you do this?

You can go to manage admin. I will just show it to you right now. You come to add-ins.

First of all, you have to check if the Analysis Toolpack is active in the application add-in. If it is active, then no worries. If it is not active, then you have to go to the Go tab and tick OK. I am not talking about the VBA, Analysis Toolpack.

So, you have to add it in and click OK. It will automatically come in the application. This is my complete Excel settings. Now we will move to the next stage where we will understand such functions of excel which will help you a lot in making the complete financial modelling.

In this particular part, we will learn some basic setup, advanced setup, basic formatting And then some functions without which you can't make a financial model. So we will start with basic convention. Convention is that which is followed by the whole industry. Along with following, if you are following it then you are following the industry.

If you are not following then you will feel that some amateur has made a model. My objective for this session is that I should tell you all the things that are part of a professional financial model. Okay, so let's start quickly. For example, number one. We don't have to start the first column of the spreadsheet from there.

The width of that will be smaller than the rest of the columns. It will be a margin column. For example, if I have to set its width.

Assume this is the width. I have to set the width of this, so I have to do two ways, either I right click on the column and set the width of the column. Again, the point is that we will use the mouse less.

So what I did is, I am here for example and I have to select row A. So I will press control and space together. So my row A will be selected.

Then I will press alt, so I will press H to go to home. You can see that it is for home. If format is O, then I will press O and press W for column width. I will tell you again, Alt H O W, how. And here I will set 1.15 in column width.

So my column width is set at once. What is its use, what is necessary to do, it is very important. It has so much important use, I will tell you in this session. Then in making financial model, first of all we will write currency on the top.

Whatever my currency is, it will be written on the top. So that you should know in which currency I am talking. Then from here my years will be plotted. From here I will plot years. So 2020 is my actual year.

Then 2021-22 will be like this. I have two options that either I do hardcode. and so on blah blah blah blah blah 4 5 and 20 26. Okay, here I will do hard coding like this.

One thing to keep in mind is that hard coding should be minimum in financial model. You need to understand this, okay. If you want to put any input of hard coding then that input should go only once, not again and again. So what I will do is, I will say in 2020, do plus 1. And I will pull it till 2026. So what happened is that if I am doing 2019, then automatically everything is changing. Okay.

Now the second thing that I get is that our financial year is not over yet. The financial year 2022 has not been ended. So the actual financial statements that I have will be here. 2021 and from 2022 to 2026, my estimate will be 5 years. Forecasting will be done.

So, I have to show this somewhere to the other person. How will he know? If you are looking at a financial model after 2 years, then how will you know, till which year is the actual estimate? There are two ways of doing this. Either you do actual on all of them.

Write estimate here. Now again this is a very rough way of doing it. We have to work professionally.

So what we will do is, we will select these two actually. Press control 1. When you press control 1, the format cell will open in front of you. The format cell will open.

You will go to numbers. Go to numbers and go to custom. Go to custom and go to the first option below general. where it is written as 0, you can see it on the screen.

After that, you will go to the type bar, you will open double quote and you will type A in capital and you will close double quote. When you are doing this, you can understand how the sample is telling in it. If you type small a, if you close double quote, it will tell small a.

We will type big A, capital A and we will press OK. When I have pressed OK, you can see it as 2020. Now, the rest of the data is estimated. We need to repeat the same activity. All the estimated years, the forecasted years, go up there and select them all.

Press Ctrl 1, come to format cell, come to numbers, come to custom, select the first one, select the first one, double quote open, E double quote close.. So it will automatically put E in front of 2022. Just in case if you want to use F instead of E for forecasting, you can use F. You have options.

There are different things in industry but you can understand what E and F mean. Now if you see there is some problem in it. The problem is that column width of D is different, column width of E is different, column width of F is different. You don't have to do all this. It doesn't work like this.

What I will do is, I'll go over here. I want to be in the D column. If I press Ctrl and spacebar, then my D will be selected.

If I press shift and right arrow, then all my columns are pressed. I press Alt H. So I went to home, O formatting and if I do I, then it will become auto width column. I don't want to do auto width column, I want all my widths to be same.

So I will do W and I will do it 10 here. By doing 10, all the widths, especially this one, are set. And these two widths where we will write everything here, I will make these two widths 12. H O W 12 So it has become a little bigger than them. Now, Once I have done that right and guys please you need to understand up Jitra come mouse use caroana up kill it on a better now Hum carengi ab hum age omara header a yeah, hamesha visible on a chair immediately visible on a chair So my control be car don't know it's called bold karuna. Fair man home page on go My bucket may John guy and I will choose some dark color.

I could take green also I could take orange also may be Kelly blue. Let's on you a la blue I think a second last Romney last voila blue Lelia and And I will make the font color white so that it is immediately visible. Now what will happen?

Now I have done this. Now when you will go down working. And you have come here for example. So now you don't know which year we are talking about.

So for this I have to do free span. How to use free span? Understand that. I will understand it in one go.

I selected the 7th row. When you select the 7th row, it means that if you apply the free span function, then the area above selection will be freezed. So for free span function, you will go to view, free span and you will free span.

You can use shortcut also, I will use shortcut. Like if you want to change font color for this, then do alt H F C and I will select white color. I came down, I pressed shift and space bar so whole row is selected.

And in alt, W, F for freeze pane and again once more F. So now this selection, everything above it has been freeze. When I come down for my work, just a minute.

When I come down, it didn't happen. I just need to do it. it once more alt w f and f okay so what happened now this is completely freeze when i come down for doing any kind of work then everything above will freeze i have put this logo of valuation school you want whatever company you are valuing you can put this logo okay now what we do now we start doing basic plot of income statement So I have typed here income statement and I will just do it.

It will be first of all top comes revenue then comes my cost of goods sold. So I get gross profit. From gross profit I will reduce selling and admin expenses.

My rate will be EBITDA. From EBITDA, I will first minus depreciation then interest. Then I will get EBIT, I am telling you a very basic statement. Then we will talk about taxes, then I will get net profit.

Or net income, whatever you want to say. Whatever you want to say, say net profit or net income. Okay, let's say net income to maintain the consistency. It is very important to maintain consistency. This what I have done now.

you will feel that I have done it fast I already know all these useless things don't be over confident in a minute he will give you a lot of money in the interview you will get this exercise file here I have guided you step by step what all things you have to perform you just have to run those things and see we have already given the exercise file in the description if you are facing any problem while doing it then what you will do is go to the next tab you will see the same thing or very similar to the previous one almost same the only difference is that we have started one dot below so I am adding a line yup Now this is exactly same I will come to the basic convention part 2 In basic convention part 2 I have to tell you some things which is very important See when you make financial model you have a structure and you input a data in it That data can be given to you by client that these are my actuals you can take it Or you can take that data from internet I have given time because purpose is not to tell you about data but to teach you formatting and basic convention So I have given basic data here I have given a sample data in this data. Basically, I have taken any random data in this. So, what I have to do is I have to plot this data here. The best way of this is You go here and do equal to the revenue of 2022. Come to basic data and merge it with this.

When you do this, then you can plot it till the bottom. It happened quickly. Do you know why it happened quickly? Because all these line items and all these line items are same.

But it is possible that the data given by the client is different from the line items of your model. So in that case, you have to go and plot the data individually. I will directly copy the data from here.

OK. I'll just copy the entire data from here time being and I'll go over here delete it for a minute and now I have to paste the data here When I need to paste the data, I have two options. Here I will do Ctrl V, it will be directly pasted. But what will it bring along?

It will bring value, format, formula. Whatever is there, it will paste as it is. I just need values.

When you just need values, then for that you have to paste special. Ctrl Alt V. Rather than pressing Ctrl and V, you need to press Ctrl Alt V. When you will do this, the window of paste special will open in front of you. This is very important window.

We will use it again and again. You will go to values and click on ok. When you go to values and click on ok, then this data has come in front of you in a very good way. Now, Now there is a problem in this data. The problem is that some of these are in decimals, some of these are in absolute numbers.

We have to maintain consistency and make a thumb rule. The thumb rule is that whatever numbers I have, they will be on one decimal and whatever percentage will be, decimal and comma will be on 3 3 distance which is US convention. So I will do control 1, please understand this a little bit.

I have selected all the numbers first, I did control 1 then format was settled, I came to custom. Now pay attention, this one is visible to you, stop the screen and see once. From here to here, this semicolon divides both the numbers. There is a semicolon in between.

I am not sure if you are able to see it or not. On the left of the semicolon, this is the formatting of the positive data. On the right of the semicolon, formatting of negative data. So I want the formatting of positive data to be like this that 3 numbers, put a comma on 3 numbers and with that dot I will make it 0. So I want formatting on 1 decimal. See it is visible above.

And in negative number I don't want that negative sign should come. I want that negative number should come in bracket. So I will delete the negative sign.

I will delete the negative sign and take the negative number in bracket. Dot 0 because negative number also need 1 decimal and I will click on OK. When this is happening then you see all the numbers are constant and in case if I put any negative here then it is automatically coming in bracket. You don't need to do that thing again and again.

This is very important and we will maintain this consistency throughout That is number one Number two, now you will see that everything is hard coded See this, we have copied and pasted everything Can I put formulas in this? Yes, you can Like I can make a formula for gross profit, EBITDA, EBIT and net income So we will make it quickly minus this copy it here then EBITDA is nothing but gross profit minus selling expenses, copy it then EBT, pay attention here pay attention here it's nothing but EBITDA minus sum of always say sum of the individually you minus, please don't do this And I copied and pasted both of them. I came to last net income EBITDA minus taxes.

Okay, both of them are equal to me. Now what happened is that there are some formulas and some hardcoded numbers. One very important convention is hardcoding color convention. throughout follow throughout follow so this means that all the formulas will be in black and all the hardcored numbers will be in blue and which should be blue?

good blue So don't take this blue, take this blue which is coming here. What you will do is, you will go in more colors, standard, go to standard and take this blue, this blue, from here third, in second row third blue, take this, this is standard color which is used in the whole industry. And you will, Now since you have used the last function and not used any other function So you will select the hardcode numbers and press F4 So it will automatically repeat the last function I want you to be doing very smart work not you know donkey work I will tell you that you have to do laborious work one by one So now the picture is clear in front of me That boss the blue color are constants My hardcoded numbers are and the black are formula numbers I hope you all understood very well These were my basic conventions Now I will come to basic formatting Basic forecasting So now You must have seen that We have You are facing problem in doing this. Come to basic forecasting and you will get this done. Now assume you have given you assumption drivers and you have to plot numbers from them.

How will you do it? We will learn it in professional way. First of all, we will plot actual numbers.

It is very easy to do here. Revenue growth can't be a plot of 2020 Because 2019 number is not available One more thing to keep in mind This column of particulars It will be aligned right And all other columns will be aligned right right sorry your this column will be aligned left and rest of the columns will be aligned right keep this in mind ok so now if you see here it is left aligned it has to be right aligned I will select all at once alt h a r alt h You use this only, right? You use mouse, I used shortcut, simple, no other thing. Now, revenue growth, what is the growth in 2021 compared to 2020?

So, 2021 divided by 2020 minus 1. When you do this, then you will get this figure that there is a growth of 0.125, means there is a growth of 12.5%. I have to convert this into percentage. To convert into percentage, there is a very easy option.

Alt H and I will press P. So it has converted into percentage. But we had talked that we will keep percentage on 2 decimals.

Now to increase the decimal, So what do you usually do? You go to home page and increase the decimal from here. Okay, don't do this.

Okay, don't do this. We will save as much time as possible. Alt H 9, oh sorry, Alt H 0. Alt H 0. I repeat it once more and it is done. I hope this is very clear. Should I do it in blue color?

Not at all, it is not constant, it is linked to formula. So, we don't have to do it in blue color, it will remain black. Cost of goods sold percentage to revenue.

It goes up. Cost of goods sold divided by Revenue copy paste here I am taking constant in SNG expenditure, it is above SNG expenditure is constant, so I am taking constant Formatting, at last I will correct it completely Depreciation, percentage of sales So your depreciation is divided by percentage of revenue 4% is coming out Interest I am taking constant which is above interest You have to add according to the data available in front of you. Did I write here interest as a percentage of sale?

If not, then you have to take constant. Tax is 30%. This will be our tax. Did I hardcode it?

Yes, I did. I did hardcode it. So, it is simple. First of all, I will...

First of all, we have to blue it. Don't leave the time for later. I will tell you how to audit the model. Now, once that is been done, I can understand that there are some percentage signs which are not proper.

So I will go here and copy it. I will select these and I will select the one below. And I will press Ctrl, Alt and V. Now I just have to press the format of this cell on selected cells.

So I will go to the format and press OK. And I will go to the format and press OK. If you want to do a shortcut, then Ctrl-Alt-V.

And you can see that there is an underscore under T in the format. So, you press T and enter. It's very simple. If anyone is not able to understand, then slow down the video and watch it.

Repeat it again and watch it. If anyone is able to understand, then take the video to 1x, 2x. But don't ignore it. Because if you feel that you already know all these things, then don't worry.

I am... I am... I am committed to give you the value and I will give you some new perspective on this. Now I have to plot the numbers for the future.

I will tell you what numbers to plot for the future. Revenue growth is for 10%. We will learn to forecast.

How to forecast in actual. I am telling you this according to formatting. If you already have forecasting.

Then how to plot numbers professionally. So 10% revenue growth will be ahead of us. Our cost of goods sold will also be 40%.

My S&G expenditure will be 2500. My depreciation to sales will be 5%. My interest expenditure will be 5% in the future. 250 and taxes will be 30% Did I hard code all this?

Yes, I did. First thing I have to do is I have to blue this. Before that I will set their formatting.

So I copied All my percentages, I selected them Ctrl V and I pasted the format Then I copied it Here and here I have pasted the format I hope this is ok, now all these numbers are hard coded So first of all I have to go to the page and without using the mouse I have to come down I have to do this, without using the mouse I have to come down Which is my recent color and select it Now I have two options, sir let's do one thing Since everything is going to be same, then let's copy it and paste it from here. This is the wrong way of doing it. What are you doing?

You are inputting hardcoded words more than once. We discussed in the starting that hardcoded number will come only once. It should not be added again and again. You understand what the problem is.

Now it is on the same sheet. But if you have the habit of hardcoding again and again and it is on another sheet or you have hardcoded the same figure at 10 places and that figure has changed. Later you will know that the figure has changed So you will not even remember at which 10 places you did the hard coding So I have to link it with this That link with the last one What is the advantage of this? Formatting will be spoiled? No problem, we will correct it in a minute What is the advantage of this?

The advantage of this is If in future I am told to take 10% or 12% Then you don't need to change anything Or if it is said that from 2024 onwards 15% will be there Then you just hardcode it in 15% And do formatting again If you do formatting then it is done Now let's correct its formatting Let's copy this format Let's paste all this. Ctrl-Alt-T. Enter. Come down.

And press F4. It takes 1 minute to do formatting. How much time will it take?

And all this speed. Just don't underestimate yourself. That I am not getting it. It will come slowly.

You have to practice. Practice it. That's why they did it.

See yaar if you don't practice then it's all vain Don't waste time, go and watch other videos Entertain yourself Don't stay here, don't learn I don't care I am doing this for one person who will watch this video till the end and learn from this video. I am making this video for that. Now I have to plot the numbers. Here I need to plot.

I have to plot. It is simple. I will go here. I will press equal to sign. Growth rate means 10% on that.

So we will select this. I will select my last year's sales. Multiply by bracket open 1 plus growth rate.

1 plus growth rate, the growth rate has come down. bracket close, enter. Don't take the problem of formatting. This is the growth rate, my plot is complete. Formatting is done in 1 minute, what is the matter of formatting?

You know how to do this. Nothing, normal, okay, do we have to keep it blue or not? There is a formula, so I will go to the black one, I will copy, I will come here, select it completely, Ctrl-Alt-V, for format T.

Enter, formatting is done. Cost of goods sold is the percentage of sales. So sales into, directly percentage will be applied. 40% of the sales.

Not 40% growth on sale. Copy this. Paste. From above, pick up the formula.

Copy it. Ctrl-Alt-V T for format. Formatting is done.

This is running constantly. The gross profit formula is pasted below Now the topic of selling and general expenditure It is hard coded, you have to directly link it There is no formula but I have linked it I have not hard coded it You have to keep all these things in mind This will differentiate you Excel operator is different Financial modeling expert is different You have to become financial modeling expert Not a plain excel operator Now Abida, the formula of Abida is directly applied I will do it quickly. Depreciation is the percentage of sales.

I took sale into I put the percentage of depreciation. We will do formatting at last. Don't take any problem.

This is direct, absolute is coming. I took this. I copied it.

I put the formula of EBIT. Taxes, EBIT is 30%. So, EBIT is 30%. EBT into 30% Net income formula is there, press it and add the formula Press Ctrl V and add the formula Now my formatting, this is the correct formatting, copy it Press Ctrl Alt V, T for format, press it and your formatting is done This is your basic formatting I have also told you how to plot with assumption driver.

Now we will go to advanced setup. When you go to advanced setup, you will get this already done. Before this, I have to tell you one more thing. Now you see. I will make this a little more beautiful.

So what I did to make it beautiful. I have to immediately highlight this income statement. So I selected this. I went to home. And I will select the color in the bucket.

I can do it with the mouse if I want to but I don't want to do it with the mouse. To tell you that you can do anything without the mouse. You have selected it completely.

Alt H and H. Press it once more. H is written in front of the bucket.

Press H once more. See, Alt. H is written on the home. So, we have to go to H.

If I press H once more, the bucket will open. And I will select this color. Done. Now I come down, I go over here and I just press F4. So this is my more professional look in the model.

I want to tell you one more thing which we have to do immediately. What happens is that we will keep making many different tables below this. So, the model should not look very clumsy, that's why it has grouping. You can call it grouping of rows.

Now, what I have to do is, I have to put a function here, that it should collapse completely. Collapse means, it should be deleted. No, it should collapse only visually.

So, it is called grouping. I will press shift and space, so my row is selected. I will press shift and downward arrow, so I have selected this whole.

Done. Now, I have to put row of all the particulars of income statement. You have to select the whole row.

You don't need such selection. This selection will not work. You have to select the whole row at once. When you do this, then you will go to Alt H. Sorry. Go to data and group it.

When you group it, you will see something here. When you close this, you will see a sheet like this. Should I include this as well?

If not, then how will I know about the income statement? Now I know that the income statement is here but it is clubbed. It is grouped. Open it. Sir, can we do this grouping manually through data?

I mean, through mouse? Yes, we can do it. How can we do it? I will tell you in a minute.

You have selected this. Whatever you want to group or club, you have selected. You shift, alt and right arrow. Shift plus alt plus right arrow. If you do this, it will be grouped.

If you do shift plus alt plus left arrow, it will be ungrouped. I have to group. I have grouped. Then I came down in Assumption Drivers. I pressed shift and space.

So the whole row was selected, I came down by pressing shift and came till here Then I pressed shift, alt and right arrow So this grouping is done Now you also clip this and this too When you do this and go away, you will see that the income statement is also clubbed. This is also clubbed. I hope you understood till here. Now we will move ahead in the advanced setup.

When you go to the advanced setup, there is one more thing in the basic formatting, it's okay, it's a very good thing. The more I tell, the less. Now I have to highlight some things in it. First of all, I will highlight the top and bottom row. Control B. And control the one below also.

First of all, the top line and bottom line will be highlighted with bold. Then what are the most important things in which I need immediate attention? Gross profit, EBITDA, EBIT.

These are the three things. So what I will do is, I will select the gross profit row only till there without selecting the whole. And now I have to put the border.

I want to put the border only on top. Usually, you go to home, go to border and put the top border. No problem, put the border. This is a little unprofessional way.

Don't use it. It is the right way but you have better way. We just selected gross profit and pressed control 1. So, format cell opened in front of me.

After format cell opened, I went to border. What I have to do after going to border? First, I have to see what style of border I have to take. Do you want to put dotted border or plain line?

What will be its width? If the line is wide or you want to add a very fine line, then I have to take this line. Now, the selection you have done of the row, of this cell, In this, you have to put the border on the top, on the left side, on the right side, or in all four places.

I just have to put it on the top. So I will select it and click on the top here. And I will click on OK. So you can see that it is highlighted that there is some calculation up to here and this is the result of that.

I have to do the same on EBITDA. Now I don't need to do it again, I just have to press F4. It's done.

I just have to press F4. The last column, the last row, sorry, the last row is my result. It's the end of the statement, the statement is ending there.

For that, what I have to do is, I won't be able to work with one single border. I select it, press Ctrl 1, on the top, I have I put single row, single border and below I have to put double border. So I put double border below and I have to click OK. When you click OK, then this model is now very professional looking model. One thing you must be thinking that where are the grids in this?

And what we will do about grid? So go to view and always keep grid line off. Now see how clumsy model is looking.

Now how clumsy it is looking. It is not looking very neat and clean. See, there is only visual representation.

So you have to off grid lines You have to off grid lines It will become neat and clean model It will see directly what you want to see Are you getting my point? Now finally we will be moving on to advanced setup In advanced setup we will learn few things That is Number one, we will understand that we will make a common size statement and change analysis statement. I have to teach you the purpose of making these statements. Anchoring is of two types, one is absolute anchoring and the other is partial anchoring.

And we will understand what is the use of both. Before that, we understand partial anchoring. Before that, I have to see one more thing.

I have to see if I have done any flaw in convention. If I have done any flaw in convention, it means that the model which I have made till now, if any convention has been missed in that. means formula was there but it was blue, constant was there but it was black, I couldn't make it blue, how will you change it? how will you check it?

so the easiest way is, you stay anywhere randomly, press control G, then go to will come when you press control G, it will prompt your window called go to and you have to go to special, can you see special? you have to go to special, if you want you can press S also, you will go to special, I pressed S, not here Ctrl G and S Alt S Press Alt S and you will go there Now I Select constant here And press OK Now what happened in this All the things are selected automatically Which is constant and hard coded So you will get to know which cells are there Where formula is not used and it is hard coded So you just have to ensure By looking at it that is there any black cell Ok Let's see, as you can see, here is NA but black, there is no problem. It's absolutely fine because it is not a number. But I have hard-coated this 3030 but I have forgotten to make it blue.

Okay, so I will immediately, you understand how the model is audited. With these tools, they will audit your model. Whenever you review. You need to have surety yourself. What I am doing is I am doing it correctly.

I have done it. Now I have to do one more check. That my formulas, Those cells are not black. So I will go to go to, special. And again I will go to formulas.

So my formula, formula is selected. I hope you are still understanding this. Now we have to make a common size statement. Common size statement means that you define every parameter of income statement in percentage of revenue.

Percentage of revenue Like Depreciation as a percentage Of sales I took But I kept interest Standard Static I didn't link it With sales But if I Common size statement Purpose is To see All All items revenue percentage pair a ticket to a behemoth carrying a move per jayang a hum pura pura income statement was if copy carrying a or men Nietzsche as it is based a mean he karuna I will not be pasting is control we karuna the cookie car I think I have a formatting it the Gandhi or EOS key which in a karna I only want the values and not the formatting or the formulas So this became my revenue statement. It became my full income statement without any formatting. Now, how much percentage of revenue is there? It is 100%. So I went up.

I went up. So revenue will be divided by revenue. D9 divided by D9.

Okay. So this 1 has come. 1 means 100%.

So first of all, we will correct its formatting. I did alt H and P. So it converted to percentage. Now I have to increase 2 decimals more. So alt H.

Alt H. And 9. Again alt. H9 It happened immediately You will take the mouse in your hand You will open home You will go here Okay, if you keep this open Then you will see You will be prompt to use the mouse So always unpin this I never pin this I always need neat and clean Excel The thing on which I have to focus That thing should be in front of me Now Revenue, now what I will do is drag it down. See what problem is happening.

I have done control V. When you are dragging down, it is doing D10 by D10. Then D11 by D11. I don't want to do this. I want now that cost of goods sold is how much percentage of revenue.

So I want the revenue column to be constant. Revenue column is constant, so here I will use partial anchoring. I will explain what is partial anchoring.

Cost of goods sold divided by sales. Divided by revenue. Now understand. Now pay attention please. This D9, I have selected this D9.

I haven't pressed enter yet. I will press F4 once. When you press F4 once, then sign of dollar before D and sign of dollar before 9. This means.

Column D is locked and row 9 is also locked. Means this is absolute anchoring. If you do anything, then it is locked. But I don't want this and I will tell you why. Okay.

Let's do it for 1 minute. Let's do it with absolute anchoring. Okay.

So 40% is coming. It is coming absolutely right. I copied it and it went down.

Cool. Now see, is EBITDA 50% of sale? Let's check once.

EBITDA is 10,000, revenue is 20,000, 50% of sale. So you can do one more thing, then copy it and I will paste it like this. No, it's wrong. Figures are looking good but what problem is there?

You have done absolute anchoring above. What is happening because of absolute anchoring? Focus here.

For next year also, COGS of next year. Comparing with last year's revenue. This means I don't want absolute anchoring here.

I want partial anchoring. Do you understand? I will press Ctrl Z and go back to 40. Now this is absolute anchoring. So I want that you anchor 9th row but don't anchor column D means when I come here then it becomes E9 and here it becomes F9 so either remove dollar sign manually or when you anchor If you are anchoring, then if you press F4, then both are dollar.

If you press F4 again, then only row is anchored. If you press F4 again, then only column is anchored. If you press F4 again, then nothing is anchored. So I will do anchoring once and then do anchoring again. So this is my partial anchoring.

I have freeze 9th row and not columns. And I have done OK. Now when I have done OK, now you see. Now see what happened. Ctrl V Now My next year EBITDA is also 50% Let's check.

Is it working properly? It is working perfectly. Let's check randomly. Is it working properly?

Yes it is working properly. It is including the depreciation of that year with the revenue of that year. And here there was no requirement of anchoring.

If you want then do it. It will be good. This is the concept of partial anchoring. Now I have to group this too. So what I will do is, select the whole row by pressing shift.

How to select? By pressing shift and space bar. That is how the row is selected. How to select the column?

By pressing ctrl and space bar. That is how the column is selected. Shift and space bar.

I selected all the rows and I Shift Alt and Right Shift Alt and Right Right arrow, so this is my grouping Grouping is done, how easy it is to work Now I want to do a change analysis. The main purpose of this change analysis is to teach you absolute anchoring. What we will do is, we will copy the entire statement as it is and paste it below.

We don't have to paste the values, we just need the entire statement with formatting. In change analysis, I want this. In change analysis, I want this. Just a minute.

I copied it completely. Ctrl-C And I pasted it. In change analysis, I want this.

If someone tells me that if you increase 10% of every line item, then how much impact will it have on your P&L? Now I have to do that. First of all, I have hard-coded 10. I want to give it blue color first. I have given blue color.

I got rid of it. Okay. Now. So I want.

That. This 2020 revenue. If it increases by 10%.

Then how much will it increase? Gross profit will also increase by 10%. Increase Now I'll just copy it and I paste it across. Something happened?

Something happened? What happened? As it is formatting. So what?

I had to paste formatting. I just have to paste formula. So I press Ctrl-Alt-V. And for formula, you will use F button. And OK.

See. All formula is pasted. Do you... The difference between absolute and partial anchoring is that the more you practice, the more your hand will be lost. Don't worry.

One thing is wrong. We will understand that in a minute. I have opened all the statements. Now my income statement is almost ready. My assumption drivers are ready.

My common size investment is ready. My change analysis statement is ready. Now I have to apply the final check, the convention check.

I have done Ctrl G, I have gone to go to and special. First of all, check the constants. Check if there is any constant which is missing in black. Let's check. Is there any selection that is left in black?

No, there is no selection that is left in black. Then, press Ctrl-G. Let's go to special.

Let's check formulas. Is there any formula that is left in black? That is left in blue by mistake. Sorry.

Yes, I got it. Can you see this? This is left in blue.

Don't do anything. Don't do anything right now. Everything is selected. Go to single one shot and make it black.

If you haven't seen it by mistake and it will be left, then that thing, see the one above also selected, that's why it was left black. So it will automatically become black. Now, what is the benefit of making this change statement? I am saying that if you want to do hard coding once, if someone says that if it changes from 50%, then what impact will it have?

Then I can give the result immediately. If it is 5%, then what impact will it have? If it is 100%, then what impact will it have?

Everything will be doubled. I hope you understood till here. Now, in advance setup 2, it is the exact same thing.

First, how you have to go to, number 1. Number 2, we will learn one more thing in it. Okay, which is advance setup, but I will do it over here. I want to perform it over here.

Number 1. Now, there is an income statement. Here, you assume for a minute that the income statement you are making is for Tata Steel. Assume for a minute. So, you have to input the name of Tata Steel at one place.

Keep in mind. You will not do this. Income statement, Tata Steel.

Then come down again. Assumption drivers, Tata Steel. I told you that the less hard coding, the better. So how will it be hard coding?

Means how will we formulate it? Because what will happen tomorrow? Will you make another model instead of Tata Steel? Will you change the name of 20 places?

It does not take that much time. Here you have to assign equal to in its starting. Then you have to lock it in double quote. Lock it in double quote.

Then you have to put the AND sign. Which is above 7. Shift plus 7. Okay. Then you have to open double quote. Give a space bar.

Put a hyphen sign. Then give a space bar. Then close double quote.

Then you have to put the AND sign. And you have to link it with this cell. Now see.

Did you understand what happened? It is written exactly the same thing But suppose this Tata is instead of steel If this was a Godrej model And I would have put Godrej here once Then automatically Godrej would have been done The more you practice this The better it is for you This is what I have told in Advanced Setup 2 I will do it once more and show you Understand it carefully Assumption drivers are I went to formula bar I put equal to sign first Assumption drivers should be in double quote Whatever is in double quote, it will be as it is I have taken it in double quote Then press and Then open double quote again give a space because I want hyphen between driver so I gave space between double quotes then I put hyphen then I want hyphen between Tata Steel so I gave space and closed double quotes and then I put end to my dynamic cell and linked it so assumption driver is Tata Steel then came down in common size statement and this happens in very fast don't worry so you are feeling like this I will directly type b5 Common size statement attached here See, we have to do change statement It is so easy to do I have taken it in double quote and double quote space hyphen space close and b5 Change statement at I Steel. Now what is happening because of this, I will tell you by ungrouping for a minute. You will understand immediately that why I have told you this thing.

I will group this also. I want to group this also. So I have selected the whole row. If you want, you can select it like this also. Shift space and I came down.

Now shift, alt and right arrow. You did that and this whole grouping happened. What is happening is, if you assume that you are making it for ITC in the future, then ITC will happen.

Whatever you want, you can do. We keep it as Tata Steel for the time being. I hope this thing is clear.

Now there is one small aspect that we have to learn immediately before we move on to functions. That aspect is this. conditional formatting if you know then well and good if not then also understand and if you know then also see once atleast in conditional formatting what i want is two types of conditional formatting by the way there are multiple ways but what is the meaning of conditional formatting if my condition meet then you change this in formatting i explain this to excel So here I want that whatever is less than 10% It should be highlighted in grey Light grey instead of black And it should be italic too Say for example and bold or whatever it is Whatever you want to do So first of all where you want to apply conditional formatting in the cells you will select them You will press alt once If you just press alt and leave it then you will see this Then you will type H and go to home Then under conditional formatting you will see L So you will type L and you will come to conditional formatting.

You will come to highlight cell rules. What is my rule? If it is less than 10% then it is less than.

So for less than I will press L again. He is saying. Format cells that are less than 10%.

We have to put percentage sign. What formatting we have to do in them? So formatting we have to go to custom format. When we go to custom format, when we go to font, then first of all I have to make its color light gray. Number one.

If you want, then select italic from here. And. We will click OK when you see something happening. And click OK. Whatever numbers were less than 10% became grey and became italic.

This is the use of conditional formatting. The second use of conditional formatting is that wherever EBITDA, say for example, if EBITDA is less than 15000, So make them red or if it is more than 15000 then make them green So what I did is I selected EBITDA Alt H L Then if you want to highlight then H Greater than then G Whatever word is there in front you have to press it, it is simple 15000 Highlight this with green Custom format fill green Let's take light green Okay If you want to remove all the custom formatting without selecting it, Custom Formatting, Clear Rules, it will ask you that you have to remove the rule on the sheet or on the whole workbook. It is written here, Clear from selected cell and from entire sheet. So it depends.

If you want to remove it from the entire sheet, then it will be removed from the sheet. You have to do this exercise again and again. The more you do it, the stronger your grip will be.

My personal experience is that I have seen that people who are very vintage eat gacha in it. Now we are coming up for some functions. The functions that I want to tell you.

First of all, I want to emphasize on the time period functions. This function will be used whenever I will be doing present value calculation. Learn this once, it will not happen again and again.

I have hardcoded a date here. That date is 15th of August 2022. Now I want 15th of August 2022. What is the month end of the year? Because your financials will be at the month end If you are doing monthly projection, then it will be at the month end If you are doing quarterly, then it will be at the quarter end Usually it doesn't happen at the month end but I am telling you that it happens at the end of the year So if you want the month end of anything Then there is a function for that That function is EOMONTH End of the month End of the month, EO month. You have written this.

As soon as you open the bracket, it will say start date. So, my start date is 15th of August 2022. This is my start date. So, after 15th of August 2022, which month should I want?

Now, here I have the option to make it 0. Write 0 with hand and close bracket and enter. So what did it do? It told me the end of 15th August, 31st August. Again what did I do in this?

I hard-coded 0. This is the deadliest thing that you are hard-coding something in formula. I won't even know. Because you can't catch this thing with goto function.

You see, I do constant and it won't select this. Whereas you have hard-coded in this. This is a limitation of this boss.

You have to always keep this in mind. This is a very deadly thing. You will not remember that you have done 0. When you work on a model for 4-5-6 hours, multiple sheets are made, then you don't remember it.

So what we have to do for that, we have plotted numbers from 0 to 6. Do the plotting, now go in the beautiful type. You have to go on efficiency, the model should be more efficient. Right, not very beautiful.

Why you didn't make it blue? Because this is not my input variable. If you want you can do it.

If you want you can just do it input. If you do it, then it will be hard coding. It depends on you. It's not very hard and fast tool. EO month.

My start date is this. Do I have to do absolute anchoring for start date? Yes, I have to do it.

My start date will always be same. My start date can never change. So for absolute anchoring, I press F4 once, put comma and my month is given here. It is written 0, 1, 2, 3. So I will select that and I will click OK.

Entered it. So, you will see this is my monthly period date. The result is the same. It's all about efficiency.

You have to learn that. You make a model that if there is any error in it, you can't even catch the error. Does not make any sense.

Model should be in your control. Not that you are in the control of the model. You should not be the slave of your model.

Rather the model should be your slave. I just dragged it. And it automatically happened.

Now understand the meaning of this formula. The formula which is here. It is saying. Tell me the end of the month. Will tell.

Which end of the month? This is the start date and after this, tell me the end of the second month. So, August is zero. Sorry, tell me the end of the first month or the end of the second month. Here, tell me the end of the second month.

So, the start date is August. So, in the first month, zero month will end there. Next first month Which will be September And second month October I hope this is ok Ok Can I do this Like this That I took this And I added 365 To it Do it No problem Do it No problem But by doing this If I want annual data, I am talking about it. So by doing this, are you reaching the end of the year? Not at all.

You are not reaching the end of the year. So for this, we use the formula of year. I will explain you how the formula of year is.

Sorry, it is the formula of date. First of all, I will put date. In date, it is asking year.

First of all, tell me which year you want to set. So I will use the formula of year. Year again and I will use this serial number of year.

I did F4 plus do this in that. So this is my year. Which year do I want?

So I want year 2022. Which month do you want? If you are running from calendar year then I want 12. If you are running from financial year then you need 4. And day 30. Do 31st or 30 whatever it is. Do 31st.

Date 31st is here. I have done complete formatting only for this. I have done this much for you.

Hajjat Baaji has done it for this. So that it becomes very easy for you. If you do this, then automatically everything is coming in front of you.

Don't worry if you don't understand it at once. Rewind the video a little. Think calmly.

Everything cannot happen quickly in life. You cannot produce a baby in a one month by making nine women pregnant. This is the quote by Warren Buffett, few things just take its own time. So understand things slowly.

Now, I want to know how much gap I have between 2 months and 2 years. If you see this, it is very easy for you to say that if this happens, we will do this, if this happens, we will do that. Don't worry. What does this mean?

Between 15th August and 31st August, I am telling you by looking at the type, there is a gap of 15 days. I am not talking about in the days terms I am talking about year terms So for this a function is used Our year frac I use this function Year frac What is the start date? 15th August I have to do absolute anchoring It will not move, start date will be same And what is the end date?

Monthly data is this So this is the difference of 0.04 years between 15th August and 31st August 2022. I will drag this and drag it till here and press Ctrl V. Similarly, I want the annual period. Do you know where you use this annual period? Present value factor. At that point of time you need this. You apply its power.

If you know the formula. And if you don't know then don't attend this class. I am taking this presumption that you know basic accounting and basic finance.

I am teaching you financial modeling over here. Okay. So I will apply a formula here. Year FRAC again. Start date.

I need to freeze my start date. I pressed F4. End date. This is my date.

Copy it And I have put the formula of ear frag I hope this is clear Where this will be used I will explain you Don't worry Okay, there is one thing In starting Do you know what we were doing in starting We had said this thing in starting That we will leave this column This is a very important column. Why is it important? I will tell you the importance of it now.

Whenever you make a new table, then on the side of its header, this column A8, you will input a hash. I will explain the reason for inputting that hash. Understand it very calmly. I am not in a hurry. If you are in a hurry, then you can leave the video.

Okay. Those who want to learn, they will learn. Now, Say for example you are here And now you have to go to common size statement Here you have to go to change analysis So you have only one option You go down and change analysis Now you have come early But suppose below this 15-20 table are there And you have to go to last table So either you will go one by one Or you will have to pick the mouse I don't want to pick the mouse I don't want to pick the mouse So what I did is I put it here What is the advantage of this?

The advantage is that you can pick the mouse I want to go to common size and I don't want to use my mouse. I want to go through keyboard. So what I did, I came here once. Then I pressed control and left arrow and came here. Then I pressed arrow and came here.

And now I will press control and down arrow. See, I came to common size statement. It helps a lot in navigation. It is really helpful in the navigation. Now, function 1, we have seen how to work on time period.

Now we will understand few very basic, I know you will be over smart that we know everything but I am telling you again that many people get into this. Don't worry. What we have done is we have done costing analysis. For costing analysis, we have done, we have taken all the above income statements.

I will take them, leave them, then you will tell later. I made a table of costing analysis and what I did was that I copied the cost portion from here Ctrl C I went down and I need only values. So all these costing values are here.

I will expense this as SNG for now in short. Now I have to link from here. Hard code is not a mistake. First complete the first row of linking.

It will be quick and won't take much time. Once it has been accessed, it has come here. Now I went here and I have completely copy-pasted it.

And here I have brought total. Total is in total. I pressed alt and equal to, so the sum's automatic formula was applied.

And above this, I want a border, so control 1. This border in the border and above if you want above. Done. This is your beautiful saree. Now, Now I assign some weights to it.

I give 0.10 as its weight. And you can see that I have already done blue formatting. If it was black, I would have changed it to blue because this is a hardcoded number.

I give 0.2 to this. So 30% is done. I give 0.20 to this. So 50% is done. I give 0.3 to this and 0.20 to this.

And I have also done its total. So 1 is there. Ctrl C and I will paste the format here.

I will paste it completely. So the format will also be automatically pasted. Now, what I have to do is, First of all I have to calculate the average. The formula to calculate the average is very simple.

You must know it. You will put the formula of average here. There is no formula of mean, there is a formula of average.

And from where to where the average is. I want from here to here. This is the formula of average and I will press control V and paste it. If I want to apply the formula of weighted average then understand that there is no formula of weighted average for that. For that formula is applied as sum product.

When you apply the formula of sum product then it will say array 1. That means tell me from whom to multiply. So I want you to from this row Array 1, then multiply it with array 2. That is, multiply 0.1 with 8000, 0.2 with 2000. Keep doing this and tell me their sum. Do product as well and tell me the sum at the end. This is my weighted average formula.

I am committed to make you One thing to keep in mind By the end end of this course, if you watch all the sessions properly, do all the exercises properly, then you can make the whole financial model by yourself. Don't worry. I guarantee you this. But you have to give the commitment that yes, I can do this much. And I copied this.

One thing you should understand, and I will try to paste it but there will be a mistake. You hold it and tell me what is the mistake. I have not locked weights. So what happened is, here it took the correct error but here it multiplied both of them and told me the total product.

These mistakes should not happen. So I will press F4 once and I will paste this formula again. I hope this is absolutely clear.

Now we'll come to median. We'll go to median. The formula of median is very straightforward.

It should be around 2000. 2000-2500 It means that in all these numbers In these 5-6 numbers There are equal numbers above 2000 and below 2000 And in between 2000 If you want you can manually check This is 2000, there are 2 numbers smaller than 2000 Yes there are 2 numbers, there are 2 numbers bigger than 2000 And 2000 is in between So this is median Now, there is a formula of minimum, which is the smallest number. I have put the min formula and I went up. If I say min function, min formula, then it will tell me which is the smallest number in all the numbers.

That smallest number is the number of interest. Similarly, there is a formula of max. Equal to max and I have selected it.

So, it has told me the maximum. Now, one more formula is small and large. Which is the smallest number among all these?

We will know that from the min. But which is the second smallest number? Which is the third smallest number?

I am not telling you all these functions in the air. We will use them again and again. I am saying that only.

That's why I am devoted so much time on this. You need to understand boss. Okay. Now. So, for this, I have run a small function.

So, it is saying array. Which array do you want to find the number in? So, I want to find in this array.

And the K is, That K is asking for sequence from you. If you write 2 here, Then it will give you the second smallest number. I will write 1. See, it will be equal to min. 200 only came, right?

It will be equal to min. I want second smallest. So, ideally 800 should come.

800 came. So, should I hard code in this? Should I hard-code 2 in formula?

Not at all. I will put it here sir. Let's take it as third.

Let's put 3 3. And I will link it with this. Leave it independently. Now if I want to apply small formula, I did small.

Array means in which you have to find the small number. In this, you tell me the small number. Sir, in which sequence you want the small number?

So tell me the third smallest number. Either third smallest or whatever is written in this column. And I will, I will, absolute anchoring bhi karunga 2000 aagaya dekho third smallest 2000 hai smallest is 200 800 2000 The more dynamic you keep the model, the better it is for you. Large is also same number.

Tell me the largest number. In which array do you want to tell? So, tell the largest number in this array. Sir, in which sequence do you want?

So, whatever sequence is written in the box, and anchoring it, absolute anchoring. In that sequence, tell the third largest number. 2000 should come in it.

The smallest is 200. Third largest should come. 2000 should come. See, 2000 has come. Okay, there is no formatting, so copy it from above Go down, Ctrl-Alt-V, go to Format, from T It has done formatting, now if you type 2 here, it will tell 2nd largest If you type 3, it will tell you the third largest, if you type 2, it will tell you the smallest. I hope you understand this thing.

You have to practice this again and again. This sheet is for you. I have given you this sheet for your practice.

If you don't practice, you will take the knowledge home and forget it in 2 days. Now, I will come to a function. That is if statement You must have used it repeatedly But we will use it in a different way I will tell you how to use it So I want that If this particular year is full year So write full here Or write stub Stub means less than full year So I will put if condition here Equal to If This is basic if condition If This annual period if it is less than 1 This is my logical test So what value should come So you write here if it is less than 1 Means it is smaller than 1 and less than 1 year So in double comma stub That it is less than 1 Less than 1 is called stub not half year Because half year is 6 months If it is 8 months or 11 months then we will call it stub If it is not stub, then what to write?

Means if it is less than 1, then write stub, else write full here And double quote close and bracket also close Enter, stub came This is your basic function which you use if statement But suppose here I want If the total is less than 17500, then write it here. If the total expense is less than 17500, then write it here. If not, then don't write. So let's see this.

If this equal to F, bracket open. This is less than 17,500. Then put this. Else, put 0. It's coming.

Now, I'll keep it all. I'll keep it all. For you.

See. This above statement is small than 17000. Did he write something smaller than 17500? No, he didn't write.

It's absolutely correct. But, here a problem is happening. You have again done hard coding in formula.

You don't have to do hard coding in formula. You should write 17500 here. You do hard code it.

Do hard code it. You have to do it only once. I will link it.

I don't want to do it twice. I won't hard code one thing twice in the financial model. I am explaining it again and again. How important it will be, you think about it. I am saying the same thing 8 times in the entire session.

Now, I will put the ifs condition again. If my total expenditure is less than this cell, then put this. So, tell me the expenditure. Else, if this value, this logical test fails, then you write 0 for me.

The result is still the same, you have to understand. But now, the dynamism is more. How is the dynamism more? I have...

absolute anchoring how is dynamism more? dynamism is more that if I want to add 18000 then I will add 18000 if this is smaller than 9000 then nothing will come I have added 9000 but there is a problem problem is here is 17500 then it is fine but if I will add 18000 then function is working fine is this giving a problem? No problem, let's fix this also. Before this, I put equal to sign. If and this I brought here.

Ok. And one I put gap after this. And I linked this with cell. It's done.

Now what's happening? Now I will do 16000 here. So this function is also working with it and this function is also working with it. So it will be 16000. Similarly do the same for the lower one. See how to do it.

Remove 17500. Equal to. Bring the absolute text in double quotes. Leave a gap. Leave space and then close double quotes.

If you don't leave space then what will happen? See. You can see that you have not left any space in between. So, you leave a space in between. Sorry.

You close the double quote and C46. What is happening? You can see that leaving a space is very important if you want a proper format.

Are you getting my point? Are you understanding this? Let's run this statement once. 17,500. Sir, I have to put 17,500 in one place and the rest of the change is happening automatically.

This is simple to see. It is simple to execute. You understand. You are automating your financial model. Sir, I am applying VBI, Power Query, Power BI.

You don't have to install it because it is not installed in the industry also. The simple model is in your control. If you automate a lot of things in this, then you will become its slave.

If there is a little problem in the model, then the whole night gets spoiled. Believe me, I am working on a very small level. Gradually, we will go to a very advanced level. Now, total. Okay, what I have done with this?

Now, if I total expenditure is more than or equal to what to this then put my expenditure l0 zero again b again b So, this has to do absolute anchoring. This one is anchor, nothing will change in this. See, it is exactly equal. All these expenses are less than 17000 and all these expenses are either 17000 or more than 17000. Okay, 17500 or more than 17500. I will do total of these. I will total these.

Now, does error checking also happen with if? Absolutely. See how error checking happens.

Equal to if this is equal to this. If this is equal to the above one, if this is happening, then OK. Write it down. Otherwise, put an error. Do you understand?

This means that if you change anything here For example, if something is wrong Then it will post an error immediately If you want to go more advanced Then you can go Okay, one more function is equal to this equal to this. So it will say true, true or false. I will add any number here.

See, it has told error and it has told false. You practice this very well. Practice this very well. We will bring the next session very soon.

Don't worry. We will immediately. I am going to increase the frequency.

Either there will be an alternate day session or it can come in a very fast speed. Before the next session, I want you to download the sheet. Do it properly.

It is very important for you to watch, start watching the session as in when it comes. If you are watching two sessions in a week, then it is very good. If you are not watching it daily, then you will feel burden.

You have to learn this with interest. People charge a lot of money in the market. 20, 25, 30, 30. You can read the comments.

Go to the first session and see the comments. You will understand what I am saying. Why it is a gem.

Okay. Now, one more thing. If you want to get in touch with me, go on LinkedIn, search my name, Parth Verma.

Parth Verma. I will just write it over here. I'll be available on LinkedIn. You can connect with me. If you want to ask anything, you can ask me over there.

Meanwhile, if you come to the next session and you get to know, for that, you subscribe and press the notification bell icon. Till then, this is Parth Verma. Thank you so much. Bye-bye.

Transcript for:Excel Tips for Financial Modeling

Transcript for:
Excel Tips for Financial Modeling