D1: Dashboard Design for Reporting & Analysis (2 days)

Learn how to build a great dashboard design that communicates information in an easy-to-interpret manner.

COURSE OVERVIEW

Dashboards provide summary views of key business metrics and processes. Communicating meaningful information in an easy to interpret format is the essence of great dashboard design. The power and flexibility of Microsoft Excel makes it an ideal platform for creating dashboards for businesses of all sizes.

  • Learn how to use Excel’s features and functions so that you can work with Excel more efficiently and more effectively
  • Master data capture and handling techniques
  • Increase the speed and safety of using Power Query to extract data directly from databases, accounting systems and other data sources.
  • Understand key data structuring practices to enable your dashboards to be easily updated month after month
  • Harness the power of Pivot Tables and Pivot Charts in creating flexible reports

Top | Back

Expert Facilitator | Run this course In-House | Course Enquiry

PREREQUISITES

This course is perfectly suited to confident users of Excel who are looking to improve their knowledge to an advanced level.

COURSE OUTLINE

Day 1: High-powered Excel for Finance 

  • Valuable time-saving techniques to reduce re-keying of data and increase automation
  • Essential features for your spreadsheets that will make them easy to maintain and use
  • The functions used by experts to increase consistency and reduce data entry
  • Range name techniques that will improve the reliability and readability of your spreadsheets
  • Best practice methods to produce flexible and reliable lookups
  • The hidden shortcuts to many common tasks that can save you hours of time every day
  • Techniques to make your models accurate, reliable and robust
  • Powerful data analysis and visualisation techniques to identify trends and anomalies
  • How and when to apply macros so that you can create powerful spreadsheet models more quickly

Day 2: Dashboard Design Principles

Design principles
  • Essential features for your spreadsheets that will make them easy to maintain and robust use.
  • Advanced interactive chart development.
  • Maximising impact through core layout guidelines.
  • Powerful data analysis and visualisation techniques to identify trends and anomalies
Interactivity

Encourage user involvement and provide multiple views through one interactive dashboard sheet.

Protection

Add protection to interactive dashboards to prevent unauthorised access to source data and workings.

SPECIFIC TOPICS COVERED

Power Query
  • Extract Data directly from CSV, TXT and XLSX files
  • Filter your data
  • Add calculations to your Queries
Tables
  • Creating a Table
  • Table Functionality
  • Flexible (Dynamic) Ranges
  • Using INDIRECT with Tables and Ranges
Pivot Tables and Pivot Charts
  • Creating a Pivot Table
  • Analysis with Pivot Tables
  • Pivot Charts
Conditional Formats
  • Preset Conditions
  • Formula Driven Formatting
  • Modifying Conditional Formats
Charts
  • Best practice for designing charts
  • Insider tricks to produce exactly the chart you need
  • Handling missing data
  • Dynamic charts and interactive controls
Interactive User Controls
  • Drop Down boxes
  • Option buttons
  • Spinners
  • Scroll Bars
Dashboard Design
  • Best practice for designing dashboards, avoiding unnecessary clutter
  • Alignment techniques
  • Colour Do’s and Don’ts
Structure and Protection
  • How to make your dashboard easy to re-use and warn you when new data needs to be added.
  • Protect your work from unauthorised viewing or editing.
  • Considering different distribution techniques.
  • Learn about risks and best practice

| Top | Back

Who should attend?

  • Accountants
  • Business Analysts
  • Finance Analysts

Top | Back

Related Courses

D2 Introduction to the Power of Power BI (1 day version)
Power BI is the biggest Excel news in the last 10 years. It gives Excel users the power to draw information out of multiple sources, link it together then perform calculations on it that can then be sliced & diced any way you want.
D3 Introduction and Exploration of Power BI (2 days version)
This Power BI course covers all of the material in the the one day course (D2) plus goes more in-depth into how to get the most from these amazing utilities: Power Query, Power Pivot, Power BI Desktop
D4 Power Query (Get & Transform) for Excel and Power BI Desktop (1 day)
Power Query is the best thing to happen to Excel in the last 10 years. It gives Excel users the power to draw information out of multiple sources, cleanse and transform that data and load it into Excel, Power Pivot or Power BI Desktop.& It will save you hours of manual data manipulation.

 

Wyn HopkinsFacilitator: Wyn Hopkins

Chartered Accountant (CA), Excel Expert (MS)   | LinkedIn |

Wyn is a Senior Manager with Access Analytic, an Australian consulting company that specialises in providing AMAZING Excel and Power BI solutions to assist companies to grow, control cost and reduce risk.

Wyn gained his Chartered Accountant qualification at PricewaterhouseCoopers in the UK in 2000.

He has extensive Business Analyst experience in FTSE 100 financial services companies (HBOS and Barclays in the UK) plus multinational clients in Perth including Chevron.

Wyn is a highly skilled developer and trainer completing many successful projects in the areas of:

  • spreadsheet re-engineering
  • Power BI
  • management reporting
  • budgeting and forecasting
  • financial modelling

 

Feedback from Previous Participants

Feedback Initials & Position
“Excellent!” B.D., Global Advanced Metals Ltd
Incredibly relevant to what I do, I should have come years ago! Tracy, JR Consulting
This course covered exactly what I was looking to learn. All of the required knowledge was explained clearly. A Birch, Principal, Vantage Performance

Top | Back

Benefits & Inclusions

Practical: Highly experienced facilitators who actively and regularly consult to clients also.
Applicable: apply what you learn to your job to further your career.
Hands-on: the course is designed to provide maximum hands-on experience.
Passionate: Our facilitators love this subject!
Clear: we specialise in translating complex subjects into concepts that are easy to understand.
On-going help: you can always e-mail the facilitator.
CD: includes worked examples, suggested solutions, reference materials, and a large variety of templates that can be immediately applied to kick-start your projects.
Comprehensive course notes.
Networking: meet industry peers and hear how they approach this subject.
Certificate of completion.
… and much more!

Top | Back

Clients who have 3-4 or more people interested in this course normally prefer to run this course on an in-house basis in their offices.

We can bring all the equipment for up to 8 people so all you need is a room!

Benefits

  • Flexibility: we can combine content from multiple courses to create a custom course just for you, add-on a workshop or some mentoring sessions then run everything at the times that suit you.
  • Timing options: if you’re in Perth, we can split courses into half or single days and schedule these at times to suit you
  • No hidden extras: our fixed fee includes all materials and equipment costs for up to 10 participants
  • Cost effective: no extra fees for extra participants (maximum of 8 per session)

In-House Details & Pricing

  • Structure: half-day or full-day sessions
  • Location: all courses are available in-house, many are also available in open sessions
  • Class size: strictly limited to 10 to allow maximum interaction with the facilitator
  • Provided: projector, laptops, manual, CD
  • In-house Fees: contact us today to discuss your requirements

In-house Course Fees

Call us on +61 8 6210 8500 to discuss your requirements or enquire online.

Discount:
A 10% discount applies when booking 10+ full days of training in any 30 day period

Notes:
– We are happy to customise our courses to suit your requirements