Excel for Reporting Efficiency

Excel Introduction

• Excel Basics — Introduction
• Excel User Interface
• Entering Data into Excel
• Raw Data Excel Introduction — Download this

Formatting in Excel

• Formatting Rows and Columns
• Control & Shift Keys — Important Shortcut Keys
• Text Formatting
• Formatting Numbers
• Formatting Dates
• Formatting Others

Working with Data in Excel

• Filtering Data in Excel
• Sorting Data
• Find & Replace
• Copy and Paste Data
• Paste Special Function
• Clean, Transform Group
• Text to Column
• Flash Fill
• Remove Duplicates
• Data Validation
• Group and Subtotal Data
• Consolidate Data
• Insert Objects

Formula & Functions Introduction

• Functions & Formulas
• Final Review & Others

Course Setup

• Course Introduction
• Before you Start
• Guidelines
• Project Walk-through
• Project Files

Text Functions Challenges

• 1. Join Text — Create a full name column
• 2. Proper — Change Name to Proper
• 3. Combine Functions
• 4. Trim — Remove Extra Space
• 5. Right Function — Extract Location ID
• 6. Left Function — Extract Employee ID
• 7. Dynamic Extraction Len & Left
• 8. Flash Fill

Date Function Challenges

• 1. DATEDIF - DAYS — Calculate Average Days
• 2. DATEDIF - MONTH — Calculate Months
• 3. DATEDIF - YEAR — Employees Less than 1 year old
• 4. Network Days — Calculate Total Working Days
• 5. YEAR Function — Employees Joined in 2023
• 6. MONTH — Employees joined in March '23
• 7. Quarter — Employees Joined in Q1 '23
• 8. TEXT — Any relieving dates on Sat or Sunday
• 9. How many people relieved in 2022
• 10. MONTH — Number of people relieved in Nov '22
• 11. Quarter — Number of people relieved in Q1 '23

Number Function Challenges

• 1. Number Functions — Employee Working Days
• 2. Number Functions — Labour Budget Summary
• 3. Number Functions — Conveyance Actuals

Lookup Functions Challenges

• Lookup Function Intro
• Problem #1 — Which employee has the highest travel amount
• Problem #3 — Reasons for Vlookup Errors & How to fix them
• Problem #4 — Replace #NA Error with Text Value Not found
• Problem #5 — What is the total Emp Count for Bangalore
• Problem #6 — What is the employee count for Manager Jayan Tharakan
• Problem #7 — How to use XLOOKUP to populate manager name
• Problem #8 — Populate Experience Category
• Problem #9 — Populate Employee Level
• Problem #10 — Populate Weekday / Weekend based on Day
• Problem #11 — How to use Xlookup with Wildcard
• Problem #12 — How many people are under Leader Deepesh Jain
• Problem #13 — What is the average Fresher Cost Rate
• Problem #14 — How to avoid creating a Temporary Lookup column
• Problem #15 — How to use Xlookup to populate Cost Rate
• Problem #16 — How to use Xlookup with multiple criterias
• Problem #17 — What is the Travel cost for Project PID002
• Problem #18 — What is the total Labour Plan cost for PID003
• Problem #19 — Calculate Monthly Labour Plan Cost

Logical Function Challenges

• Logical Functions Introduction
• Problem #1 — Categorize Baselocation vs Others
• Problem #2 — Category Travel as Approval Required and Not required
• Problem #3 — Calculate 2H forecast scenario 1
• Problem #4 — Populate Weekend vs Weekday in Travel
• Problem #5 — Forecast Scenario 2
• Problem #6 — Forecast Scenario 3
• Problem #7 — What is the standard vs premium employee count
• Problem #8 — 2H Forecast scenario 4
• Problem #9 — How many Level 2 approval travel charges are there
• Problem #10 — Forecast Scenario 5
• Problem #11 — Categorize Conveyance Charges
• Problem #12 — Create Experience Categories
• Problem #13 — Forecast Scenario 6

Summarize Data — Pivot Tables

• Pivot Tables — Setting the Context
• Problem #1 — Create a summary showing Employee Count by Location
• Problem #2 — What is the Avg. Experience for each role
• Problem #3 — What is the average experience by role & project
• Problem #4 — Modify the Pivot table to change the design
• Problem #5 — What is the count of employees under each leader
• Problem #6 — Active vs In-Active employee count under each leader
• Problem #7 — What is the premium vs standard employee count by project
• Problem #8 — How many people joined in each year by month
• Problem #9 — How many people were relieved each year by Quarter
• Problem #10 — No. of people joined under each leader by year & Month
• Problem #11 — Architect role with highest average cost rate
• Problem #12 — Emp Count & Avg. cost rate for Tester & DBA Roles
• Problem #13 — Percentage count of employees by location
• Problem #14 — Percentage of conveyance spent in 2013
• Problem #15 — Other expense total & percentage for 2023
• Problem #16 — Other expense 2H '22 vs 1H '23 variance & percentage
• Problem #17 — Plan labour variance & percentage variance
• Problem #18 — Labour Actual variance & percentage variance
• Budget vs Actual IT Project Financial Analysis

MIS Reporting

• MIS Reporting Introduction
• MIS Reporting — Why to use Functions for MIS Reporting
• Problem #1 — Create a summary showing location wise count
• Problem #2 — Create a summary showing Premium vs Standard count
• Problem #3 — Populate Employee count by Location & Status
• Problem #4 — Populate Employee Count & Average Cost rate
• Problem #5 — Calculate subtotal of employee cost rate
• Problem #6 — FILTER & Average to calculate Grand total Average
• Problem #7 — Travel Budget vs Actual Reporting Requirements
• Problem #8 — Requirement analysis to report structure
• Problem #9 — Populate the Travel budget & actual numbers
• Problem #10 — How to install Power Query
• Problem #11 — Designing & validation of Report
• Problem #12 — Report 2 — Requirement Analysis
• Problem #13 — Understanding KPIs & Calculations
• Problem #14 — How to do your data study
• Problem #15 — How do you structure your report
• Problem #16 — Extract source data into calculation file
• Problem #17 — Clean & Transform Budget Revenue
• Problem #18 — Clean & Transform Revenue Actuals
• Problem #19 — Clean & Transform Labour Plan Data
• Problem #20 — Clean & Transform Labour Actual
• Problem #21 — Clean & Transform Travel Plan
• Problem #22 — Clean & Transform Travel Actuals
• Problem #23 — Clean & Transform Other Expenses Plan
• Problem #24 — Clean & Transform Other Exp Actuals
• Problem #25 — Populating the numbers with SUMIFs
• Problem #26 — Complete Remaining calculations
• Problem #27 — Calculating QTD and YTD Numbers
• Problem #28 — Enabling ALL FILTER for Project ID
• Problem #29 — Formatting the Report
• Problem #30 — Final Validation

Headcount Status Dashboard

• Dashboard Introduction
• Two different approaches to Dashboard Building
• Dashboard Creation Process
• Dashboard Creation — Client Requirements
• Problem #1 — Analyze Client requirements
• Problem #2 — What is a wireframe and why to create it
• Problem #3 — How to create Pivot Table summaries
• Problem #4 — How & why to create a story board
• Problem #5 — Create your Charts
• Problem #6 — Which chart to choose when
• Problem 7 — Format the chart elements
• Problem 8 — Arrange Charts in Dashboard Layout
• Problem 9 — Making the alignments right
• Problem #10 — Create KPI Cards
• Problem #11 — How to format your first chart
• Problem #12 — How to use Templates
• Problem #14 — Format KPI Visuals
• Problem #15 — Create a Project Lead Slicer
• Problem #16 — Final Changes
• Course Wrap up

Setup & Project Files

• Before you Start
• Guidelines
• Project Files
• Project Files v.01

Excel Dynamic Dashboard

• 1. Requirement Analysis
• 2. Study Data & KPI Calculations
• 3. Prepare Employee Data
• 4. Prepare Travel Data
• 5. Create Calculation Sheet Part 1
• 6. Calculation Sheet Employee Data Part 2
• 7. Calculation Sheet Travel Part 1
• 8. Calculate Forecast Figures
• 9. Calculation Sheet Travel Continued
• 10. Creating Charts
• 11. Finalizing Headcount Dashboard
• 12. Finalizing Travel Dashboard
• 13. Finalizing Travel Dashboard Part B
• 14. Setting up Access Control
• 15. Setting up Access Control Part B

Income Statement & Analysis

• 1. Dataset Introduction
• 2. Prepare Calculation Sheet
• 3. Creating a new Amount column
• 4. Structuring Income Statement
• 5. Formatting Income Statement
• 6. Income Statement Vertical Analysis
• 7. Formatting Vertical Analysis
• 8. Income Statement Horizontal Analysis
• 9. Vertical Analysis Conditional Formatting
• 10. Trend Analysis
• 11. Trend Analysis Formatting
• 12. Operating Expense Budget vs Actual Analysis Part A
• 13. Operating Expense Budget vs Actual — Formatting

Forecasting & Scenario Planning

• 14. Excel Forecasting Function Part A
• Excel Forecasting Function Part B
• Best case / Worst case / Base case Scenario Template
• Forecasting Basecase Scenarios — Part 1
• Forecasting Basecase Scenarios — Part B
• Forecasting Basecase Scenarios — Part 3
• Forecasting Basecase Scenarios — Part 4
• Forecasting Basecase Scenarios — Part 5
• Goal Seek
• Data Table
• Solver
• Course Wrap up

Introduction

• 1. Session Introduction
• 2. How this learning is organized
• 3. Excel BI Framework
• Instructions (Important)
• Project Data Files (Download)
• Project Files

Get Data

• 4. Getting Data into Excel
• 5. Extracting first data file into Excel
• 6. Load Data & save

Power Query — Import & UI

• 7. Power Query UI Overview — 1
• 8. Power Query UI Overview — 2
• 9. Power Query UI Overview — 3
• 10. Power Query UI Overview — 4
• 11. Power Query UI Overview — 5
• 12. Power Query UI Overview — 6
• 13. Power Query Applied Steps
• 14. Power Query Import Project Data
• 15. Import Sales data from folder

Power Query — Transformation

• 16. Project Data Transformation — 1
• 17. Project Data Transformation — 2
• 18. Project Data Transformation — 3
• 19. Project Data Transformation — 4
• 20. Project Data Transformation — 5
• 21. Project Data Transformation — 6
• 22. Project Data Transformation — 7
• 23. Project Data Transformation — 8
• 24. Project Data Transformation — 9
• 25. Project Data Transformation — 10
• 26. Project Data Transformation — 11
• 27. Data Transformation — Merge Queries
• 28. Data Transformation — Group Data
• 29. Data Transformation — Append

Data Modelling

• 30. Data Modelling Introduction
• 31. Data Modelling — Fact & Dimension Tables
• 32. Data Modelling — Primary & Foreign Keys
• 33. Data Modelling — Connecting Tables
• 34. DAX & Reporting — Introduction

DAX & Financial Reporting

• 35. DAX & Reporting — Creating a Measure
• 36. DAX & Reporting — Aggregation Measures
• 37. DAX & Reporting — Aggregation Functions Part B
• 38. DAX & Reporting — Text, Date Functions
• 39. DAX & Reporting — The Filter Context
• 40. DAX & Reporting — GST Calculation with Iterative function
• 41. DAX & Reporting — Iterative function measure
• 42. DAX & Reporting — Forecast model with Iterative Function
• 43. DAX & Reporting — Forecast best & base case scenarios
• 44. DAX & Reporting — Creating a disconnected table
• 45. DAX & Reporting — Dynamic Toggle Button
• 46. DAX & Reporting — Handling Default State in measure
• 47. DAX & Reporting — Using SWITCH for Scenarios
• 48. DAX & Reporting — Dynamic Forecast with HASONEVALUE
• 49. DAX & Reporting — Changing Filter Context with Calculate
• 50. DAX & Reporting — Calculate with multiple filters
• 51. DAX & Reporting — Sales Trend Analysis against Q4 '23
• 52. DAX & Reporting — Sales Trend Analysis Avg Part B
• 53. DAX & Reporting — Formatting Sales Trend Analysis
• 54. DAX & Reporting — Opex Spend Analysis Part A
• 55. DAX & Reporting — Opex Budget vs Actual Analysis — Part B
• 56. DAX & Reporting — Opex Variance Analysis Part C
• 57. DAX & Reporting — P&L Reporting Automation — Part A
• 58. DAX & Reporting — CoA, P&L KPIs, Opening Stock
• 59. DAX & Reporting — EBITDA, Net Margin Calculation
• 60. DAX & Reporting — Formatting P&L Report
• 61. DAX & Reporting — Analyzing Revenue Drop — PVM Analysis
• 62. DAX & Reporting — Price Volume Impact Analysis — Part B
• 63. DAX & Reporting — Formatting PVM Analysis
• 64. DAX & Reporting — Analyzing EBITDA Drop
• 65. DAX & Reporting — Opex BvM Analysis
• 66. DAX & Reporting — Opex Spend Ratio Analysis
• 67. DAX & Reporting — Opex Category Ratio Analysis
• 68. DAX & Reporting — Opex to Revenue Analysis
• 69. DAX & Reporting — Opex Monthly Budget vs Actual %
• 70. DAX & Reporting — Opex Spend Deep dive
• 71. DAX & Reporting — Formatting Opex Analysis
• 72. DAX & Reporting — Change number formats
• 73. DAX & Reporting — Opex Reporting Formatting Continued
• 74. DAX & Reporting — Revenue Mix Analysis
• 75. DAX & Reporting — Revenue Mix Analysis Formatting
• 76. DAX & Reporting — Revenue Mix Percentage performance
• 77. DAX & Reporting — Addressing Challenges with ALL Filter
• 78. DAX & Reporting — Analyzing Product Sales Drop
• 79. DAX & Reporting — Analyzing YTD Budget vs Actual
• 80. DAX & Reporting — Year over Year Analysis