Excel Financial Reporting, Analysis, & Business Intelligence

Course Curriculum

Chapter 1: Getting started

Lecture 1: Adding Power Pivot & Power Query to Excel

Lecture 2: Resources for Excel 2010 and 2013 Version users

Lecture 3: Course data file

Lecture 4: Understanding Data

Lecture 5: Why use GL | Why our GL is in plus and minus values

Lecture 6: Adding data to data model

Lecture 7: Data Modelling

Lecture 8: Adding Power Pivot and exploring it further

Chapter 2: Preparation of Profit & Loss Statement

Lecture 1: Preparing P&L

Lecture 2: Slicing P&L for Countries

Lecture 3: Calculating Gross Profit & EBITDA

Lecture 4: Homework: Calculate Operating Profit, PBIT, & Net Profit

Lecture 5: Calculating Operating Profit, PBIT, & Net Profit

Lecture 6: Adjusting Connections for Slicers

Chapter 3: Getting started with DAX

Lecture 1: Introduction to DAX

Lecture 2: Using Calculate function – Calculate Sales

Lecture 3: Calculating Gross Profit using Calculate function

Lecture 4: Calculating EBITDA & Operating Profit

Lecture 5: Calculating PBIT & Net Profit

Lecture 6: Calculating Gross Profit Margin

Lecture 7: Operating Profit Margin & Net Profit Margin

Lecture 8: Next steps!

Chapter 4: Introduction to next level DAX

Lecture 1: Introduction to the Problem – the default behavior of Pivot Table

Lecture 2: YTD | QTD | MTD

Lecture 3: Minimum and Maximum

Lecture 4: Minimum Date Across the Data

Lecture 5: Calculating the TTD Value

Chapter 5: Preparation of Balance Sheet

Lecture 1: Preparing Balance Sheet

Lecture 2: Calculating Asset Turnover

Lecture 3: Return on Capital Employed

Lecture 4: Return on Equity

Lecture 5: Gearing

Lecture 6: Interest Cover

Chapter 6: Working Capital Ratios

Lecture 1: Homework: Current Ratio & Quick Ratio

Lecture 2: Current Ratio

Lecture 3: Quick Ratio

Lecture 4: Receivables Collection Period

Lecture 5: Payables Payment Period

Lecture 6: Inventory Turnover Period

Chapter 7: Trial Balance | Drill down | Account Transactions or Ledgers

Lecture 1: Preparing Trial Balance

Lecture 2: Drilling down values

Lecture 3: Preparing Ledgers (Accounting Transactions)

Chapter 8: Preparing the Cash Flow Statement

Lecture 1: Revising the Cash Flow Statement Structure

Lecture 2: Defining Cash Flow Statement Structure

Lecture 3: Understanding Value Type in Structure

Lecture 4: Data Modelling for Cash Flow Structure

Lecture 5: Setting up Cash Flow Statement

Lecture 6: Making our measures work for Cash Flow Statement

Lecture 7: Defining measures for positive only and negative only values

Lecture 8: Calculating the Cash Flow Statement Value

Lecture 9: Calculating the Closing Balance of Cash for Cash Flow Statement

Lecture 10: Calculating the Opening Balance of Cash for Cash Flow Statement

Lecture 11: Checking the accuracy of values

Lecture 12: Styling Cash Flow Statement

Chapter 9: Statement of Changes in Equity

Lecture 1: Revising Statement of Changes in Equity structure

Lecture 2: Defining and Understanding SOCE structure

Lecture 3: Data Modelling for SOCE

Lecture 4: Setting up Statement of Changes in Equity

Lecture 5: Making DAX measures work for SOCE

Lecture 6: Defining the SOCE values measure

Lecture 7: Adding Opening Balance in SOCE measure

Lecture 8: Adding Slicers

Chapter 10: Basic Dashboard for cross-country comparison

Lecture 1: Introduction

Lecture 2: Setting up the Financial Performance Dashboard

Lecture 3: Financial Performance Dashboard – Continued

Lecture 4: Financial Performance Dashboard Continued Part 3

Chapter 11: Income Statement Analytics Dashboard

Lecture 1: Introduction

Lecture 2: Preparing Dashboard

Lecture 3: Preparing Dashboard – Part 2

Chapter 12: Balance Sheet Analytics Dashboard

Lecture 1: Section introduction

Lecture 2: Preparing Dashboard

Lecture 3: Adding charts to Dashboard

Lecture 4: Adding charts to Dashboard Part 2

Chapter 13: More ways to prepare analytics | Rolling Values | Moving Average | Average Value

Lecture 1: Introduction to the section – problem statement

Lecture 2: Calculating Rolling Values

Lecture 3: Calculating Moving Averages

Lecture 4: 3 Month Rolling Value and Monthly Moving Average

Lecture 5: Average Value for the Balance Sheet related values

Lecture 6: Calculating Average Value considering all the individual data points

Lecture 7: Previous Period Value and Period on Period Growth

Lecture 8: Calculating Previous Month Values and MoM Growth for P&L Values

Lecture 9: Calculating Previous Quarter Values and QoQ Gowth for P&L Values

Lecture 10: Calculating Dynamic Previous Period Value and PoP Growth for P&L Values

Chapter 14: Horizontal and Vertical Analysis of Income Statement & Balance Sheet

Lecture 1: Horizontal Analysis of Profit & Loss Statement

Lecture 2: Horizontal Analysis of Balance Sheet

Lecture 3: Vertical Analysis of P&L