Speaker 1: In this video I'm going to teach you how to build a KPI dashboard in about 10 or 15 minutes, but first let's take a step back and define KPIs. KPIs stand for Key Performance Indicators, and usually these are financial or operational metrics that can indicate the health of the business, and usually they pack much more information in them than just giving a dollar amount alone. For example, if the CEO is asking you about the health of cash collection of accounts receivable, you can either give him the accounts receivable amount month over month, which will be dollar amounts, or better yet, you can give him a KPI such as DSO or Day of Sales Outstanding that will show him month over month how the DSO or the number of days of collecting AR from the point of sale to the point of cash collection have been either going down or going up, and that will have much more information than if you just give him the dollar amount on its own. And if you're new to this channel, welcome, my name is Bill Hanna, and I'm the financial controller, I'm a licensed CPA, I've been in finance for the last 15 years, and this channel is all about giving you the summary of my experience over the last decade and a half. So if you're in finance or if you own a business, you'll find a lot of useful content in this channel, so go ahead and subscribe. So the way I'm going to structure the video today is first I'm going to give you four reasons why you should be using KPIs in your business, and then after that I'm going to jump into my computer here and show you a step-by-step tutorial on how to create the dashboard, and again, it shouldn't take you more than 10 or 15 minutes, especially if you download my free templates, which I'm going to provide in the description below, it shouldn't take you more than 10 or 15 minutes at the most to implement. Okay, so why do we use KPIs in finance? Why do we financial professionals like to use KPIs? Okay, so reason number one is simplicity. KPIs come in the form of a ratio or a percentage, and so usually that's easier to understand than giving a list of accounts or an income statement. Giving a percentage is much more easy to understand than giving data in the form of dollar amounts. Secondly, KPIs are more informative or have more information packed in them. So when you provide a gross margin KPI, you are providing information on sales, cost of goods sold, and gross profit all in one KPI. Rather than providing all of these numbers, you can provide one KPI gross margin that will pack much more information in it. The third reason we use KPIs is benchmarking. Benchmarking means that we can compare our performance in the current period to either a prior period or a target. We can also compare our performance to another company, whether in the same industry or a different industry. So as an example, we go back to the gross margin. If we know that we are, for example, in food manufacturing, our gross margin is 25%, we can then easily compare that to prior periods and see if whether we are improving our gross margin or maybe we need to do some more work there. Or we can also compare our gross margin to another company altogether or the industry average and figure out whether we are doing as well as we should be doing or should we do more to improve our gross margin. And the fourth reason is motivation. Usually people can rally much easier around percentages than just giving them dollar amounts alone. And the reason for that goes back to our reason number one and two, which is simplicity and information. Basically a KPI is much more simpler to understand than dollar amounts and has also more information packed into it. And that allows people to get more motivated and rally around achieving company goals when they know the KPIs. And now let's jump into my computer and I'm going to show you a step-by-step tutorial on how to create the KPI dashboard. Basically if you look here, I chose to do this in Google Sheets, but you can do this in Excel. They're very, very similar in functionality. But this tab here is the dashboard and as you can see, it has big numbers and big font, so it's easy to understand. And this is like the element of a good KPI dashboard. Number one, big font. So use here, for example, if you look at DSO, you can immediately see if you're looking at this KPI dashboard that the number of days sales outstanding is 31. And then secondly, I use color coding. So as you can see, the green means good and red or pink here means bad. As you change the period and here drop down menu to change the period, this will change and it's dynamic. So I'm using here conditional formatting and maybe now this is a good time for me to tell you what are the Excel functions that you need to know in order to create this dashboard. Number one, you need to know conditional formatting. So conditional formatting just means that you highlight the cell that you want the color to change based on the content. You go to format, conditional formatting, and in here you can create rules. And in this, for example, in this rule here, I created a rule that says if the value is less than 45 days, why I choose 45 days is because, see here, target for my day of sales outstanding is 45 days. And the rule here says if the value is less than 45 days, make it green. So if my DSO is less than 45 days, make it green. If it's equal to or greater, or rather if it's greater than 45 days, then make it pink. So that will change if I change here the month that I'm looking at. So see here is green. So that's the second thing. So number one, make the fonts big so that the reader can easily see what's going on. And then secondly, make the color coding with conditional formatting so that you're able to see how the company is doing at a quick glance. So I immediately look, I see green or pink, and I know how each metric is doing. So first one is conditional formatting. Second function you need to know is index match. So if you look here in the formula bar up here, this is an index match formula. And if you need to brush up on index match, you can look it up on YouTube. There are various videos that can teach you index match. You can use index match or VLOOKUP, depending on your preference. So that's the second thing you need to know in order to do this KPI dashboard. And then thirdly, basic Excel functions such as additions and subtraction. And these are just like, usually everybody knows these functions. The other feature of this KPI dashboard is that you can go to the months here on top and change it from the dropdown menu easily. So if I change the months, the data will change because all of this data is linked to the months up here with an index match formula. And here I'm using certain KPIs, and you can use the KPIs that apply to your company. In here I'm using the KPIs that apply to my specific situation, but basically here I have working capital efficiency KPIs such as DSO, DPO, and non-current AR. Sales KPIs, I'm using customer acquisition costs, sales versus budget, gross margin. And then for cost KPIs, I'm using KPIs such as operating expenses, actual versus budget, and then cost per full-time employee. But you can easily download this template from the link in the description below and then customize it to whatever KPIs apply to your specific situation. Okay, now let's talk about the structure of this Google Sheet or Excel file. Basically it's made up of three tabs. As you can see here at the bottom, the tab that is a dashboard, this is the final product. But then I have two other tabs here that are helping me to get to the dashboard. So the number one tab, which is the raw data, this is the data that you can just get from whatever accounting package you are using. So basically here I'm getting my information such as sales, cost of goods sold, gross profit, and getting this data from QuickBooks or whatever system you're using, and then you paste it in here, and this is just the raw data that you're going to use. The next tab, which is staging. So the next tab in staging, what I'm doing here is that I'm grabbing the data from my raw data tab. So for example here, to calculate DSO or day sales outstanding, I'm grabbing accounts receivable, and you see how in the formula here, it's pointing to the data tab. So I'm just grabbing that from the data tab and then dragging that across here, across the months, and then I'm also grabbing credit sales because I'm going to need that as well. And then the number of days in the month is just an easy formula that says equal day, and then the sale here. So it's the number of days that are in January 2020, which is 31 days. And then the next one over, or the next row over, I'm calculating my DSO. So DSO or day sales outstanding, the formula for that, as you can see here in the formula bar, takes accounts receivable and divides it by credit sales times the number of days in the month. So basically you create this formula here and you drag it across, and then you layer in your target. So my target here for my company is 45 days. So 45 days is the target that we want to be able to collect our accounts receivable from the point of sale to the point of collecting the cash. And that's basically it. You do this for day sales outstanding here, and then you do it for every other KPI. So for the next KPI over, DPO or days payable outstanding, you grab the accounts payable balance, the cost of goods sold balance, which is also grabbing from the data tab, and then also the number of days in the month. And then the formula for DPO is going to be accounts payable divided by cost of goods sold times the number of days in the month. And basically that will give you here the number, so 107 days. And then you put in your target for DPO. In my case, my target for days payable outstanding is 90 days, because usually you want to pay your accounts payable or what you owe in a longer window of time, which is here the target is 90 days, longer than the time it takes to collect from your customers. So you want to be able to collect sooner and take longer to pay out to your vendors. So that's basically it. Once here you layered DSO and DPO, and you do the same for all of the other metrics here, and you'll see all the formulas here provided as well, then you can go into the dashboard tab and do an index match on these things. So for example, for DSO, I'm doing an index match that will grab from here the DSO in row seven as pertained to the month that is being selected here. So when I change the month, it's changing DSO. So that's basically really what you need to do for index match. So you need to learn the index match formula in order to do this, but you can also download this template. I'm going to provide the link down below, as I said, and you can find the formula in there as well. So besides grabbing the number of the actual result for DSO, I'm also grabbing the actual target. So I'm grabbing the 45 days, so the reader can immediately see that we have 54 days of DSO compared to budget 45 days, and that's why it's pink or red. And also I'm showing him prior months, so he can grab the prior period with the same index match, except for this one for index match. If you can see up here in the formula, I'm doing a D4 minus one, which is D4 is here at the month, February 2020, minus one means subtract one months. And that's how it's providing me with the data for prior months DSO. And here, I want to remind you again, I mentioned this at the beginning of the video, but it's very crucial to make this KPI dashboard as easy to read as possible. So that's why I use big fonts and I use color coding green for good and pink or red for bad. So that when someone reads it immediately, they can tell whether we're doing well or we're not doing well. And that's really it. All you have to do is create this one tab here for the dashboard itself. That's easy to read with color coding, you know, for here, green is good and then pink is bad. So that easily when someone looks at it, they can immediately see, read what they're looking at here. And then all you have to do is create a data tab with the raw data. So this is the raw data coming out of your accounting system, whether it's QuickBooks or other, just paste it here. And then in the staging tab, this is where you create the formulas for the KPIs that you choose. You can choose the same KPIs I'm using here, or you can create your own KPIs. So I'm going to leave a link down in the description below for you to download this file. But basically you can then create the dashboard tab, which is going to be grabbing the information from the staging tab. And that's really it. You see, it's not really that difficult at all. So go ahead and download the template from the link in the description below and let me know in the comment section if you have any questions. And if you liked the video, don't forget to give it a thumbs up and I'll see you in the next video.