Data Analysis and Dashboard Reporting in Excel

Discover how to "tame" your data and make it work for you!  

With the huge amount of data available to us every day, a well-built and designed dashboard reports is one of the best ways to interpret and communicate large quantities of information. Learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs. 

Many of us struggle to make sense of all the data that is available in this information age.  With such a deluge of reports and statistics which don’t mean anything or can’t be made sense of, it’s no wonder that data is often misinterpreted or not communicated at all.  Learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs.  Covering dashboard reporting, balanced scorecards, visual design of charts and tables, we will also discuss the new charting and analysis features of Excel 2013 and 2016.

This course is very hands-on and practical.  Each participant will be expected to create their own reports, charts and models utilising the tools and techniques covered during the course.

By the end of the day, participants will:

1. Understand the principles of data analysis
2. Learn to synthesise and summarise information into a logical framework.  
3. Consider when to use a chart or a table
4. Know how to use visual effects to improve their reports and presentations
5. Explore how to summarise, present and communicate data clearly and concisely
6. Learn the principles of good dashboard design and presenting data graphically
7. Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn't know you had!)  

• Tool selection 
  Evaluation of Excel vs. other BI alternatives, including Power Pivot in terms of speed, usability and cost
• What’s new in Office 2016
  Technical differences between versions. Considerations when building data models for users of different versions.
• Shortcuts
  Speed up your data manipulation in Excel with the use of shortcuts
• Tables & PivotTables 
  Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
• Power Pivot
  Demonstration of the Microsoft add-in to Excel, PowerPivot

• Essential Excel tools & functions
  Overview of must-know tools and functions for data analysis such as LOOKUPS, logical and aggregation functions
• Creating a robust formula
  Nest functions together to create succinct and robust formulas
• Working with text
  Using text and concatenation functions to string numbers and text together to show in a dashboard or chart.
• Cleaning your data
  Tools and tricks to clean data before using it to build a report or dashboard

• Working with charts in Excel 2013/16
  Overview of new charting features of Excel
• Creating a Combo or Bubble chart
  Show correlations by charting on different axes and chart types or bubble charts
• Waterfall charts
  Comparison of different methods of waterfall charts, create your own template for future use.
• Charting with dynamic ranges
  Explore various methods for dealing with varying data ranges in chart data sources

• Tables vs. Charts/Graphs
  Deciding the best method for visual display
• Visual Design
  Design principles for effective graphic data presentation
• Which chart?
  Choosing the correct chart to use to display different data and messages from your findings
• Charting dos and don’ts
  Tips and tricks for a quicker chart build and was to making charts more robust and automated

• What is a dashboard?
  Purpose and benefits of Dashboards
• Common mistakes when building a Dashboard
  Creating a Dashboard in Excel

• What is a dashboard?
  History and definition of dashboard reports
• Purpose and benefits of Dashboards
  Why dashboards are so popular
• Common mistakes when building a Dashboard
  Critique of poorly built dashboards
• Creating a Dashboard in Excel
  Using provided data, create your own dashboard in Excel

• Principles of dashboard design best practice
  Dashboard layout, colour choice and display   principles
• Selecting the correct chart to display data
  Getting your message across most effectively by choosing the correct medium of display
• Adding icons and images to dashboards
  Quick and easy ways to add automated colour and icons to your reports
• Effective use of colour and logos
  Ways to make an impact with colour
• Making dashboards dynamic
  Save time by using clever formulas which automatically update information on the dashboard

Danielle Stein Fairhurst, Principal Consultant at Plum Solutions,  is an MBA qualified business professional with many years' experience as a financial analyst.  She is the author of "Financial Modeling in Excel for Dummies", May 2017 and  "Using Excel for Business Analysis: a Guide to Financial Modelling Fundamentals", Revised Edition, John Wiley & Sons, April 2015.   With her talent for financial modelling and professional approach, she helps her clients create meaningful financial models in the forms of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.

Danielle is regularly engaged as a speaker, course facilitator, financial modelling consultant and analyst. She runs regular training seminars around Australia and globally, and her custom built training courses have been described by attendees as well-presented, neatly structured, informative, practical, and extremely relevant to their everyday needs. She is available for presentations, workshops, training courses, lectures and seminars. Topics include Financial Modelling in Excel, Budgeting & Forecasting and Data Analysis & Reporting.

She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management, and has taught management accounting subjects at Sydney University. Danielle lives in Sydney with her husband and two children, and enjoys the challenge of balancing a healthy family lifestyle with her passion for business.