DAY 1 SESSION DETAILS
Day 1 Keynote: THE FUTURE OF EXCEL – The Excel Dev Team
The Excel Dev Team will discuss the future of Excel.
DAY 1 MODELLING TRACK AM: FINANCIAL MODELLING AND RISK MANAGEMENT
Day 1 Modelling Track am: MANAGING SPREADSHEET CHAOS – Smita Baliga (PwC USA)
The dirty secret: the world is run on spreadsheets. So, how do you manage them? PwC has been remediating spreadsheet risk in some of the world’s largest financial institutions, responding to spreadsheet catastrophes and regulator demands. Learn about the methodologies, best practices and technologies PwC USA has been using with their clients to create sustainable processes to effectively govern the usage of spreadsheets and reduce your risk.
Day 1 Modelling Track 1 am: METHODS FOR TESTING SPREADSHEETS – Félienne Hermans (Delft University Spreadsheet Lab)
Spreadsheets are known to be error prone, but do they have to be? There is ample research showing that error rates in software are very similar to those in spreadsheets. Could we apply lessons from software to make spreadsheets safer? Feliénne explains how in this session.
DAY 1 TRACK 1 PM: FINANCIAL MODELLING AND RISK MANAGEMENT
Day 1 Modelling Track pm: AVOID COMMON ERRORS IN YOUR SPREADSHEET LAYOUT – Liam Bastick (Australia MVP)
Ever seen a key output in a model and wondered whether it was in A$’000, A$m, Euros, kilograms or even sliced dolphins? Consistency, Robustness, Flexibility, Transparency: the principles of CRaFT should be employed whenever building a financial modelling spreadsheet.
This session provides tips on layout and common errors in model layout so that you can set your spreadsheets out just like a pro.
Day 1 Modelling Track pm: BEST PRACTICE MODELLING – Ian Bennett (PwC Australia)
The secret to creating risk free spreadsheets is simple:
(i) a structured processed centred on building right-first-time and
(ii) excellent design principles.
Spreadsheets are very risk-prone, but this session teaches practical steps on how to avoid errors by applying PwC’s tried and tested model build methodology and their 15 design best practices.
You will increase confidence in your decisions and lower your operational risk.
Day 1 Modelling Track pm: THE FAST STANDARD FOR FINANCIAL MODELLING – Andrew Revfeim (F1F9)
The purpose of the FAST Standard is to establish a continuously updated and developed guideline for building financial models that creates a shared global language. These recommendations are founded upon the acronym FAST, demonstrating the belief that models should be flexible, appropriate, structured, and transparent
DAY 1 ANALYST TRACK AM: ANALYSE, VISUALISE AND PRESENT YOUR DATA
Day 1 Analyst Track am: TABLES TIPS AND TRICKS – Zack Barresse (USA MVP)
Turn the tables on Excel! Take this session to build an Excel Tables Master Toolbox for any challenge that Excel throws at you – calculated columns, dynamic ranges, structured referencing – pitfalls, poof!
Day 1 Analyst Track am: DATA VISUALIZATIONS AND CONDITIONAL FORMATTING – Bill Jelen (USA MVP)
Learn how to quickly convert a boring black and white document into a visually meaningful report using icon sets, color scales, data bars and conditional formatting. Move beyond the built-in visualization tools to control settings for each icon. Use formulas to base icons on other cells. See how to use the camera tool to actually make use of the “Three Triangles” icon set to show changes from period to period. Use Sparklines to create word-sized charts that summarize trends over many periods.
DAY 1 ANALYST TRACK PM: ANALYSE, VISUALISE AND PRESENT YOUR DATA
Day 1 Analyst Track pm: AN END TO MANUAL EFFORT: THE POWER QUERY EFFECT – Ken Puls (Canada MVP)
The sad reality is that not all data is stored in nicely curated databases and often – even when it is – the data analyst doesn’t have access. Instead we have to piece together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today.
In this session you’ll learn how Power Query can clean up, reshape and combine your data with ease. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future refreshable with a single click when next needed.
Day 1 Analyst Track pm: BUILDING A DASHBOARD: EXTENDING POWER QUERY – Jon Peltier (USA MVP)
You’ve collected your data from a variety of sources, pieced it together, and shaped it to show the trends of your markets and performance of your company. You’re not finished.
In this follow-up to the Power Query session, you’ll learn how to take the results of a Power Query analysis and turn it into a powerful dashboard. You’ll be able to share your dashboard with colleagues and managers, and your dashboard will show all important aspects of your analysis so business decisions can be made quickly and with confidence.
Day 1 Analyst Track pm (Sydney and Melbourne only): GEOGRAPHIC ANALYSIS WITH 3D MAPS – Bill Jelen (USA MVP)
Plot that pivot table on a map using 3D Maps (formerly Power Map). Fly through your data sets to spot geographic trends. Learn how to color-code, customize, and filter your data sets on the map. Create tours and movies. Move beyond the globe and plot your data on a map of the store or warehouse floor. Visualize where your customers shop.
Day 1 Analyst Track pm (Auckland only): TELL A STORY WITH YOUR DATA – Ingeborg Hawighorst (New Zealand MVP)
Taking charts and dashboards a step further, Power BI combines Power Query, Power Pivot and Power View into a new stand-alone tool. Create stunning interactive dashboards and reports that can easily be shared with others in your organization, and even on mobile devices. This session will walk through the steps involved in taking raw data or an existing Excel data set and turn it into a compelling story that comes alive on the screen.
DAY 2 SESSION DETAILS
Day 2 Keynote: EXCEL EVERYWHERE, COLLABORATION AND BUSINESS INTELLIGENCE – The Excel Dev Team
The Excel Dev Team will discuss the proliferation of Excel across devices and services, and the implications for collaboration and Business Intelligence.
DAY 2 MODELLING TRACK AM: EXTEND YOUR SKILLS
PIVOT TABLE DATA CRUNCHING – Bill Jelen (USA MVP)
Take a deep dive into Pivot Table features that you might have missed. Quickly create a year-over-year reports from daily data. Create a report of the top ten customers with a total of all customers. Control multiple pivot tables from one set of slicers. Use the data model to count distinct. Produce budget versus actual reports from a top-down budget and bottom-up actuals.
LOOKOUT FOR LOOKUP – Liam Bastick (Australia MVP)
Need your models to automatically refer to data elsewhere and finding your formulae slow and clunky? When should you use LOOKUP / HLOOKUP / VLOOKUP / INDEX / MATCH / OFFSET / SUMIF / SUMIFS / SUMPRODUCT / INDIRECT / and / or Pivot Tables? This session will tip-toe through the referencing minefield to suggest which method(s) to use when – and which ones to use never.
DAY 2 MODELLING TRACK PM: MAKE SENSE OF YOUR DATA
THE IMPACT OF POWER PIVOT – Ken Puls (Canada MVP)
Released as a free add-in in Excel 2010, this tool has now been incorporated directly into Excel 2013 and Excel 2016 and is already revolutionizing the way analysts are treating data in their organizations.
By the end of this session, you will understand how powerful this tool is. You will see firsthand how easy it is to tie together multiple data sources, limit the number of formulas needed in a model, extract measurements that were never before possible and know why Excel will easily retain its dominance in the Business Intelligence marketplace.
SCENARIO, SENSITIVITY & SIMULATION ANALYSES – Liam Bastick (Australia MVP)
What if you knew about what-if analysis?
This session explains the difference between a scenario and a sensitivity, and the importance of deterministic or probabilistic modelling, creating waterfall (bridge) charts and tornado charts. Using basic tools such as Data Tables in Excel, attendees will learn how to create simple simulation analysis without spending thousands on expensive third-party software.
A small amount of elementary VBA will be shown to simplify one or two tasks
DAY 2 ANALYST TRACK AM: AUTOMATE YOUR WORKFLOW
HOW TO VBA: STREAMLINING YOUR PROCESSES – Jon Peltier (USA MVP)
VBA is what put the Killer into the Killer App which is Excel. The designers of Excel put a lot of power into it, but the designers of VBA enabled you to turn it up to 11.
You’ll see some of the ways you can streamline your routine activities, saving time, reducing boredom, and eliminating errors that can crop into an oft-repeated manual process. VBA can be used to automate data collection and cleanup, statistical analysis, and chart generation. By automating Word and PowerPoint, Excel can even automate reporting activities that may otherwise take days per month.
MAKE YOUR VBA RUN 100S OF TIMES FASTER – Charles Williams (UK MVP)
The way you write VBA can have a huge impact on speed. I will show you a few simple tricks you can use to make your VBA fast, both for macros and User Defined Functions. You will learn the importance of transferring data efficiently between Excel and VBA, how to call worksheet functions from VBA, how to write efficient UDFs, and how to use arrays to speed up your processing
DAY 2 ANALYST TRACK PM: EXTEND YOUR SKILLS
AUTOMATING DATA – EXCELLING IN AWESOME – Zack Barresse (USA MVP)
Like walking down easy street? Enjoy making things work for you, not against you and your team? This session will make you a Data Automation Maniac. See the potential you have at your fingertips.
HOW TO MAKE EXCEL CALCULATE YOUR WORKBOOKS FASTER – Charles Williams (UK MVP)
Are you tired of waiting for Excel to finish calculating?
At the heart of Excel is an incredibly powerful and flexible calculation engine. I will show you how to find out where the calculation bottlenecks are in your workbooks, and ways of short-circuiting and bypassing these bottlenecks. Learn how to apply the Golden Rules for Faster Calculation, the impact of Volatility and Multi-threading, how to exploit Excel’s smart calculation engine and how to speedup lookups and array formulas.