Understanding Data Dashboards and KPIs

• Data dashboards serve as data visualization tools providing multiple, real-time updated outputs

Key Performance Indicators (KPIs)

• KPIs in data dashboards can include an organization's financial position, inventory, pending orders, project progress, account status, customer orders, and customer service metrics

Principles of Effective Data Dashboards

• Data dashboards should present all relevant KPIs for an organization's operations on a single screen
• Data dashboards benefit from using of multiple dashboards for related KPIs
• KPIs should clearly convey meaning and be relevant to user decisions
• Data dashboards benefit when following effective data visualization principles
• Use color selectively to emphasize specific values and distinguish categorical variables

Application of Data Dashboards

• Investment dashboards monitor an organization's or individual's investment performance, including price, rate of return, and risk relative to the market
• Manufacturing dashboards monitor production processes by facility, product, or shift, with information like quantity, quality, speed, and problem frequency
• Marketing dashboards track sales and promotional efforts by product, service, or campaign, including quantities sold, success rates, sales prices, and customer metrics
• Human resource dashboards monitor workforce performance by individual, division, or shift, with data on employee numbers, turnover, employment length, satisfaction, absenteeism, and training
• Technical support dashboards track support of technology users by system or location, including problem types, response times, outstanding issues, downtime, and software usage
• Personal fitness/health dashboards can monitor pulse, blood pressure, body temperature, BMI, exercise, steps, diet, and blood sugar levels

Donor dashboards

• Donor dashboards monitor donor activity and potential for nonprofits, tracking donations, contacts, expenditures, conversion rates, cash flow, and campaign status

Data Dashboard Taxonomies: Data Updates

• Static dashboards are manually updated and are inexpensive, useful for slowly changing KPIs, and infrequently updated
• Dynamic dashboards automatically update with new data, require more effort to develop, are useful for rapidly changing KPIs, and are frequently updated

Data Dashboards Taxonomies: User Interaction

• Noninteractive dashboards do not allow user customization and are useful when data are stable
• Interactive dashboards allow user customization

Interactive dashboards, enabled by:

• Drill-down features that provide more detailed info on elements or KPIs
• Hierarchical filtering that restricts the displayed data to user-selected categories
• Time interval widgets that let users specify the displayed time period
• Customization tools that allow users to select/deselect/combine variables and categories or add/hide custom text/statistics

Data Dashboards Taxonomies: Organizational Function

• Operational dashboards help lower-level managers monitor rapidly changing business conditions and are updated many times per day.
• Strategic dashboards aid executives in monitoring KPIs related to high-level organizational goals and are updated often.
• Tactical dashboards assist mid-level managers in assessing strengths and weaknesses for strategy development and are updated less often than operational or strategic dashboards.
• Analytical dashboards assist analysts in finding trends, predicting outcomes, and discovering insights and are updated infrequently.

Data Dashboard Purpose

• Data dashboards support an organization's operations, decision-making, and strategic planning

Data dashboard objectives include:

• Tracking KPIs and monitoring processes
• Assessing goal attainment
• Developing/enhancing insight
• Sharing information
• Measuring performance
• Forecasting and data exploration

Data Dashboard Users' Needs

• The design team should understand the:
  • Needs of end users
  • How addressing the needs supports organizational objectives
• This knowledge enables the developer to determine:
  • The necessary information
  • Most effective presentation

Data Dashboard Display

• Consider appropriate chart types, use of visual attributes and Gestalt principles, practical layouts, strategies to avoid complexity, and the display device

Data Dashboard Context

• Consider how a KPI varies over time, compare KPIs to goals, and compare KPIs internally across the organization or externally

Data Dashboard Engineering

• Organize charts/tables for user analysis, maintain/assess effectiveness, and accommodate future shifts in objectives

Data Dashboard Organization

• Store dashboard data in a separate worksheet
• Avoid drawing directly from the source data worksheet
• Extract/store data for charts/tables in a separate worksheet
• Prevent users from making permanent changes

Data Dashboard Testing

• Test at each step to minimize errors/miscommunications

Failure examples include:

• Faulty final product
• Costly revisions
• Damaged credibility

Espléndido Jugo y Batido, Inc. (EJB) Data Dashboard

• EJB manufactures bottled fruit juices and smoothies with five fruit and four vegetable flavors
• EJB ships products from seven distribution centers, primarily within the Midwest
• The provided EJB data file contains three years of historical data from orders
• Each data record has a quantity of one flavor

EJB Dashboard: Project Needs

• EJB Goal is to allow mid-level operations to management to track its sales over the most recent three years
• EJB Specific objectives include tracking dollar, delivery time and sales by channel
• The data dashboard will automatically update with new data

EJB Dashboard: Variable Requirements

• Dashboard’s KPIs include total dollar sales and average delivery times
• Variables of interest are: month, year, date, flavor, distribution center, and existing customer status
• The dashboard requires a stacked column chart, line chart, clustered column and bar charts, and tables

EJB Dashboard: Chart 1

• Chart 1 uses a PivotChart for total sales with slicers for distribution center, year ordered, and whether the customer is new
• The EJBChart1 file contains the the EJBData table with fields and charts and slicers in Sheet 1

EJB Dashboard: Chart 1 - Notes

• Review instructions in Chapter 6 for building PivotCharts
• Excel PivotTables are limited to columns, bar, line, pie, and radar.
• Slicers allow filtering. Timelines filter dates in PivotTables.
• The Developer Tab has tools to interact with a data dashboard

EJB Dashboard: Chart 2

• Chart 2 is a PivotChart for total sales with slicers for year ordered, month ordered, and category
• The line chart will graphically display total sales for any combination of years ordered, months ordered, and category
• Building instructions are included Chart2 tab of the EJBChart1 file

EJB Dashboard: Chart 3

• Chart 3 is a PivotChart for total sales with slicers for year ordered, flavor, and category
• The clustered column chart allows graphically display total sales for any combination of years ordered, flavor, and category

EJB Dashboard: Chart 4

• Chart 4 is a PivotChart for average time to delivery with slicers for distribution center and year ordered
• The clustered bar chart view will graphically display the average time to delivery for any combination of distribution centers and years ordered

EJB Data Dashboard: Build a PivotTable

• Follow a series of steps to create a PivotTable, which will display total dollar sales and average delivery time across category and flavor by distribution center, year and month and day
• Select any cell and complete a dialog box and select PivotTable in the Tables group and rename Dashboard the newly crated worksheet

The EJB Data Dashboard: a Layout

• The objective is to minimize eye travel by putting charts next to each other
  • Chart 1 and Chart 3 display sales history by year
  • Chart 1 and Chart 4 display analyses at the distribution center level
  • Chart 2 and Chart 3 display sales at category level
  • The table of total sales should be located at the top

Common Mistakes in Data Dashboard Design

• Not considering organizational needs/objectives
• Not considering the environment for use
• Failing to position charts properly
• Using inappropriate graph types
• Neglecting chart/table design principles
• Creating dashboards that are cluttered or sparse
• Designing unattractive displays
• Not considering future needs

Summaries

• Data dashboards help users understand data and make better decisions
• Principles and applications of data dashboards help build better dashboards
• It is important to consider the dashboard's purpose and users' needs
• You can select the information and display it in the dashboard
• Excel provides the tools: PivotTables, PivotCharts, and slicers.
• Connect slicers to PivotTables
• Protect your dashboards from user-made changes