The Excel Dashboards Guide: Planning Considerations

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 ?

In the previous Blog post we understood the main aspects and types of Dashboards. In this second of a 4-part Blog post, we start preparing ourselves for the much-anticipated Dashboard.

The Big Picture

The place to start is the business requirements analysis document, we prepared (or received) before we planned and implemented the application. The Dashboards’ elements should already be described right there.

These requirements are a result of a picky interrogation we carried out when we learned from the customer about his business, requirements and expectations. In most cases, the customer would not know which Dashboards’ elements he wants/needs and we help him with that. Compiling a business requirements document is an art/science in itself, to be explored in another time.

One way or another, we first identify all of the dashboards’ elements, of all types.

Next, we consider the ROLE of the person consuming these dashboards. I’d “wear his shoes” to think about the business process he participates in, the resources he manages and the business questions he has on a daily basis. It is very similar to the process we went through with the customer in the analysis phase – we do it again with now, even without the customer.

Taking the user’s role together with the required dashboards’ elements, we decide on the different dashboards and give each a suitable title. This is not necessarily the title to be presented eventually to the user, but rather a title telling us what is the main business purpose of that dashboard. For example:

• Business performance for this month, past quarter and past year, optionally filtered per customer.
• Financial performance.
• 12-months forecast of sales, by sales person.
• Department manager’s view of employees’ training, certifications and competencies.
• Alert on key exceptions in manufacturing, for the production line manager (per line).

Note that some filtering options are already described in this stage, implying dynamic rendering of the dashboard’s elements with selectors placed on top for the user to set.

Serving the Right User with the Right Answers

Next, would be assigning each dashboard element to its rightful dashboard. Sometimes a given element may appear in more than one dashboard.

I keep thinking about the dashboard title as I do it, and ask myself: “how is this element contributing to the business role served by this dashboard?”.

I’m not bothering my mind with questions pertaining to the implementation of the different elements: the data required, the visual layout and design, etc. I’m currently still focusing on serving the business person with the dashboards.

Preparing the Dashboard View

At this point, we’re ready to zoom in one step deeper and arrange the elements in the frame.

The leading thought here is twofold:

1. Layout of the elements in a way that makes sense.
2. The best presentation pattern each element should expose in order to best answer the business question.

The layout is also driven by the business thinking of the user. Am I splitting the frame into two sections by their business purpose? Is there a common thread and business logic that calls to group some elements together? Is it vertically or horizontally? Are there dynamic tables that need some room for expansion (tables in dashboards are typically very short, but still may be dynamic in length)?

I may have a couple of versions of sketches before I’m happy with the result. The following is an example of a dashboard layout I prepared. This time it is not taken from a project I delivered to a customer, but rather the solution of the closing assignment in my online program: Computer Programming and Databases with Excel VBA & SQL.

As you can see, I already determined which elements are dynamic (subject to the filters values at the top). The (all/customer) labels tells me that the data presented will be refreshed as the filters are changed by the user. There are two possible data views in this example: one for a specific customer (if selected) and the other for the company/department (all customers).

Obviously, there can be changes later as you fine-grain the visual effect of the dashboard (more on that in the closing Blog post in this series), but usually it should come pretty close. I know you can’t wait for the last post to see the end result of the above sketch, so here it is:

At this stage, we’re ready to start implementing our beautiful and insightful dashboard – we start with staging the data. All on that – next week.