Excel dashboard reports typically show internal data. But you can easily add public data that managers need.
Here, for example, is a modified version of Report 5 from IncSight DB. In the original version, the entire report showed internal data. But in this version, the nine small charts show web data of interest to Acme’s managers.
- Top Competitors: Row 1 shows actual revenue for Acme’s top competitors: Apple, Dell, and HP.
- Key Economic Data: Row 2 shows key economic data: the key currency exchange rate, the number of employees in its target market, and the Leading Index of Economic Indicators.
- Prices of Key Resources: Row 3 shows trends in the prices of key resources: gasoline, cardboard shipping containers, and electricity.
The data for these nine charts comes from an Excel database workbook, which uses KydWeb to return data in real time from the web. But why do I use an Excel database workbook between the report and the source data?
Where Should the Data Reside?
When you create any Excel dashboard report, you link charts and tables to the underlying data. So the first question is, where should the original data reside?
There are at least three significant reasons for the data to reside in a separate workbook, not in the report itself.
First, it’s much faster, easier, and less error-prone to update a column of data in one table in one worksheet than it is to update data sprinkled throughout a large report workbook.
Second, any number of dashboard reports can use data from the same Excel database. So we only need to update from each source once.
Third, the source data often must be transformed in various ways. For example:
- When data is missing — which can happen frequently — zeros or error values must be converted to #N/A values for the benefit of Excel’s charts.
- Dashboards often display ratios or sums of several data series found elsewhere.
- Because many sources provide data in thousands or millions of dollars or other units, the data must be converted to ones so that ratios and unit-of-measure labels work reliably.
Formulas in the reports can do all of that work easily when they return data from the Excel database.
Therefore, it typically makes sense to use an Excel database workbook to contain dashboard data, including web data.
A Simple Example of a Complex Dashboard Display
Suppose you want a monthly dashboard report to include a chart that shows the long-term trend in your own Gross Profit Margin as compared with the average margin for your top three publicly traded competitors.
Without KydWeb, here’s what you probably would need to do:
- Find a web site that offers more than a few quarters of financial statement data.
- Copy or download the data for each company to Excel.
- Copy the data to one worksheet where you perform the calculations.
- Copy the data to your dashboard workbook for display.
- Each month thereafter, repeat the process as new quarterly data is available.
With KydWeb, you set up your web database workbook to contain formulas that return the data from the web. Then, in your report, you set up formulas to return and summarize the data from the web database workbook. To update the report for a new month, you just change the report date and then recalculate Excel. (That is, you press F9.)
The Key Changes to Your Dashboard Workbook
If you have IncSight DB or have read “Dashboard Reporting With Excel“, this figure should look somewhat familiar. It shows a section of a Figure Data Support (FDS) sheet that supports one chart in the dashboard report.
The original version of IncSight DB, contains only two sections, Actual Raw Data and Target Raw Data. Here, I’ve added an External Raw Data section.
This External section is returning data from the web database workbook, which uses KydWeb functions to get its data from three pages on three web sites. Honestly, I have no idea how the three measures shown could be combined into one value for charting. But if you want to display ratios or per-capita data from a combination of sources, you could calculate your results easily in this section.
The web is a tremendous resource for enhancing our managers’ knowledge about their business environment. One way or the other, try to include interesting and useful web data in your dashboards and reports.
