2014
Excel-Based
Dashboards
Three Rules of this Seminar
1. There Are No Dumb Questions.
Please ask questions if you need further explanation. We welcome your questions and will do our best to provide accurate and meaningful answers.
2. Obtain the Information For Which You Came.
If you have a specific interest in a topic, please let us know. If your topic is not already part of the course materials, we will do our best to accommodate your request. Our instructors are anxious to meet your needs, but, if you do not ask questions (rule #1), they cannot give you the information that you want.
3. Share and Share Alike.
Our instructor is here to share all possible information related to the course. We hope that you will be willing to do the same. If you have some great tips or experiences, please share them with us and with the other participants.
Course Development
The shareholders, associates, and staff of K2 Enterprises developed this course and the related course materials. Our goal is to deliver top quality educational courses that help you better use and understand the tools and concepts that are the focus of this session.
We designed our course presentation style for busy professionals. Through numerous short case studies and feature reviews, we strive to deliver concise information in a context that is easy to understand and easy to absorb.
To maximize the use of your time and the benefits of attending this course, we have worked hard to filter out trivial and obscure features. Our goal is not to cover every keystroke and menu option but to provide a fast-paced course that focuses on state-of-the-art information technology and what it can do for you and your staff.
About K2 Enterprises
Mission Statement
K2’s goal is to produce and to deliver the highest quality technology seminars and conferences available to business professionals. We work cooperatively with professional organizations such as state CPA societies, associations of Chartered Accountants, and vendors of technology products. K2 also provides consulting services and advice on technology. We make every effort to maintain a high level of integrity, family values, and friendship among all involved.
K2 Enterprises is named after the second highest mountain in the world. (The summit of K2 is 28,251 feet above mean sea level; Mount Everest is 29,026 feet above mean sea level.) The shareholders and associates of K2 Enterprises are listed in the following table.
Shareholders
William C. Fleenor, CPA, Ph.D. Loranger, LA will@k2e.com
Randolph P. Johnston, MCS Hutchinson, KS randy@k2e.com
Val D. Steed, CPA.CITP, MA Centerville, UT val@k2e.com
Thomas G. Stephens, CPA.CITP Woodstock, GA tommy@k2e.com
Associates
Karl W. Egnatoff, CPA.CITP Myrtle Beach, SC karl@k2e.com
Ilene Eisen, CPA.CITP, MBA Monterey, CA ilene@k2e.com
John K. Keegan, CPA.CITP Oakhurst, NJ john@k2e.com
Lawrence A. McClelland, MBA, JD Carriere, MS mac@k2e.com
Steven M. Phelan, CPA.CITP Oklahoma City, OK steven@k2e.com
Joseph A. Sanford, CPA Fort Smith, AR joe@k2e.com
Robert H. Spencer, Ph.D. Elizabethtown, KY bob@k2e.com
Brian F. Tankersley, CPA.CITP Knoxville, TN brian@k2e.com
Christopher A. Whitfield, CPA.CITP, CGMA, MCP Charlotte, NC chris@p2llc.com
Over the past twenty-nine years, our team members have collectively delivered more than 13,000 national and international courses and conference sessions on the subject of technology. To help us stay on top of technology, our team members read numerous technology trade magazines, attend many technology conferences, and talk with numerous technology companies each year to obtain the latest information to include in our courses. In addition, we personally experience everything we teach and recommend to our audiences. Because our
The K2 Curriculum
Two-Day Seminar
Excel Boot Camp: Two Days of Intensive Excel Training
Full-Day Seminars
Advanced Excel
Budgeting, Forecasting, and Business Analytics
Cloud Computing
Excel-Based Dashboards
Excel Best Practices
Excel Budgeting and Forecasting Techniques
Excel Financial Reporting and Analysis
Excel PivotTables for Accountants
Excel Tips, Tricks, and Techniques for Accountants
The Mobile Office
Paperless Office
PDF Documents - Beyond the Basics
QuickBooks Advanced Features, Tools, and Techniques
QuickBooks for Accountants
Sage 50/Peachtree Accounting Software – Managing Businesses More Effectively
Small Business Internal Controls, Security, and Fraud Prevention and Detection
Technology for CPAs - Don't Get Left Behind
Top Accounting Solutions: Cloud and On-Premise
Half-Day Seminars
Excel Macros - Part I
Excel Reporting – Best Practices, Tools, and Techniques
Excel Tables – Revolutionize How You Work with Excel!
iPad – An Effective Business Tool
PDF Forms - What Accountants Need to Know
Technology Update
In addition to the thirty-one full and half-day courses listed above, K2 Enterprises also produces approximately 25 one and two-day technology conferences each year. You can find a complete listing of our course offerings, including dates, locations, and course descriptions, on our web
On-Site Training
Working cooperatively with your state CPA organization, we are pleased to offer all of our seminars and conference sessions as on-site training opportunities. On-site training offers companies of all sizes numerous advantages, including the following:
Customization. K2 Enterprises can customize the content of all on-site training events to meet the specific needs of your organization. For example, we can combine selected parts of existing courses into one customized course to meet your specific needs. Likewise, we can develop custom content from the ground up and deliver it to your team members, thereby ensuring that your training objectives are met.
Cost savings. For organizations of all sizes, on-site training can provide significant cost savings when compared to public training venues. In addition to potentially reduced registration fees, these cost savings accrue in the form of reduced travel costs and reducing the amount of time team members spend away from the office.
Convenience. Because you select the date, time, and location of your on-site training event, on-site training is more convenient for you and your team members than traditional training options. Many organizations schedule on-site training in conjunction with staff meetings, retreats, and customer/client events to leverage further the value of all participants’ time.
Customization, cost savings, and convenience – the three C’s of “training to go.” For more information on how you and your organization can experience these benefits or to schedule an on-site
training event, please visit
http://www.k2e.com/training/on-site-training.html or contact cheryl@k2e.com.
Web-Based Training
K2 Enterprises offers web-based CPE to accounting and business professionals throughout the United States. K2's team of award-winning instructors delivers two-hour, four-hour, and eight-hour webinars on numerous technology-focused topics, including Microsoft Excel, QuickBooks, and Cloud computing. For more information on
these webinar offerings or to register, please visit
New for 2013, K2 Enterprises now provides self-study training opportunities. Self-study courses offer you the advantage of receiving top-quality training at a time and location that fits into your busy schedule. You can start a course today and complete it in the future, if that is necessary to help you achieve the work-life balance you desire. For more information on self-study offerings or to register, please visit
http://www.k2e.com/training/self-study.
K2 Internet Sites
As a service to our attendees, K2 Enterprises maintains multiple Internet sites that contain information and links that are relevant to the needs of CPAs and other business professionals and that enrich your experience during and after our seminars.
The K2 web sites contain details on the topics that we present, tips on using the software and hardware that we cover, and information on the products that we use daily and can recommend with confidence. Since the hardcopy materials provided in our seminars often age quickly, you will find additional supporting content and the most up-to-date information on our web sites. Our pages contain links to the web sites of some of the top accounting and content management software solutions as well as to other web sites of interest and even to a few sites that are just fun places to visit. We invite you to visit our web sites often. If you have any comments, questions, or suggestions regarding our web pages, please send an email to
webmaster@k2e.com
www.k2e.com
This is the primary web site for K2 Enterprises. Here you can
access information regarding CPE opportunities, including seminars, conferences, on-site training, web-based training, and self-study opportunities. You can also access a library of technology tips and articles, and you can link to K2-related web sites.
www.cpafirmtech.com
CPA Firm Technology contains technology-related information targeted specifically to public accounting firms. Included are product reviews, hardware and operating system discussions, affinity programs for CPA firms, and Cloud computing resources.
www.accountingsoftwareworld.com
Accounting Software World provides information related to accounting and financial management solutions. You can find software and service reviews, product comparisons, white papers, and other material of interest to those seeking information about accounting software and service solutions.
www.totallypaperless.com
For information about document management and paperless
process, please visit Totally Paperless. This site provides reviews
on hardware and software used by all types of businesses in document imaging and management processes.
Introducing Excel-Based Dashboards
hy use dashboards, and why use Excel? It is really quite simple – managers need access to increasing amounts of information in formats that are easy to access and even easier to understand. With increasingly complex accounting and information systems, navigating a seemingly endless maze of menus and applications to gain access to critical business information is simply no longer a viable option for many. Continuing to do so can be inefficient, wasteful, and technically challenging, often beyond the skills of even experienced users. Further, because of these issues, managers have a disincentive to generating key financial and operational reports, thereby perhaps limiting overall organizational performance. In sum, traditional reporting processes leave much room for improvement. In contrast to traditional reporting processes, dashboards provide managers and executives with access to critical corporate data in a format purposefully designed to be both easy to access and easy to understand. The term “dashboard” flows from the concept of a dashboard in a car which contains gauges measuring critical components of the automobile’s performance – speed, fuel levels, engine coolant temperature, etc. The driver of the automobile can simply glance at the gauges on the dashboard to get a quick read of the overall performance of the vehicle. There is no need to seek out individual measurements; rather, the dashboard provides all critical measurements to the driver, allowing the driver to focus on the more important task of safely maneuvering the vehicle. In the same fashion, a corporate dashboard allows managers and executives to get quick and easy access to key corporate data without the cumbersome need of generating individual reports for each measurement. Rather, the dashboard displays all critical measurements in one location, freeing managers and executives for the more important tasks of planning, leading, and directing corporate activities.
Key advantages to dashboard reports include those listed below.
Dashboards provide an easy-to-understand visual presentation of key corporate
performance measurements.
Dashboards are able to identify new trends in markets and in corporate performance at
early stages in their development. As such, managers and executives are able to capitalize on positive trends and to correct negative trends.
Dashboards help to measure both efficiencies and inefficiencies by presenting
comparative data across time and across organizational dimensions.
Dashboards offer the opportunity of increased productivity, freeing managers and
executives of the need to seek out critical data and to generate reports; instead, the dashboard organizes and displays all key data on demand, potentially saving many hours of mundane effort that adds little value to the organization.
Dashboards enable managers and executives to make better decisions. Because
dashboards can collect and present data from numerous sources, they offer managers
and executives a comprehensive view of organizational performance and positioning. With such a complete set of data inputs available to them, more informed – and therefore, presumably better – managerial decisions are the direct results of dashboard reporting.
Dashboards assist in aligning individual and team performance with organizational
strategies and goals. By providing a direct comparison of desired performance to actual performance, dashboards help to identify at an early stage situations where actual performance is not coordinated with desired performance.
For all of the reasons outlined above, plus many others not mentioned here, dashboards are rapidly ascending the ladder of corporate performance reporting tools. That, however, does not answer the question of why Excel is often the preferred technology tool for preparing and presenting dashboards. The answers to this question may be even more obvious than the answers to the question of why dashboards exist in the first place.
Perhaps no single factor is more important to the use of Excel in preparing dashboards than the ubiquitous nature of Excel. Quite simply, Excel is everywhere and is used by virtually all accounting and financial professionals. As such, organizations can deploy Excel-based dashboards often without any additional investment required in either software or hardware. Further, because of the omnipresent nature of Excel, most accounting and financial professionals already have at least a minimal working knowledge of how to use the application, further minimizing the deployment cost of Excel-based dashboards.
In addition to the current widespread use of Excel, the functionality offered by Excel is particularly well suited to dashboards. Consider some of the key activities related to preparing dashboard reports – retrieving data from external data sources, sorting and filtering data, performing both simple and complex calculations, and presenting visual images of numerical data. Excel has long provided exceptional functionality in each of these four core areas, and Excel 2007 and Excel 2010 have further elevated the application’s capabilities in each of these areas. As such, Excel lends itself quite readily to dashboard reporting.
Figure 1 - Sample Excel-based Dashboard
This is not to say, however, that all Excel users are well prepared to generate meaningful and useful Excel-based dashboards. In fact, many of the same shortcomings appear when viewing Excel-based dashboards. The following are among the most common deficiencies cited.
Ineffective overall design. Dashboard design is an art, not a science. However, even the least artistic Excel user should strive to design dashboards that present data in a
clean, simple, and concise format. For instance, in Figure 1, notice the use of simple
charts and tables to display data and the uncluttered presentation of key information. Further, what is not evident in Figure 1 is the fact that the dashboard automatically extracts data from underlying spreadsheets and databases without the need for manual user intervention.
Incorrect use of charts and graphs Excel offers numerous – in fact, almost endless – choices for charts and graphs. Selecting the correct type of chart enhances the reader’s ability to understand the data presented in the chart. On the other hand, selecting an inappropriate chart type can cause a user to draw potentially incorrect conclusions about the data presented in a chart. For instance, using line graphs to present time-series data over unequal periods – years compared to quarters, for instance – will distort the data and could lead managers and executives to draw incorrect conclusions about the data.
Limited use of conditional formatting to highlight key data Conditional formatting is a long-standing feature of Excel, and Excel 2007 and Excel 2010 offer substantially improved conditional formatting options. Conditional formatting causes the format of
data to change based on the data values. Thus, conditional formatting is an excellent tool for highlighting data that meets specific criteria. For instance, in a dashboard used to compare budget versus actual performance, the dashboard designer might format all unfavorable variances exceeding a specified percentage of the budget with conditional formatting. Despite this extreme power, many Excel-based dashboards do not utilize conditional formatting at all, likely because the persons creating the dashboards are unaware of the ability to apply conditional formatting.
Manual processes used for data input Most, if not all, of the data feeding into a dashboard already exist in other spreadsheets or databases, including the databases utilized by common accounting applications. Whenever possible, use the data connectivity and interoperability features of Excel and other databases to minimize the amount of manual data input required when generating a dashboard. Doing so not only increases efficiency but also reduces the opportunity for data input errors.
The remainder of this course focuses on educating and equipping existing Excel users with tools necessary to generate highly effective Excel-based dashboards, while avoiding many of the pitfalls often encountered when doing so.
C h a p te r 1 -
Building Blocks in Excel
Obtaining and Accessing Data
efore any Excel-based dashboard can be constructed, the data on which the dashboard is to be based must be obtained or accessed. This data may already reside in the current Excel workbook; a different Excel workbook; an entirely different database, including SQL Server and Access; on the Web; or in a text file. Fortunately, it is possible to import almost any type of data into an Excel workbook, negating the requirement to engage in inefficient and error-prone manual data entry processes.
The Data tab of the Ribbon controls Excel’s data import functions as shown in Figure 2. Here,
users may initiate the process of connecting to external data sources, refreshing data, and editing and managing existing data connections.
Figure 2 - Data Importing Tools on the Data Tab of the Ribbon
Excel has the ability to extract data directly from ODBC-compliant databases using the Data
Connection Wizard or Microsoft Query. ODBC is a standard database access method developed by the SQL Access group in 1992. With ODBC, it is possible to access data stored in any ODBC-compliant database from within any ODBC-compliant application. ODBC manages this by inserting a middle layer called an ODBC driver between the application and the database management system (DBMS) that translates the application's data queries into commands that the DBMS understands. ODBC provides two-way integration between Excel
and the general ledger, although read-only ODBC connections are the norm. Figure 3 presents
a graphical representation of the data access process.
Figure 3 - Accessing External Data Directly from within Excel Using ODBC
While the Data Connection Wizard limits data access to a single table or view in an external database, Microsoft Query allows users to relate and query multiple tables, views, or other queries. Microsoft Query is an add-in from Microsoft that operates with Microsoft Office. It provides a Windows-based query-by-example interface similar to Microsoft Access for accessing data stored in external databases.
In our first example, Carl, the owner of Carl’s Computer Shop, wants to extract some data from
the company’s accounting system – QuickBooks Enterprise Solutions – for use in creating a
dashboard helping him to analyze sales data. To create quickly a table of all invoice line items,
first open the QuickBooks company file. Then, from within Excel, click From Other Sources,
From Data Connection Wizard on the Data tab. In the first pane of the Data Connection Wizard, select ODBC DSN and click Next. In the second pane, select QuickBooks Data and
click Next. In the final pane, highlight the InvoiceLine Table and click Finish as shown in
Figure 4.
Figure 4 - Using the Data Connection Wizard to Query QuickBooks Enterprise Solutions
Select Table as the import type, confirm the location, and then click OK in the Import Data
dialog box to complete the process. The imported table dynamically links to the underlying data source and can be refreshed as required. Once Excel imports the data via the ODBC query, Carl can manipulate using some of the techniques described later in this course to produce a
Figure 5 - Sample Dashboard Component Created from ODBC-Based Data
In our next example, we will use Microsoft Query to relate multiple tables before importing data from an ODBC-compliant application. In this case, an ODBC connection to the desired application already exists. ODBC connections are typically set up during the installation of compliant applications, or the connections are set up manually through the Windows Control
Panel. To begin the process, click From Other Sources, From Microsoft Query on the Data
tab. In the Choose Data Source dialog box, select Xtreme Accounting and click OK to open
the Query Wizard – Choose Columns pane. In the Available Tables and columns list on the
left, select the following fields: Customer Name from the Customer Table, Order Date from
the Orders Table, and Product ID, Unit Price, and Quantity from the Orders Detail Table as
shown in Figure 6.
Click Next, Next, and Next through to the Query Wizard – Finish pane. Select View data or edit query in Microsoft Query and click Finish to open Microsoft Query to see how the data from the three tables is related. Create a user-defined field in the column immediately to the
right of Quantity. To do so, type in the column header Quantity * “Unit Price.” Be sure to
enclose “Unit Price” in quotation marks. Rename the newly created field “Extension” by
double-clicking on the field and typing Extension into the Columnheading area as shown in
Figure 7.
Figure 7 - User-Defined Field in Microsoft Query
Note the relationships between the three tables – Customers, Orders, and Orders Detail – and
then click the Return Data button to import the data into Excel as shown in Figure 8.
Select Table as the import type, confirm the location, and then click OK in the Import Data
dialog box to complete the process. The imported data dynamically links to the underlying data source and can be refreshed as required. Once Excel queries the data, the dashboard designer
creates components such as those shown in Figure 9.
Figure 9 - Dashboard Components from Multi-Table ODBC Query
Note that users can rename, modify, or delete Excel data connections in the Workbook
Connections dialog box, which is accessible by clicking Connections on the Data tab as shown in Figure 10.
Figure 10 - Clicking Connections on the Data Tab to Modify Workbook Connections
Using ODBC queries solves one of the biggest problems facing those desiring to build Excel-based dashboards – that is, how to initially access required data and how to update it and refresh it as necessary.
Conditional Formatting
Though dashboards generally incorporate graphical data, they present tabular data as well.
When a dashboard presents tabular data, Excel’s Conditional Formatting tools can be
excellent ways to call attention to significant values, variances, trends, and other data. Though conditional formatting is not a new feature to Excel, Excel 2007 and Excel 2010 greatly enhance this feature, and many of these enhancements readily lend themselves to use in Excel-based dashboards.
Conditional Formatting Basics
Conditional Formatting Based on Values
The simplest form of conditional formatting is that based on values. For instance, conditional
formatting based on values has been applied in the Variance $ column shown in Figure 11.
There, any cell containing an unfavorable variance greater $15,000 is automatically shaded and the text displayed in red.
Figure 11 - Conditional Formatting Based on Values
To apply this conditional format, start by selecting the range of cells from D5 through D18.
Then, select ConditionalFormatting from the Home tab of the Ribbon and choose Highlight
box, specify both the criteria for the conditional format and the desired format. Click OK to
applythe conditional format to all selected cells.
Figure 12 - Defining a Conditional Format Rule
Note that cell references can replace “hard-coded” criteria when creating conditional formats. For instance, in Figure 12, the criteria of “-15000” could be replaced by a cell reference of “G3,” thereby allowing a user to rapidly change the materiality threshold of the report interactively.
Such a change would produce the results shown in Figure 13.
Figure 13 - Conditional Formatting Applied with a Cell Reference
Conditional Formatting Based on Formulas
In addition to applying conditional formatting based on discrete value inputs, conditional formatting can also be applied based on formulas. For instance, formula-based conditional formatting could identify all unfavorable variances exceeding 15% of the budgeted amount.
Figure 14 - Conditional Format Applied with a Formula
The formula and format specified in Figure 14 will format any unfavorable variance exceeding
15% of the budgeted amount with a red background and white text as shown in Figure 15.
Because dashboard designers can apply multiple conditional formatting rules to the same cells, it is possible to “layer” these rules on top of each other. Perhaps in addition to highlighted unfavorable variances of greater than 15% in red, management wants to highlight favorable variances of greater than 5% in blue. To do so, add a second conditional format rule as shownin Figure 16
.
Figure 16 - Adding a Second Conditional Format
With the second conditional format in place, the worksheet now might resemble that shown in
Figure 17.
New Conditional Formatting Options in Excel 2007 and
Excel 2010
Excel 2007 and Excel 2010 provide significant new functionality in the area of conditional formatting, greatly expanding the usefulness of conditional formatting as a tool for enhancing Excel-based dashboards. Two of the more significant enhancements are:
Removing the previous limitation of only three conditional formats applied to a given
cell so that now users are limited only by the amount of available memory on the computer; and
Adding the Conditional Formatting Rules Manager. As shown in Figure 18, the
Conditional Formatting Rules Manager provides a convenient location from which to manage all conditional format rules in a workbook.
Figure 18 - Conditional Formatting Rules Manager
Beyond the enhancements described above, Excel 2007 and Excel 2010 also add a number of
new conditional format options. One such option is that of in-cell data bars. In the example
provided in Figure 19, in-cell data bars call the reader’s attention to top-selling items without
the need to sort the items out of their alphabetical sort order. Like all conditional formats, as the underlying values change, the conditional formats will change also.
Figure 19 - Using In-Cell Data Bars as Conditional Formats
Applying icon sets as conditional formats is another new feature of Excel 2007 and Excel 2010.
Icon sets are similar in nature to in-cell data bars and call attention to values requiring
management action. For example, the icon sets used in Figure 20 segregate sales into three
categories – high selling, average selling, and low selling – and call attention to items in each category.
Figure 20 - Using Icon Sets as Conditional Formats
Conditional Formatting in Excel-based Dashboards
Based on the examples presented above, it should be evident that conditional formatting is an important building block for preparing and presenting Excel-based dashboards. Proper use of conditional formats can focus a reader’s attention on items requiring attention and action such as large budget variances, slow-selling items, outstanding accounts receivable invoices, and declining trends in customer satisfaction surveys. Accordingly, whenever an Excel-based dashboard presents tabular data, consider using conditional formatting for enhancing the presentation of that data.
Tables
A Table is a list of data in which each column has a heading or field name and in which each row represents a record. The table features in Excel ease and enhance the way users sort, filter, format, and analyze information.
Creating a Table
To create a table in Excel using the default style, place the cursor inside of the data range and
press CTRL + T. In the Create Table dialog box, confirm the table range and check My table
Figure 21 - Create Table Dialog Box
Click OK to create the table as shown in Figure 22. Instantly, Excel creates the table with the
default style. In this case, the default style bands adjacent rows with different colors for readability. Drop-down filter arrows are visible at the top of each column. The arrows are for setting filters and sorting and are not visible on printouts. Excel names any fields that do not
have a name at the top of the column Column1, Column2, etc; users can rename these fields by
typing over the labels.
Figure 22 - Creating a Table Instantly with CTRL+T
Alternatively, users can create the table from the Ribbon. Again, position the cursor inside of
the data range. From the Ribbon, select Format as Table from the Home tab. Select a style
from the gallery and then confirm the data range in the Create Table dialog box. Click OK to
create the table.
Using CTRL + T is a faster and easier process to create a table, but it always creates a table in
the default Table style. To change the default Table style, click on Format as Table on the
Using Table Names
Excel names each table with a generic name and displays it in the Properties group of Table
Tools, Design. The table name is part of structured referencing, discussed later in this chapter. Naming tables with a representative name such as Sales or Invoices will make building formulas with structured referencing easier and more intuitive, especially if multiple tables are present in a single workbook.
Working with Tables
Let us review the sample table. It consists of monthly sales data for thirteen products over the
past six months. First, use the check boxes in Table Style Options to explore how table styles
adapt to meet specific needs. Figure 23 shows the results of our efforts.
Figure 23 - Using Table Style Options to Adjust Table Formatting
Altering the options in Table Style Options changes the styles presented inthe Style Gallery. Formatting tables with styles may be more effective if
options are set before styles are applied.
Continuing with our example, let us name the table. Click inside the table and select Table
Tools, Design. Enter Sales in the Table Name box.
Now, add some new records to the table. The easiest method is to position the cursor in the first
blank row immediately below the table and begin typing. Press TAB to advance the cursor to
the next field. The table automatically expands to include the new row. Users can add a new
row by pressing TAB while in the last row of the right-hand column of the table or by dragging
the resize handle in the lower right-hand corner of the table to expand its size. Enter the following records in rows 21 and 22 of our sample table.
14 Astringent, Facial, 9 oz 15,744 17,056 15,088 15,744 19,680 23,616 15 Astringent, Facial, 16 oz 9,684 10,491 9,281 9,684 12,105 14,526
Notice that immediately upon appending the rows to the bottom of the table, the definition of the table automatically expanded to include the additional data. The fact that tables dynamically resize to accommodate the volume of data in the table is perhaps their most significant single attribute because, as the definition of the table adjusts to match the volume of data, all objects that use the table as a data source also automatically adjust to the new volume of data. Further, tables not only automatically adjust vertically as new rows are appended or deleted, but they also adjust horizontally as new columns are appended or deleted.
Now, we will add a column to the table. The column will contain a formula to total sales by
product for the last six months. Position the cursor in cell I7, type in the column label Total,
and press ENTER. The table immediately expands to include the new column. Now, position
the cursor in cell I8. To create the formula, perform the steps listed below.
1. Type in =SUM( and move left one cell to H8. (Do not press ENTER.)
2. Hold down the SHIFT key while using the LEFT ARROW to expand the range to
include cells H8 through H3. (Do not press ENTER.)
3. Type ) to complete the formula and press ENTER.
Immediately, Excel copies the formula down the full length of the column. Note the contents and format of the formula as shown below.
=SUM(Sales[[#This Row],[Jan]:[Jun]])
The format of this formula is Structured Referencing, which allows users to refer to columns,
rows, or specific ranges within a table using tags. Next, try positioning your cursor in any cell
outside of the table and then enter the formula shown below.
=SUM(Sales[Apr])
The formula totals sales for April – and it does not matter if the table contains 100 records or 500,000! Add another record, and the total updates to include all rows for April.
At first glance, auto-expansion of tables may appear to be just another ho-hum feature, but beneath this facade lurks great power because any formula, chart, or PivotTable that references the table will automatically incorporate any new data added to the table. Have you heard of dynamic defined names? Tables in Excel can perform the same function but without the need for creating defined names based on relatively complex formulas. This becomes even more important in a dashboard environment. Imagine a scenario where data extracted from an ODBC connection is stored in a table in Excel. As the number of records entered into the table via the query changes (based on changes in the volume of transactions in the source database), the table automatically expands and contracts to accommodate the updated amount of data. Further, any chart, PivotTable, or PivotChart that uses the table as its data source also incorporates the data from the table. This means that users of the dashboard can have confidence in knowing that they are working with current data.
Figure 24 - Adjusting Conditional Formatting Rules
An interesting option for analyzing more thoroughly the integrity of aworkbook is to utilize the web-based service from ClusterSeven (www.clusterseven.com). With this service, you can upload an existing Excel workbook and ClusterSeven’s Risk Assessment Tool will analyze the spreadsheet for potential violation of a number of Excel best practices. Upon completing the analysis, ClusterSeven provides an annotated copy of the workbook that highlights all of the potential errors.
C h a p te r 2 -
Excel Charts and Graphs
hile this is not a course in creating charts in Excel perse, any discussion of
Excel-based dashboards necessarily involves a discussion of the charting functions in Excel. Though charting in prior versions of Excel was more than adequate for most users, Excel 2007 greatly improved the charting function because Microsoft introduced a new core charting engine in Office 2007 that serves Excel, Word, and PowerPoint. In Excel 2007 and Excel 2010, users can create and customize charts with fewer clicks and dialog boxes, which make it much easier and faster to produce professional quality charts to incorporate into Excel-based dashboards. This chapter serves to provide a summary overview of charting functions and tools in Excel and then to offer a “deep dive” into advanced charting techniques that are especially useful in dashboard settings.
Chart Types
Excel includes seventy-three predefined chart types in eleven broad categories. The following Table summarizes the types and number of charts available in Excel.
Column – 19 Line – 7 Pie – 6 Bar – 15 Area – 6 XY – 5 Stock – 4 Surface – 4 Doughnut – 2 Bubble – 2 Radar – 3
Column charts, the most common of all chart types, display data points in vertical columns. Column charts are commonly used to compare discrete values and do not imply the passage of
time. A bar chart is very similar to a column chart except that a bar chart has a horizontal
orientation while a column chart has a vertical orientation. Bar charts are useful when comparing
a large number of values and when category labels are lengthy. Excel groups cylinder, cone,
and pyramidcharts with column and bar charts.
Use line charts to plot continuous data over time. Line charts are exceptionally useful at helping to identify trends in data. A line chart assumes that all of the data points plotted are spaced evenly in time. If they are not, avoid using line charts because they might provide misleading
information. As an alternative, an XY chart will always space the data points according to their
relative time position. An areachart is essentially a line chart with the area below the line filled.
Like line charts, area charts are useful for displaying values over time.
XY charts, or scatter plots, show the relationships between variables plotted on the X- and Y- axes. The values plotted on the X-axis are independent of the values plotted on the Y-axis. In other words, the values plotted on the X-axis drive or cause the values plotted on the Y-axis.
Importantly, there is no category axis in an XY chart. Both axes display values. Bubblecharts
are like XY charts but with an additional data series represented by the size of the bubbles. All data series are values – there are no category axes.
Pie charts are commonly used to display the relative makeup of data. They are useful for identifying proportional values or contributions to a total. For pie charts to be effective, use no more than five or six data points. When you have a greater number of data points, consider using
a bar chart, pie of pie chart, or bar of pie chart instead. A doughnutchart is similar to a pie
chart except that it can display more than one series of data. However, because each successive series of data is placed in concentric rings, it can be relatively easy to misinterpret the meaning of the chart. Given this limitation, doughnut charts are usually best utilized with only one data series. If multiple series of data are required, consider using a stacked column chart instead.
Radarcharts have separate axes for each category of data. These axes extend outward from the center of the chart. Radar charts are useful for identifying relationships among data series
and for making comparisons of data values. With a surfacechart, colors are used to distinguish
values, not series. Surface charts can display two or more data series on a surface and are often used to find optimum combinations between two sets of data.
Stockcharts are very useful for displaying information regarding security prices, such as high, low, or closing stock prices. In addition, stock charts may display opening values and volume. Stock charts may also be used to display scientific data. For example, stock charts may be useful in charting rainfall or temperatures.
Choosing a Chart Type
With all of the choices available, sometimes the most difficult part of creating a chart is choosing which type is best for a given situation. A few guidelines may be helpful.
When comparing items to other items, column charts, bar charts, and cylinder, cone,
and pyramid charts are the best choice.
When comparing data over time, line charts and area charts are usually superior to
other options.
To make relative comparisons of one data point to another, pie and doughnut charts are
useful. Stacked bar charts, stacked column charts, 100% stacked bar charts, and 100%
stacked column charts are also good choices.
Causal relationships in data are often best depicted with XY charts. If three data values
need to be plotted, use a bubble chart instead of an XY chart.
Creating a Chart
Creating a chart in Excel requires four basics steps:
2. Selecting an appropriate chart type;
3. Selecting a chart layout and style from the Chart Tools tab; and
4. Fine-tuning the chart's look and feel on the Chart Tools Layout or Format tabs or by
using a context-sensitive menu.
Generally, arranging the data so that the X-axis categories are in columns, and the data series are in rows produces the best charts using the default process. While most users instinctively highlight their data before creating a chart, that step is unnecessary unless you want to chart two
or more noncontiguous data ranges. To select noncontiguous data ranges, use CTRL + Click
and Drag to highlight the ranges.
To create the first example chart, position the cursor within the data. Press ALT + F1 to create
a chart as an object on the worksheet that contains the data and then reposition and resize the chart as desired. To resize a chart, first select the chart and then click on one or more of the eight
resizing handles on its border, indicated by the rectangles in Figure 25, dragging them to resize
the chart.
Figure 25 - Using the Chart Border to Resize and Reposition a Chart
To reposition a chart, first select the chart and then click on its border, making sure to avoid the resizing handles. The cursor will change to a compass rose as shown in the circle in Figure 25, at which point users may drag the chart to its desired position. After repositioning the chart, the
Figure 26 - Simple Column Chart Created with a Single Keystroke
To create a chart on a separate chart sheet, position the cursor within the datato appear on the chart and press F11. Both one-click methods create a chart
using the default chart type. To change the default chart type, click on a chart
to display the Chart Tools tab. Select the Chart Tools, Design tab and then
click Change Chart Type. In the Change Chart Type dialog box, select the
desired default chart type, click Set as Default Chart, and click OK.
Now that we have a chart, select Change Chart Type from the Chart Tools, Design contextual
tab and review the various chart types available. In our example, a simple column chart is appropriate for our needs, but users may change a chart's type at any stage of creation or use. Our chart needs a title and the Y-axis formatted so that it displays dollar signs and zero decimal
places. To choose a layout that includes a chart title, select the Chart Tools, Design tab. Expand
the Chart Layout gallery and select a layout that contains a chart title and a legend on the
right-hand side as shown in Figure 27. Note that the chart layout gallery will change, depending on
Figure 27 - Selecting a Chart Layout
In the chart, click and highlight the text of the placeholder label Chart Title and type in DNM
Marketing LLC. Press ENTER to continue on another line. Change the font to 12pt and then
enter Sales by Division. Click anywhere on the chart to end the edit process.
Figure 28 - Changing the Number Format of the Y-Axis
In the latest version of Excel, users can no longer double-click on a chart element to format an element. However, the formatting process can be shortened by using the context-sensitive menu. For example, to format the vertical axis of any chart, just right-click in the vertical axis area and
select Format Axis. Any chart element can be formatted using this shortcut process.
Charting with Tables
As discussed beginning on page 12, tables are dynamically adjusting ranges of data. Because the definition of a table changes dynamically as the volume of data in the table changes, objects – including charts – that are built using tables as their data sources automatically change. This means that if you build a chart based on a table and later add more data to the table, the chart will automatically include the new data; likewise, if you delete data from the table, the chart will
contract. For example, the data in Figure 29 is a table named RegionalSales.
Clicking inside the RegionalSales table and pressing ALT+F1 builds a chart in the default style
based on the table. Now for the power of tables: as shown in Figure 30, adding the fourth quarter
column to the table causes the chart to add fourth quarter data without any manual intervention whatsoever.
Figure 30 - Adding Data to a Table Causing Charts Built Based on the Table to Expand Automatically
Additionally, as shown in Figure 31, filtering a table causes a chart based on the table to filter
Figure 31 - Filtering a Table Causing Charts Based on the Table to Filter Automatically
Communicating More Effectively with Charts
Communicating effectively with charts requires a strategy. Before creating a chart, you need to take time to determine what information the chart is to communicate and what type of chart will be most effective at communicating the message. If possible, include the message in the title of
the chart. For example, compare the two charts in Figure 32. Note how the top chart is more
effective at communicating the message than the bottom chart because the message is in the title.
Figure 32 - Put the Message in the Title of the Chart
Dashboard-Specific Charting Techniques
Thermometer Charts
Thermometer charts are commonly used to indicate progress made toward reaching a goal, such as progress toward sales goals or fund-raising goals. Although Excel does not directly support
thermometer charts, shown in Figure 33, they are easy to create as a variant of a common
column chart.
To create a thermometer chart, create a column chart based on a single data point. Here are some tips for successful creation of thermometer charts.
1. Be sure that the data point has a blank row above and below. Otherwise, Excel will
attempt to plot additional points.
2. If the chart is to show relative percentages, modify the Y-axis so that the Minimum and
Maximum values on the Scale tab are set to 0 and 1, respectively. Adjust any other scaling option to meet your needs.
3. Format the Data Series and set the Gap width to 0 on the Options tab so that the column uses the entire width of the plot area.
4. Modify the X-axis to set Tick Mark Labels to None on the Patterns tab.
Note that the thermometer chart shown in Figure 33includes a dynamic text box, as described
beginning on page Error! Bookmark not defined..
Figure 33 - Thermometer Chart
Gauge Charts
Gauge charts are popular for showing one number as a percentage of a total and are popular in
digital dashboards. Gauge charts, shown in Figure 34, are not a standard chart type in Excel but
can be constructed relatively easily from a standard pie (or doughnut) chart. The trick is to plot the data in 50% of the chart space.
To create a gauge chart, perform the following steps.
1. Plot exactly three data points into a pie (or doughnut) chart.
a. The first data point in the series must be set to 50% (or to one-half of the total
b. The second data point should be set to one-half of the desired plot value. In other
words, if you want the gauge chart to appear to plot 80% as the second data
point, set the value of the second point equal to one-half of that amount or 40%.
c. The third data point should be a “plug” value such that the sum of the three data
points is 100%.
2. Hide the 50% data point. To do this, double-click on the 50% section of the chart and
then right-click and choose Format Data Point. On the Fill tab, select No Fill and on
the Border Color tab, select No line.
3. On the SeriesOptions tab, set the Angle of the first slice to 90 degrees.
Figure 34 - Gauge Chart
Like the thermometer chart in Figure 33, the gauge chart in Figure 34includes an interactive
text box as described beginning on page Error! Bookmark not defined..
C h a p te r 3
-PivotTables and PivotCharts in Dashboards
PivotTables and PivotCharts
ost advanced Excel users agree that PivotTables and accompanying PivotCharts
are among Excel’s most powerful functions. While it is beyond the scope of this course to go into detail on advanced PivotTable and PivotChart functionality, simple PivotTables and PivotCharts can be quite valuable in dashboard settings. In addition to being very easy to create, PivotTables and PivotCharts offer significant benefits when used in dashboards.
The underlying data supporting the PivotTable or PivotChart can reside almost
anywhere: Excel, Access, SQL Server, or any other ODBC-compliant database.
Dashboard designers can sort and filter items in a PivotTable to specific criteria.
PivotTables and PivotCharts can be formatted to meet almost any need, including
conditional formatting which can be applied directly to a PivotTable.
PivotTables can summarize data using one of eleven predefined calculations such as
sum, count, average, or variance. PivotTables can also summarize based on user-defined calculations.
We begin our discussion of PivotTables and PivotCharts with a review of PivotTable fundamentals and then move into dashboard-specific PivotTable and PivotChart applications.
Fundamentals of PivotTables
To create a PivotTable, first ensure that the underlying data resides in columns with column headers at the top of each column. Though not required, removing blank rows and replacing blank cells with zeroes is recommended. Note that sorting the data prior to creating the
PivotTable is not necessary. The data should resemble that shown in Figure 35.
Figure 35 - Sample PivotTable Data
Once the data is in proper order, simply click anywhere inside the data range and choose Insert
and PivotTable from the Ribbon to open the dialog box shown in Figure 36. Accept the defaults
and click OK to create the basic outline of the PivotTable.
Upon clicking OK in the Create PivotTable dialog box shown in Figure 36, Excel displays the
PivotTable Field List and an outline of the PivotTable as shown in Figure 37.
Figure 37 - Creating a PivotTable
In the PivotTable Field List, click and drag the items to display in the PivotTable into the appropriate quadrants in the bottom half of the PivotTable Field List to finish constructing the basic PivotTable. For example, to construct the PivotTable with Dates as columns, Models as rows, Sales Units summed at the intersection of each unique combination of Date and Model, and Region and Product as top-level filters, drag each of those fields to the quadrants shown in
Figure 38 – Arranging Fields in the PivotTable Field List
Grouping Data
PivotTables’ Grouping features allow users to extend the process of analyzing data in a
PivotTable. Grouping can occur automatically – such as with dates – or users can create custom
groups to facilitate specific needs. In the example shown in Figure 39, automatic grouping
organizes the date data by months, quarters, and years. In this example, automatic grouping was accessed by right-clicking on one of the dates.
Figure 39 - Using Automatic Grouping in a PivotTable
PivotTables Based on Tables
In the previous example, the “normal” range of data pictured in Figure 35 housed the data
feeding into the PivotTable. Beginning with Excel 2007, Tables are often the preferred basis
for this data. As discussed previously, among other features tables provide users with dynamic definitions of data – that is, as the volume of data in the Table changes, so, too, does the definition of the table and, by extension, any object (such as a PivotTable) that uses the table as its data source. This means that as users add or delete rows or columns to or from a table, any PivotTable based on the Table automatically reflects the new volume of data upon its next refresh.
As shown in Figure 40, to construct a PivotTable from a table, simply click inside the Table
and choose Summarize with PivotTable from the Table Tools Design contextual tab on the
Figure 40 - Building a PivotTable from a Table
Upon clicking Summarize with PivotTable, the dialog box shown in Figure 36 opens, and the
remainder of the process for creating a PivotTable is unchanged from that described previously.
Dashboard-Specific PivotTable Applications
Sorting and Filtering
In addition to grouping, sorting and filtering are two functions that enhance a PivotTable’s analytical capabilities. Though available in prior versions of Excel, Excel 2007 significantly improved sorting and filtering capabilities.
Sorting PivotTable Results
Suppose one had constructed a basic PivotTable and desired to sort the data in the PivotTable in some specific order. Users can accomplish this easily by clicking the drop-down arrow next to the row field name and choosing one of the three sort options from the pop-up menu:
1) Sort A to Z, 2) Sort Z to A, or
3) More Sort Options….
The first two options are relatively straightforward – they sort the PivotTable in either ascending or descending order based on the values in the row field selected. However, upon choosing
More Sort Options…,Excel presents additional choices – namely, the basis for the sort. For
instance, as shown in Figure 41, a user can select whether the PivotTable will sort by the model
Figure 41 - More Sort Options in a PivotTable
Filtering Results
In addition to sorting, filters are useful tools for analyzing data in a PivotTable. Through applying filters, users can pare down large volumes of records into smaller subsets that provide meaningful and actionable information. In addition to manually checking boxes next to the items desired for display, PivotTables provide three other forms of filtering data:
1. Label filters, 2. Value filters,and 3. Date filters.
Label Filters
Suppose one wanted to see all of the items in a PivotTable that started with the characters “7200.” Rather than sorting the PivotTable, hiding rows, or manually filtering the PivotTable,
applying LabelFilters is likely the easiest way of accomplishing that objective. To apply a
Label Filter, simply click the drop-down arrow next to the row field name that contains the data
to filter and choose LabelFilters from the pop-up menu. In the LabelFilter dialog box, choose
the “Begins With” option and enter “7200” as the criteria as shown in Figure 42. Click OK,
and the PivotTable filters to just those items that begin with “7200.” Note that users may employ wildcard characters when creating Label Filters.
Figure 42 - Creating a Label Filter
Value, Including Top and Bottom “X” Results
Value Filters allow users to filter PivotTable data to only those rows whose values meet specific criteria such as “greater than,” “less than,” or “between.” In addition, Value Filters allow users to filter PivotTable data to ranking criteria such as “Top 10” or “Bottom 10” results. To apply a Value Filter, click the drop-down arrow next to the row field name that contains the data to filter
and choose Value Filters. Next, choose the criteria on which the filter should be based – e.g.,
“greater than,” “less than,” etc. Finally, enter the specific numeric values for the filter criteria.
For instance, in the example shown in Figure 43, a user has established criteria that filter the
results to just those rows where the values are greater than or equal to 5,000,000.
Figure 43 - Establishing Criteria for Value Filters
Date Filters
Excel 2007 enhanced Date Filters to allow users to specify time-oriented filters relativeto the
computer’s system clock. For instance, date filters now allow users to select a filter such as “This Quarter,” which is automatically computed based on the computer’s current date and time. Relative Date Filters update automatically whenever the date in the computer changes. To apply a Date Filter to a PivotTable, click the drop-down arrow next to the date row field that contains
the data to filter and choose Date Filter. Select the desired criteria from the pop-up menu. In
some cases – filters between a starting and ending date, for instance – users must enter date criteria.
Applying Slicer Filters
A new filter added to Excel 2010 is that of Slicer. Slicer is essentially a visual Report Filter,
needs. Multiple Slicers can be added to the same PivotTable, extending their functionality and usefulness.
To add a Slicer to a PivotTable, from the PivotTable Tools,Options contextual tab, choose
InsertSlicer to open the Insert Slicer dialog box shown in Figure 44.
Figure 44 - Adding Slicers to a PivotTable
Click in the boxes next to each of the fields for which a Slicer is to be added and click OK.
Then, drag the Slicer to its desired location on the PivotTable report. Note that in the example
presented in Figure 45, two Slicers are present – one for the Region and one for the Product
Line. Based on the filters applied by these Slicers, the PivotTable has been filtered to show only results for the Northeast and West Regions and only for the Drives and Keyboards Product Lines.
Figure 45 - Slicers Added to a PivotTable Report
Note that users can customize the physical appearance of a Slicer by clicking on the Slicer,
followed by selecting the SlicerTools, Options contextual tab on the Ribbon. Options such as
color, size, and alignment are all accessible from that contextual tab. Additionally, by choosing
PivotTable Connections on the Slicer Tools, Options contextual tab, users can link one slicer to multiple PivotTables.
A Dashboard Example
The example below demonstrates how a PivotTable can summarize the top five selling items from a company’s sales data. In this example, the data summarized by the PivotTable
summarizes resides in SalesData, an Excel table. With the table selected, from the TableTools
contextual tab Design, click Summarize with PivotTable to open the Create PivotTable
dialog box shown in Figure 46 and click OK to create the PivotTable on a new worksheet.
In the PivotTableFieldList, add Product to the Row Labels quadrant and DollarsSold to the Values quadrant as shown in Figure 47. Note that the structure of the PivotTable changes based on selections in the PivotTable Field List; the actual PivotTable updates automatically in columns A and B of the worksheet.
Figure 47 - Initial Draft of a PivotTable
Next, click the drop-down arrow next to Row Labels, select ValueFilters, and choose Top10
to open the Top 10 Filter dialog box shown in Figure 48. Accept the default information and
click OK to apply the filter.
Figure 48 – PivotTable Top 10 Filter Dialog Box
Again, click the drop-down arrow next to Row Labels. This time, select MoreSortOptions to
open the Sort dialog box shown in Figure 49. Set the sort options to Descending based on Sum
Figure 49 - PivotTable Sort Dialog Box
Finally, applying a few conventional formatting techniques turns the PivotTable into that
displayed in Error! Reference source not found.. With the example completed, think of the
speed and ease with which the PivotTable report summarizes, sorts, and filters sales data.
PivotCharts
When is a PivotTable not a PivotTable? The answer is, “When it is a PivotChart.” A PivotChart
is a graphical representation of a PivotTable with all of the analytical power of the PivotTable. PivotCharts link inextricably to PivotTables so that changing the data or organization of one automatically changes the other. Because PivotCharts are just extensions of PivotTables, they are ideal tools for accountants and other business professionals to summarize large quantities of data and to present those summaries in an easy-to-understand graphical format on an Excel-based dashboard.
Users create PivotCharts in numerous ways, but since we already have a PivotTable created, we will create our first PivotChart from that PivotTable. To do so, start by clicking in the
PivotTable. Then, from the PivotTable Tools contextual tab Options, select PivotChart.
Choose the chart type – columns, line, bar, etc. – and click OK to create a PivotChart similar to
Figure 50 - Initial PivotChart
Having created the initial PivotChart, a little cleanup is in order. Using some of the techniques
discussed earlier in this course, we can transform the PivotChart into that shown in Error!
Reference source not found..
$- $50,000.00 $100,000.00 $150,000.00 $200,000.00 $250,000.00 $300,000.00 1680B 2112B 1680J 1915H 1915G
Total
TotalCopyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited.
May 14, 2014
Val D. Steed, CPA, MA, CITP Shareholder K2 Enterprises
Copyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited. Val Steed
• Utah State University
• BS, MA, CPA, CITP
• Deloitte, Haskins, & Sells
• Hansen, Steed, Bradshaw & Malmrose PC
• K2 Enterprises
• LinkedIn, Twitter, Facebook, Intuit Community
• val@k2e.com
• http://iPadCPA.BlogSpot.Com
Copyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited.
About
K2
Enterprises
• Provides live and on‐demand Continuing Professional Education (CPE) in 48 US states and in Canada • Largest provider of technology‐focused CPE for
accountants and financial professionals in
North America
• Services offered
–Live in‐person presentations (conferences & seminars)
–Webinars
–On‐site training
–On demand self study materials
Copyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited.
• www.k2e.com‐ CPE Info
• www.CPAFirmTech.com– CPA Firm Info
• www.AccountingSoftwareWorld.com– Accounting
Software Info
• www.TotallyPaperless.com– Paperless Info
• K2 Enterprises Youtube Site ‐The K2 Enterprises
YouTube channel with over 160 free technology training videos
Copyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited.
Today’s
Learning
Objectives
Upon completing this session, participants should be able to:
–Identify the key information elements to be included on a dashboard
–List examples of dashboards in use today –Use Excel’s dashboard‐centric tools to create
customized dashboards
Copyright © 2014 K2 Enterprises, LLC. Reproduction or reuse for purposes other than a K2 Enterprises’ training event is prohibited.
But
First…
• Why Dashboards?–Need for access to information in formats that are easy to understand
–Current methods can be too complex, too time‐
consuming, too challenging
–Dashboards remove the disincentive to accessing critical information
