Financial Modeling: Ratio Analysis Overview

Hi guys, my name is Parth and welcome to the Valuation School channel. Thank you, thank you so much for the overwhelming response. I am really, really happy that so many people are taking benefits, learning financial modeling and I could contribute a little value in your learning journey. I am really humbled and thank you so much for all your support till now and I am expecting that we will continue to get such support in the future. In the 5th session of financial modeling, we will learn to do ratio analysis where I will tell you to do ratios in a professional way. I will tell you how to do this analysis. that how you calculate the ratio, how to do trend analysis, how to calculate mean, median and what not so watch the whole video till the end, please don't watch it in 1.5x speed if you like the video then like, subscribe and irrespective whether you like the video or not please put down in the comment, if you like the video then praise me I will get the motivation to work further, I am absolutely humble if you don't like the video then please put down in the comment, I will get the motivation to work further, I am absolutely humble If you think I could do something better, please put down in the comment as well so that I can improve myself in the next videos. So let's start quickly. Yes, so let's start. Till last session, we took the example of Tata Motors. Let's continue that example. And I made the income statement with you and we made the balance sheet with you. We saw how you can copy the entire cash flow. As you can see, you will get the last session on the i button. After watching the last session, follow it. Don't watch it directly. Please. Now, cash flow statement. I have changed it a little bit. I have written all the activities here. And I will make my data sheet red. So that you won't touch it. It is red, so you don't have to mess with it. It is a simple logic. When I come down here, I will see that in cash flow, activities and in the fourth, net cash flow. So I have plotted it as it is and I will tell you the reason why I have moved from that cash flow to this cash flow. I have to simplify it. You understand, our objective right now is this, I have to first of all remove your fear of financial modeling. I want you to make sure that you do not afraid of financial modeling. You will learn valuation like this, it is not a big deal. DCF, in the next 2 or 3 sessions, we will be going to cover DCF. That is not a big challenge. The challenge is that Student gets scared. He got frightened that oh my god financial remodeling. Right, if you get rid of that fear, you will excel in your life. So let's stop learning from Babaji and we will start directly. So we have all the financials plotted, all the balance sheets are telly. What I will do is, I wanted to tell you some things. I will complete this in this one. First of all, gross profit. I will bold this, press CTRL 1, I will go in borders, I will go in automatic and wait a little, I will go in automatic I will pick this color, I will take this first one below the none, I have to put border on top and bottom, we have learnt all this I have put a border here, similarly I will first press F4 and place the borders I want a border at EBITDA I want a border at EBT Earnings before tax I want a border at net profit And that's it I want a border at total liabilities Total non current asset Total current asset And total asset It has become a little better And I will just bold the rest of my penal items. Sorry I am not in a good health but yeah no worries. I will try to edit that thing. Yep, okay perfect. Now come to ratio analysis. You will see that this sheet is made. It is not a big deal. You will say that I will make it again in a minute. I have to insert a new sheet. I have to click on OK. First row, select first row, Alt H O W, 1.15, Alt W V G, grid lines will disappear. I have to start from here, what I did directly, I went to financials, top, historicals, I copied this as it is. I found a new sheet, sheet 4, I pasted it here, I went to this column, in the first column, I wrote ratio analysis instead of historical financial statement. Okay, I will do the ratio analysis of the last 10 years. I don't want ratio analysis on LTM. So I have disappeared it. Now you learn here, remove it from here also This sheet is almost similar, now see I will make it a little bigger, it is exactly similar Now I have already plotted so that I don't want to waste your time I will delete this sheet, I hope you understood, if not then try to slow down First of all I have plotted all the ratios related to growth here Sales growth, gross margin growth Sales growth, EBITDA growth, EBIT growth, Net profit growth, Dividend growth After that I have plotted all the ratios of margins here Gross margin, Operating margin, EBITDA margin, EBIT margin, EBT margin, Net profit margin Then I have plotted the percentage of sales, this is very important That's why I have plotted here sales expense to the percentage of sales depreciation as a percent of sales and operating income as a percentage of sale which is again nothing but operating margin now then i have written returns and where you can check health of any company we will do a very important discussion about sustainable growth rate so you stay till last return on capital employed retained earnings return on equity self-sustained growth rate and interest coverage ratio these five ratios are such that you can understand the health of the company that how healthy the company is when it comes to financials. Then I have plotted some ratios of working capital here. Debtor turnover ratio, credit turnover, creditor turnover ratio, inventory turnover ratio, fixed asset turnover ratio, capital turnover ratio. And in the last I have calculated the days. Debtor day, payable day, inventory day, cash conversion cycle. We will learn about this. I will tell you what each meaning is as we go ahead. I just don't want to bore you that first stick the theory then that. No problem, we have to repeat it. Then I have plotted some cash flow scale ratios here. Let's start one by one. First of all, we will start with sales growth ratio. Now see, if you go to financial statement, then already we have plotted sales growth ratio here. But you can see that I have not put IF error here. Why am I saying IF error again and again? So that when you upload this sheet on screener and some company's data is not available, then it will not hit the hashtag NA atleast your financial model that is the worst thing you can do with your financial model if it is showing error I will put if error here and I will directly link it with this particular cell this particular cell here I will link it with D7 0 Please don't read this in the form of formatting First we complete the whole of MARS13 Then we will copy paste it If error Because I have already calculated the growth and EBITDA ratios Where I haven't done it, like I haven't done it here So we will do it here, no problem If error This divided by Means MARS14 divided by MARS13 Minus 1 Zero? Yup? No? Just a minute. Yes. You won't hear that thunk thunk sound. That's why I disabled that. Yes. Uh, after that my next... I have EBITDA growth after EBITDA growth I have EBIT growth I have taken percentage of sales but I have not taken growth so I will take growth If error this March 14 divided by March 13 minus 1 If there is an error then give 0 ok I have done it both are similar very similar Let me just check growth similar it's good net profit growth if error financials this divided by this march 14 divided by march 13 minus 1 comma if error comes then zero wow something wrong We've done something wrong. Let me just check. C33 by D33 by C33. D33 by C33. Just do it once more A fairer This divided by I didn't do minus 1 so it's coming like this You'll get it right on time Divided by this minus 1. Dividend growth. Did we ever calculate dividend growth? Did we calculate dividend? Yes, we did. Here is the dividend. But we haven't calculated the growth of dividend. Let's calculate it. If error. If someone is feeling bored, leave it. You can move forward. It's okay. This divided by this minus 1. Financial modeling is very boring. That's why it hurts when you pay for it This is coming in negative because if you just expand it more, you will see that both are not similar One is small and the other is big. Can you see? That's why it's giving a negative growth rate. Earlier it was 2.23 and now it's 2.22 But that's okay. My formula is correct. Let's go So I have completed the growth. Simple I will select it. Alt H P percentage. Alt H 0. 1 decimal. Alt H 0. 2 decimals. Perfect. Copy. How did I do it? I am here. See. I am in this cell. I pressed shift. I came down by pressing shift. Press Ctrl Shift and it will come down directly Press Shift and then downward arrow and it will be selected one by one Press Shift and then Ctrl and then downward arrow and it will be selected all at once Press Shift and then select right arrow First copy it, then press Shift and then select right arrow Press Ctrl B and press Ctrl B It is perfect, nothing has changed here Just check it, it's okay. I will keep it as it is, I won't do anything. In March 2021 EBIT growth, just check. It converted from positive to negative, that's why it's showing. It's okay. So it's degrowth. Now, Gross margins. Gross margins are already plotted, today it's totally plotted. I have gross margin, EBITDA margin, I have EBITDA margin, I have depreciation, if I remove it, EBIT comes out but I don't want that, it's okay, why do we call it operating profit? And I have my EBIT margins, I have my net margins, so I have net net all the margins and I will check once again that I have not used IFERROR formulas there, I have not used it in margins, so I will use it here. IFERROR Gross margin this is 0. My gross margin is here. Let's leave my operating margin for a minute. EBITDA margin. If error. Let's do the easy first. EBITDA margin is this. 0. EBIT margin. EBIT and operating are the same. So I'll remove this for a minute. I am leaving EBIT margin for a minute. I am coming to EBT margin first. If error, historical, EBT, zero, net profit margin, net profit margin, net profit margin, come quickly, here it is, perfect. So I have all the margins now I need to plot EBIT margin So its very simple, what we will do is We will use IFERROR We will do IFERROR, we will come here After coming here We will first select We will first convert EBITDA to EBIT So what I will do is I will Add EBITDA plus minus depreciation. So it will become earning before interest and tax. Now what is being made is EBIT. I will put one more bracket here. You guys do this together. It will be very beneficial. And directly I will divide it by sales. And I will close the bracket. Comma 0. Again close the bracket. Enter. Okay. Here. What he did is he did division first. Board mass. Basic board mass rule. Yes. Come on. And if you want to see if it is correct or not. Then it should be in decreasing order. All margins. Means gross margin will be highest. EBITDA margin will be lower than that. EBIT margin will be lower than that. EBT margin will be further lower. And net operating margin will be the lowest. Alt H. Alt H P Alt H 0 Alt H 0 Copy Paste Sales expense as a percentage of sales Is this out? Yes, I have this S&G expense as a percentage of sales Is depreciation as a percentage of sales? Yes, it is Operating income as a percentage of sales? Yes, it is So, I don't have operating income as a percentage of sales No, I don't have Operating income as a percentage of sale, I have to calculate that. First of all, if error, sorry, if error, SNG expenditure, 0, spelling error, yeah, it came here. Depreciation as a percentage of sales. Error is spelling error. Yup, it's down. Operating income as a percentage of sales. Which is nothing but your operating margin. So I will take it directly from here. 9%. Now you understand. Why I didn't put if error here? Because from where I took it, if error is on its source. That's why I don't need to apply 2 times if error. There is no logic in it. I will select both of them. Alt H P, Alt H 0, Alt H 0. I have selected all of them. Copy, paste. I hope you understood. It must have been clear to you. Now understand. Return on capital employed and return on equity. Return on capital employed means that for those people who have invested capital in the business, before giving them money, business has generated how much earning I will tell you the most simple thumb rule of this this is the thumb rule which is that sorry which is that you will start with sales you will first produce everything which is required in order to produce the good which is your cost of goods sold that how much cost it took to make the goods sold that is COGS then you will reduce if there are any additional unnecessary there are any necessary sales expenditure after that whom will you first pay even before paying tax interest will you pay borrowers will you pay it is a compulsory payment why it is a compulsory payment they will file this suit against your business they won't let you run the business and they are also someone who have deployed the capital in your business So this means that after that I will pay to the taxman If you don't pay to the taxman then tomorrow he will seal my business so I will have to pay to the taxman So sales I will explain you like this Sales minus COGS which is your gross profit Then you will distribute the amount to selling in general expenditure which is your EBITDA In EBITDA you will then reduce the interest and depreciation so depreciation if i reduce the depreciation from EBITDA what will come? EBIT is the earning which is available for the disposal of all the stakeholders everyone who has invested in my business everyone who has employed the capital, deployed the capital in my business which I employed and I have generated that income by employing that capital in the business so I have my borrowing and my equity now please understand I am not applying the if error for a minute, we will use the if error first of all in the top, I want this, do you know what I want? I will just show you This is my return on capital employed. This is my return on capital employed. The percentage will come. The percentage of return I have generated against the people who gave me capital. Is return on capital post tax? The definition written in front of you is pre-tax return on capital employed. This is pre-tax return on capital employed. Sir, why don't we see post tax return on capital employed? It's a foolish thing to do. Because interest is a tax saving expenditure. Right, why you are adjusting it? Okay, now I have to bring this formula and calculate here. What I will do is this minus depreciation. EBITDA minus depreciation. So what I am getting now is EBITDA. I will close the bracket. Divided by, again bracket open. I have opened bracket in denominator. Sum, again I have opened the bracket. Sum of what? Sum of equity share capital, reserves. Because reserves are not of the company, reserves are of equity shareholders. The reserves of the balance sheet, reserves in the balance sheet does not belong to the company. It does belong to the equity shareholder. What do you call equity share capital plus reserve? Equity shareholder fund, right? Shareholders fund. And I will add borrowings. Now all three of them have employed capital. Two people have employed capital in the business. Equity shareholder and borrowers. And I will close this. 18.6% is my return on capital. What I need to do is, I just need to apply a fairer formula. That's it. I hope this is perfectly fine. Now retained earnings. I have already mentioned retained earnings. What I will do is, this is the retained earnings. Now please understand, return on equity. Now you understand what to do. What is the amount of earnings for equity shareholders disposal? Those who have studied finance academically, those who have learnt the finance academically, They must have understand there was this thing called as EAES Earnings Available for Equity Shareholder What is said is that If your profit after tax is PAT If there is preference share from that Then minus their dividend Whoever will be left, that equity shareholder can take it to their home I hope this is very clear So what I have to do is I need to divide EAES Earnings Available from for equity shareholder divided by shareholder fund this will be my return on equity is return on equity pre-tax or post-tax think before telling me it will be post-tax right? any payment tax of equity shareholder comes under the benefit of the company company cannot take the benefit tax benefit of paying something to the owner right if this happens then what will be the sense of income tax? so what we will do is we will apply if error directly Now we will find out what is available on the disposal of equity shareholder. See, I have calculated EPS on net profit directly. Why did I do this? Because in Indian industries, in Indian companies, usually preference shares are not in such a trend. And we are very lucky that in Indian companies, classes of shares are not there. Now open Google's balance sheet and see Alphabet Incorporation. You will got to know that there were 16, 17, 30, 34 type of different categories of shares equity shares only right and they were the one who have you know invented this phenomena of giving different kind of differential shareholding so now what i'll do is i will use this net profit divided by sum of what sum of shareholder fund in dono ka sum perfect this close comma zero 25% return on equity there Before explaining the next one, I'll just do the formatting. Have you read this formula? Have you read this formula in academics? G is equals to B into R. Do you know what G is equals to B into R? The G in this is called self-sustained growth rate. Self-sustained growth rate means that business doesn't require funding from outside. The income that the business is getting, Business is regenerating that. So please tell me, can a business regenerate this income? Think about it, can a business return this profit? It is also called ploughing back of profits. It means that the profit is ploughed and put back. It is also called reinvestment. Can a business reinvest this profit? Not at all. If you reinvest this, how will you pay all these expenses? Can a business reinvest EBITDA? Not at all. It has to do with depreciation accounting and distribute interest. Can business do EBT? Absolutely not. Because taxman has to pay the money. Can business reinvest this amount? Absolutely can. Why? Don't you want to distribute the dividend? No. Is it compulsory to distribute the dividend? Absolutely not. It is at the discretion of the management, discretion of the board of directors. Can you think and tell me, in a country like India, or in any emerging market, giving a dividend is a very good signal for the company. Unless, understand, India has a growth opportunity, US doesn't have a growth opportunity. US's economy has been standing for 150-200 years. They have the sophisticated economy running over there. The market's data is available for more than 184 years. We have data of 40 years. We have data of 30-34 years. So in that case, in any emerging market for that matter, The reinvestment requirement is higher or lower. It is higher. Why? Because this is our time to invade the world. To grow. It's not the time of the US. Don't give logic to technology. But think about it. Whose growth is more? I mean, in today's time, the cars in the US, Chevrolet cars, will have more growth in 10 years. Or the growth of a company like Tata Motors will be more in 10 years. In India, the growth will be more. India is the largest market. We have officially bypassed. We have officially crossed China in terms of population very recently. So in Indian context or any emerging, don't remember this from India. In emerging market, it is highly highly recommended that if business has logic, if business return on capital employed is more than weighted average cost of capital, please think, see, understand, pay attention here. If business... Return on capital employed, return on capital is more than cost of capital. So what dividend should be distributed, the company should not distribute it. That shareholder is distributing dividends to make them happy, to manage the sentiment of the market, do you understand? If the company does not distribute dividends, then the screener puts it in cons, that they are repeatedly having profit but not distributing the dividend. That is the algorithm, we are not talking about that. But if my ROC is higher than VAC, my cost of capital, and we will understand what cost of capital is, then there is no point in distributing dividends. Absolutely no point. Zero. Zero point. No point. Why? Because my wax is more, my ROC is less. Okay. Then why are you reinvesting? You don't know how to run the shop. It's a simple logic. If you will input more money in the business, you will destroy the wealth of your shareholders. So it's better that the amount that is coming, 100% to 0% retain karovat ke paas. But agar mera ROC is higher than my VAC, my cost of capital, then it is prudent, it is a best business decision to keep as much as amount with the business and reinvest it in the business. So reinvestment is the major criteria. The retained earnings is the major criteria in order to calculate the self-sustained growth rate. Think about it once more. It's okay. I know it might take time and I may be repeating. You will understand. You move forward. But for those who didn't come, it is very important. Try to understand it once more. Self-sustained growth rate. Means I will earn money from myself and I will grow myself. When you see self-sustained growth rate in startup culture, then you get a word bootstrapped. Bootstrapped word. Like zeroda. Zerodha is a bootstrapped company. Self-sustained growth career. They are sustaining their own growth without using any outside capital. But sir, they are taking borrowing. I am not saying that. If they are taking borrowing, then also self-sustained growth rate can come out. I am not talking about that. So how is self-sustained growth rate calculated? Whatever I have retained, on that, how much profit am I generating for equity shareholders in the future? Why? Because you understand, the reinvestment, is it... Is it beneficial for borrowers or for equity shareholders? You gave Tata Motors a loan of Rs 1 crore. Today Tata Motors'market cap is Rs 100 crores. For example, you gave a loan of Rs 1 crore. It is a 10 year loan. Every year Tata returns Rs 10 lakhs to you as principal plus interest from 12%. Up. Tata is reinvesting the money that is left after giving interest to interest principal If it is reinvesting in business, then business is growing Sales are increasing They have added capex and expanded their capacity When sales are increasing, then definitely profit is also growing When profit is growing Sorry, when profit is growing, then market observes it closely And market is rewarding it Price is in the market Increase with a data of reinvestment Tata and your borrowing Was 1 CRE which is decreasing slowly Every year 10 lakhs Tata in next 5 years Reinvested and took Market cap from 100 crores to 400 crores 4X market cap so the reinvestment Benefit to equity shareholder Or to you who are borrowing No, what benefit did you get? Tata's market cap, even if it touches 1000 crores, you will still get the 10 lakh rupees back and the interest back. So, the benefit of sustainable growth rate or self-sustained growth rate is always given to the equity shareholder, not the borrower. So, B equal to R, G is equal to B into R is what? It is retained earning multiplied by Return on equity. I'll just calculate it. If error. If error, I'll tell you why. Aage retained earning is ka zero ho jayega. This into this and zero. So it is close to 23.96% 23.96% is the self sustained growth rate of self sustained growth rate of Tata Motors Now we will move forward There is one thing that you guys didn't tell me Just check Boss you have told me How did these growth figures come here? This is LTM figures Just check, I did it now Here growth can't come in the first year Margers can come Rest can come, but that can't come Interest Coverage Ratio Please understand It's a simple logic Go to historical Interest Sorry Before paying interest What do you have? You have EBITDA plus depreciation. Sorry, minus depreciation. So, your EBIT came. Earnings before interest and taxation. I will divide this directly with interest. So, this is 4.77, my interest coverage ratio came. Interest coverage ratio means that in a year, the company Please listen carefully. The company is earning so much in one year that it can pay the interest 4.77 years and 4.47 times. The yearly interest can be paid for 4.77 years. Definitely subject to all other things being equal. So if it was 47.7, it would be more comfortable for me. If it was 1, I don't know what 1 would mean. It's going neck to neck. Company is in loss for sure because EBIT is 1 and company has to pay interest 1. So after that nothing is left for the lower ones. For sure, right? So if any company's interest coverage ratio is as high as mine, then the benefit is as high. We will calculate this in x, we will calculate it in times. So for that, the easiest way is, you can do one thing here, okay? Now watch, please understand. I did this, I did this, so I closed this bracket, I added and, and I added x. This is such a foolish way of doing it. Now when you are doing this, you cannot operate, no mathematics is operating on this. See, you cannot reduce the decimal, you cannot increase the decimal. Don't do this. What we will do is, we will select it here, we will press Ctrl 1, we will go in numbers, we will come in customs. I will come in customs and here I will type that I want it in double digits and Double quote X double quote close or okay 4.77 X. I hope this is very much clear to you guys This comment copy got them and I will paste it till here. I'll be there for you. Okay problem. Oh Jati Yeah, I got a feather. Ka formula. Naila Gaia. I thought I've been to 0 to 0 car. K 0 car data Yeah, but has the divide Gabi car. Not bad. Go on each is zero again So it will give you a hashtag div zero error. So we don't want any kind of error. See here the interest coverage ratio of the company went negative. The company is still surviving neck to neck. Just see and check. How much interest is going? 9300 is going. Depreciation the company has claimed more than necessary this year. See it is going neck to neck at this time. It is very poor in terms of interest coverage ratio. Is this mean that I am giving sell call on Tata motors? Not at all. Don't even consider that. Don't even think about it. Datter turnover ratio. Datter turnover ratio means that how much sales is generating for a data of 1 rupee. One data is generating how much sales for me. That is my data turnover ratio. So what I will do is What I will do is I will go over here. I will use the sales divided by data receivable. You can see 17.22 is coming. Should I paste this format below? Yes, you can. So my sales is 17.22 times of my total data that is what it signifies. For now understand this much. 17.23. Credit or turnover ratio. Same thing. Now I don't have credit sales. If there was credit sales then I would have taken credit sales. But I am going a little rough here and I will take the complete sales. Sales divided by other liabilities. I am saying again that I am going rough here. I have here Payable figure is not directly You understood one thing I will tell you When you will be working in investment bank When you will be working in valuations In future And when you will work in valuation Or in investment banking So many such opportunities will come When you will not have proper data availability When you will not have proper data availability So you don't have to stop at that time Use the proxy data and move on We will see in the future If it doesn't make sense, then remove it from here. There is no problem. And I knew it that it won't make sense. Despite that, I have kept it here just to make this thing aware to you. That you should not stop just because the data is not available. You need to find something or other. Or okay, let's assume that Tata Motors is not getting operating margin. It is difficult to find it. No problem. For Maruti, put it forward. Sir, there is no difference between Maruti and Tata. I don't know. There is a lot of difference between both. But now you have to move forward. When you get it later, change it. why you are stopping yourself right from moving ahead so don't do that inventory sorry yeah now my next is inventory turnover ratio inventory turnover ratio same thing wherever turnover is coming put sales sales if i put it in error sorry i have all the formulas sales divided by but it's not recommended for you You will start taking things very casually. Control page page down there keep a job character my drive sales divided by Oh Wow inventories Yeah, fix the turnover ratio similarly your sales Results capital work in progress. No, sir fix assets zero capital turnover ratio capital pay equity capital You bought or you keep the efficiently say generate car para business How much business does a sale generate on equity capital of 1 rupee? I will do sum of equity shareholder fund. I will copy this the format. I will copy the format. It will increase a little bit speed. As we will move forward, means we will move forward in the next sessions. Because it is expected from you that you are watching it after doing all the sessions. Right. Now. Please understand. Let's go. Dater days. Now how to calculate the data days? You need to understand. Meaning, how many days of data are available to me? For this, the easiest way to tell you what is it? If there is an issue in terminology, then please google it. If you don't do it, then no one will come to explain. You need to understand this. We are sitting very distantly and educating each other. 365 divided by my debtor turnover ratio Now what is coming out is 21 days This means on an average in 21 days the debtor pays me On an average in 21 days the debtor is paying to me Okay now Payable days if error, 365 divided by creditor turnover ratio. I pay creditor in 148 days. That's great. Dater is paying me in 21 days, I pay creditor in 148 days. Now let's see the position of inventory. 365 divided by inventory turnover ratio. Zero. I have 40 days of inventory. I mean, I have as much inventory as I have in my stock, in my company. And if the company stops production today, then it will sustain the next 40 days of sales. Okay. These are my days. 21 day 148 days 40 days cash conversion cycle means company is rotating cash once in how much time conversion means what company is doing sum of data days I got money divided by inventory day sum of these two minus Payable days. That is my cash conversion cycle. 86 days ka shortfall padega mere pas. 86 days ka mere pa shortfall padega. Is case ke andar. Cash conversion cycle ke andar. 86-87 days ka mere pa shortfall padega. Aap samajhna. Aap merku isko days me convert karna hai. Matlab days me hai merku saath me day likhna hai iske. Toh bahut easy hai toh koi bachcho ka khel hai. Aapnik toh bahut smart ho yaar abhi. Control 1 karenge. Custom me jaenge. Go to custom and select 0 I want days in decimal, what will I do by taking days in decimal Double quote D A Y A S Sir, days is stuck. Ctrl 1, double quote, space days. Now it's here. Sir, I want the d of days to be smaller. Ctrl 1, d of days to be smaller. Copy. Sir, I don't want days. For example, you know you don't want days. If you don't want it, then write it here in days. Sir, but you know that data days are in days. You know that the person who is giving you money doesn't know anything about making a financial model. He is a fool, that's why he is giving you money. He would have made it himself. Like you are smart, you are not giving money to make financial model. Okay, perfect. Now, CFO by sales cash flow from operations by sales historical cash flow from operations CFO divided by sales sales is at the top 0 this will go in times it can go in percentage also it can go in times whatever you want If it is small then take it in times only CFO by total asset Company's CFO This is the company's CFO Divided by total asset Total asset is this Over here 0 will earn a lot CFO divided by borrowings. You will get borrowing. Here it is. Zero. I will alt-hp, alt-h0, alt-h0. My work is now perfectly completed. I will have to do a little formatting. I will have to do this again and again. I can't do anything. You have to take money from the front to make a model. Press F4 F4. I made it once. Now I am just pressing F4 F4. Remove it. It won't work. I will write one more thing in days. Okay. So we have completed the ratio analysis here. It is not complete yet with the ratios plotted. So what we will do is, I will do one thing, I will, you see this, where the ratio is ending, the column after that, I am making it a little broad. Please understand this clearly. I am selecting this cell. I am going to insert. After going to insert, do you see this spark line here? Spark lines. In sparklines you have to go to line If you go to line, it is asking you what is the range of your data What is the range of your data So you have to select from here to here Then it will ask you if the location range is M5 which you have selected So yes it is M5 And you have to press ok As you can see as soon as you press ok, what it has done It has made a trend line chart of this Now you have to win this I think this is the sweet spot app. You don't have to do anything, just select it and drag it down to here. So it has made a complete line chart. It's absolutely correct. It's running from 0% to 100% and 0% to 100% This is the same line chart. Okay. Now, I will go here. Because here I have 10 years of data and here I have 9 years of data. So it won't be justified at all. Insert, Insert, Sparklines, Line, Data Range, these 10 years, M11. I will drag it. So you are continuously seeing a fall in margins. Do you see a continuous fall in margins? Think about it yourself. It was 13% then it became 14%, gave a slight rise and after that it is falling continuously. 14.97, 14.91, 10, 18, 8, 6 and what now? It has covered the journey of 6 to 8.8. I hope you are understanding how to plot this. It is said that it goes to sparklines but it is never... I don't use it like this that I have written spark lines here Nobody does that right So you should not be doing it Now please understand You guys forgot to do one thing Please pay attention Okay now what I will do is I will just copy this particular cell over here for the purpose of formatting and what i want is mean okay this is not a normal average i am explaining you this will not be a normal average okay first is mean then median i will copy the median as it is Now we will use the formula of average. First we will use if error In if error, we will use average If average is this, it's ok Else give 0, HP 0 You have taken out the mean Now you go down Paste it below but you have to cover the data a little bit We have our mean in place We will calculate the median now. So in median, Actually I wanted to put trim mean but it won't work because data set is very small. Those who don't know trim mean, search trim mean on google and you will understand. I can take it till here in median. There is problem in average but there is no problem in median. Even if there is a blank cell in median, it won't distort my result. If error. 0 HP 0 0 will be like a smooth work butter smooth take formatting of days I will change the format to days I will change the format to days I will change the format to days Now Now I will extend the borders till the end Ctrl 1 border Color will be our dark navy blue color This border tap tap done Now we just need to press F4 Go down I will select this press F4 I will select this I will press F4 I will select this I will press F4 My calculation of mean and median is very good If you want to extend it If you want to write it to make it in length You can call this as trend And if you do alt H m c with this Then the trend will be extended I will take it in the middle If you want to change any color in the spark line, then as soon as you click on the box, you will see the whole box. Click on the box and select the spark line. The spark line box will be activated. And you can put a marker over there or you can put markers. You can see the markers. He has marked every point and you can change the color of marker and make it a little sophisticated This color is not looking good honestly Let's check the light color, it still looks good Perfect Let's check the black, I believe it is better than red Marker color, markers, yup So I can do this, if you want to change the line change the blue line so you can change the line of spark from here like you can take light blue to make it look more appealing like this our ratio analysis is complete in the next session we will touch upon common size statement and peer analysis we will take both in different sessions thank you so much for watching this very soon we will have another session don't worry now the speed will be fast a lot of people are you know I have raised my expectations. You people have raised my responsibility towards you guys. So definitely I will be more responsible because of you guys only. So thank you so much. If you liked it, please give me feedback in the comments. If you have any feedback, positive, negative, whatever it is, it's absolutely free. You are absolutely free to give the feedback. And please like this video. Like is very important. You like very little. From the people who watch, only 1-2 people. 2% people are liking the video. So please do that. And subscribe to our channel. And share it with your friends. If you find it really useful. This sheet. Solution sheet. As well as problem sheet. Means. The sheet which I have made. Plus solution sheet. Both the sheets. You can find them in the description below. Our telegram group link is in the description. You can join it. I am going to take a live session. Very soon. I take a mentoring session every month. If you want to interact. with me in a live manner on a zoom call please do that uh that's it so this is parth varma thank you so much okay one minute if you are very confused that sir i don't understand which area i should make a career in finance what should i do whether i should go into equity research or i should go into you know valuation i should go into investment banking what should i do so i would highly recommend you to watch this particular this particular video sorry this here here here yes here only it will happen see the video on finance career road understand there okay thank you so much and we'll see you soon this is Parth Verma Babai signing off thank you

I am really humbled and thank you so much for all your support till now and I am expecting that we will continue to get such support in the future. In the 5th session of financial modeling, we will learn to do ratio analysis where I will tell you to do ratios in a professional way. I will tell you how to do this analysis. that how you calculate the ratio, how to do trend analysis, how to calculate mean, median and what not so watch the whole video till the end, please don't watch it in 1.5x speed if you like the video then like, subscribe and irrespective whether you like the video or not please put down in the comment, if you like the video then praise me I will get the motivation to work further, I am absolutely humble if you don't like the video then please put down in the comment, I will get the motivation to work further, I am absolutely humble If you think I could do something better, please put down in the comment as well so that I can improve myself in the next videos.

So let's start quickly. Yes, so let's start. Till last session, we took the example of Tata Motors. Let's continue that example.

And I made the income statement with you and we made the balance sheet with you. We saw how you can copy the entire cash flow. As you can see, you will get the last session on the i button. After watching the last session, follow it.

Don't watch it directly. Please. Now, cash flow statement.

I have changed it a little bit. I have written all the activities here. And I will make my data sheet red.

So that you won't touch it. It is red, so you don't have to mess with it. It is a simple logic.

When I come down here, I will see that in cash flow, activities and in the fourth, net cash flow. So I have plotted it as it is and I will tell you the reason why I have moved from that cash flow to this cash flow. I have to simplify it.

You understand, our objective right now is this, I have to first of all remove your fear of financial modeling. I want you to make sure that you do not afraid of financial modeling. You will learn valuation like this, it is not a big deal. DCF, in the next 2 or 3 sessions, we will be going to cover DCF. That is not a big challenge.

The challenge is that Student gets scared. He got frightened that oh my god financial remodeling. Right, if you get rid of that fear, you will excel in your life. So let's stop learning from Babaji and we will start directly.

So we have all the financials plotted, all the balance sheets are telly. What I will do is, I wanted to tell you some things. I will complete this in this one. First of all, gross profit. I will bold this, press CTRL 1, I will go in borders, I will go in automatic and wait a little, I will go in automatic I will pick this color, I will take this first one below the none, I have to put border on top and bottom, we have learnt all this I have put a border here, similarly I will first press F4 and place the borders I want a border at EBITDA I want a border at EBT Earnings before tax I want a border at net profit And that's it I want a border at total liabilities Total non current asset Total current asset And total asset It has become a little better And I will just bold the rest of my penal items.

Sorry I am not in a good health but yeah no worries. I will try to edit that thing. Yep, okay perfect. Now come to ratio analysis. You will see that this sheet is made.

It is not a big deal. You will say that I will make it again in a minute. I have to insert a new sheet. I have to click on OK. First row, select first row, Alt H O W, 1.15, Alt W V G, grid lines will disappear.

I have to start from here, what I did directly, I went to financials, top, historicals, I copied this as it is. I found a new sheet, sheet 4, I pasted it here, I went to this column, in the first column, I wrote ratio analysis instead of historical financial statement. Okay, I will do the ratio analysis of the last 10 years.

I don't want ratio analysis on LTM. So I have disappeared it. Now you learn here, remove it from here also This sheet is almost similar, now see I will make it a little bigger, it is exactly similar Now I have already plotted so that I don't want to waste your time I will delete this sheet, I hope you understood, if not then try to slow down First of all I have plotted all the ratios related to growth here Sales growth, gross margin growth Sales growth, EBITDA growth, EBIT growth, Net profit growth, Dividend growth After that I have plotted all the ratios of margins here Gross margin, Operating margin, EBITDA margin, EBIT margin, EBT margin, Net profit margin Then I have plotted the percentage of sales, this is very important That's why I have plotted here sales expense to the percentage of sales depreciation as a percent of sales and operating income as a percentage of sale which is again nothing but operating margin now then i have written returns and where you can check health of any company we will do a very important discussion about sustainable growth rate so you stay till last return on capital employed retained earnings return on equity self-sustained growth rate and interest coverage ratio these five ratios are such that you can understand the health of the company that how healthy the company is when it comes to financials.

Then I have plotted some ratios of working capital here. Debtor turnover ratio, credit turnover, creditor turnover ratio, inventory turnover ratio, fixed asset turnover ratio, capital turnover ratio. And in the last I have calculated the days. Debtor day, payable day, inventory day, cash conversion cycle. We will learn about this.

I will tell you what each meaning is as we go ahead. I just don't want to bore you that first stick the theory then that. No problem, we have to repeat it.

Then I have plotted some cash flow scale ratios here. Let's start one by one. First of all, we will start with sales growth ratio.

Now see, if you go to financial statement, then already we have plotted sales growth ratio here. But you can see that I have not put IF error here. Why am I saying IF error again and again? So that when you upload this sheet on screener and some company's data is not available, then it will not hit the hashtag NA atleast your financial model that is the worst thing you can do with your financial model if it is showing error I will put if error here and I will directly link it with this particular cell this particular cell here I will link it with D7 0 Please don't read this in the form of formatting First we complete the whole of MARS13 Then we will copy paste it If error Because I have already calculated the growth and EBITDA ratios Where I haven't done it, like I haven't done it here So we will do it here, no problem If error This divided by Means MARS14 divided by MARS13 Minus 1 Zero? Yup?

No? Just a minute. Yes. You won't hear that thunk thunk sound.

That's why I disabled that. Yes. Uh, after that my next...

I have EBITDA growth after EBITDA growth I have EBIT growth I have taken percentage of sales but I have not taken growth so I will take growth If error this March 14 divided by March 13 minus 1 If there is an error then give 0 ok I have done it both are similar very similar Let me just check growth similar it's good net profit growth if error financials this divided by this march 14 divided by march 13 minus 1 comma if error comes then zero wow something wrong We've done something wrong. Let me just check. C33 by D33 by C33.

D33 by C33. Just do it once more A fairer This divided by I didn't do minus 1 so it's coming like this You'll get it right on time Divided by this minus 1. Dividend growth. Did we ever calculate dividend growth? Did we calculate dividend? Yes, we did.

Here is the dividend. But we haven't calculated the growth of dividend. Let's calculate it. If error.

If someone is feeling bored, leave it. You can move forward. It's okay. This divided by this minus 1. Financial modeling is very boring. That's why it hurts when you pay for it This is coming in negative because if you just expand it more, you will see that both are not similar One is small and the other is big.

Can you see? That's why it's giving a negative growth rate. Earlier it was 2.23 and now it's 2.22 But that's okay.

My formula is correct. Let's go So I have completed the growth. Simple I will select it.

Alt H P percentage. Alt H 0. 1 decimal. Alt H 0. 2 decimals.

Perfect. Copy. How did I do it?

I am here. See. I am in this cell. I pressed shift.

I came down by pressing shift. Press Ctrl Shift and it will come down directly Press Shift and then downward arrow and it will be selected one by one Press Shift and then Ctrl and then downward arrow and it will be selected all at once Press Shift and then select right arrow First copy it, then press Shift and then select right arrow Press Ctrl B and press Ctrl B It is perfect, nothing has changed here Just check it, it's okay. I will keep it as it is, I won't do anything.

In March 2021 EBIT growth, just check. It converted from positive to negative, that's why it's showing. It's okay.

So it's degrowth. Now, Gross margins. Gross margins are already plotted, today it's totally plotted.

I have gross margin, EBITDA margin, I have EBITDA margin, I have depreciation, if I remove it, EBIT comes out but I don't want that, it's okay, why do we call it operating profit? And I have my EBIT margins, I have my net margins, so I have net net all the margins and I will check once again that I have not used IFERROR formulas there, I have not used it in margins, so I will use it here. IFERROR Gross margin this is 0. My gross margin is here.

Let's leave my operating margin for a minute. EBITDA margin. If error.

Let's do the easy first. EBITDA margin is this. 0. EBIT margin. EBIT and operating are the same.

So I'll remove this for a minute. I am leaving EBIT margin for a minute. I am coming to EBT margin first. If error, historical, EBT, zero, net profit margin, net profit margin, net profit margin, come quickly, here it is, perfect.

So I have all the margins now I need to plot EBIT margin So its very simple, what we will do is We will use IFERROR We will do IFERROR, we will come here After coming here We will first select We will first convert EBITDA to EBIT So what I will do is I will Add EBITDA plus minus depreciation. So it will become earning before interest and tax. Now what is being made is EBIT. I will put one more bracket here. You guys do this together.

It will be very beneficial. And directly I will divide it by sales. And I will close the bracket.

Comma 0. Again close the bracket. Enter. Okay.

Here. What he did is he did division first. Board mass.

Basic board mass rule. Yes. Come on. And if you want to see if it is correct or not.

Then it should be in decreasing order. All margins. Means gross margin will be highest.

EBITDA margin will be lower than that. EBIT margin will be lower than that. EBT margin will be further lower. And net operating margin will be the lowest. Alt H. Alt H P Alt H 0 Alt H 0 Copy Paste Sales expense as a percentage of sales Is this out?

Yes, I have this S&G expense as a percentage of sales Is depreciation as a percentage of sales? Yes, it is Operating income as a percentage of sales? Yes, it is So, I don't have operating income as a percentage of sales No, I don't have Operating income as a percentage of sale, I have to calculate that. First of all, if error, sorry, if error, SNG expenditure, 0, spelling error, yeah, it came here.

Depreciation as a percentage of sales. Error is spelling error. Yup, it's down. Operating income as a percentage of sales.

Which is nothing but your operating margin. So I will take it directly from here. 9%.

Now you understand. Why I didn't put if error here? Because from where I took it, if error is on its source. That's why I don't need to apply 2 times if error. There is no logic in it.

I will select both of them. Alt H P, Alt H 0, Alt H 0. I have selected all of them. Copy, paste. I hope you understood. It must have been clear to you.

Now understand. Return on capital employed and return on equity. Return on capital employed means that for those people who have invested capital in the business, before giving them money, business has generated how much earning I will tell you the most simple thumb rule of this this is the thumb rule which is that sorry which is that you will start with sales you will first produce everything which is required in order to produce the good which is your cost of goods sold that how much cost it took to make the goods sold that is COGS then you will reduce if there are any additional unnecessary there are any necessary sales expenditure after that whom will you first pay even before paying tax interest will you pay borrowers will you pay it is a compulsory payment why it is a compulsory payment they will file this suit against your business they won't let you run the business and they are also someone who have deployed the capital in your business So this means that after that I will pay to the taxman If you don't pay to the taxman then tomorrow he will seal my business so I will have to pay to the taxman So sales I will explain you like this Sales minus COGS which is your gross profit Then you will distribute the amount to selling in general expenditure which is your EBITDA In EBITDA you will then reduce the interest and depreciation so depreciation if i reduce the depreciation from EBITDA what will come?

EBIT is the earning which is available for the disposal of all the stakeholders everyone who has invested in my business everyone who has employed the capital, deployed the capital in my business which I employed and I have generated that income by employing that capital in the business so I have my borrowing and my equity now please understand I am not applying the if error for a minute, we will use the if error first of all in the top, I want this, do you know what I want? I will just show you This is my return on capital employed. This is my return on capital employed.

The percentage will come. The percentage of return I have generated against the people who gave me capital. Is return on capital post tax? The definition written in front of you is pre-tax return on capital employed. This is pre-tax return on capital employed.

Sir, why don't we see post tax return on capital employed? It's a foolish thing to do. Because interest is a tax saving expenditure.

Right, why you are adjusting it? Okay, now I have to bring this formula and calculate here. What I will do is this minus depreciation. EBITDA minus depreciation.

So what I am getting now is EBITDA. I will close the bracket. Divided by, again bracket open.

I have opened bracket in denominator. Sum, again I have opened the bracket. Sum of what?

Sum of equity share capital, reserves. Because reserves are not of the company, reserves are of equity shareholders. The reserves of the balance sheet, reserves in the balance sheet does not belong to the company. It does belong to the equity shareholder. What do you call equity share capital plus reserve?

Equity shareholder fund, right? Shareholders fund. And I will add borrowings. Now all three of them have employed capital.

Two people have employed capital in the business. Equity shareholder and borrowers. And I will close this.

18.6% is my return on capital. What I need to do is, I just need to apply a fairer formula. That's it.

I hope this is perfectly fine. Now retained earnings. I have already mentioned retained earnings. What I will do is, this is the retained earnings. Now please understand, return on equity.

Now you understand what to do. What is the amount of earnings for equity shareholders disposal? Those who have studied finance academically, those who have learnt the finance academically, They must have understand there was this thing called as EAES Earnings Available for Equity Shareholder What is said is that If your profit after tax is PAT If there is preference share from that Then minus their dividend Whoever will be left, that equity shareholder can take it to their home I hope this is very clear So what I have to do is I need to divide EAES Earnings Available from for equity shareholder divided by shareholder fund this will be my return on equity is return on equity pre-tax or post-tax think before telling me it will be post-tax right? any payment tax of equity shareholder comes under the benefit of the company company cannot take the benefit tax benefit of paying something to the owner right if this happens then what will be the sense of income tax? so what we will do is we will apply if error directly Now we will find out what is available on the disposal of equity shareholder.

See, I have calculated EPS on net profit directly. Why did I do this? Because in Indian industries, in Indian companies, usually preference shares are not in such a trend.

And we are very lucky that in Indian companies, classes of shares are not there. Now open Google's balance sheet and see Alphabet Incorporation. You will got to know that there were 16, 17, 30, 34 type of different categories of shares equity shares only right and they were the one who have you know invented this phenomena of giving different kind of differential shareholding so now what i'll do is i will use this net profit divided by sum of what sum of shareholder fund in dono ka sum perfect this close comma zero 25% return on equity there Before explaining the next one, I'll just do the formatting.

Have you read this formula? Have you read this formula in academics? G is equals to B into R.

Do you know what G is equals to B into R? The G in this is called self-sustained growth rate. Self-sustained growth rate means that business doesn't require funding from outside.

The income that the business is getting, Business is regenerating that. So please tell me, can a business regenerate this income? Think about it, can a business return this profit? It is also called ploughing back of profits.

It means that the profit is ploughed and put back. It is also called reinvestment. Can a business reinvest this profit? Not at all. If you reinvest this, how will you pay all these expenses?

Can a business reinvest EBITDA? Not at all. It has to do with depreciation accounting and distribute interest. Can business do EBT? Absolutely not.

Because taxman has to pay the money. Can business reinvest this amount? Absolutely can.

Why? Don't you want to distribute the dividend? No. Is it compulsory to distribute the dividend? Absolutely not.

It is at the discretion of the management, discretion of the board of directors. Can you think and tell me, in a country like India, or in any emerging market, giving a dividend is a very good signal for the company. Unless, understand, India has a growth opportunity, US doesn't have a growth opportunity. US's economy has been standing for 150-200 years. They have the sophisticated economy running over there.

The market's data is available for more than 184 years. We have data of 40 years. We have data of 30-34 years. So in that case, in any emerging market for that matter, The reinvestment requirement is higher or lower.

It is higher. Why? Because this is our time to invade the world. To grow. It's not the time of the US.

Don't give logic to technology. But think about it. Whose growth is more? I mean, in today's time, the cars in the US, Chevrolet cars, will have more growth in 10 years. Or the growth of a company like Tata Motors will be more in 10 years.

In India, the growth will be more. India is the largest market. We have officially bypassed. We have officially crossed China in terms of population very recently.

So in Indian context or any emerging, don't remember this from India. In emerging market, it is highly highly recommended that if business has logic, if business return on capital employed is more than weighted average cost of capital, please think, see, understand, pay attention here. If business...

Return on capital employed, return on capital is more than cost of capital. So what dividend should be distributed, the company should not distribute it. That shareholder is distributing dividends to make them happy, to manage the sentiment of the market, do you understand? If the company does not distribute dividends, then the screener puts it in cons, that they are repeatedly having profit but not distributing the dividend.

That is the algorithm, we are not talking about that. But if my ROC is higher than VAC, my cost of capital, and we will understand what cost of capital is, then there is no point in distributing dividends. Absolutely no point.

Zero. Zero point. No point.

Why? Because my wax is more, my ROC is less. Okay.

Then why are you reinvesting? You don't know how to run the shop. It's a simple logic.

If you will input more money in the business, you will destroy the wealth of your shareholders. So it's better that the amount that is coming, 100% to 0% retain karovat ke paas. But agar mera ROC is higher than my VAC, my cost of capital, then it is prudent, it is a best business decision to keep as much as amount with the business and reinvest it in the business.

So reinvestment is the major criteria. The retained earnings is the major criteria in order to calculate the self-sustained growth rate. Think about it once more. It's okay.

I know it might take time and I may be repeating. You will understand. You move forward. But for those who didn't come, it is very important. Try to understand it once more.

Self-sustained growth rate. Means I will earn money from myself and I will grow myself. When you see self-sustained growth rate in startup culture, then you get a word bootstrapped.

Bootstrapped word. Like zeroda. Zerodha is a bootstrapped company.

Self-sustained growth career. They are sustaining their own growth without using any outside capital. But sir, they are taking borrowing. I am not saying that.

If they are taking borrowing, then also self-sustained growth rate can come out. I am not talking about that. So how is self-sustained growth rate calculated? Whatever I have retained, on that, how much profit am I generating for equity shareholders in the future?

Why? Because you understand, the reinvestment, is it... Is it beneficial for borrowers or for equity shareholders?

You gave Tata Motors a loan of Rs 1 crore. Today Tata Motors'market cap is Rs 100 crores. For example, you gave a loan of Rs 1 crore.

It is a 10 year loan. Every year Tata returns Rs 10 lakhs to you as principal plus interest from 12%. Up. Tata is reinvesting the money that is left after giving interest to interest principal If it is reinvesting in business, then business is growing Sales are increasing They have added capex and expanded their capacity When sales are increasing, then definitely profit is also growing When profit is growing Sorry, when profit is growing, then market observes it closely And market is rewarding it Price is in the market Increase with a data of reinvestment Tata and your borrowing Was 1 CRE which is decreasing slowly Every year 10 lakhs Tata in next 5 years Reinvested and took Market cap from 100 crores to 400 crores 4X market cap so the reinvestment Benefit to equity shareholder Or to you who are borrowing No, what benefit did you get? Tata's market cap, even if it touches 1000 crores, you will still get the 10 lakh rupees back and the interest back.

So, the benefit of sustainable growth rate or self-sustained growth rate is always given to the equity shareholder, not the borrower. So, B equal to R, G is equal to B into R is what? It is retained earning multiplied by Return on equity. I'll just calculate it. If error.

If error, I'll tell you why. Aage retained earning is ka zero ho jayega. This into this and zero.

So it is close to 23.96% 23.96% is the self sustained growth rate of self sustained growth rate of Tata Motors Now we will move forward There is one thing that you guys didn't tell me Just check Boss you have told me How did these growth figures come here? This is LTM figures Just check, I did it now Here growth can't come in the first year Margers can come Rest can come, but that can't come Interest Coverage Ratio Please understand It's a simple logic Go to historical Interest Sorry Before paying interest What do you have? You have EBITDA plus depreciation. Sorry, minus depreciation.

So, your EBIT came. Earnings before interest and taxation. I will divide this directly with interest. So, this is 4.77, my interest coverage ratio came. Interest coverage ratio means that in a year, the company Please listen carefully.

The company is earning so much in one year that it can pay the interest 4.77 years and 4.47 times. The yearly interest can be paid for 4.77 years. Definitely subject to all other things being equal. So if it was 47.7, it would be more comfortable for me.

If it was 1, I don't know what 1 would mean. It's going neck to neck. Company is in loss for sure because EBIT is 1 and company has to pay interest 1. So after that nothing is left for the lower ones.

For sure, right? So if any company's interest coverage ratio is as high as mine, then the benefit is as high. We will calculate this in x, we will calculate it in times. So for that, the easiest way is, you can do one thing here, okay? Now watch, please understand.

I did this, I did this, so I closed this bracket, I added and, and I added x. This is such a foolish way of doing it. Now when you are doing this, you cannot operate, no mathematics is operating on this.

See, you cannot reduce the decimal, you cannot increase the decimal. Don't do this. What we will do is, we will select it here, we will press Ctrl 1, we will go in numbers, we will come in customs.

I will come in customs and here I will type that I want it in double digits and Double quote X double quote close or okay 4.77 X. I hope this is very much clear to you guys This comment copy got them and I will paste it till here. I'll be there for you. Okay problem. Oh Jati Yeah, I got a feather.

Ka formula. Naila Gaia. I thought I've been to 0 to 0 car. K 0 car data Yeah, but has the divide Gabi car. Not bad.

Go on each is zero again So it will give you a hashtag div zero error. So we don't want any kind of error. See here the interest coverage ratio of the company went negative.

The company is still surviving neck to neck. Just see and check. How much interest is going?

9300 is going. Depreciation the company has claimed more than necessary this year. See it is going neck to neck at this time.

It is very poor in terms of interest coverage ratio. Is this mean that I am giving sell call on Tata motors? Not at all. Don't even consider that.

Don't even think about it. Datter turnover ratio. Datter turnover ratio means that how much sales is generating for a data of 1 rupee.

One data is generating how much sales for me. That is my data turnover ratio. So what I will do is What I will do is I will go over here.

I will use the sales divided by data receivable. You can see 17.22 is coming. Should I paste this format below? Yes, you can.

So my sales is 17.22 times of my total data that is what it signifies. For now understand this much. 17.23. Credit or turnover ratio.

Same thing. Now I don't have credit sales. If there was credit sales then I would have taken credit sales.

But I am going a little rough here and I will take the complete sales. Sales divided by other liabilities. I am saying again that I am going rough here.

I have here Payable figure is not directly You understood one thing I will tell you When you will be working in investment bank When you will be working in valuations In future And when you will work in valuation Or in investment banking So many such opportunities will come When you will not have proper data availability When you will not have proper data availability So you don't have to stop at that time Use the proxy data and move on We will see in the future If it doesn't make sense, then remove it from here. There is no problem. And I knew it that it won't make sense. Despite that, I have kept it here just to make this thing aware to you. That you should not stop just because the data is not available.

You need to find something or other. Or okay, let's assume that Tata Motors is not getting operating margin. It is difficult to find it. No problem.

For Maruti, put it forward. Sir, there is no difference between Maruti and Tata. I don't know.

There is a lot of difference between both. But now you have to move forward. When you get it later, change it. why you are stopping yourself right from moving ahead so don't do that inventory sorry yeah now my next is inventory turnover ratio inventory turnover ratio same thing wherever turnover is coming put sales sales if i put it in error sorry i have all the formulas sales divided by but it's not recommended for you You will start taking things very casually.

Control page page down there keep a job character my drive sales divided by Oh Wow inventories Yeah, fix the turnover ratio similarly your sales Results capital work in progress. No, sir fix assets zero capital turnover ratio capital pay equity capital You bought or you keep the efficiently say generate car para business How much business does a sale generate on equity capital of 1 rupee? I will do sum of equity shareholder fund. I will copy this the format.

I will copy the format. It will increase a little bit speed. As we will move forward, means we will move forward in the next sessions. Because it is expected from you that you are watching it after doing all the sessions. Right.

Now. Please understand. Let's go.

Dater days. Now how to calculate the data days? You need to understand. Meaning, how many days of data are available to me?

For this, the easiest way to tell you what is it? If there is an issue in terminology, then please google it. If you don't do it, then no one will come to explain.

You need to understand this. We are sitting very distantly and educating each other. 365 divided by my debtor turnover ratio Now what is coming out is 21 days This means on an average in 21 days the debtor pays me On an average in 21 days the debtor is paying to me Okay now Payable days if error, 365 divided by creditor turnover ratio. I pay creditor in 148 days. That's great.

Dater is paying me in 21 days, I pay creditor in 148 days. Now let's see the position of inventory. 365 divided by inventory turnover ratio.

Zero. I have 40 days of inventory. I mean, I have as much inventory as I have in my stock, in my company.

And if the company stops production today, then it will sustain the next 40 days of sales. Okay. These are my days.

21 day 148 days 40 days cash conversion cycle means company is rotating cash once in how much time conversion means what company is doing sum of data days I got money divided by inventory day sum of these two minus Payable days. That is my cash conversion cycle. 86 days ka shortfall padega mere pas. 86 days ka mere pa shortfall padega. Is case ke andar.

Cash conversion cycle ke andar. 86-87 days ka mere pa shortfall padega. Aap samajhna. Aap merku isko days me convert karna hai.

Matlab days me hai merku saath me day likhna hai iske. Toh bahut easy hai toh koi bachcho ka khel hai. Aapnik toh bahut smart ho yaar abhi.

Control 1 karenge. Custom me jaenge. Go to custom and select 0 I want days in decimal, what will I do by taking days in decimal Double quote D A Y A S Sir, days is stuck.

Ctrl 1, double quote, space days. Now it's here. Sir, I want the d of days to be smaller.

Ctrl 1, d of days to be smaller. Copy. Sir, I don't want days.

For example, you know you don't want days. If you don't want it, then write it here in days. Sir, but you know that data days are in days. You know that the person who is giving you money doesn't know anything about making a financial model. He is a fool, that's why he is giving you money.

He would have made it himself. Like you are smart, you are not giving money to make financial model. Okay, perfect.

Now, CFO by sales cash flow from operations by sales historical cash flow from operations CFO divided by sales sales is at the top 0 this will go in times it can go in percentage also it can go in times whatever you want If it is small then take it in times only CFO by total asset Company's CFO This is the company's CFO Divided by total asset Total asset is this Over here 0 will earn a lot CFO divided by borrowings. You will get borrowing. Here it is. Zero. I will alt-hp, alt-h0, alt-h0.

My work is now perfectly completed. I will have to do a little formatting. I will have to do this again and again. I can't do anything. You have to take money from the front to make a model.

Press F4 F4. I made it once. Now I am just pressing F4 F4. Remove it. It won't work.

I will write one more thing in days. Okay. So we have completed the ratio analysis here. It is not complete yet with the ratios plotted.

So what we will do is, I will do one thing, I will, you see this, where the ratio is ending, the column after that, I am making it a little broad. Please understand this clearly. I am selecting this cell. I am going to insert.

After going to insert, do you see this spark line here? Spark lines. In sparklines you have to go to line If you go to line, it is asking you what is the range of your data What is the range of your data So you have to select from here to here Then it will ask you if the location range is M5 which you have selected So yes it is M5 And you have to press ok As you can see as soon as you press ok, what it has done It has made a trend line chart of this Now you have to win this I think this is the sweet spot app.

You don't have to do anything, just select it and drag it down to here. So it has made a complete line chart. It's absolutely correct.

It's running from 0% to 100% and 0% to 100% This is the same line chart. Okay. Now, I will go here. Because here I have 10 years of data and here I have 9 years of data.

So it won't be justified at all. Insert, Insert, Sparklines, Line, Data Range, these 10 years, M11. I will drag it. So you are continuously seeing a fall in margins. Do you see a continuous fall in margins?

Think about it yourself. It was 13% then it became 14%, gave a slight rise and after that it is falling continuously. 14.97, 14.91, 10, 18, 8, 6 and what now? It has covered the journey of 6 to 8.8.

I hope you are understanding how to plot this. It is said that it goes to sparklines but it is never... I don't use it like this that I have written spark lines here Nobody does that right So you should not be doing it Now please understand You guys forgot to do one thing Please pay attention Okay now what I will do is I will just copy this particular cell over here for the purpose of formatting and what i want is mean okay this is not a normal average i am explaining you this will not be a normal average okay first is mean then median i will copy the median as it is Now we will use the formula of average. First we will use if error In if error, we will use average If average is this, it's ok Else give 0, HP 0 You have taken out the mean Now you go down Paste it below but you have to cover the data a little bit We have our mean in place We will calculate the median now. So in median, Actually I wanted to put trim mean but it won't work because data set is very small.

Those who don't know trim mean, search trim mean on google and you will understand. I can take it till here in median. There is problem in average but there is no problem in median. Even if there is a blank cell in median, it won't distort my result. If error.

0 HP 0 0 will be like a smooth work butter smooth take formatting of days I will change the format to days I will change the format to days I will change the format to days Now Now I will extend the borders till the end Ctrl 1 border Color will be our dark navy blue color This border tap tap done Now we just need to press F4 Go down I will select this press F4 I will select this I will press F4 I will select this I will press F4 My calculation of mean and median is very good If you want to extend it If you want to write it to make it in length You can call this as trend And if you do alt H m c with this Then the trend will be extended I will take it in the middle If you want to change any color in the spark line, then as soon as you click on the box, you will see the whole box. Click on the box and select the spark line. The spark line box will be activated. And you can put a marker over there or you can put markers.

You can see the markers. He has marked every point and you can change the color of marker and make it a little sophisticated This color is not looking good honestly Let's check the light color, it still looks good Perfect Let's check the black, I believe it is better than red Marker color, markers, yup So I can do this, if you want to change the line change the blue line so you can change the line of spark from here like you can take light blue to make it look more appealing like this our ratio analysis is complete in the next session we will touch upon common size statement and peer analysis we will take both in different sessions thank you so much for watching this very soon we will have another session don't worry now the speed will be fast a lot of people are you know I have raised my expectations. You people have raised my responsibility towards you guys.

So definitely I will be more responsible because of you guys only. So thank you so much. If you liked it, please give me feedback in the comments.

If you have any feedback, positive, negative, whatever it is, it's absolutely free. You are absolutely free to give the feedback. And please like this video. Like is very important. You like very little.

From the people who watch, only 1-2 people. 2% people are liking the video. So please do that. And subscribe to our channel. And share it with your friends.

If you find it really useful. This sheet. Solution sheet.

As well as problem sheet. Means. The sheet which I have made. Plus solution sheet.

Both the sheets. You can find them in the description below. Our telegram group link is in the description.

You can join it. I am going to take a live session. Very soon. I take a mentoring session every month.

If you want to interact. with me in a live manner on a zoom call please do that uh that's it so this is parth varma thank you so much okay one minute if you are very confused that sir i don't understand which area i should make a career in finance what should i do whether i should go into equity research or i should go into you know valuation i should go into investment banking what should i do so i would highly recommend you to watch this particular this particular video sorry this here here here yes here only it will happen see the video on finance career road understand there okay thank you so much and we'll see you soon this is Parth Verma Babai signing off thank you

Transcript for:Financial Modeling: Ratio Analysis Overview

Transcript for:
Financial Modeling: Ratio Analysis Overview