NTRODUCTION TO DASHBOARD REPORTING - EXCEL

NTRODUCTION TO DASHBOARD REPORTING - EXCEL

1. A dashboard is a visual display of the most important information needed to achieve one or more objectives which fits entirely on a single computer screen so it can be monitored at a glance . INTRODUCTION TO DASHBOARD REPORTING What is a dashboard?

2. What is the purpose of dashboard? What triggered them to want the dashboard in the first instance? Who is the audience? You may want to speck to the people who will be receiving the dashboard about how they plan to use it? What are the KPI’s or performance measure? What is the time period the date should incorporate? INTRODUCTION TO DASHBOARD REPORTING Researching user requirement

3. Lots of important data Any update in your data, automatically updates your dashboard If you are conducting regular repeat analysis and reporting, it will be best to design your dashboard at once and then simply maintain the correctness of your database It provides more flexibility and customization compared to a pivot table It is a one time effort and looks neat, intelligent and impressive! Its an art that will make your boss very happy! SOME PRACTICAL ADVANTAGES OF A DASHBOARD INTRODUCTION TO DASHBOARD REPORTING

4. There are major two types of Dashboard Static Dashboard Dynamic Dashboard TYPE OF DASHBOARDS INTRODUCTION TO DASHBOARD REPORTING

5. On the other hand there are major three types of Dashboard Strategic Dashboard Tactical Dashboard Operations Dashboard TYPE OF DASHBOARDS INTRODUCTION TO DASHBOARD REPORTING

6. TYPE OF DASHBOARDS INTRODUCTION TO DASHBOARD REPORTING

7. Monitor trends Align execution with strategy NOT real-time STRATEGIC DASHBOARDS TYPE OF DASHBOARDS

8. STRATEGIC DASHBOARDS TYPE OF DASHBOARDS

9. Manage performance against preset target Analyze — link results to actions Discover problems & opportunities TACTICAL DASHBOARDS TYPE OF DASHBOARDS

10. TACTICAL DASHBOARDS TYPE OF DASHBOARDS

11. Measure performance Detailed insights / respond as needed Real-time (or almost so) OPERATIONS DASHBOARDS TYPE OF DASHBOARDS

12. OPERATIONS DASHBOARDS TYPE OF DASHBOARDS

13. STRUCTURE OF DASHBOARDS INTRODUCTION TO DASHBOARD REPORTING Data Dashboard An interface Dashboards are used typically for tracking metrics linked to some data

14. This data has a specific structure Data Dashboard An interface For a simple MS Excel Dashboard, it should be in a “list format” Note: It can be in a “tabular” or any other format, but lists are the easiest to handle. *list format ensures unique records *tabular format is generally a 2x2, 3x4 or a column-row combination

15. Example of a “list format” The adjacent data stores records of expenditures For Jan and Feb Across India and Singapore For 5 different line items Each “record” is uniquely represented by { region, month, expenditure } The unique ID of a record is “#” Therefore, Record # 7 is { Singapore, Jan, Audit and Tax fees } = 44 You can Add more records Example: {India, Mar, Rent} = 80 Or add more details to each record by adding another column Example: {#, REGION, MONTH, WEEK, EXPENDITURE} = AMOUNT One can add as many records or details he wishes to.

16. To match our metric requirements we need to filter out the right data Data Dashboard An interface This filtered data can be further processed t o meet our very specific requirements

17. This filtering out and processing is done at the “interface” Data Dashboard An interface A very simple and flexible filter in Excel is the function “SUMIFS()”

18. Table Scorecards Grids Charts Diagrams Gauges Maps Decomp Tree Tree Map COMPONENT OF A DASHBOARD INTRODUCTION TO DASHBOARD REPORTING

19. When to use: ▪ Values are familiar ▪ Need to lookup a value ▪ Small differences are important ▪ Mixed units of measure Design Considerations ▪ Try to keep square- ish ▪ Comparisons are done by column ▪ Limit length with Top/Bottom GRIDS COMPONENT OF A DASHBOARD

20. Highlights Items of interest Data Bars Helpful to read magnitude Color Scales/Icons Can overwhelm the data Arrows Used for change over time GRID EMBELLISHMENTS COMPONENT OF A DASHBOARD

21. Are a special type of grid Created for KPIs and Objectives Can be methodology oriented Balanced Scorecard, 6 Sigma, … Used as a navigation aid A list with data SCORECARDS COMPONENT OF A DASHBOARD

22. Scorecards are less about data and more about process SCORECARDS COMPONENT OF A DASHBOARD

23. BASIC CHARTS COMPONENT OF A DASHBOARD

24. Maps are highly specialized Maps are great for Demographics Useful in showing regional components MAPS COMPONENT OF A DASHBOARD

25. Hard to set gauge scale for the general case GAUGES COMPONENT OF A DASHBOARD

26. Best for continuous process Poor Data-Ink Ratio GAUGES COMPONENT OF A DASHBOARD

27. Diagrams are underutilized Graphical Representations Root Cause Impact /Fishbone Diagram DIAGRAMS COMPONENT OF A DASHBOARD

28. Process Diagram Impact Diagram DIAGRAMS COMPONENT OF A DASHBOARD

29. Balanced Scorecard Strategy Map DIAGRAMS COMPONENT OF A DASHBOARD

30. It isn’t difficult to use report and dashboard interchangeably. In fact, the line between reports and dashboards frequently gets muddied . It is common to see reports inaccurately referred to as “dashboards” just because they include a few charts. DASHBOARD REPORTS VS DETAILS REPORTS INTRODUCTION TO DASHBOARD REPORTING

31. A document that contains data used for reading or viewing. As simple as a data table or as complex as a subtotaled view with interactive drill downs, similar to Excel’s Subtotal or pivot table functionality. Often allow for the end users to apply their own judgment and analysis to the data. DETAILS REPORTS DASHBOARD REPORTS VS DETAILS REPORTS

32. A visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. DASHBOARD REPORT DASHBOARD REPORTS VS DETAILS REPORTS

33. 1. Typically graphical in nature, providing visualizations that help focus attention on key trends, comparisons, and exceptions 2. Often display only data that are relevant to the goal of the dashboard 3. Contain predefined conclusions that relieve the end user from performing his own analysis (given that dashboards are designed with a speci 􀃶c purpose or goal) DASHBOARDS HAVE 3 MAIN ATTRIBUTE DASHBOARD REPORTS VS DETAILS REPORTS

34. EXCEL SKILLS FOR DEVELOPING A DASHBOARD INTRODUCTION TO DASHBOARD REPORTING Charting PivotTables Excel Tables Formatting Formulas Not VBA