Speaker 1: this is vertical analysis and then the third is going to be ratio analysis which means that you're comparing two financial figures on your financial statements to each other so i layer in my results for the three months right and then i have the change from the most two recent month march to february and then a change in terms of percentage. Hey guys welcome back to another video with the financial controller Bill Hanna here in today's video we'll be discussing financial statement analysis and the types of analyses that I perform as a corporate controller so there are four main types we'll be going through an overview of these analyses and then we'll discuss each one in more detail so let's dive in and take a look all right so as an overview of financial statement analysis there are two main categories of analysis there is accounting analysis which is the purpose of it is to find accounting errors and then there is financial or managerial analysis which is the is the purpose of which is to help make business decisions. So with accounting analysis, the most common type is gonna be month over month analysis, which we call horizontal analysis. The reason why we're calling it horizontal is because we're going horizontally from left to right, comparing a period to the next, and looking at the change and the change percentage and commenting on these changing and trying to figure out if there are accounting errors, because this is the whole purpose of this thing is to find accounting errors, right? And then the second category is gonna be financial or managerial analysis, which breaks down to three types. And the purpose of all of them is to help make business decisions, right? So these are gonna be more strategic in nature. So the first one is gonna be actual to budget, where we'll be going through the actual results and comparing it to the budget and looking at the change and commenting. And pretty much this analysis will drive what kind of changes we need to make to future, for example, spending, to figure out the best way to run the company more efficiently. This is actual to budget. The second one is going to be vertical analysis. So vertical is as opposed to horizontal. So remember here we said horizontal, we're looking left to right. Well, with vertical, we're going top to bottom, right? And the reason is that we're measuring line items on say the income statement as a percentage of one of the light items on the income statement. Most commonly, you're measuring expenses to the revenue as a percentage, right? So we'll go through that as an example in a minute. This is vertical analysis. And then the third is gonna be ratio analysis, which means that you're comparing two financial figures on your financial statements to each other and getting the result as a percentage or a ratio. The most common example here, for example, is current ratio. current ratio looks at the ratio between your current assets and current liabilities to figure out whether the company has sufficient funding to meet its short-term obligations. So we're going to go ahead and go through an example of each of these analysis here in a minute but if you're new to the channel my name is Bill Hanna I'm a corporate controller and a licensed CPA in the great state of New York and the purpose of this channel is to give you the summary and pretty much the juice of my experience over the last maybe 15 to 17 years working in accounting and finance in this channel so if you haven't subscribed go ahead and subscribe to the channel and if you like the subject of this video give us a thumbs up that will help the channel reach more viewers thank you in advance. I also teach this specific topic on analysis and accounting in general in the controller Academy I'm gonna leave a link down below. Okay so we jump back in and we said we have four types of analyses that I perform as a corporate controller one is an accounting analysis and then three of them are going to be financial or managerial analyses, right? And if you stick around till the end, I'm going to tell you exactly out of these four, which are the most commonly used types of analyses that are must-have for any corporate controller. All right, so let's take a look at the first one and how I set up my analysis in Excel for my month-over-month comparison for a horizontal accounting analysis. All right, so this is my setup in Excel and this is for a business called spa booker and spa booker it's important to understand what kind of business they're in for you to understand how to analyze the income statement so spa booker is a platform an online marketplace that connects the consumer on one end and then the spas on the other end where the consumer can go in the mobile app and set up an appointment for a spa treatment it's very similar to the concept of uber or lyft so it's an online marketplace and then spa booker sits in between the consumer, and a spa place and makes a commission on each of these transactions, right? So let's jump back in. All right, so we got the income statement for three months, for January, February, and March, right? And I like to have three months periods where I'm comparing March to February, but then I like to have January also as further context. So for example, if I see that my revenue went up by 22% in platform fee between March and February, I also like to have January in here so that I can have further context into what happened in January. And then I can see the story of how the revenue is developing month over month. So I layer in my results for the three months. And then I have the change from the most recent month, March, to February, and then a change in terms of percentage. And then I have here my comment column where I'm providing my commentary. And remember, the whole purpose of this whole analysis here is to find accounting mistakes. So you're not so much necessarily defending these numbers. You are trying to objectively analyze the numbers and find accounting mistakes. And if you find an accounting mistake, then you just got to go and fix it. Nothing bad here. It's just you finding accounting errors that could happen during the month in close and then going back to fix them. So in the commentary, I like to have a threshold for analysis. So for example here, I say the comment is required for changes over 10%. And this is something you can decide in your company with your team, with your leadership, figure out, speak to your CFO, your controller, figure out what is the threshold that is gonna be worth your time investigating, right? Because you don't wanna be spending time investigating $5 or $10, right? You wanna be going over the big guys, right? So figure out the threshold. In some companies, I've seen that being 2%, maybe 5%. In some companies where it's a smaller company, it's 10%. Just figure it out, the percentage that makes sense, and then go over the commentary over the variances that are over this percentage. So in this case here, we set up 10%. And also, I like to layer in my headcount information. So headcount information is going to help drive the changes that I see in payroll or some of the other costs I count that are associated with headcount. So for example, January headcount was 26. It went up to 32, it went up to 38. It changed month over month at six, which is 19%. So pretty much, I should expect that my payroll costs are gonna be moving in the same direction, but if I see them moving in a different direction, then I'm gonna be stopping here and saying, wait a minute, it doesn't make sense for payroll to be changing in a much bigger or smaller difference than my headcount information. So when I begin analyzing the income statement account by account, so I begin with the first line on the revenue, which is platform fees. And I can see that went up by 22%, right? So my commentary on this is to review the sales by customer report for reasonableness, and then provide the commentary that I've reviewed it and ask the question. So for example, when we look at this tab here that we set up for sales by customer, I can see the trend line on how my, by account, my revenue is going up month over month, right? So my commentary is gonna be that I asked the question, looked at the detail by customer, and asked the question within the sales team and figure out if it's reasonable for revenue to go up by that percentage, right? And generally, revenue should go up month over month, right? If you see it going down, then it's a sign of trouble overall. But in this case here, it is trending correctly and going up month over month. And as I continue to go through the income statement, I'm gonna start going through some of the cost of sales item. And the first one is going to be a server cost. And a server cost in this case is with AWS or Amazon Web Services. And as you might imagine, any business that relies on web traffic, such as SpotBooker, as revenue goes up, you also should expect that your server costs should start going up as well. So we see here that the web cost or the web hosting cost went up by 7%, right? And we'll say here that this is in line with change in revenue. Even though we see change of revenue is 17%, the increase here is 7%, which is good because you want your revenue change to outpace your cost, right? And that makes sense. And now, as we go down through the statement, we'll see some items such as the salaries and benefits to customer support. And you'll see that all of these accounts went up by 20% month over month. So this is an increase of 20%. And this is why it's helpful to have the headcount information trending up here so that I can see, for example, the headcount went up by 19%, and the payrolls cost went up by 20%. So it's in line. But remember, the whole reason for this analysis here is for you to find mistakes. So if this trending here is not 20%, say it's 30% or 40%, then that means something is wrong. So you're not necessarily defending these numbers that you're seeing in the income statement. You're trying to find if there's any kind of correlation that it make sense that will indicate that you might have an accounting error which is the whole purpose of this analysis okay so this here is a good number to see 20% correlates to 19% right if it's not correlating you have to go back and figure out if there is a mistake in the way you booked payroll right so i'm going to comment here and say that increase is in line with increase in head count and you pretty much go through the rest of the income statement and look at the increases that that are over 10% and provide commentary on the change month over month. And again, you're not defending, but rather trying to find if there is an accounting mistake. All right, so back to this overview of the types of financial statement analysis. We just covered the accounting analysis month over month to find accounting errors. Now let's take a look at the financial or managerial analyses. The first one is gonna be actual versus budget. Let's take a look at how I set up my file. All right, so in Excel here, I'm analyzing the P&L for SpotBooker for March of 2022. And I have a column for actual results and I have a column for budget, right? So I'm gonna layer in my actual by account and then I'm gonna layer in the budget and then I'm gonna have a column for variance, dollars and various percentage. And then I'm gonna do the same thing where I'm gonna put my headcount information at the first row to show what is the actual headcount versus budget. And then the variance is four and that's 12%, which means that hiring is ahead of plan, right? And I have a tab here for my headcount information. So I'm gonna show you in a second, if I flip over to headcount, right? I have my actual headcount data and I have my budget by department, right? So the way to look at this is for example, the budget for customer support was to have six headcount in March, but in actuality, I have seven, right? So that tells me that I'm hiring ahead of budget or more than budget, right? If I flip back to the profit and loss statement, this is gonna be a guiding number for me to know where I stand in terms of hiring for budget versus actual. And just like I did before, I'm gonna provide a threshold for commentary because I don't wanna analyze every single dollar on the income statement, right? I wanna go after the things that are more impactful. I wanna spend my time wisely. If you have a bigger team and you have the resources, maybe lower that threshold to maybe 2% or 3%. But in this case here, I'm setting it at 10% because I'm gonna be spending my time efficiently on the things that have the bigger impact on the P&L. And now I can begin analyzing my accounts one by one. So I'll begin with my revenue accounts and I have the first one, platform fees. And I'll see here that my actual to budget, I am behind budget by $14,000 or 12%, right? So that's gonna be something that I have to comment on. And the way I'm gonna comment in this one is that I'm gonna speak with my sales team and figure out why are we behind plan. So when I speak to them, they told me that the slight lag versus budget is due to list number of stores signing up versus planned, right? So you can examine that and make sure that that statement is correct by looking at the way the budget is built and the number of stores that are being signed up and compare it to actual number of stores and see if that statement is true, right? This is how we analyze this kind of number. The transaction fee is off from budget by 4%, so it's below my threshold of investigation, so I'm not gonna go through it. But if you set up your threshold to be 3%, then you'll have to comment on this item here by investigating further. And you do the same thing going down the list, right? So you go through the accounts, you know, your cost of sales, your server cost, this is AWS cost, right? it's lagging behind budget, below budget by 5%, which is good, because you want it to correlate with revenue, right? So revenue is down by 5%, and so is your cost with AWS down by 5%, which makes sense, because there's a correlation between the traffic and the hosting fees with AWS. Now, when you look at your accounts for salaries and benefit to customer support in this case, right, it's down, or actually rather it's up by 12%, right? and that's why it's helpful to have the headcount budget versus actual on top here so that you see that you're ahead of budget by 12% and so is your cost is also ahead of your budget by 12% and that makes sense. And then you go through pretty much the rest of the statement and go account by account and provide commentary in the same way by investigating why the budget is different than actual and provide a commentary and then this pretty much is going to help management, make decisions in the business going forward, whether we need to reduce the resources being spent in certain areas, invest more in selling in certain areas, so on and so forth. This is how you analyze actual versus budget. All right, the next analysis is going to be vertical analysis. And before discussing why even it's called vertical analysis, I wanted to give a big shout out to my Patreon supporters. I'm going to leave a screenshot here for my supporters. thank you so much for supporting the channel and patreon pretty much is a place where we meet once a month and you can chat with me on discord via voice chat and ask me your questions we discuss things that vary from financial statement questions to career development even questions whether you should leave your job and go to another job things like that we discuss so go ahead i'm going to leave a link down below to patreon where i also leave valuable excel files and downloads that you can go ahead and check out but let's dive back in we're talking about vertical analysis. So for vertical analysis, the reason why I scored vertical, remember, we just hit horizontal here on the left. Horizontal was going left to right and comparing period over period, right? With vertical, we're going top to bottom. That's why I scored vertical. It makes sense. So in vertical, I'll show you the setup in Excel in a second, but you pretty much are comparing some of the items on the income statement to one fixed item such as revenue, most commonly done this way. And then looking at, you know, whether you are as a percentage of revenue, for example, you're trending up or down on these expenses. So let's take a look on the Excel file and how I set it up for this kind of analysis. Alright, so for vertical income statement analysis, what I do is I layer in my income statement by account for three months. So I have here, January, February and March. And then I create three columns here where I measure each line item as a percentage of revenue. So obviously, revenue as a percentage of itself is 100%. But then as you go down the cost of sales accounts, for example, so server cost, right? When you look at it as a percentage of revenue, you're simply just dividing the server cost as a percentage of total revenue, and that gave you 21%, right? And what you wanna see is that you wanna see, for example, server cost, you wanna see improvement, right? So this one here, that's why I highlighted in green, because I'm seeing a good trend line, right? You don't want to see this one going from like 20%, 21% to 19, you know, to like 30%. You don't want that. That would be something that you highlight in red. In this case here is actually a good trend where the percent, as a percentage, my server cost is going down as a percentage of revenue. Same thing here, I'm going to highlight the salaries to customer support, which is trending down as a percentage of revenue, which tells me that I'm able to support the growing revenue with less cost or maybe fixing my costs in terms of headcount for customer support, which is a good thing. So I want to highlight the success. And then also I want to highlight any kind of red flags. So for example, as I scroll down, you know, hotels, for example, the amount of spend as a percentage of revenue went up from 3% to 4%. And although it's not a big increase, it's just an example I want to show you that's where you can see trend lines that are not trending in the right direction and you want to highlight that and discuss it with your management to figure out where are the areas that you can improve in the business and this is the whole purpose of this vertical analysis is to highlight the areas that need improvement in the P&L. Alright so going back to the overview we've covered three types of analyses and accounting analysis and then we covered actual versus budget vertical analysis and now we'll take a look at ratio analysis so the reason why ratio analysis is helpful is because it compares two financial figures to each other and gives you the result in terms of a percentage or a ratio so an example is current ratio so current ratio measures the relationship between current assets and current liabilities and gives you the result in terms of ratio the higher the ratio the better because that means that you have enough current assets to cover your near term obligations and current liabilities. And so it's a really helpful way to look at the business because it gives you a quick way of understanding where you stand in some of these things, profitability, liquidity, unsolvency. So let's take a look at how I set up my file for this kind of analysis. All right, so in terms of setup in the Excel file, I go ahead and list my ratios. So I selected a couple of profitability ratios, gross margin, operating margin, liquidity ratio such as current ratio and then a couple of solvency ratios. I like to always list the purpose of these ratios for anyone who is reading the file to understand what I'm doing, and then the formula and then the results for January, March, February and March and kind of go to the right here and measure each month so I can establish a trend line for these results. So for the first one, gross margin, for example, that shows the amount of profit before deducting selling general and admin expenses and it takes gross profit divided by gross revenue and spits out the result in terms of percentage. In January it was a negative 14% and then it begins to trend up in Feb to 29% and into 30% in March and the goal is 60% and the goal is something that you can set up by speaking to your leadership in the company and also discussing in terms of industry so in this case here this is a software company and it's common to have a gross margin close to 60% and so that's what I'm gonna set up the goal here to be 60% and I want to see this number trending closer to 60% in the future. And to make it easy for myself I put a couple of tabs in here one for profit and loss and one for the balance sheet so I can easily just link these numbers gross profit divided by revenue and I can get my result here and display it here on the right. So you can set up ratio analysis in this simplistic view that you see right here or you can set up a more sophisticated KPI dashboard like the one that you can download on my website I'm gonna leave a link down below to the controller KPI dashboard which is something that you can set up and it's easy to navigate where you can change the month and that will change the results you can go ahead and check out the link down below I'll teach you how to create it and you can download a finished product which you can customize your needs or you can just set up this simplistic view which is pretty good it will go from gross margin to operating margin measuring each month the trend line and then also shown the goal I have here liquidity ratio a current ratio which is current assets divided by current liabilities giving me a number and then I'm gonna have a goal so the goal always with current ratio is to have more than one because that means that you have much more than your current liabilities and current assets so that means that you can meet your current or near-term obligations of the company. With the solvency ratios we've got debt-to-equity ratio which is the relative proportion in equity that is financed by liabilities so for example liabilities divided by equity gives you 15% and that means that 15% of the company's business is financed via liabilities versus equity which is good you want that to be less than one otherwise the company can be too leveraged into liabilities and then you have financial leverage which looks at the total assets divided by total equity and then you provide your goal and your explanation of the ratio so this is a quick way to kind of set up a ratio analysis for the company again I use either this view here which is simplistic or a more sophisticated dashboard view here which I'm going to leave a link down below too as well and as I mentioned at the beginning of the I'm going to share with you the common analyses that I use at month end. So out of all these four analyses, the ones that I rely on each month is going to be this first one here, accounting analysis to find accounting error month over month. This is going to be a stable analysis for me each month. And the second one is going to be actual versus budget. This is something that I do every month. So these first two are the two that I rely on the most. With vertical and ratio analysis, I would only do if I have the resources enough people on my team to conduct the analyses. If I don't have time for these two resources, I will always default to these first two. These are the most helpful because the first one tells me if I'm making accounting mistakes and the second one tells me how I'm spending or operating compared to budget because the budget is the basically the North Star for the company and what I need to stick to and if I am underperforming against budget, I need to figure out why, or if I'm overperforming, it also is good to find out why you're overperforming so that you can learn from the company's results. So these are the two results or two analyses that I rely on each month. Thank you so much for watching, and if you found this video helpful, go ahead and give it a thumbs up, and I'll see you in the next video. You