SPECIALISTS IN FINANCIAL MODELLING

With the huge amount of data available to us every day, a well-built and designed dashboard report is one of the best ways to interpret and communicate large quantities of information. Using new Modern Excel tools, 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 using Excel or Power BI.

Dashboard Reporting in Modern Excel

What is Modern Excel?

Any version of Excel released from Excel 2010 onward is referred to as “Modern Excel” because it introduced new tools such as Power Pivot, Power Query and Power BI. The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable! Many of us struggle to make sense of all the data that is available in this information age and 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.

Understanding how Modern Excel tools can help analyse data and create dashboards to communicate your message is a critical skill for Excel users today.

Prerequisites

The course is designed for users who do use Excel on a regular basis and are comfortable with using its tools and functions.  At minimum, it is assumed that participants will know how to:

  • Navigate confidently in Excel
  • Create and use Excel tools and functions

Prior knowledge of Power Pivot, Power Query or Power BI is not required

Who should attend this course?

This course is aimed at managers and other professionals who need to create dashboards and other reports in order to analyse interpret and present information.  It is expected that students should have had some exposure to business, finance and reporting principles. This course builds on students’ existing knowledge of Excel tools and functions and incorporates these into complex and dynamic reports and dashboards.

Delivery Format

This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.

A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.

All sessions are recorded, and participants can access recordings for 12 months following the course.

At each step during the course, participants build and practice each formula, tool and technique and will build their own dashboards to take away for future use. 

Software used

You must have a Windows laptop or workstation (not Mac) with your preferred version of Excel installed; Excel 365 is recommended to get the best from this training. Please load Power BI Desktop (free install) prior to the course. A good Internet connection is required, along with a quiet room, microphone and webcam (preferable).

Learning Objectives

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. Participants will:

  • Understand the principles of data analysis
  • Learn to synthesise and summarise information into a logical framework.
  • Know how to use visual effects to improve their reports and presentations
  • Explore how to summarise, present and communicate data clearly and concisely
  • Consider the principles of good dashboard design and presenting data graphically
  • Evaluate the new Modern Excel tools available for data analysis and dashboards
  • 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!)

DAY ONE (3 hours + clinic)

Data Visualisation for Dashboards

  • Turning data into information
  • Debunking the big data myth
  • Presenting financial data
  • How dashboards combat information overload
  • Find the story. Tell the story.

Data Analysis Tools

  • What’s new in Excel 2019 / Excel 365?
    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 Excel Tools

  • Introduction to Power Pivot, Power Query and Power BI
    Evaluation of Excel vs. other BI alternatives in terms of speed, usability and cost
  • Data modelling
    building relationships between multiple tables
  • Get and Transform your data
    extracting and cleansing data with Power Query
  • Power Pivot
    introduction to data warehousing
  • Dashboarding with Power BI
    creating visuals in Power BI Desktop

Using Excel as a Data Analysis Tool

  • Essential Excel tools & functions
    Overview of must-know tools and functions for data analysis
  • Creating a robust formula
    Nest functions together to create succinct and robust formulas

DAY TWO (3 hours + clinic)

Practical Dashboard Building 1

  • Building a dashboard using standard Excel, PivotTables and PivotCharts
  • Preparing data using Power Query and then building a dashboard in Excel
  • Building, modelling and sharing reports in Power BI

Charting & Table Design

  • Working with charts in Excel 2019 / 365
    Assess the use of waterfalls, treemaps and sparklines
  • Data visualisation for visual analytics
    Show your message such as correlations by charting on different axes and chart types
  • Tables vs. Charts/Graphs
    Deciding the best method for visual display
  • Selecting the correct chart to display data
    Getting your message across most effectively by choosing the correct medium of display

DAY THREE (3 hours + clinic)

Dashboard Reporting & Design

  • Purpose and benefits of Dashboards
    Why dashboards are so popular
  • Common mistakes when building a Dashboard
    Critique of poorly built dashboards
  • Principles of dashboard design best practice
    Dashboard layout, colour choice and display principles
  • 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

Practical Dashboard Building 2

  • Using your own data, or examples provided, we spend the last part of the course creating dynamic dashboards using Excel, Power Query, Power Pivot or Power BI incorporating the tools and techniques discussed during the course.

Comments from past students

“It was an outstanding course with relevant and dynamic content, tips and shortcuts to creating interesting and informative dashboards.”
“The course covered a wide range of topics related to Excel data processing and modelling.”
“Structured over 3 half days & very professionally run and interesting with proper workshops & exercises.”
“Danielle knows her stuff and imparts with humour; Susan administered excellently.”
“Great introduction to Power BI – I feel more likely now to be able to progress setting up monthly reports in Power BI and export to pdf for distribution.”
“I liked that the course didn’t just take us through the technical aspects of how to build a dashboard, but also included info such as which chart type to use, how visual information is interpreted, dos and don’ts and simple, understandable dashboards vs bad ones.”

DOWNLOAD THE COURSE OUTLINE


Session Times

Tuesday 10th – Thurs 12th November
3 x 3 hour sessions
plus clinic each day

3-6pm Sydney
2-5pm Brisbane
12-3pm Perth
12-3pm Singapore
5-8pm Auckland
8-11am Dubai
4-7am London
11pm-2am (-1) New York

PRICE is AUD$445 plus GST (approx US$315)
GST is only applicable to Australian residents

Take this Course