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
→1. 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