Masterclass for Experts: Dimensional Modeling for Excel Pros

22.11.2019 – Microsoft Bulgaria Hall

Lector: Ken Puls, Canada – Microsoft MVP, President of Excelguru Consulting

Ken Puls is Data Platform and Excel Expert, master of VBA programming, Power Query, Power Pivot, Data cleansing and reshaping.

He is blogger, author and trainer with over 20 years of business and financial modelling experience. His passion lies in exploring tools to turn data into information, and teaching others how to benefit from them.

What is Dimensional Modeling and why should you care?

The classic Excel PivotTable did a great job of letting us quickly pivot and slice data for years. There was a ton of logic built into the tool in order to make it easy for end users, but that ease of use had a cost: it didn’t scale to multiple data source tables. And worse, it actually kept you from learning the terms to scale your own knowledge.At the core of every good Power Pivot or Power BI solution is the Data Model. But do you really know how to work with it properly? What your shape your tables should have? When you should split tables up, when you should flatten them, and how to manipulate the data on the fly to do so?

Why should you attend?

Developed by a CPA with real world experience who is also a renowned Business Intelligence expert, this course is intended to teach you the right way to build solid and scalable dimensional models.

You’ll learn key concepts and terminology around data warehousing and dimensional modelling including Facts, Dimensions, Relationships, Schemas, Keys and more. You’ll learn why the PivotTable has been “too helpful”, and how it can impact your ability to extend your data models.

Armed with the background theory on modeling, it’s then time to get your hands dirty with hands-on examples of solving several “many to many” join problems. From composite keys to bridge tables, slowly changing dimensions to flattening snowflakes, you’ll learn which tools and techniques to use and when.

This is an exciting course, as it pairs two of Excel’s most powerful features (Power Query and Power Pivot) together in one place, using each for what they were designed to do. You’ll leave armed with not only the experience, but handy reference cards to apply these techniques to your own data, and to determine “Is this a Power Query job, or a DAX job?”

And the best part of all of this? The material in this course is 100% portable to Power BI as well. You’re not learning for one program, but rather for two!

Who should attend?

Data professionals who are responsible for building business intelligence reports, whether you use Excel or Power BI.

Topics covered:

  • The importance of importing data to Power Pivot with Power Query
  • Review of the core Power Pivot benefit
  • Dimensional modelling terms and techniques
  • Solving common join problems when linking tables
  • Creating calendar tables on the fly with Power Query
  • Linking tables with different date granularity
  • Dealing with slowly changing dimension tables
  • DAX patterns for solving true Many to Many joins
  • Data optimization rules to keep your models performant

Course format

This is a hands-on course. Participants should bring a laptop running one of the following:

  • Excel 2013 Professional Plus or Excel 2013 ProPlus with the free Power Query add-in installed
  • Excel 2016 or higher
  • Excel 365

Prerequisite knowledge

At a minimum, participants must have experience using PivotTables, and should have exposure to Power Pivot is an asset, but not required.

Masterclass for Advanced: “Master Your Data Using Power Query”

20.11.2019 – hotel “Maison” Hall

Lector: Ken Puls, Canada – Microsoft MVP, President of Excelguru Consulting

Ken Puls is Data Platform and Excel Expert, master of VBA programming, Power Query, Power Pivot, Data cleansing and reshaping.

He is blogger, author and trainer with over 20 years of business and financial modelling experience. His passion lies in exploring tools to turn data into information, and teaching others how to benefit from them.

Course Overview

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.

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. *

*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

Importing Data

  • 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

Conditional Logic

  • Creating conditional columns
  • Manual IF and IFERROR tests
  • Creating columns from example

Best Practices

  • Query structuring
  • Query folding

Masterclass for Advanced: “Data Modeling Using Power Pivot”

22.11.2019 – hotel “Maison” Hall

Lector: Gašper Kamenšek, Slovenia – Microsoft MVP, owner of Excel Olympics

Gašper Kamenšek is Excel and Power BI expert, owner of Excel Olympics. Speaker at more than 50 conferences and events, Trainer with more than 900 courses and seminars. When he is not lecturing Excel, Gasper works into various BI projects consisting either of PowerPivot and SQL with Excel or VBA. His passion in Excel made him starts an Excel blog in 2014. It’s called Excel Unplugged.

Introduction

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.

  1. Analyzing data from multiple tables in one Pivot Table (could be done but only if certain conditions were met);
  2. 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.

Software Requirements

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

Data Model

  • 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)
  • CALCULATE
  • 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 for Experts: “My Perfect Data Model in Modern Excel”

20.11.2019 – Microsoft Bulgaria Hall

Lector: Gašper Kamenšek, Slovenia – Microsoft MVP, owner of Excel Olympics

Gašper Kamenšek is Excel and Power BI expert, owner of Excel Olympics. Speaker at more than 50 conferences and events, Trainer with more than 900 courses and seminars. When he is not lecturing Excel, Gasper works into various BI projects consisting either of PowerPivot and SQL with Excel or VBA. His passion in Excel made him starts an Excel blog in 2014. It’s called Excel Unplugged.

Introduction

There have been numerous sessions on conferences lately (and I’ve done a few myself) titled “The greatest Sales report in the world” or “The best DAX formula in the world”… The problem is that every such view is subjective. What might be the best-looking chart in the world to me, might make no sense to you. I might love VLOOKUP, some of you haven’t used VLOOKUP for years in favor of the INDEX-MATCH combination.

And this is why the title of this Masterclass is not “THE Perfect Data Model…” but is instead “MY perfect Data model…”. This Masterclass will give you insights into the way I use Modern Excel (Power Query, Power Pivot, advanced DAX and advanced Excel functions) to build models that are easy to maintain and are built for best performance and easy scalability.

Software Requirements

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

This Masterclass will be a hands-on Workshop where we will build the Data Model and a report on top of that, and most of all it will be an advanced DAX Masterclass along with a few tips and tricks I use to make things dynamic and reusable. For those of you, that remember last years T1000 measure, this will be the development of that idea but getting the most out of it. This Masterclass requires basic Power Query and Power Pivot knowledge.

During our day together we will start with basic data, do some amazing data transformations with Power Query (so Power Query must be installed on the participant’s computers), load Data directly into Power Pivot, where we will build a data model and write all the DAX measures we will need. (Please get a good nights sleep before attending as we will dig deep into advanced DAX concepts and functions). Now by this point in the Masterclass, you should already be in love with our data model.

Nevertheless, we will take it a step further and build a report on top of our Data Model so we will all have a nicely wrapped BI solution and a sense of achievement by the end of this Masterclass.

Along with 64 bit Excel, Power Query and Power Pivot also bring a smile and willingness to learn.

Masterclass: Building Better Financial Models

22.11.2019 – MDV Professional Education Hall

Lector: Liam Bastick, AustraliaMicrosoft MVP, Director of SumProduct

Liam Bastick has over 30 years’ experience in financial model development / auditing, valuations, M&A, business strategy, training and consultancy.

Liam has headed Ernst & Young’s modelling team in Melbourne and was a Director in their strategic valuations team in London. He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Italy, Malaysia, Netherlands, New Zealand, Singapore, Switzerland, United States and Vietnam, with many internationally recognized clients, constructing and reviewing strategic, operational and valuation models for many high-profile IPOs, LBOs and strategic assignments.

He is a regular contributor to the Institute of Chartered Accountants in Australia (ICAA), Certified Practising Accountants Australia (CPAA), the Chartered Institute of Management Accountants (CIMA). He is also an experienced facilitator for ICAA.

Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.

Course Overview

Ever burnt the midnight oil trying to get a modelled Balance Sheet to balance?  Unable to reconcile different approaches used to calculate operational Cash Flow Statements?  This course is focused on building models efficiently and effectively.  Our simple process has been adopted by many seasoned professionals without resorting to balancing figures, circulars and macros.

Find out why our most common feedback is, “I wish someone had shown me this when I first started modelling”.

Starting with a blank Excel canvas, this one-day masterclass develops a small, straightforward “three-way integrated financial model” which enables a fool proof method to be demonstrated readily and practised comprehensively.  By the end of the course, attendees will have built a full set of forecast financial statement outputs and will be able to repeat this process and apply it to their own model developments.

Key Benefits

  • Understand the difference between the three primary financial statements and what is meant by “three-way integrated financial modelling”
  • Develop a straightforward approach that can be used for all your financial modelling projects
  • Get the Balance Sheet to balance in seconds
  • Know the difference and be able to reconcile Direct and Indirect Cash Flow Statements
  • Avoid common mistakes and pitfalls.

Course Outline

  • The importance of financial modelling
  • Six stages of financial modelling
  • Key Excel functions
  • Best Practice considerations

Accounting considerations

  • Why accounting is important, and knowing the key differences between tax and accounting
  • Explaining the three primary financial statements and why order is important:
    • Income Statement
    • Balance Sheet
    • Cash Flow Statement
  • Direct versus indirect:
    • Costs
    • Cash flows
  • What does “three-way integrated” mean?
  • Importance of Opening Balance Sheet
  • The wonderful world of control accounts

Laying out a model

  • Best Practice revisited
  • Inputs vs. calculations vs. outputs
  • Constants vs. variables, consistent formulae
  • Importance of space
  • Formats vs. styles
  • Time series considerations
  • Three types of proactive checks
  • Simple hyperlink navigation system

Building the model (case study used throughout the day)

  • Keep it simple stupid using short, no-nonsense formulae
  • The four types of input
  • Internal referencing
  • Importance of copying
  • Key modelling considerations
  • Working systematically without shortcuts
  • Identifying and dealing with work in progress
  • Fixing errors.

Course format

This is a hands-on course.

Software: most versions of Excel may be used for this course, but recommended Excel 2013 or higher.

Masterclass: Financial Modelling Tools for Professionals

23.11.2019 – hotel “Maison” Hall

Lector: Tony De Jonker, NetherlandsMicrosoft MVP, the principal of De Jonker Consultancy & AlwaysExcel,

Tony De Jonker specializes in Financial Modelling, Analysis, Reporting and Training for clients worldwide. He is the founder and presenter of the Annual Excel events such as Excel Experience Day, Excel Expert Class, Excel Summer Boost and Amsterdam Excel BI Summit. Tony offers a range of Excel Business related training courses in Dutch, English and German based on more than 34 years of spreadsheet modelling and more than 42 years of Finance & Accounting experience. He has written more than 120 articles on the use of Excel in Business for the Dutch Controller’s Magazine.

Course Overview

Most Financial professionals spend a lot of time creating weekly and monthly reports. This course will provide them the tools to build better reports in less time by combining new and existing features readily available in Excel. We will demonstrate this by creating a dynamic Cash Flow Forecast and an Actual Time Comparison Report. In addition, you will learn how to add scenario analysis to your models and leverage your presentation through the use of animated charts. The course is set up to be a hands-on workshop to optimize your learning experience.

Course Outline

The course contains the following topics:

Cash Flow Forecast

  • Create a weekly Cash Flow Forecast based on open outstanding Accounts Receivables and Accounts Payable lists
  • Install a dynamic weekly timeline
  • Automatically allocate the amounts to the right time buckets based on business rules for payment and customer payment behavior pattern
  • Consolidate weekly forecasts from multiple entities

Actual Time Comparison Report

  • Import monthly Trial Balances by Country and connect them to your Chart of Accounts
  • Creating a monthly Trial Balance by Country and Report level without any formula
  • Creating a YTD Trial Balance by Country and Report Level without any formula
  • Build in checks & balances
  • Set up  custom Balance Sheet and Profit & Loss Statement added with a slicer by Country using one dynamic single retrieve formula
  • How to deal with negative numbers in your custom reports
  • Comparing current period items with last period in one dynamic chart using one single retrieve formula and one selection box.
  • Storing commentary notes and displaying them on command in your chart.

Sensitivities & Scenario Management

  • Sensitivity
    • Identifying key variables for sensitivity
    • Building in sensitivities
    • Displaying the results of sensitivity analysis
  • Scenarios
    • How to run multiple sets of inputs through a single calculation engine
    • Building, managing and extending a scenario manager with a bit of VBA

Storytelling using amazing charts

Add features to make your charts standout such as:

  • Scroll bar to gradually unveil your data
  • Check boxes to select/deselect choices
  • Morphing Line Chart showing Actuals and Budget in one line with different colors
  • Dynamic annotation call-outs
  • Conditional Formatting in Bar Charts

Who should attend?

  • Business & Finance Analysts
  • Finance Managers
  • CFO’s
  • Financial Modelers
  • Project Managers
  • Project Accountants
  • Corporate Finance Managers
  • Finance Professionals
  • Accountants

Requirements

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.

Course Materials

You receive full course notes plus data of practical templates, tools & solutions that can be used to efficiently build effective and robust financial models.