EXCEL DASHBOARDS & KEY PERFORMANCE INDICATORS DENNIS MCGOVERN - - PDF document

1

EXCEL DASHBOARDS & KEY PERFORMANCE INDICATORS

DENNIS MCGOVERN MCGOVERN CONSULTING GROUP , LLC WWW.MCGOVERNCG.COM

• My kids ask me what I do for a living.
• My answer is to think and figure it out.

1 2

2

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

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

3 4

3

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?

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?

5 6

4

MAILCHIMP BASIC DASHBOARD

• Campaign Success
• Opens
• Clicks

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.

7 8

5

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.

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.

9 10

6

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

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.

11 12

7

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
• ne 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.

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?

13 14

8

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.

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.

15 16

9

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

• ther unexpected benefits.

SALES DASHBOARD

• At-a-Glance View of Performance
• Sales and Marketing Alignment
• Individual Sales Rep Dashboards

17 18

10

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

BENEFITS TO EXECUTIVES

• All KPI’s in one place
• Internal Communications Improve
• External Communications Increase
• You Save Time as there is clear focus

19 20

11

INDUSTRY SPECIFIC DASHBOARDS

• Monthly Unique Visitors
• Signups
• Product Qualified Leads (PQLs)

REAL-TIME ECOMMERCE DASHBOARD EXAMPLE

• Revenue
• Sessions
• Ecommerce Conversion Rate
• Average Order Value

21 22

12

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

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

23 24

13

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.

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

25 26

14

MICROSOFT POWER BI

• Have clients using this
• Using it for data analysis and

visualization

• Time people are working –

when at home.

CAPTERRA – COMPARES SOFTWARE

• If you are looking for software

to evaluate this is a great site to start with to learn what is

• ut there.
• https://www.capterra.com/sem-

compare/business-intelligence- software?gclid=EAIaIQobChMI5oz0hMXZ6QIV BLLICh2rBwFDEAAYAyAAEgJTivD_BwE

27 28

15

GOAL FOR TODAY! CASHFLOW KPI

Cash Flow KPI Cash Flow KPI Working Capital Cash Conversion Cycle Operating Cash Flow Accounts Receivable Turnover Cash Rotation (365 / Cash Cycle) Accounts Receivable Cash Flow from Investing Activities Accounts Payable Turnover Cash Flow from Financing Activities Accounts Payable Cash Flow #/% Invoices Past Due

29 30

16

GREAT RESOURCE FOR DEFINITIONS AND CALCULATIONS

• Corporate Finance Institute
• https://corporatefinanceinstitute.com/

CASHFLOW KPI – WORKING CAPITAL

• The working capital ratio is calculated simply by dividing total current assets by total current
• liabilities. For that reason, it can also be called the current ratio. It is a measure of liquidity,

meaning the business's ability to meet its payment obligations as they fall due.

• Current refers to money you need and use in your short-term operations. This means that

working capital excludes long-term investments in fixed assets such as equipment and real estate.

• Current assets include: cash, short-term investments, pre-paid expenses, accounts receivables

and inventories.

• Current liabilities include: credit card debt, accounts payable, bank operating credit, the

portion of long-term debt expected to be repaid within one year, accrued expenses and taxes payable. 31 32

17

CASHFLOW KPI – OPERATING CASH FLOW FORMULA

• The Operating Cash Flow Formula is used to calculate how much cash a

company generated (or consumed) from its operating activities in a period, and is displayed on the Cash Flow Statement. The formula for each company will be different, but the basic structure always includes three components: (1) net income, (2) plus non-cash expenses, (3) plus the net increase in net working capital.

• CASHFLOW KPI – OPERATING CASH FLOW FORMULA
• #1 Net Income
• Net income is the net after-tax profit of the business from the bottom of the income statement. It is the link

between the income statement and the cash flow statement.

• #2 Non-Cash Expenses
• Non-cash expenses are all accrual-based expenses that are not actually paid for with cash or credit in a

given period. The most common examples of non-cash expenses include depreciation, stock-based compensation, and unrealized gains or losses.

• #3 Non-Cash Working Capital
• Non-cash working capital is all current assets (except for cash) less all current liabilities. An increase in current

assets causes a reduction in cash, while an increase in current liabilities cases an increase in cash.

• The most common non-cash working capital items include:
• Accounts receivable, Prepaid expenses, Inventory, Accounts payable, Current portion of long-term debt,

Deferred revenue

33 34

18

CASHFLOW KPI – INVESTING CASH FLOW FORMULA

• Cash Flow from Investing Activities is the section of a company’s cash flow

statement that displays how much money has been used in (or generated from) making investments during a specific time period. Investing activities include purchases of long-term assets (such as property, plant, and equipment), acquisitions of other businesses, and investments in marketable securities (stocks and bonds).

CASH ROTATION (365/CASH CYCLE)

• The cash turnover ratio (CTR) is an efficiency ratio that shows the number of

times cash is turned over in an accounting period. The cash turnover ratio works most effectively for companies that do not offer credit sales.

• Revenue is a company’s income and can be found on the income statement
• Cash and Cash Equivalents are the most liquid assets on a company’s

balance sheet.

35 36

19

MONTHS EXPENSES ON HAND

• Calculated as current assets divided by average monthly expenses
• Measures the number of months an organization can operate without having

access to non-current assets

• What is the Average Cash on Hand for Small Businesses? While there are still

many subjective variables that need to be accounted for, the general rule of thumb will tell you that your business should have 3 to 6 months' worth of

• perating expenses in cash at any given time.

CASH BALANCE YEAR OVER YEAR OR MONTH OVER MONTH

• Not everything has to be a calculation in a dashboard
• It can be visually displayed data
• My clients always want to see cash year of year to see how they are trending

37 38

20

ACCOUNTS RECEIVABLE TURNOVER

• The accounts receivable turnover ratio, also known as the debtor’s turnover

ratio, is an efficiency ratio that measures how efficiently a company is collecting revenue – and by extension, how efficiently it is using its assets. The accounts receivable turnover ratio measures the number of times over a given period that a company collects its average accounts receivable.

ACCOUNTS PAYABLE TURNOVER

• Accounts-payable turnover is calculated by dividing the total amount of

purchases made on credit by the average accounts-payable balance for any given period. Payment requirements will usually vary from supplier to supplier, depending on its size and financial capabilities

39 40

21

AP BY VENDOR

• This is not a calculation but just a report from your Accounting System
• Each Vendor & Total Amount Due

REVENUE TRENDING (SALES TREND)

• This again is not looking at a calculation but just data from your accounting

system

41 42

22

REVENUE AGAINST EXPENSE

• This again is not looking at a calculation but just data from your accounting

system

TOTAL EXPENSES BY MONTH

• When is it too much?
• When do you decide value of data?

43 44

23

NET INCOME

• This again is not looking at a calculation but just data from your accounting

system

EXPENSES BY CLASSIFICATION (GROUPING)

• This again is not looking at a calculation but just data from your accounting

system

45 46

24

HEALTHCARE COST PER EMPLOYEE

• Total healthcare cost divided by the number of employees

SO WHAT ELSE SHOULD OR CAN BE IN A DASHBOARD?

• What is your business?
• Who is your customer?
• Average Ticket Sale?
• Average Sale Per Table?
• Average Take Out Order?
• Orders Per Day?

47 48

25

GOING BEYOND

• Automation, macros, visual basic increase efficiency & accuracy.
• What tasks are you doing each month?
• Financials
• Payroll Entry
• Positive Pay File for the Bank
• Bank Reconciliation
• Recording Sales Numbers

EXCEL FUNCTIONS

• Great resource for charting and graphing in Excel
• https://trumpexcel.com/excel-functions/

49 50

26

LET US CONNECT

• Dennis McGovern
• 888-876-1544 ext 2
• dmcgovern@mcgoverncg.com
• https://www.linkedin.com/in/dennismcgovern/

51