The Excel Dashboards Guide: Orientation

How should a dashboard strategy be conceptualized? How do you translate the accumulated data into an effective business compass for the decision maker? Start your journey here

The importance of Dashboards

Dividing information systems’ role into two main focus areas, we have the operational processes on one hand (OLTP) and the analytics on the other (OLAP).

Considering the ultimate goal of supporting and driving the business, OLTP systems streamline the daily work, govern the processes and control the data as it is being accumulated in the database. OLAP solutions are tasked with revealing the desired business insight to drive business decisions.

The two prominent interfaces available for management are reports and Dashboards. A report is typically a reflection of a situation in a given point in time (a “snapshot”), presented in a static document. We’ll be dealing with reports in depth in another time.

A Dashboard, on the other hand, is a flexible visualization of key metrics (or Key Performance Indicators – KPIs), on a live screen, usually subject to filters and selectors at the disposal of the user.

A well planned and designed Dashboard, geared towards the business, is the ultimate expression of the information system investment and daily operational effort.

As such, the first elements you should envision when compiling the business requirements as you prep towards the development of a solution – are the dashboards. The underlying processes, applications and data – are all designed to serve the Dashboards, or Business Intelligence (BI).

Types of Dashboards

I like to view the different types of Dashboards according to their business purpose.

Alert Dashboards

The purpose of an alert Dashboard is to raise “red flags”, or defined exceptions that require immediate intervention. Some examples that come to mind:

• Open invoices past payment due date.
• An out of order production line.
• Quality lab results outside of normal boundaries.
• Top 5 presales opportunities.

As you can see, every one of the above “flags” implies some human intervention: calling the customer to collect a late payment; resolving production line break; tracking failed quality contributor along the production process; and putting more effort to close the top open deals.

Typically, I’d open the application with the Alert Dashboard as the “home page”. In many cases, it should be the one page open throughout the better part of the day in front of a manager.

The below Dashboard is mixed. The top section, “To Do”, is the Alert Dashboard, while the lower section, “To Know”, is the Performance Dashboard. This Dashboard is unique also in the fact that is serves a sales person as part of his operational work (in the same Excel Workbook), rather than management view.

Performance Dashboards

As the name implies, performance dashboards reflect the performance of the company (or department), along a series of KPI’s. Some KPI’s that come to mind:

• Total sales (this month, possibly compared to same period last year, or previous 3 months).
• Manufacturing capacity.
• Marketing campaigns situation (funnel progress).
• Total trained vs. Pending for training employees.

The following Dashboard presents several sales related KPI’s to management. See how the user can play with the different selectors (orange filtering cells) for immediate change of view.

Trends Dashboards

Trends Dashboards typically present KPI’s along a timeline, either in history (past performance trend) or in the future (forecast).

Most performance KPI’s can be explored along a timeline, to present consistent results, or growth results, or seasonality impact, and the like.

The following Dashboard is a combination of performance (top section) with trends (bottom section). Note the delicate trend lines (dashes) showing the general trend over time (just set this chart property for Excel to calculate and present it automatically for you). Another interesting point in this Dashboard, is how we present vertical “columns” analyzing specific KPI’s, both current (top) and trend (bottom). This layout has meaning in both the horizontal as well as the vertical arrangement of the elements.

Dashboards in Excel

Excel is a perfect tool for laying out Dashboards. You can take advantage of all Excel has to offer in terms of preparing the data, designing the layout, presenting both data and graphical charts, real-time calculation (filters, maybe?) and VBA for acquiring the data from the Database and calculating queries.

In some projects, I’d have Dashboards featured in their own Worksheets (“tabs”) within the operational Workbook, while in other situations I’d have dedicated Workbooks for operations and others serving only Dashboards.

As you could see above, despite the differentiation of Dashboards by type (business purpose), in some situations it is beneficial to combine elements of different purpose in the same Dashboard. This is especially relevant for smaller applications serving a single user, or Dashboards serving operational decision making and not pure management view.

In this Blog post, I elaborate on Dashboards that serve as a trigger to operational actions, kind of bringing the OLTP and the OLAP worlds together in one place.

We will continue exploring Dashboards next week, as we look into planning considerations of Dashboards.