Excel Dashboards & Reports For Dummies. Michael Alexander

Чтение книги онлайн.

FIGURE 1-2: Dashboards provide at-a-glance views into key measures relevant to a particular objective or business process.

Preparing for Greatness

      Imagine that your manager asks you to create a dashboard that tells him everything he should know about monthly service subscriptions. Do you jump to action and slap together whatever comes to mind? Do you take a guess at what he wants to see and hope it’s useful? These questions sound ridiculous, but these types of situations happen more than you think. I’m continually called to create the next great reporting tool but am rarely provided the time to gather the true requirements for it. Between limited data 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 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.

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 СКАЧАТЬ