How To Create a KPI Dashboard in 10 Minutes!

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 stands 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 given a dollar 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 months which will be dollar amounts or better yet you can give him a kpi such as dso or day sales outstanding that will show him month over months 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 the 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 gonna structure the video today is first i'm gonna give you four reasons why you should be using kpis in your business and then after that i'm gonna jump into my computer here and show you a step-by-step tutorial on how to create the dashboard then 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 a 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 like given dollar amounts alone and the reason for that goes back to our reason number one and two which is simplicity and information usually a kpi is much more simpler to understand than dollar amounts and it 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 um 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 and 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 daily 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 uh the month that i'm looking at so see here is green so that's 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 um so that you're able um to see uh how the company is doing at a quick glance so i immediately look i see green or pink and i know how the 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 um so that's the second thing you need to uh to know in order to do this kpi dashboard and then thirdly you know 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 drop down 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 then 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 cost sales versus budget gross margin and then for cost kpis i'm using kpis such as operating expenses actual versus budgets 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 fun package you are using so basically here i'm getting my information such as sales cost of goods sold gross profit and you know 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 and the next tab which is staging so the next step 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 um 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 and the month is just an easy formula that says equal uh day and then the cell here so it's the number of days that are in january um 2020 which is 31 days and then the next one over or the next row over i'm calculating my dso so dsos or these sales outstanding the formula for that as you can see here in the formula bar uh takes accounts receivable and divides it by credit sales times the number of days and the months so basically you create this formula here and you drag it across um 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 uh 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 um the cost of goods sold balance which is also grabbing from the data tab um and then also the number of days in the months and then the formula for dpo is going to be accounts payable divided by cost of goods sold times the number of days and the months and basically that will give you here the number so uh 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 and 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 pay out um 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 7 as pertain 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 uh and 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 gonna i'm gonna 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 the actual target so i'm grabbing the 45 days so the reader can immediately see um you know that we have 54 days of dso compared to budget uh 45 days and that's why it's pink or red and also i'm showing him prior uh prior months so you can grab the prior period with the same index match um except for this one for index match if you can see up here in the formula i'm doing d4 minus one which is d4 is here at the month february 12 2020 minus one means subtract one months and that's how is providing 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 um 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 um just paste it here and then in the staging tab this is 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 um so i'm going to leave a link down in the description below for you to download this file but basically um you can then create the dashboard tab which is going to be grabbing uh 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 comments section if you have any questions and if you like the video don't forget to give it thumbs up and i'll see you in the next video [Music] you

Channel: The Financial Controller

Views: 39,142

Rating: 4.9476862 out of 5

Keywords: KPIs, learn KPIs, how to create a KPI dashboard, KPIs for business, Key Performance Indicators

Id: b2QUdg5FyiY

Length: 13min 7sec (787 seconds)

Published: Thu Oct 08 2020