Responsive image

Analytics and Dashboards using Modern Excel (Power Query and Power Pivot): A Practical Programme for Accounting and Finance Professionals

Expert Speaker Simon Lindley and Elaine Chong

$663.40 (including GST)

Download
Brochure

Overview   

This practical workshop introduces the capabilities and techniques of “Modern Excel” (Power Query and Power Pivot), what they can do and why everyone using traditional Excel should understand and learn at least the basics of this new way of working.

Hands-on practice will be provided, and participants will get to work through a case study starting from raw data to producing and analysing the company’s sales transaction data using Power Pivot tables, Power Pivot charts and building an analytical dashboard. 

Highly relevant to the changing role of finance and to the growing importance of analytics, this workshop will allow you to practice hands-on the application of ideas using Modern Excel.  Many of the ideas and principles learnt such as tidying data, data modelling using relationships, creating analytics dashboards are also relevant to help participants in understanding and contributing to wider analytics projects even if based on other software solutions.

The workshop will provide you with a strong foundation to build on after the session.  It will also include a ‘showcase’ of what is possible using more advanced techniques and how skills learnt are directly transferable to use with Microsoft Power BI.

What you’ll learn

  • Changing role of the finance function and the shift from reporting to analytics
  • The need for ‘Tidy data’ for modelling and the best way to get it
  • Excel as the problem and possibly the solution?
  • Analysing transaction level data - more than traditional Excel sheets can handle
  • Updating for data changes with a single click
  • Avoiding the need to build a single data table to Pivot from - no need for vlookups!
  • Pivoting and analysing data across multiple tables/ dimensions using relationship modelling and Power Pivot
  • Introduction to analytical dashboard design
  • How to create reports and analysis that are not limited to a Pivot Table/ Pivot Chart format
  • Skills and knowledge transfer to Power BI and to systems thinking for other projects
  • Be aware of more advanced techniques you can build on after this workshop

Programme Outline 

1. Why change?  The changing role of finance and the need for new tools and techniques

2. The raw data problem and 3 ways to fix it (manual, bridging, scripting)

3. Power Query (“Get & Transform”) - Extract, Transform and Load data 

4. Power Pivot data modelling - connecting data without vlookups and ‘fat tables’

5. Power Pivot tables - Pivot tables taken to the next dimension

6. Analytical dashboards - using Timelines, Slicers, Power Pivot Charts to analyse and explore

7. Beyond the basics - what else can Modern Excel do (intro/ demo to more advanced techniques and methods beyond this one day course)

8. Next steps - how to develop your skills after this workshop

In order to participate in the hands-on Excel section of this workshop, you will need to bring along a laptop with a working version of Excel Power Pivot installed and activated.  

Power Pivot working add-in

Please check that you have “Power Pivot” activated (check under Excel Options - Add-ins - it should be included under the Active Application Add-ins list, if not add it under the Manage COM Add-ins selection and Go button at the bottom).  Please note that Excel 2010 is the earliest version of Excel which will support Power Pivot and if it is not already installed you will have to download it - go to the Microsoft Download center website and search for “Power Pivot for Excel 2010”, follow the instructions to download and install.

Power Pivot builds on the capabilities of basic Pivot tables.  This workshop will assume that users already know how to create normal pivot tables and manipulate these by dragging fields into Filters/ Columns/ Rows/ Values.  

This workshop will be capped at a manageable class size, as it requires hands-on application and supervision by the Trainer.

Target Audience

Accounting and finance staff currently creating reports/ analytics using ‘Traditional Excel’ i.e. typical Excel sheets and formulae, rather than ‘Modern Excel’ techniques of Power Query, Relationship modelling and Power Pivot.  

Managers and team leaders who may not need to prepare the information - but want to understand the potential of these new techniques for faster reporting, more powerful and flexible analytics - allowing users to do more of their own investigation and analysis.  

Expert Speaker

Simon Lindley and Elaine Chong 

Simon is an international trainer, speaker and consultant in business transformation through analytics. He brings expertise in visual analytics and dashboard design to transform the role and performance of finance teams. 

Simon is a Chartered Accountant, Chartered Engineer (IT) and MBA with a Masters Diploma in corporate governance. He has also training and qualifications in statistics and data science.

Elaine is an international speaker, public and corporate trainer in business analytics and best practice reporting using Excel. She has extensive experience and numerous awards and recognition in this area. Her training and inspiration has led many of her students to make dramatic improvements in their performance and careers. 

Elaine is a Chartered Accountant, MBA and Certified Master Trainer.

In addition to training and public speaking on the subject of analytics, both trainers work as consultants helping clients by designing and building reporting tools/ analytics models and dashboards using traditional/ modern Excel and Power BI.

Other events you may be interested in...

Prices shown are in SGD and inclusive of 7% GST but do not include handling & shipping (if applicable)
Copyright © 2016 Wolters Kluwer