Applied charts and dashboards series
For more information on the sessions, click on the 'Agenda' tab.
- Differentiate between reports, charts, summaries, and dashboards
- Learn how to use Excel’s tools to set up your data
- Understand how to take advantage of different charts and Excel features
- Develop dashboards to tell stories
Attendees will learn how to design and develop reports and dashboards to present a cohesive story to their stakeholders.
SESSION 1: A Necessary Introduction
This session sets the scene, understanding why a picture tells a thousand words – and knowing which words you should be substituting.
- Four key elements of a successful business
- Importance of narrative
- Framing the story – various models
- Strategic Options Analysis
- Key Driver Analysis
- Problem Statement Approach
- Situation, Complication, Resolution Model
- Differences between a report, a chart, a summary and a dashboard
SESSION 2: Key Tools of the Trade
This session brings together some of the key features and functions in Excel necessary to create the underlying data, charts and other outputs.
- A litany of functions: EDATE / EOMONTH, INDEX / MATCH and other similar functions, SUMIF vs. SUMPRODUCT, LOOKUP
- Why you should never use VLOOKUP and HLOOKUP
- Custom number formatting
- Conditional formatting
- Interactivity using OFFSET, TEXT and other useful tools
SESSION 3: Turning Data into Information
How do you convert masses of data into key insights and other useful information? This looks at some of the professional tips of using Excel to assist you.
- Working with Tables
- Using PivotTables
- Working with PivotCharts
- Slicers vs. filters vs. FILTER
- Quick charts, Sparklines and Analyze (sic) Data
- Microsoft’s Recommendations
SESSION 4: Chart Basics
A picture tells a thousand words – if it’s the right story and the right picture. This session considers key charting issues.
- Say it with charts
- Don’t say it with charts: bad charts
- Chart tips and tricks
- Importance of colour
- Importance of quantities
- Importance of placement
- Which chart when and why
- Chart Data Tables: positioning and / or hiding data
SESSION 5: Key Charts
Many charts are easy to add and play with. Let’s ignore those. Here, we consider other key charts that are worth considering, but harder to construct.
- Tornado charts and Data Tables
- Waterfall charts – worth reconsidering
- Bullet / multiple bullet charts
- Broken charts
- Conditional charts
- Legend tricks
SESSION 6: Interactive Dashboards
This final session draws on topics throughout the rest of the series, highlighting interactivity and impact.
- Differences between a report, a chart, a summary and a dashboard revisited
- Positioning and other layout considerations
- Interactivity tricks:
- Data validation
- Drop down boxes
- Text boxes
- Dynamic ranges using OFFSET
- Dynamic arrays
- Chart Title commentaries and the TEXT function
- Brief consideration of Power BI
Liam has over 30 years’ experience in financial model development / auditing, valuations, M&A, strategy, training and consultancy. He has considerable experience in many different sectors (e.g. banking, energy, finance, media, mining, oil and gas, private equity, real estate advisory services, transport and utilities). He is a regular contributor to the Chartered Accountants Australia and New Zealand (CAANZ), Certified Practising Accountants Australia (CPAA), the Chartered Institute of Management Accountants (CIMA), the Institute of Chartered Accountants in England and Wales (ICAEW), the American Institute of Certified Public Accountants (AICPA), Finance 3.0 and various LinkedIn specialist discussion groups. He is a regular contributor to FM Magazine, Insight, InTheBlack and CFO Innovation magazines, and presents regular webinars for CAANZ, CPA and ICAEW. He has also been a guest writer for several valuations and actuarial faculties worldwide, specialising in probability and simulations analysis. He is recognised as a Most Valuable Professional (MVP) in Excel by Microsoft, one of only c.70 appointed globally and one of only several specialising in financial modelling. He has authored several books recently: An Introduction to Financial Modelling, Excel Insights, Power BI Insights and the upcoming Continuing Financial Modelling.
Sub-Topic: Business Reporting, Financial Reporting, Presentation
Format: Recorded Webinar
Proficiency Level: All
CPD: Upto 9 hours