Academic journal article Journal of Accountancy

Dashboard Your Scorecard: Unleash the Power of Excel for Visual Data Analysis

Academic journal article Journal of Accountancy

Dashboard Your Scorecard: Unleash the Power of Excel for Visual Data Analysis

Article excerpt

[ILLUSTRATION OMITTED]

Dashboard reports created in Microsoft Excel are powerful, flexible and easy to design. In much the same way that an automobile dashboard graphically displays numerous measures of performance from the gas level to oil pressure, a computer dashboard presents critical data in a variety of visual formats. From this organized visual display, optimal business decisions can be made quickly and efficiently.

Most enterprise systems provide dashboards that decision makers can easily customize to fit their specific needs. But suppose the data you need to analyze is regularly downloaded from your enterprise system and Microsoft Excel is the only available data analysis tool. If you are reasonably proficient at using charts, PivotTables and conditional formatting, this article will show you how you can design your own dashboard with minimal time and effort. Adding a combo box control will further unleash the power of Excel to customize the information displayed in graphics.

Imagine the possibilities for using dashboards to quickly analyze data in a variety of ways and to communicate meaningful information to specific target audiences. Imagine the effectiveness of your explanations of companywide financial or operational trends when, in the same window, you can instantly respond to a question about trends for a division, location, product line or employee.

You can learn to perform these data analyses with Excel as we guide you through the dashboard shown in Exhibit 1. Excel 2010 commands and illustrations are included in the article. Any differences in Excel 2007 commands are noted.

THE SAMPLE DASHBOARD

For illustration purposes, suppose your company has implemented a balanced scorecard with the goal of increasing sales of its line of brick. In addition to enabling you to analyze sales trends, the dashboard will prove useful in management's monthly performance assessment meeting.

The charts provide three views of total unit sales: percentage of sales by representative (pie chart), unit sales by representative and customer category (stacked bar), and unit sales by brick style for a selected sales representative (column chart). The table shows a year-to-date analysis of unit and dollar sales for each sales representative. To the right of the table are Sparklines, one-cell charts available in Excel 2010.

[GRAPHIC 1 OMITTED]

[ILLUSTRATION OMITTED]

SELECTING SCORECARD MEASURES

Suppose one measure of your success is the extent that each sales representative sells the entire product line, not just a few select brick styles, to all three categories of your customers--commercial, government and residential. To monitor this performance measure, you regularly download sales data from your enterprise system as shown in Exhibit 2.

[ILLUSTRATION OMITTED]

If you are familiar with the PivotChart feature, you are aware that only a single chart can be created from a PivotTable. While you can alter the data and chart type to view a revised chart, you can view only one chart at a time. However, using the GETPIVOTDATA function, you can view multiple charts of the same PivotTable data. All the tables and charts in Exhibit 1 were created with GETPIVOTDATA functions that extracted data from just two PivotTables. Using a combo box, you can drill down into the data to instantly display a custom chart showing performance measures for selected items, such as by representative, in this case.

Understanding the value of dashboards created in Excel, let's create the dashboard for our illustrative company and see how this in-depth analysis is performed.

PREPARING YOUR DATA

When working with data tables, you should anticipate that the size of the data table will change. For example, as our illustrative company earns additional sales, the data table will extend beyond its current 140 rows. By formatting the data as a table, Excel will extend the data table range automatically to include the new data. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.