Using Dashboards For a Real-Time View to Productivity

The information age has changed how we all gather, organize, locate and consume information. Devices like the Amazon Kindle (which launched less than five years ago) and the iPad (which was released slightly less than two years ago) have made massive changes to the publishing and computer industries.

While there is no substitute for timely, accurate, historical, GAAP-based financial statements as an indicator of business performance, many business owners are using graphical, on-demand, real-time summary reports to direct their daily efforts. These one-page reports, also known as Dashboards, are becoming more commonplace and represent a significant engagement opportunity for QuickBooks ProAdvisors®, QuickBooks® consultants other any other accounting professional.

Figure 1 – The Company Status Report in QuickBooks is an example of a customizable dashboard report

Dashboard reports are very different from traditional accounting reports in their format, focus and preparation. A dashboard presents a wide range of reports from numerous financial and operational sources on a single page, and uses graphs and tables to summarize a wide array of data. The performance measures in a dashboard are updated frequently, and should represent the best information available at the time the report is prepared. The real-time reporting of a wide array of financial measures from numerous data sources requires accounting professionals to release data that has not been reconciled and analyzed as part of a month-end financial reporting process. Routines and processes may need to be created to verify the raw data against expected amounts so that users do not made decisions based on flawed data.

Many dashboards, such as the Company Status Report from QuickBooks Enterprise Solutions 12.0, are included as part of a larger application. These reports can be customized with a limited set of options, and are generally linked to a single software application such as QuickBooks, SalesForce.com or industry-specific business management tools. Although the initial setup of the prepackaged reports is very easy, most of these tools do not allow users to calculate or present their own performance measures from other software packages.

The lack of functionality in many of these tools drives users with more sophisticated needs to third-party tools like QuickBooks Statement Manager and Microsoft Excel, Profitably, SAP Crystal Reports, or business analytics packages like SAP Crystal Presentation Design (formerly known as Xcelsius Present). The Intuit® Marketplace also includes a wide range of custom reporting tools and solutions that can be reviewed, and QuickBooks Enterprise Solutions includes a Custom Reporting solution which allows sophisticated users to pull data directly from the QuickBooks database. Although these tools are capable of gathering, summarizing and consolidating a wide range of data, the reports typically use advanced tools like report writers and ODBC drivers. Adjustments to business processes and end-user training are frequently needed to obtain consistent, accurate, reliable results from reports created with these tools.

Figure 2 – Microsoft Excel can be used to create dashboard reports

While a complete discussion of the tools and techniques needed to create custom dashboard reports is beyond the scope of this article, there are five steps common to most dashboard reporting projects:

Step 1: Identify Key Performance Indicators and Benchmarks
Step 2: Locate and Extract the Required Data
Step 3: Update, Organize, and Present the Data
Step 4: Document the Data Sources and Processes
Step 5: Review and Revise the Dashboard(s) as Needed

Step 1: Identify Key Performance Indicators and Benchmarks

While most dashboards will contain information from financial management applications like QuickBooks, the most important performance measures may require data from other applications or tracking systems. Important benchmarks that are used to evaluate the organization’s progress toward its strategic goals are also called Key Performance Indicators, or KPIs. For example, a physician practice might track the number of patients seen, the mix of third-party payers for a period of time, accounts receivable summary statistics, collection activities by provider and referrals to other specialists. A dashboard for this type of medical group might combine data from financial systems such as QuickBooks, with information extracted from a medical billing system, reports from external collection agencies and an electronic medical records system. While many medical practices will use similar metrics, each organization will select different KPIs based on their industry, market segment, strategic objectives and management style.

Some common key performance indicators include:

  • Customer and transaction counts
  • Average transaction amount
  • Percentage of transactions which include high margin add-ons
  • Employee headcount and turnover
  • Average gross margin
  • Sales per square foot of selling space
  • Overhead costs per transaction
  • Sales by product type
  • Return on assets
  • Net interest margin
  • Cash balance in operating bank account
  • Upcoming payroll tax deposit due dates

Step 2: Locate and Extract the Required Data

These measures can also be compared to budgeted amounts and historical results, as well as industry averages and surveys from industry trade groups. For example, a physician group might use survey information from groups like the Medical Group Management Association (as shown in Figure 3) to evaluate how its performance measures compared to similar practices around the country.

Figure 3 – A physician production dashboard prepared by a trade group (source: MGMADashboards.com)

After the source for the information is identified, the user must determine how and when the information can be updated. Some metrics will be updated periodically (e.g., square feet of selling space or industry averages), while other measures may require the organization to extract data from other systems for current and historical period. For example, the number of patients seen by a physician each day could be tracked manually and input into a spreadsheet, or it could be extracted from medical billing records using a database query.

Step 3: Update, Organize and Present the Data

Once the performance measures and data sources are identified, management must decide how often the report will be published and updated. Some metrics (e.g., sales) can be updated from tools like QuickBooks, while other statistics may need to be manually updated in spreadsheets or databases. Management will need to evaluate the accuracy and integrity of the related data sources, and test the accuracy of the data sources. In some cases, metrics on a dashboard may be updated using different schedules or sources. Management should be aware of the frequency of the updates, as well as how the amounts compare to other published reports, since these differences may cause an employee or consultant to lose credibility.

Because many different performance measures can be used in a single dashboard, I generally advise users who prepare their dashboards in Excel to prepare only one chart or table per worksheet. Data can be imported from outside sources (e.g., ODBC databases or web queries), and any charts or tables can be created on this worksheet. The information on each sheet can be summarized on a master sheet using tools like the Camera tool and then published to a summary sheet in the same workbook. This summary sheet can be published in a variety of formats (Excel, PDF or HTML/web page) to portals, or simply printed out and distributed to the management team as needed.

Figure 4 – Limiting each worksheet to one table or graph makes it easier to reuse dashboard components for other purposes

Step 4: Document the Data Sources and Processes

Once the initial dashboards are created and adjusted as needed, the creator should document all of the data sources and create procedural checklists that can be used by others in the organization to ensure the consistency of the information in the reports. Database queries and other information sources should be documented, as well as the general flow of information from source records into the finished reports. These documents will be invaluable when training new employees or troubleshooting inconsistent data in future reports.

Step 5: Review and Revise the Dashboard(s) as Needed

After the dashboard is created and used for a period of time, the data sources and performance measures may need to be adjusted. Different variations of the base report may be created for different groups within the organization, and based on changes in the organization’s objectives, may result in tracking and reporting of different KPIs. By organizing, tracking and documenting the flows of information, users can easily make future adjustments to reports without having to completely recreate all measures on the report.

While not every organization needs dashboard reporting, the technique of using these reports to publish a summary of real-time information is gaining widespread acceptance. Common software tools like QuickBooks and SalesForce.com include basic dashboard functions, and tools such as Microsoft Excel can be used to extract data from corporate databases in real time and report information in a variety of formats. Accounting professionals and software consultants may find that these simple, attractive reports may increase the usefulness of accounting information, and also the value of their services to the organizations they serve.

About the Author

Brian Tankersley

Brian Tankersley

Brian F. Tankersley, CPA.CITP, CGMA, is a speaker and author with K2 Enterprises. He has made presentations for 46 state CPA societies, as well as similar organizations across Canada, and has been named five times as a Top Thought Leader in Accounting Technology. He is also one of the authors of the Accounting Firm Operations and Technology Survey, an annual research survey of hundreds of U.S. accounting firms. Brian blogs at www.cpatechblog.com, and is @BFTCPA on Twitter.

See all posts