Advanced Business Excel

2. Course Description

Business Excel is the foundational language of data-driven decision-making. This course focuses on transforming raw, "dirty" data into interactive dashboards and predictive models. By mastering advanced functions, logic, and visualization, students learn to provide the analytical clarity required in modern corporate environments.

3. Course Outcomes (COs)

CO Code	Course Outcome Description	Bloom’s Level
CO1	Apply advanced logical and lookup functions to solve multi-variable business problems.	Apply (L3)
CO2	Analyze large-scale datasets to identify trends, outliers, and key performance indicators (KPIs).	Analyze (L4)
CO3	Design automated data-cleaning workflows using Power Query and Text functions.	Create (L6)
CO4	Construct interactive, executive-level dashboards using Pivot Tables and Slicers.	Create (L6)

 

4. Integrated Business Datasets

Instead of simulations, students will work exclusively on two master datasets throughout the term:

• Dataset A: The Retail Omnichannel Sales Master (50,000+ Rows)

Contents: Raw sales data from 15 regions, containing messy dates, duplicate entries, and inconsistent SKU naming.

Usage: Used for mastering Power Query, data cleaning, and XLOOKUP cross-referencing.

• Dataset B: The Corporate HR & Attrition Tracker

Contents: Employee demographics, performance ratings, salary bands, and exit interviews.

Usage: Used for Pivot Table analysis, Conditional Formatting heatmaps, and building Attrition Dashboards.

5. Detailed Session Plan (14 Sessions)

Module 1: Data Architecture & Cleaning (Sessions 1–4)

• Session 1: Logic and Referencing. Mastering IFS, AND/OR, and Absolute/Mixed referencing.
• Session 2: The Advanced Lookup Suite. Moving beyond VLOOKUP to XLOOKUP and INDEX-MATCH.
• Session 3: Text & Date Engineering. Using LEFT, MID, LEN, and NETWORKDAYS on Dataset A.
• Session 4: Introduction to Power Query. Automating the cleaning of Dataset A to create a structured database.

Module 2: Analytical Modeling (Sessions 5–9)

• Session 5: Advanced Pivot Tables. Calculated fields, grouping, and timeline analysis on Dataset B.
• Session 6: "What-If" Analysis. Using Goal Seek and Data Tables for pricing and break-even models.
• Session 7: Data Visualization Principles. Waterfall charts for P&L and dual-axis combo charts.
• Session 8: Financial Arithmetic. PMT, NPV, and IRR functions for project evaluation.
• Session 9: Mid-Term Lab Exam. Solving a live case using Dataset A.

Module 3: Business Intelligence & Dashboards (Sessions 10–14)

• Session 10: Dashboard Architecture. Defining KPIs and wireframing an interactive report.
• Session 11: Interactive Elements. Linking Slicers to multiple pivots and creating dynamic headers.
• Session 12: Capstone Part 1: Building the Retail Sales Dashboard using Dataset A.
• Session 13: Capstone Part 2: Building the HR Talent Dashboard using Dataset B.
• Session 14: Final Presentation & Speed Challenge. Automating a multi-sheet report under time pressure.

6. Assessment Scheme

• Daily Lab Tasks (20%): Accuracy in cleaning and updating the two master datasets.
• Mid-Term Lab (20%): Individual skill test on lookup and logic functions.
• The Dashboard Project (30%): Submission of a fully interactive BI Dashboard.
• End-Term Practical (30%): Solving a complex, time-bound business scenario.

 

7. Resource Requirement

• Software: Microsoft Excel 365 (Desktop Version).
• Textbook: Microsoft Excel 2019 Data Analysis and Business Modeling by Wayne Winston.