Excel Dashboards and Reports for Dummies. Alexander Michael

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

Читать онлайн книгу Excel Dashboards and Reports for Dummies - Alexander Michael страница 4

Excel Dashboards and Reports for Dummies - Alexander Michael For Dummies

Скачать книгу

Because dashboards are designed with a specific purpose or goal, they inherently contain predefined conclusions that relieve the end user from performing his own analysis.

Figure 1-2 illustrates a dashboard that uses the same data shown in Figure 1-1. This dashboard displays key information about the national park overnight-visitor stats. As you can see, this presentation has all the main attributes that define a dashboard. First, it’s a visual display that allows you to quickly recognize the overall trending of the overnight-visitor stats. Second, you can see that not all the detailed data is shown here – you see only the key pieces of information relevant to support the goal of this dashboard, which in this case would be to get some insights on which parks would need some additional resources to increase visitor rates. Finally, by virtue of its objective, this dashboard effectively presents you with analysis and conclusions about the trending of overnight visitors.

       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 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.

      

Скачать книгу