PGDM Core Subject
Advanced Excel for Business
Course Objective
2. Course Description
This course advances the student's digital fluency by moving into the realm of Automated Analytics and Predictive Modeling. Students will master the use of advanced formulas, complex pivot tables, and dashboarding to bridge the gap between business requirements and technical execution. The module focuses on converting large datasets into actionable business insights through interactive, executive-level reports.
3. Course Objectives (Learning Goals)
- To master Advanced Formulas and Financial Modeling for complex business valuation and project evaluation.
- To design Interactive Executive Dashboards using advanced Pivot Tables, Slicers, and dynamic headers.
- To automate repetitive tasks and data workflows using Macros and Power Query.
- To demonstrate Data Stewardship by curating and visualizing large datasets to derive actionable insights.
4. Course Outcomes (COs)
Mapped to Bloom’s Taxonomy and Taxila’s Program Outcomes (POs).
|
CO Code |
Course Outcome Description |
Bloom's Level |
Primary PO Mapping |
|
CO1 |
Apply advanced financial arithmetic ($NPV$, $IRR$, $PMT$) to evaluate investment proposals. |
Apply (L3) |
PO1 |
|
CO2 |
Analyze large-scale organizational data to identify trends and outliers via advanced Pivot Tables. +3 |
Analyze (L4) |
PO2 |
|
CO3 |
Design automated multi-sheet reports and workflows using Macros and Power Query. +2 |
Create (L6) |
PO1 |
|
CO4 |
Construct interactive BI Dashboards that link technological investment to key business outcomes. +2 |
Create (L6) |
PO2 |
5. CO-PO Articulation Matrix
Correlation: 3 (High), 2 (Medium), 1 (Low).
|
CO Code |
PO1 |
PO2 |
PO3 |
PO4 |
PO5 |
PO6 |
PO7 |
PO8 |
|
CO1 |
3 |
3 |
- |
- |
2 |
- |
- |
1 |
|
CO2 |
3 |
3 |
- |
- |
1 |
- |
- |
2 |
|
CO3 |
3 |
2 |
1 |
1 |
2 |
- |
- |
3 |
|
CO4 |
3 |
3 |
- |
2 |
3 |
- |
- |
2 |
|
AVG |
3.0 |
2.8 |
1.0 |
1.5 |
2.0 |
- |
- |
2.0 |
6. Assessment Scheme
|
Component |
Marks |
Description |
Mapped CO |
|
Daily Lab Tasks |
20 |
Accuracy in updating and cleaning the Master Datasets. |
CO2, CO3 |
|
Mid-Term Lab |
20 |
Individual skill test on advanced lookup, financial functions, and logic. |
CO1, CO2 |
|
Dashboard Project |
30 |
Submission of a fully interactive BI Dashboard for Retail or HR. |
CO4 |
|
End-Term Practical |
30 |
Solving a complex, time-bound business scenario involving automation. |
All |
7. Detailed Syllabus & Session Plan (14 Sessions)
Each session is 90 minutes. Students will utilize Dataset A (Retail Omnichannel) and Dataset B (Corporate HR) throughout.
Module 1: Advanced Arithmetic & Modeling (Sessions 1-5)
Session 1: Advanced Logical Arrays. Mastering nested IFS, AND/OR logic for multi-variable business problems.
Session 2: The Modeling Suite. Using XLOOKUP, INDEX-MATCH, and indirect referencing for dynamic modeling.
Session 3: Financial Engineering. Applying PMT, NPV, and IRR functions for project evaluation.
Session 4: "What-If" Analysis. Utilizing Goal Seek, Scenario Manager, and Data Tables for pricing models.
Session 5: Mid-Term Lab. High-speed modeling challenge using Dataset A.
Module 2: Business Intelligence & Automation (Sessions 6-10)
Session 6: Advanced Pivot Architectures. Calculated fields, custom groupings, and timeline analysis on Dataset B.
Session 7: Power Query Mastery. Automating the ingestion and cleaning of "dirty" data to create structured databases.
Session 8: Intro to Macros. Recording and editing basic VBA macros to automate multi-sheet reporting.
Session 9: Dashboard Architecture. Defining KPIs and wireframing interactive, executive-level reports.
Session 10: Interactive UI Elements. Linking Slicers to multiple pivots and creating dynamic headers.
Module 3: Capstone & Future Tech (Sessions 11-14)
Session 11: Capstone Part 1. Building the Retail Sales Dashboard using Power Query and Dataset A.
Session 12: Capstone Part 2. Building the HR Talent & Attrition Dashboard using Dataset B.
Session 13: Speed Challenge. Automating a complex data-to-dashboard workflow under time pressure.
Session 14: Final Presentation. Defending analytical insights derived from the dashboards to a mock board.
8. Textbooks & Resources
Mandatory Textbook: Microsoft Excel 2019 Data Analysis and Business Modeling by Wayne Winston.
Lab Resources:
Dataset A: Retail Omnichannel Sales Master (50,000+ Rows).
Dataset B: Corporate HR & Attrition Tracker.