Excel Dashboards and Reports for Dummies. Alexander Michael

Between limited information and unrealistic deadlines, the end product often ends up being unused or having little value.

      This brings me to one of the key steps in preparing for dashboarding: collecting user requirements.

      In the non-IT world of the Excel analyst, user requirements are practically useless because of sudden changes in project scope, constantly changing priorities, and shifting deadlines. The gathering of user requirements is viewed to be a lot of work and a waste of valuable time in the ever-changing business environment. But as I mention at the start of this chapter, it’s time to get into the dashboard state of mind.

      Consider how many times a manager has asked you for an analysis and then said “No, I meant this.” Or “Now that I see it, I realize I need this.” As frustrating as this can be for a single analysis, imagine running into it again and again during the creation of a complex dashboard with several data integration processes. The question is, would you rather spend your time on the front end gathering user requirements or spend time painstakingly redesigning the dashboard you’ll surely come to hate?

      The process of gathering user requirements doesn’t have to be an overly complicated or formal one. Here are some simple things you can do to ensure you have a solid idea of the purpose of the dashboard.

Establish the audience for, and purpose of, the dashboard

      Chances are your manager has been asked to create the reporting mechanism and he has passed the task to you. Don’t be afraid to ask about the source of the initial request. Talk to the requesters about what they’re asking for. Discuss the purpose of the dashboard and the triggers that caused them to ask for a dashboard in the first place. You may find, after discussing the matter, that a simple Excel report meets their needs, foregoing the need for a full-on dashboard.

      If a dashboard is indeed warranted, talk about who the end users are. Take some time to meet with a few of the end users to talk about how they’d use the dashboard. Will the dashboard be used as a performance tool for regional managers? Will the dashboard be used to share data with external customers? Talking through these fundamentals with the right people helps align your thoughts and avoids the creation of a dashboard that doesn’t fulfill the necessary requirements.

Delineate the measures for the dashboard

      Most dashboards are designed around a set of measures, or key performance indicators (KPIs). A KPI is an indicator of the performance of a task deemed to be essential to daily operations or processes. The idea is that a KPI reveals performance that is outside the normal range for a particular measure, so it therefore often signals the need for attention and intervention. Although the measures you place into your dashboards may not officially be called KPIs, they undoubtedly serve the same purpose – to draw attention to problem areas.

      

The topic of creating effective KPIs for your organization is a subject worthy of its own book and is out of the scope of this endeavor. For a detailed guide on KPI development strategies, pick up David Parmenter’s Key Performance Indicators: Developing, Implementing, and Using Winning KPIs (Wiley Publishing, Inc.). That book provides an excellent step-by-step approach to developing and implementing KPIs.

      The measures used on a dashboard should absolutely support the initial purpose of that dashboard. For example, if you’re creating a dashboard focused on supply chain processes, it may not make sense to have human resources head-count data incorporated. It’s generally good practice to avoid nice-to-know data in your dashboards simply to fill white space or because the data is available. If the data doesn’t support the core purpose of the dashboard, leave it out.

      

Here’s another tip: When gathering the measures required for the dashboard, I find that it often helps to write a sentence to describe the measure needed. For example, rather than simply add the word Revenue into my user requirements, I write what I call a component question, such as “What is the overall revenue trend for the past two years?” I call it a component question because I intend to create a single component, such as a chart or a table, to answer the question. For instance, if the component question is “What is the overall revenue trend for the past two years?” you can imagine a chart component answering this question by showing the two-year revenue trend.

I sometimes take this a step further and actually incorporate the component questions into a mock layout of the dashboard to get a high-level sense of the data the dashboard will require. Figure 1-3 illustrates an example.

       Figure 1-3: Each box in this dashboard layout mockup represents a component and the type of data required to create the measures.

      Each box in this dashboard layout mockup represents a component on the dashboard and its approximate position. The questions within each box provide a sense of the types of data required to create the measures for the dashboard.

Catalog the required data sources

      When you have the list of measures that need to be included on the dashboard, it’s important to take a tally of the available systems to determine whether the data required to produce those measures is available. Ask yourself the following questions:

      ✔ Do you have access to the data sources necessary?

      ✔ How often are those data sources refreshed?

      ✔ Who owns and maintains those data sources?

      ✔ What are the processes to get the data from those resources?

      ✔ Does the data even exist?

      These are all questions you need answered when negotiating dashboard development time, data refresh intervals, and change management.

      

Conventional wisdom says that the measures on your dashboard shouldn’t be governed by the availability of data. Instead, you should let dashboard KPIs and measures govern the data sources in your organization. Although I agree with the spirit of that statement, I’ve been involved in too many dashboard projects that have fallen apart because of lack of data. Real-world experience has taught me the difference between the ideal and the ordeal.

      If your organizational strategy requires that you collect and measure data that is nonexistent or not available, press Pause on the dashboard project and turn your attention to creating a data collection mechanism that will get the data you need.

Define the dimensions and filters for the dashboard

      In the context of reporting, a dimension is a data category used to organize business data. Examples of dimensions are Region, Market, Branch, Manager, or Employee. When you define a dimension in the user requirements stage of development, you’re determining how the measures should be grouped or distributed. For example, if your dashboard should report data by employee, you need to ensure that your data collection and aggregation processes include employee detail. As you can imagine, adding a new dimension after the dashboard is built can get complicated, especially when your processes require many aggregations across multiple data sources. The bottom line is that locking down the dimensions for a dashboard early in the process definitely saves you headaches.

      Along those same lines, you want to get a clear sense of the types of filters that are required. In the context of dashboards, filters are mechanisms that allow you to narrow the scope of the data to a single dimension. For example, you can filter on Year, Employee, or Region. Again, if you don’t account for a particular filter while building your dashboarding process, you’ll likely be forced into an unpleasant redesign of both your data collection processes and your dashboard.

      If you’re confused by the difference between dimensions and filters, think about a simple Excel table. A dimension is like a column of data (such as a column containing employee names) in an Excel table. A filter, then, is the mechanism that allows you to narrow your table to show only the data for a particular employee. For example, if you apply Excel’s AutoFilter to the Employee column, you are building