EXCEL DASHBOARDS & KEY PERFORMANCE INDICATORS DENNIS MCGOVERN MCGOVERN CONSULTING GROUP , LLC WWW.MCGOVERNCG.COM 1 • My kids ask me what I do for a living. • My answer is to think and figure it out. 2 1
LEARNING OBJECTIVES • Review the purpose of dashboards • Determine the best configuration for the target audience • Examine relevant Excel functions • Integrate Excel basics with dashboards to highlight key performance indicators 3 COVID-19 & DASHBOARDS & DATASETS • Every press conference seems to have a chart, graph, dashboard • Focused on Total Cases & Deaths, but is that really where we should focus? • When can we move to from Red to Yellow to Green? All these decisions are data driven decisions (we hope). • https://coronavirus.1point3acres.com/en?fbclid=IwAR07YfEG5854GVMymu 5ggdR1tr9k8echYwAGIHjY2_tSq6tBVKI4h9zX680 4 2
QUESTIONS • How many of us are providing reports to people that are non-financial people – but stakeholders? • How many of us question if the person looking at the report understands what they are looking at? • How many of us are currently using dashboards? • How many of us are using dashboards but not for Accounting/Financial data? • How many of us wish we had a tool but just don’t have time or the knowledge to build it? 5 WHAT ARE BENEFITS OF DASHBOARDS • Total Visibility into Your Business • With your dashboard, you will know exactly what’s going on in your business. You know what’s working and what’s not working. For instance, your dashboard could show you exactly how your last email blast to your customers went. How many people opened the email? How much sales did it bring in? 6 3
MAILCHIMP BASIC DASHBOARD • Campaign Success • Opens • Clicks 7 WHAT ARE BENEFITS OF DASHBOARDS • Improved Results • Bob Parson’s, the founder of GoDaddy, said it best when he quipped, “Measure everything of significance. Anything that is measured and watched, improves.” The fact is that when you see your key metrics in your dashboard, you intuitively start improving your results. To this end, the best dashboards automatically show success indicators, such as a green arrow facing up when you’re doing well and a red arrow facing down when you’re not. When you see the red arrows, you naturally want to fix those areas so they become green. And you do, and sales and profits soar as a result. 8 4
WHAT ARE BENEFITS OF DASHBOARDS • Increased Productivity • Dashboards allow you to measure performance numerically. For instance, your customer service manager could see metrics such as the return rate, the average speed of answering phone calls, etc. Importantly, when they see their performance numerically, particularly if you use the green and red arrows mentioned above, they’ll naturally work harder to improve their performance and results. 9 WHAT ARE BENEFITS OF DASHBOARDS • Increased Profits : As discussed, your dashboard shows you exactly which areas of your business are performing poorly. When you know this, you know exactly where to focus your time to increase results. Conversely, your competitors probably don’t know this, and thus spend their time in the wrong areas. By knowing precisely what’s working in your business and what’s not, it’s simple to increase both sales and profits. 10 5
PURPOSE OF DASHBOARDS • A business dashboard tracks the data that is important to you from various sources and presents that information in an easy-to-understand dashboard. • The information provided in a dashboard is clean, consistent, and easy to understand. This makes it so any employee from any department in your enterprise can view the dashboards and know the key performance indicators (KPIs) that are important to them 11 WHEN TO SWITCH TO REAL TIME DASHBOARDS • If your reporting cycle is not monthly, but weekly or biweekly you should think about integrating to a solution that has the ability to pull the data into through an API connection or automating the dashboards through scripts or some other method. 12 6
COST OF NOT DOING IT • What is the cost of not building dashboards? • Let's break it down: Let's say you manage a team of five. That team each spends one hour a week updating spreadsheets. • That's a total of five hours a week, and 20 hours a month. • Let's say the average full-time employee costs about $50 an hour. • With that said, your spreadsheet is costing you approximately $1,000 a month to maintain verses programming it or just updating some numbers/fields . Ouch. • By implementing efficiencies this easily pays for itself. 13 WHO HAS GOALS? • Does everyone on the team know what they are? • Does everyone track against it? • Does everyone know where you stand against the goal? 14 7
IMPACT BUSINESS PERFORMANCE • As you may now be seeing, business dashboards are more than just a pretty view of data. Effective business dashboards impact business performance and goals. Applying these dashboards to each department and sharing those dashboards keeps everyone on the same page and department KPIs become transparent. 15 WHAT IS A DASHBOARD • A data dashboard is an information management tool that visually tracks, analyzes and displays key performance indicators (KPI) , metrics and key data points to monitor the health of a business, department or specific process. They are customizable to meet the specific needs of a department and company. Behind the scenes, a dashboard connects to your files, attachments, services and API’s, but on the surface displays all this data in the form of tables, line charts, bar charts and gauges. A data dashboard is the most efficient way to track multiple data sources because it provides a central location for businesses to monitor and analyze performance. Real-time monitoring reduces the hours of analyzing and long line of communication that previously challenged businesses. 16 8
TYPES OF DASHBOARDS • Operational Dashboards – This is the most common dashboard type, with metrics updating in real-time showing data related to daily operations. The main purpose of an operational dashboard is to provide a comprehensive snapshot of performance, which means that you should incorporate a large amount of detail without using too many drilldowns. • Analytical Dashboards – Use data from the past to identify trends that can influence future decision-making. Users should be able to interact with the data on an analytical dashboard, so many of them incorporate pivot tables and drilldowns. The ideal audience for viewing analytical dashboards are database analysts, as they typically require a level of understanding that a typical business user may not possess. • Strategic Dashboards – Track performance in relation to your key performance indicators, to better align actions with strategy. If you are looking for a dashboard to share with your whole organization, consider creating a strategic dashboard. Transparency of data can lead to an increase in motivation and other unexpected benefits. 17 SALES DASHBOARD • At-a-Glance View of Performance • Sales and Marketing Alignment • Individual Sales Rep Dashboards 18 9
BUSINESS DASHBOARD BENEFITS FOR MARKETING • KPI’s all in one place • Your Data Is Always Accessible • Your Sales and Marketing Results in One Place • It Saves You Money: Better Target Your Customers • It Saves You Time • Optimize on the Fly • Know how your campaigns are performing • Know when conversion rates change abruptly 19 BENEFITS TO EXECUTIVES • All KPI’s in one place • Internal Communications Improve • External Communications Increase • You Save Time as there is clear focus 20 10
INDUSTRY SPECIFIC DASHBOARDS • Monthly Unique Visitors • Signups • Product Qualified Leads (PQLs) 21 REAL-TIME ECOMMERCE DASHBOARD EXAMPLE • Revenue • Sessions • Ecommerce Conversion Rate • Average Order Value 22 11
REAL-TIME MEDIA DASHBOARD EXAMPLES • Daily Active Users (DAU) • Monthly Active Users (MAU) • DAU/MAU ratio • Time Spent Per User Per Day (their own calculations) • Time Spent Per User Per Month (their own calculations) • Videos Watched Per User • Total Videos Viewed • 28-Day Rolling Churn • Total Clips Played Per Day/Week • Total Time Spent on Whole Platform 23 BEST PRACTICES FOR CREATING AN EFFECTIVE BUSINESS DASHBOARD • Understand Your Audience (The End User) • Utilize Better Visualizations • Choose Metrics & Data That Matter • Share for Collaboration 24 12
MISTAKES TO AVOID WHEN CREATING YOUR BUSINESS DASHBOARD • Beginning With Too Much Complexity • Awaiting Business Intelligence (BI) Deployment Projects • Needlessly Filling Your Business Dashboard with Widgets and Graphics • Incorporating Overly Complicated Metrics • Overlooking Dashboard Maintenance • Not minimizing the data in the reports you are using to create the dashboards but using a trial balance where you are adding numbers together. Build the reports, groups, create custom reports to be used for your dashboard. 25 TYPES OF DASHBOARDS • Your Current Accounting Software, sales solutions, order system, email solution – may offer this as a standard set, an add on module or offer a plugin that connects to your database • A single chart in Excel • Multiple charts in Excel • Microsoft Power BI • Solutions that Plug into your solution ton of them out there and the price ranges is wide. • Central repositories that pull data from multiple solutions into a reporting tool 26 13
Recommend
More recommend
