Masterclass: “Data Modeling Using Power Pivot”

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

Course Overview

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: Mastering Financial Modelling using Modern Excel

Lector: Tony De Jonker, Netherlands – Microsoft MVP, the principal of De Jonker Consultancy & AlwaysExcel,

Course Overview

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.

Course Outline

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

• 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

Who should attend?

• Business & Finance Analysts
• Finance Managers
• Investment Managers
• Investment Analysts
• CFO’s
• Financial Modellers
• Project Managers
• Project Accountants
• Investment & Corporate Bankers
• 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.