UNLOCK EXCEL

Powered for accounting and finance

Program

SYDNEY

Sorry, there is no session available in your chosen stream.

Day One

Monday 8 May

Morning
8.00am - 8.30am
Registration and arrival refreshments
8.30am - 9.55am
KN1: Microsoft presents: What's new in Excel

Speaker: Dany Hoter (Israel), Senior Program Manager, Microsoft

9.55am - 10.25am
Morning tea and networking break
Concurrent sessions
10.25am - 11.35am
Data analysis, presentation and visualisation
A1: Best practices for chart data and formatting

Grandma was right, a stitch in time saves nine. Our MVP will show you how spending just a few minutes extra with your data before producing the chart will save you hours of chart agony later on. You will also learn which chart types work best with certain types of data, and how best to format them to make your message clear and simple.

Speaker: Jon Peltier MVP

10.25am - 11.35am
Business intelligence and financial modelling
A2: The power of Power Pivot

Power Pivot can help you master multiple data sources to reveal the hidden gems. You’ll find out how to link multiple tables with no VLOOKUPS and then serve all Tables into a single pivot, custom PivotTable formulas that actually work, disconnected table tricks and more. It’s time to dominate your data!

Speakers: Liam Bastick MVP and Ken Puls MVP

Concurrent sessions
11.40am - 12.40pm
Data analysis, presentation and visualisation
B1: Dynamic charts without code

Charts are the core of dashboards and reports, and often require user choices and inputs. In this eye-opening hour you’ll discover different techniques to make charts dynamic, including:

• worksheet layout
• tables
• named ranges
• formulas

The best thing? No VBA code required.

Speaker: Ingeborg Hawighorst MVP

11.40am - 12.40pm
Business intelligence and financial modelling
B2: The myth of modelling best practices

The secret to creating high-quality low-risk free spreadsheets is simple:

• a structured process centred on building right first-time
• excellent consistent design principles
• a good understanding of bad practices 

You will learn practical steps on how to avoid errors by applying PwC’s tried and tested model-build methodology and their 15 design best practices, and by avoiding the Axis of Spreadsheet Evil.

Speaker: Ian Bennett, Partner, PwC

Lunch
12.40pm - 1.25pm
Lunch and networking break
Concurrent sessions
1.25pm - 2.25pm
Data analysis, presentation and visualisation
C1: Filter tips with no health warnings

Filters come in all shapes and sizes. This session will look at the use of Filters in manipulating data for other purposes, including:

• the benefits and limitations of the original AutoFilter and using Data Filtering within Tables today
• the addition of Filters everywhere via the right click
• extra facilities added by filtering on like values, colour, cell colour, icon, etc.
• Advanced Filter and its many uses in extracting data for further analysis, and its importance in understanding Power Pivot

Speaker: Roger Govier MVP

1.25pm - 2.25pm
Business intelligence and financial modelling
C2: Strategic forecasting 101

This one’s all about how to win friends and influence people! Our MVPs will show you how to build forecasts that operations managers will buy into, such as very simple regression analysis, including:

• presenting easy-to-understand regression analysis charts
• measuring your variances to see if you’re getting better
• standard deviation charts
• run charts
• fan charts
• new built-in forecasting capability

Speakers: Liam Bastick MVP and Jon Peltier MVP

Concurrent sessions
2.30pm - 3.30pm
Data analysis, presentation and visualisation
D1: Getting friendly with pivot tables

Pivot Tables offer a powerful tool to analyse data, from high-level trends to detailed drill-down to thorough insights. If you’ve found them daunting, this step-by-step walk-through, jam-packed with practical advice and real-life scenarios, will have you feeling confident in no time.

Speakers: Roger Govier MVP and Ingeborg Hawighorst MVP

2.30pm - 3.30pm
Business intelligence and financial modelling
D2: The Power Query effect

No more working through the lunch break and late nights at the office.

Power Query helps you import, transform and automate your data processes. In other words, it’ll save you a tonne of time.

Among others, you’ll learn to:

• automate your mundane file clean up tasks
• source data from the web
• unpivot data in a few clicks
• join tables in several ways (with no VLOOKUPS)

Speakers: Ken Puls MVP and Mynda Treacy MVP

3.30pm - 4.00pm
Afternoon tea and networking break
4.00pm - 5.00pm
KN2: Our favourite chart tips and tricks

Fast, fun, informative. Learn the pros’ favourite tips and tricks for quickly and easily creating charts that tell a clear story.

Speakers: Ingeborg Hawighorst MVP, Jon Peltier MVP and Mynda Treacy MVP

5.00pm - 6.00pm
KN3: Drill the developer

This is your chance to pose your most pressing problems to the ultimate expert in Excel. That aggravating issue with your otherwise perfect Pivot Table, the mysterious muddle in your model, or even the feature you’d like to see in the next version of Excel. Don’t be shy, drill the developer!

Speakers: Dany Hoter (Israel), Senior Program Manager, Microsoft

Day two

Tuesday 9 May

Morning
8.00am - 8.30am
Registration and arrival refreshments
8.35am - 9.30am
KN4: Microsoft returns: Moving from regular PivotTables to multi-table …

Speaker: Dany Hoter (Israel), Senior Program Manager, Microsoft

9.30am - 10.00am
Morning tea and networking break
Concurrent sessions
10.00am - 10.55am
Data analysis, presentation and visualisation
E1: Interactive Excel dashboards

How can you best capture your audience’s attention? Create a compelling dashboard that delivers the key points in a single page, with a single glance. Using no additional software or add-ins, just plain Excel, Mynda Treacy MVP will demonstrate how to build interactive Excel dashboards that can be updated in less than a minute.

Speaker: Mynda Treacy MVP

10.00am - 10.55am
Business intelligence and financial modelling
E2: What you need in a model template (and what you don’t)

Do you know what to include in a model template? It’s time to find out. Learn about:

• constructing the page template/s
• setting up time series
• absolute versus relative referencing (yes, seriously!)
• the importance of checks – and the three types you need
• setting up a table of contents in 60 seconds
• formats versus styles

Speaker: Liam Bastick MVP

Concurrent sessions
11.00am - 11.55am
Data analysis, presentation and visualisation
F1: Keeping spreadsheets under control

Is your spreadsheet bigger than Ben-Hur and even more complex to manage? Roger and Jon will help you regain control via the following:

• data validation
• ways of preventing changes upstream in dependent scenarios
• sheet protection pros and cons
• prevention of row and column insertion
• simple VBA event code to provide greater validation
• simple VBA code to allow expansion of tables on protected sheets

Speakers: Roger Govier MVP and Jon Peltier MVP

11.00am - 11.55am
Business intelligence and financial modelling
F2: Spreadsheets: The good, the bad and the ugly

Out, damned errors, out we say! Spreadsheets are probably the world's most successful end-user programming language, widely used in industry. 95 per cent of all US companies use them for their financial reporting. Significant business decisions are often based on information coming from spreadsheets. But we also know that they are error-prone. Should we abandon them? What is causing the errors? Can we fix that? An update on the current state of affairs in spreadsheet research.

Speaker: Bas Jansen, Information Specialist, Delft University of Technology (The Netherlands)

Lunch
Concurrent sessions
12.00pm - 12.55pm
Data analysis, presentation and visualisation
G1: Slicers inside and out

Slicers are filters on steroids. They work to visually filter data by clicking on the type of data you want.

In under an hour you’ll learn how to:

• seamlessly integrate slicers into reports and dashboards
• configure slicers to work with multiple pivot tables
• style slicers to complement stunning visuals
• capture slicer selections in formulas
• write simple VBA code to help automate them

Speakers: Ingeborg Hawighorst MVP and Mynda Treacy MVP

12.00pm - 12.55pm
Business intelligence and financial modelling
G2: Dashboard in a dash and Power BI

It’s time to step outside Excel for powerful data modelling and visualisation. Power BI is an amazing business analytics tool that enables you to create interactive reports and share them with others. Together with Ken Puls MVP, you’ll explore the free Power BI desktop, including how Quick Insights will get you off the ground running and how to dashboard your KPIs.

Speaker: Ken Puls MVP

12.55pm - 1.35pm
Lunch and networking break
Concurrent sessions
1.35pm - 2.30pm
Data analysis, presentation and visualisation
H1: What if I care? Sensitivity and scenario analysis

This session will look at best practices in What-If Analysis in Excel, including:

• the difference between scenario and sensitivity analyses
• why Scenario Manager is useless and why OFFSET with Data Tables is better
• presenting different scenarios in a summary dashboard
• sensitivity analysis with data tables
• creating cool tornado charts

Speakers: Liam Bastick MVP and Jon Peltier MVP

1.35pm - 2.30pm
Business intelligence and financial modelling
H2: Introduction to VBA

VBA is Excel’s programming language, and it’s easier than you think. Learn what it can do and why you should use it, including:

• an introduction to the VB editor
• a simple automation
• invoking code with a button
• using event code to trigger code automatically
• writing code once and using it repeatedly
• the importance of Application settings for speed improvement
• employing arrays to reduce inefficiency

Speakers: Roger Govier MVP

Concurrent sessions
2.35pm - 3.30pm
Data analysis, presentation and visualisation
I1: Advanced conditional formatting

Go beyond data bars and icon sets. In this session you’ll learn how to create amazing user experience with conditional formatting formulas, relative references and pixie dust. Just BYOP (Bring Your Own Pixie Dust).

Speakers: Jon Peltier MVP and Mynda Treacy MVP

2.35pm - 3.30pm
Business intelligence and financial modelling
I2: Business intelligence on the go

Don’t bury reports deep in your company’s system – they can be shared, viewed and manipulated on the go. With the MVPs as your guides, you’ll discover how to:

• create and optimise reports for mobile devices
• view BI reports and dashboards on mobile devices
• link company data with cloud reporting
• share reports with both internal and external users
• set up alerts for targets and KPIs

Speakers: Ingeborg Hawighorst MVP and Ken Puls MVP

3.30pm - 4.00pm
Afternoon tea and networking break
4.00pm - 5.00pm
KN5: Our favourite Excel tips and tricks

Fast, fun, informative. Learn the pros’ favourite tips and tricks to unlock the power of Excel, increase productivity and save time.

Speakers: Liam Bastick MVP, Roger Govier MVP and Ken Puls MVP

5.00pm - 6.00pm
KN6: Ask the experts

For the past two days they’ve amazed and astounded you, engaged and entertained you, educated and extended you. Now the MVPs are ready and waiting to answer all your Excel-related questions. Six MVPs and you – it’s an opportunity not to be missed.

Speakers: Liam Bastick MVP, Roger Govier MVP, Ingeborg Hawighorst MVP, Jon Peltier MVP, Ken Puls MVP and Mynda Treacy MVP

Sectors

  • Data analysis, presentation and visualisation

  • Business intelligence and financial modelling

Disclaimer of liability
CPA Australia reserves the right to alter any part of this program at any time without notice.