Masterclass: “Master Your Data Using Power Query”
Lector: Ken Puls, Canada – Microsoft MVP, President of Excelguru Consulting
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 course, you’ll learn how Power Query can clean up, reshape and combine your data with ease – no matter where it comes from. 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.
If you need to learn one skill in Excel today, how to work with Power Query is it. Not only will it change the way you “Get & Transform” data in Excel, but it’s also the system used to collect data for Power BI desktop, meaning these skills are transferable to other programs.
Power Query is built in to Excel 2016. For Excel 2010 and 2013, you’ll need to ensure that you have the free Power Query add-in installed. *
* Note that depending on your version of Excel 2013, you may not be able to connect to some “business” data sources like Microsoft Exchange, SQL Azure or SharePoint. All other data sources demoed in the course are available regardless of the Office 2013 version you have.
Course at а Glance
Review of Essential Tools
- The blueprint of “good data”
- Working with Excel tables
- Working with PivotTables
- Pivot Table layouts and formatting
- Individual CSV, text and Excel files
- Individual Non-delimited text files
- Importing multiple “flat” files at one time
- Cleaning and manipulating data
- Refreshing imports
Appending and Merging Tables
- Append (stack) data from multiple tables
- 7 ways to merge (join) data from multiple tables (with no VLOOKUPs)
- Many to many merges
Pivoting, Un-Pivoting and Transposing Data
- Un-pivot tables with ease
- Pivoting stacked data
- Understanding the Transpose feature
- Un-pivoting subcategorized data
- Grouping data
- Creating conditional columns
- Manual IF and IFERROR tests
- Creating columns from example
- Query structuring
- Query folding
Masterclass: “Data Modeling Using Power Pivot”
Lector: Gašper Kamenšek, Slovenia – Microsoft MVP, owner of Excel Olympics
Pivot tables have always been the key reason why data analysts choose Excel. So in all their awesomeness two key shortages have lingered in the background.
- Analyzing data from multiple tables in one Pivot Table (could be done but only if certain conditions were met);
- Analyzing datasets with more than 1.000.000 rows (there was OLAP, but that was a sort of “what you see is what you get” deal);
Then in 2009 Power Pivot came around and it was a game changer. All of a sudden the user could create data models with multiple data sources and calculate “time intelligent” calculations and running totals with ease and then include all these calculations in a single(!) pivot table.
So this course is all about Power Pivot and empowering attendees with a workshop style learning process by building a Sales Data Model getting data from multiple sources.
Attendees should have Excel Professional Plus (Pro Plus) 2010 or higher. If they use Excel 2016 Pro Plus or 2013 Pro Plus they need no extra installations, for version 2010 Pro Plus Power Pivot Add-Inn for Excel 2010 will need to be installed.
Course at а Glance
- Importing Data From Multiple Sources (Excel, Access, Web, Txt or CSV)
- Fact Tables and Dimensions
- Creating Connections in our Data Model
- Calendar Table
- Sort By Column
- Data Types
- Hide what you don’t need
DAX language and creation of calculations (Measures)
- Basic Calculated Columns
- Basic Calculated Fields (Measures)
- The X functions
- time intelligence
- YTD (Year to Date)
- PY (Previous Year)
- PM (Previous Month)
- Functions for filtering data
Advanced Data Modelling Techniques
- Disconnected Slicers
- Managing Calculations
- New Calculations connected to Disconnected Slicers
- Change Pivot Tables to Functions
- CUBE functions
- OLAP Tools in Excel
Sales Data Model
- Comparing Actuals to Plan (Forecast)
- Actual vs. Budget
- Comparing Actuals to previous periods
Masterclass: Invincible! Excel & Power Query in the Real World
Lector: Oz du Soleil, USA – Microsoft MVP, Excel Trainer, Data Management Consultant
This master class will be a journey through real life situations where uncooperative data was brought under control through strategic use of both native Excel and Power Query. We’re going to do some stuff!
We’re going to transform data and build models, weaving together what we know about Excel and Power Query BUT inside real-world constraints where we must think ahead and plan our moves.
- OVERVEW OF POWER QUERY
- NATIVE EXCEL vs POWER QUERY
- A much as much of a blessing Power Query has been, there are still some things that are easier to do in native Excel. We’re going to compare some differences because they’re critical to developing effective and sustainable strategies.
- BASIC M-CODE
- One-Time Solutions vs. Automated Solutions
- To Import or to Copy-Paste
- Case Study #1, #2, #3…
- SOFTWARE REQUIREMENTS: Power Query is built in to Excel 2016. For Excel 2010 and 2013, you’ll need to ensure that you have the free Power Query add-in installed.
Video teaser for the Masterclass:
Masterclass: Mastering Financial Modelling using Modern Excel
Lector: Tony De Jonker, Netherlands – Microsoft MVP, the principal of De Jonker Consultancy & AlwaysExcel,
This course is designed to help you build a robust and dynamic Cashflow Forecast as well as a fully integrated Financial model containing a projected Balance Sheet, Income Statement and Cashflow Statement confronted with Actuals. A well-designed financial projection model can be invaluable for providing estimates of an organization’s future financial condition given certain performance assumptions. It allows business users to improve the reliability, quality and timeliness of their decision making. Financial Modelling techniques can be used in many different areas, such as Capital Budgeting, Business Valuation, Financial Reporting, Analysis, Budgeting and Forecasting.
Traditional financial modelling is normally geared towards building a stand-alone model without merging the projected numbers with detailed Actuals. Furthermore, you might be dealing with a multitude of companies or departments that you want to consolidate. Traditionally, these types of models contain too many unique formulas, which make them hard to grasp and maintain.
Mastering Financial Modelling helps you create a flexible model with a minimum number of unique formulas by using Modern Excel features, such as Tables, Power Query and the Data Model. You will discover how to present custom Financial Reports and combine them with the outcome of different scenarios.
Principles of Financial Modelling
- Objective of Financial Modelling
- The direct method versus the indirect method of forecasting
- Best Practice Principles
- Typical layout, structure and flow of a suitable financial model
Creating a dynamic Cashflow Forecast
- Basic structure of the model
- Setup of a flexible timeline – months versus weeks
- Construct timing flags to indicate the occurrence of events and allow for timing flexibility
- Forecasting customer amounts using average days sales outstanding
- Forecasting vendor payments using payment terms
- Forecasting periodical payments
- Forecasting personnel expenses
- Forecasting loans
- Collecting forecast data
- Constructing dynamic custom Cashflow Forecast and dashboar
Creating an integrated Financial Model
- Setup of the Chart of Accounts as the heart of financial reporting
- Capturing assumptions in tables and parameters
- Input of Actuals in Trial Balance Format
- Projecting sales/revenues
- Projecting cost of sales
- Projecting operational costs
- Projecting CAPEX and depreciation
- Projecting debtors and creditors
- Projecting interest and loans
- Converting projections into journal entries
- Adding Actuals and Projections to the Data Model
- Comparing Actuals with Projections and dealing with different granularities
- Creating Balance Sheet, Income Statement and Cashflow Statement
- Consolidating multiple entities
Sensitivities & Scenario Management
- Identifying key variables for sensitivity
- Building in sensitivities
- Displaying the results of sensitivity analysis
- How to run multiple sets of inputs through a single calculation engine
- Building, managing and extending a scenario manager with a bit of VBA
Who should attend?
- Business & Finance Analysts
- Finance Managers
- Investment Managers
- Investment Analysts
- Financial Modellers
- Project Managers
- Project Accountants
- Investment & Corporate Bankers
- Corporate Finance Managers
- Finance Professionals
You will be required to bring a laptop computer with Microsoft Excel Professional Plus 2013 or higher and should be familiar with general accounting concepts as well as basic Excel.
You receive full course notes plus data of practical templates, tools & solutions that can be used to efficiently build effective and robust financial models.