Part XI: Capstone Projects

🏫 School Management System

📋 Learning Objectives

• Design a multi-sheet relational database structure in Excel
• Use VLOOKUP, INDEX-MATCH to link data across sheets
• Build dynamic dashboards with COUNTIFS, SUMIFS
• Create a VBA UserForm for data entry with validation
• Apply conditional formatting for visual alerts

📊 Dataset Description & Sheet Structure

The workbook consists of 5 interlinked sheets plus a Dashboard sheet:

Sheet 1: Students (Master Data)

Sheet 2: Classes (Structure)

Sheet 3: Fees

Sheet 4: Attendance

Sheet 5: Results

🔧 Design — Sheet Layout Plan

Create a workbook named GFS_School_Management.xlsx with the following sheet tabs (colour-coded):

📐 Key Formulas Used

📊 Pivot Tables

1. Class-wise Student Count: Rows = Class, Columns = Section, Values = Count of StudentID
2. Fee Collection Summary: Rows = Fee Type, Columns = Status, Values = Sum of Amount
3. Monthly Attendance Summary: Rows = StudentID+Name, Columns = Month, Values = Count of "P" status
4. Subject-wise Average Marks: Rows = Class, Values = Average of each subject column

📈 Charts

• Bar Chart: Class-wise student strength (clustered by section)
• Pie Chart: Fee collection status distribution (Paid vs Pending vs Late)
• Line Chart: Monthly attendance trend per class
• Column Chart: Subject-wise average marks comparison across classes

🖥️ Dashboard Layout

The Dashboard sheet contains:

• Row 1-3: School header with logo placeholder, date, academic year
• Row 5-8: 4 KPI cards — Total Students (=COUNTA(Students!A:A)-1), Total Fee Collected, Overall Attendance %, Overall Pass %
• Row 10-25: Left — Class strength bar chart; Right — Fee status pie chart
• Row 27-42: Left — Attendance trend line chart; Right — Subject average column chart

🤖 VBA Automation — Student Data Entry Form

VBA
' === UserForm: frmStudentEntry ===
' Controls: txtStudentID, txtName, cmbClass, cmbSection,
'           txtDOB, cmbGender, txtFatherName, txtPhone, txtAddress
'           btnSave, btnClear, btnClose

Private Sub UserForm_Initialize()
    ' Auto-generate next StudentID
    Dim lastRow As Long
    lastRow = Sheets("Students").Cells(Rows.Count, 1).End(xlUp).Row
    If lastRow = 1 Then
        txtStudentID.Value = "GFS001"
    Else
        Dim lastID As String
        lastID = Sheets("Students").Cells(lastRow, 1).Value
        Dim nextNum As Long
        nextNum = CLng(Mid(lastID, 4)) + 1
        txtStudentID.Value = "GFS" & Format(nextNum, "000")
    End If
    txtStudentID.Enabled = False

    ' Populate Class dropdown
    Dim cls As Variant
    For Each cls In Array(1,2,3,4,5,6,7,8,9,10,11,12)
        cmbClass.AddItem cls
    Next
    ' Populate Section dropdown
    cmbSection.AddItem "A"
    cmbSection.AddItem "B"
    cmbSection.AddItem "C"
    ' Populate Gender
    cmbGender.AddItem "M"
    cmbGender.AddItem "F"
End Sub

Private Sub btnSave_Click()
    ' Validation
    If txtName.Value = "" Then
        MsgBox "Student Name is required!", vbExclamation
        txtName.SetFocus: Exit Sub
    End If
    If cmbClass.Value = "" Then
        MsgBox "Please select a Class!", vbExclamation
        Exit Sub
    End If
    If Not IsDate(txtDOB.Value) Then
        MsgBox "Enter valid Date of Birth (DD-MMM-YYYY)!", vbExclamation
        txtDOB.SetFocus: Exit Sub
    End If
    If Len(txtPhone.Value) <> 10 Or Not IsNumeric(txtPhone.Value) Then
        MsgBox "Enter valid 10-digit phone number!", vbExclamation
        txtPhone.SetFocus: Exit Sub
    End If

    ' Save to Students sheet
    Dim ws As Worksheet
    Set ws = Sheets("Students")
    Dim nr As Long
    nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nr, 1).Value = txtStudentID.Value
    ws.Cells(nr, 2).Value = txtName.Value
    ws.Cells(nr, 3).Value = CLng(cmbClass.Value)
    ws.Cells(nr, 4).Value = cmbSection.Value
    ws.Cells(nr, 5).Value = CDate(txtDOB.Value)
    ws.Cells(nr, 6).Value = cmbGender.Value
    ws.Cells(nr, 7).Value = txtFatherName.Value
    ws.Cells(nr, 8).Value = txtPhone.Value
    ws.Cells(nr, 9).Value = txtAddress.Value
    ws.Cells(nr, 10).Value = Date  ' Admission Date = Today

    MsgBox "Student " & txtName.Value & " registered successfully!" & vbCrLf & _
           "ID: " & txtStudentID.Value, vbInformation
    btnClear_Click  ' Reset form
End Sub

Private Sub btnClear_Click()
    txtName.Value = ""
    cmbClass.Value = ""
    cmbSection.Value = ""
    txtDOB.Value = ""
    cmbGender.Value = ""
    txtFatherName.Value = ""
    txtPhone.Value = ""
    txtAddress.Value = ""
    ' Regenerate next ID
    UserForm_Initialize
End Sub

📝 Step-by-Step Implementation Guide

1. Create Workbook: Open Excel → Save As GFS_School_Management.xlsm (Macro-Enabled)
2. Create Sheets: Add 6 sheets — Dashboard, Students, Classes, Fees, Attendance, Results. Colour-code each tab.
3. Set Up Students Sheet: Enter headers in Row 1. Apply Data Validation — Class (List: 1-12), Section (List: A,B,C), Gender (List: M,F). Format DOB column as Date.
4. Set Up Classes Sheet: Enter class structure. Use =COUNTIFS(Students!C:C, A2, Students!D:D, B2) for Current Strength column.
5. Set Up Fees Sheet: Add headers. Use =VLOOKUP(A2,Students!A:B,2,FALSE) in column B for auto-name lookup. Add Data Validation for Status (Paid/Pending/Late) and Payment Mode (Cash/Online/Cheque).
6. Set Up Attendance Sheet: Add headers. Use Data Validation for Status (P/A/L). Apply Conditional Formatting: P=Green, A=Red, L=Yellow.
7. Set Up Results Sheet: Add subject columns. Calculate Total with =SUM(C2:G2), Percentage with =H2/500*100, Grade using IFS formula, Rank using =RANK(H2, H$2:H$100).
8. Build Pivot Tables: Insert → PivotTable for each summary. Place on Dashboard sheet.
9. Create Charts: Insert charts from pivot data. Format with school colors.
10. Build Dashboard: Arrange KPI cards and charts. Add school header. Freeze panes at Row 4.
11. Create VBA Form: Alt+F11 → Insert → UserForm. Add controls and paste code above.
12. Add Macro Button: On Students sheet, Insert → Button → Assign Macro frmStudentEntry.Show.

✅ Final Deliverables Checklist

• Students master sheet with 50+ sample records and data validation
• Classes sheet with auto-calculated current strength
• Fees sheet with VLOOKUP-linked student names and payment tracking
• Attendance sheet with conditional formatting (P/A/L color codes)
• Results sheet with auto-calculated Total, Percentage, Grade, Rank
• Dashboard with 4 KPI cards and 4 charts
• Working VBA Student Entry Form with validation
• At least 2 Pivot Tables with slicers
• Print-ready report card format on a separate sheet

📋 Assessment Rubric

📝 Student Result Management System

📋 Learning Objectives

• Implement the CBSE 9-point grading scale using IFS
• Use RANK, PERCENTILE, and LARGE for rank generation
• Master INDEX-MATCH for flexible data retrieval
• Create a print-ready report card template
• Automate PDF report card generation with VBA

📊 Dataset Description

CBSE 9-Point Grading Scale

📐 Key Formulas

📊 Charts

• Bar Chart: Subject-wise class average marks — shows which subjects need improvement
• Pie Chart: Grade distribution — proportion of A1, A2, B1, B2, etc.
• Column Chart: Section-wise comparison (X-A vs X-B average) for each subject
• Histogram: Total marks frequency distribution (bins: 0-100, 101-200, ... 501-600)

🤖 VBA — Auto-Generate Report Cards & Export PDF

VBA
Sub GenerateAllReportCards()
    Dim wsMarks As Worksheet, wsReport As Worksheet
    Set wsMarks = Sheets("MarksEntry")
    Set wsReport = Sheets("ReportCard")

    Dim lastRow As Long
    lastRow = wsMarks.Cells(Rows.Count, 1).End(xlUp).Row
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\ReportCards\"

    ' Create folder if not exists
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath

    Dim i As Long
    For i = 2 To lastRow
        ' Populate Report Card
        wsReport.Range("B3").Value = wsMarks.Cells(i, 1).Value  ' RollNo
        wsReport.Range("B4").Value = wsMarks.Cells(i, 2).Value  ' Name
        wsReport.Range("B5").Value = wsMarks.Cells(i, 3).Value  ' Class

        ' Marks (fetched via INDEX-MATCH formulas already in sheet)
        wsReport.Calculate

        ' Export as PDF
        Dim fileName As String
        fileName = savePath & wsMarks.Cells(i, 1).Value & "_" & _
                   Replace(wsMarks.Cells(i, 2).Value, " ", "_") & ".pdf"

        wsReport.ExportAsFixedFormat Type:=xlTypePDF, _
            fileName:=fileName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False

        Application.StatusBar = "Generated: " & i - 1 & " of " & lastRow - 1
    Next i

    Application.StatusBar = False
    MsgBox lastRow - 1 & " Report Cards exported to:" & vbCrLf & savePath, vbInformation
End Sub

✅ Final Deliverables

• MarksEntry sheet with 50+ student records, all formulas for grades, totals, ranks
• GradeScale reference sheet with CBSE 9-point mapping
• ReportCard template sheet with INDEX-MATCH formulas, school header, and print layout
• Analysis sheet with subject-wise averages, pass/fail counts, topper lists
• 4 charts: subject average bar, grade pie, section comparison, histogram
• VBA macro to auto-generate and export PDF report cards

📋 Assessment Rubric

📅 Attendance Management System

📊 Dataset Description

📐 Key Formulas

🤖 VBA — Attendance Entry UserForm

VBA
' === UserForm: frmAttendance ===
' Controls: txtDate (with DTPicker), cmbDepartment,
'           lstStudents (ListBox), btnMarkAll, btnSave

Private Sub cmbDepartment_Change()
    ' Filter students by department
    lstStudents.Clear
    Dim ws As Worksheet: Set ws = Sheets("DailyAttendance")
    Dim i As Long
    For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(i, 3).Value = cmbDepartment.Value Then
            lstStudents.AddItem ws.Cells(i, 1).Value & " - " & ws.Cells(i, 2).Value
        End If
    Next i
End Sub

Private Sub btnSave_Click()
    Dim attDate As Date
    attDate = CDate(txtDate.Value)

    ' Find column for this date
    Dim ws As Worksheet: Set ws = Sheets("DailyAttendance")
    Dim col As Long
    For col = 4 To 34
        If ws.Cells(1, col).Value = attDate Then Exit For
    Next col

    If col > 34 Then
        MsgBox "Date column not found! Ensure date headers are set.", vbExclamation
        Exit Sub
    End If

    ' Mark attendance from checkboxes
    Dim i As Long, row As Long
    For i = 0 To lstStudents.ListCount - 1
        row = i + 2  ' Adjust based on department filter
        If lstStudents.Selected(i) Then
            ws.Cells(row, col).Value = "P"
        Else
            ws.Cells(row, col).Value = "A"
        End If
    Next i

    MsgBox "Attendance saved for " & Format(attDate, "DD-MMM-YYYY"), vbInformation
End Sub

✅ Final Deliverables

• DailyAttendance sheet with calendar-style layout (31 date columns per month)
• MonthlySummary sheet with COUNTIF-based present/absent/leave counts
• YearlySummary sheet with cumulative attendance percentage
• Conditional formatting: ≥75% green, 65-74% yellow, <65% red
• Shortage alert list — auto-filtered students below 75%
• Department-wise attendance comparison chart
• VBA UserForm for daily attendance marking
• Print-ready monthly attendance report format

📋 Assessment Rubric

📦 Inventory Management System

📋 Learning Objectives

• Design a multi-sheet inventory tracking system with relational lookups
• Use SUMIFS for stock-in, stock-out, and current stock calculations
• Implement reorder level alerts with IF and conditional formatting
• Build a dashboard showing stock levels, fast-moving items, and alerts
• Create VBA forms for stock entry and exit transactions

📊 Dataset Description

Sheet 1: Products (Master Catalog)

Sheet 2: StockIn (Purchase Records)

Sheet 3: StockOut (Sales Records)

Sheet 4: CurrentStock (Auto-Calculated)

📐 Key Formulas

🖥️ Dashboard Layout

The Dashboard displays:

• KPI Row: Total Products | Total Stock Value | Items Below Reorder | Monthly Revenue
• Bar Chart: Category-wise stock levels
• Pie Chart: Store-wise sales contribution
• Table: Top 5 fast-moving items (highest stock-out quantity)
• Alert List: Auto-filtered products needing reorder (conditional formatted red)

🤖 VBA — Stock Entry Form

VBA
Sub ShowStockEntryForm()
    frmStockEntry.Show
End Sub

' === In frmStockEntry Code ===
Private Sub btnSave_Click()
    Dim ws As Worksheet
    If optStockIn.Value Then
        Set ws = Sheets("StockIn")
    Else
        Set ws = Sheets("StockOut")
    End If

    Dim nr As Long
    nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

    ' Auto-generate Transaction ID
    Dim prefix As String
    prefix = IIf(optStockIn.Value, "SI", "SO")
    ws.Cells(nr, 1).Value = prefix & Format(nr - 1, "000")
    ws.Cells(nr, 2).Value = CDate(txtDate.Value)
    ws.Cells(nr, 3).Value = cmbProduct.Value
    ws.Cells(nr, 4).Value = CLng(txtQuantity.Value)

    ' Lookup price from Products sheet
    Dim priceCol As Long
    priceCol = IIf(optStockIn.Value, 6, 5)  ' Cost Price or Selling Price
    Dim unitPrice As Double
    unitPrice = Application.VLookup(cmbProduct.Value, Sheets("Products").Range("A:H"), priceCol, False)
    ws.Cells(nr, 5).Value = unitPrice
    ws.Cells(nr, 6).Value = CLng(txtQuantity.Value) * unitPrice

    ' Check reorder after stock-out
    If optStockOut.Value Then
        Dim currentStock As Long
        currentStock = Application.SumIfs(Sheets("StockIn").Range("D:D"), _
            Sheets("StockIn").Range("C:C"), cmbProduct.Value) - _
            Application.SumIfs(Sheets("StockOut").Range("D:D"), _
            Sheets("StockOut").Range("C:C"), cmbProduct.Value)
        Dim reorderLvl As Long
        reorderLvl = Application.VLookup(cmbProduct.Value, Sheets("Products").Range("A:G"), 7, False)
        If currentStock <= reorderLvl Then
            MsgBox "⚠️ REORDER ALERT!" & vbCrLf & _
                   "Product: " & cmbProduct.Value & vbCrLf & _
                   "Current Stock: " & currentStock & vbCrLf & _
                   "Reorder Level: " & reorderLvl, vbExclamation, "Low Stock Alert"
        End If
    End If

    MsgBox "Transaction saved successfully!", vbInformation
End Sub

✅ Final Deliverables

• Products master catalog with 50+ items across 4 categories
• StockIn sheet with purchase transaction records
• StockOut sheet with sales transaction records
• CurrentStock sheet with SUMIFS-calculated live stock levels
• Reorder alert system with conditional formatting
• Dashboard with KPIs, charts, and alert table
• VBA form for stock-in/stock-out entry with auto-reorder alerts

📋 Assessment Rubric

💰 Personal Finance Manager

📊 Dataset Description

Sheet: Income

Sheet: Expenses

Sheet: Budget

📐 Key Formulas

📈 Charts

• Stacked Bar: Income vs Total Expense per month (shows savings gap)
• Doughnut Chart: Expense category breakdown (shows where money goes)
• Line Chart: Monthly savings trend over 12 months
• Thermometer Chart: Savings goal progress (target: ₹5,00,000 for marriage fund)

🤖 VBA — Expense Entry Form

VBA
Private Sub UserForm_Initialize()
    txtDate.Value = Format(Date, "DD-MMM-YYYY")
    Dim cats As Variant
    cats = Array("Rent","Food","Transport","Entertainment","EMI","Savings","Medical","Shopping","Utilities","Other")
    Dim c As Variant
    For Each c In cats
        cmbCategory.AddItem c
    Next
    cmbMode.AddItem "UPI"
    cmbMode.AddItem "Card"
    cmbMode.AddItem "Cash"
    cmbMode.AddItem "Auto-Debit"
    cmbMode.AddItem "Net Banking"
End Sub

Private Sub btnSave_Click()
    If Not IsDate(txtDate.Value) Then MsgBox "Invalid date!": Exit Sub
    If txtAmount.Value = "" Or Not IsNumeric(txtAmount.Value) Then MsgBox "Enter valid amount!": Exit Sub
    If cmbCategory.Value = "" Then MsgBox "Select category!": Exit Sub

    Dim ws As Worksheet: Set ws = Sheets("Expenses")
    Dim nr As Long: nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(nr, 1).Value = CDate(txtDate.Value)
    ws.Cells(nr, 2).Value = txtDescription.Value
    ws.Cells(nr, 3).Value = cmbCategory.Value
    ws.Cells(nr, 4).Value = CDbl(txtAmount.Value)
    ws.Cells(nr, 5).Value = cmbMode.Value

    ' Check budget status
    Dim budgetAmt As Double
    On Error Resume Next
    budgetAmt = Application.VLookup(cmbCategory.Value, Sheets("Budget").Range("A:B"), 2, False)
    On Error GoTo 0
    If budgetAmt > 0 Then
        Dim monthSpent As Double
        monthSpent = Application.SumIfs(ws.Range("D:D"), ws.Range("C:C"), cmbCategory.Value, _
            ws.Range("A:A"), ">=" & DateSerial(Year(CDate(txtDate.Value)), Month(CDate(txtDate.Value)), 1), _
            ws.Range("A:A"), "<=" & CDate(txtDate.Value))
        If monthSpent > budgetAmt Then
            MsgBox "⚠️ BUDGET ALERT!" & vbCrLf & cmbCategory.Value & " spending: ₹" & _
                Format(monthSpent, "#,##0") & vbCrLf & "Budget: ₹" & Format(budgetAmt, "#,##0"), vbExclamation
        End If
    End If
    MsgBox "Expense recorded: ₹" & Format(CDbl(txtAmount.Value), "#,##0"), vbInformation
End Sub

✅ Final Deliverables

• Income sheet with categorized income records (6 months minimum)
• Expenses sheet with 100+ transactions across 10 categories
• Budget sheet with monthly budget vs actual comparison
• SavingsGoals sheet with target tracking and progress bars
• Dashboard with income vs expense chart, category pie, savings trend
• VBA expense entry form with budget alert notifications

📋 Assessment Rubric

🧾 GST Billing System

📊 Dataset Description

Sheet: Customers

Sheet: Products

Sheet: Invoices

Logic: If Customer State = Seller State (UP), apply CGST + SGST (each = GST Rate ÷ 2). If different state, apply IGST (= full GST Rate).

📐 Key Formulas

🤖 VBA — Invoice Generator Form

VBA
' === Invoice Generator Macro ===
Sub GenerateInvoice()
    Dim wsInv As Worksheet: Set wsInv = Sheets("PrintInvoice")
    Dim wsData As Worksheet: Set wsData = Sheets("Invoices")

    Dim invNo As String
    invNo = InputBox("Enter Invoice Number:", "Invoice Generator")
    If invNo = "" Then Exit Sub

    ' Clear previous invoice
    wsInv.Range("A10:N25").ClearContents

    ' Company Header (pre-formatted on PrintInvoice sheet)
    wsInv.Range("B2").Value = "SHARMA & SONS GENERAL STORE"
    wsInv.Range("B3").Value = "GSTIN: 09AABCS1234K1Z5"
    wsInv.Range("B4").Value = "Address: Aminabad, Lucknow, UP - 226001"

    ' Invoice Details
    wsInv.Range("F2").Value = "Invoice No: " & invNo
    wsInv.Range("F3").Value = "Date: " & Format(Date, "DD-MMM-YYYY")

    ' Customer Details via VLOOKUP
    Dim custID As String
    custID = Application.VLookup(invNo, wsData.Range("A:C"), 3, False)
    wsInv.Range("B6").Value = "Bill To: " & Application.VLookup(custID, Sheets("Customers").Range("A:F"), 2, False)
    wsInv.Range("B7").Value = "GSTIN: " & Application.VLookup(custID, Sheets("Customers").Range("A:F"), 3, False)

    ' Line items
    Dim row As Long, printRow As Long
    printRow = 10
    Dim totalTaxable As Double, totalCGST As Double, totalSGST As Double, totalIGST As Double

    For row = 2 To wsData.Cells(Rows.Count, 1).End(xlUp).Row
        If wsData.Cells(row, 1).Value = invNo Then
            wsInv.Cells(printRow, 1).Value = printRow - 9  ' Sr No
            wsInv.Cells(printRow, 2).Value = Application.VLookup(wsData.Cells(row, 4).Value, _
                Sheets("Products").Range("A:F"), 2, False)  ' Product Name
            wsInv.Cells(printRow, 3).Value = wsData.Cells(row, 5).Value  ' Qty
            wsInv.Cells(printRow, 4).Value = wsData.Cells(row, 6).Value  ' Rate
            wsInv.Cells(printRow, 5).Value = wsData.Cells(row, 7).Value  ' Taxable
            wsInv.Cells(printRow, 8).Value = wsData.Cells(row, 9).Value  ' CGST
            wsInv.Cells(printRow, 10).Value = wsData.Cells(row, 11).Value ' SGST
            wsInv.Cells(printRow, 12).Value = wsData.Cells(row, 13).Value ' IGST
            wsInv.Cells(printRow, 14).Value = wsData.Cells(row, 14).Value ' Total

            totalTaxable = totalTaxable + wsData.Cells(row, 7).Value
            printRow = printRow + 1
        End If
    Next row

    ' Grand Total row
    wsInv.Range("N" & printRow + 1).Value = Application.Sum(wsInv.Range("N10:N" & printRow))
    wsInv.Range("B" & printRow + 1).Value = "GRAND TOTAL"

    ' Print preview
    wsInv.PrintPreview
End Sub

✅ Final Deliverables

• Customers master sheet with GSTIN and state information
• Products catalog with HSN codes and GST rates (5%, 12%, 18%, 28%)
• Invoices data sheet with CGST/SGST/IGST auto-calculation
• PrintInvoice sheet — formatted, print-ready GST invoice
• TaxSummary sheet — monthly CGST, SGST, IGST totals for return filing
• VBA invoice generator macro with print preview
• Data validation on all entry fields

📋 Assessment Rubric

💼 Payroll Management System

📊 Dataset Description

Sheet: EmpMaster

Sheet: MonthlySalary (Calculated)

📐 Key Formulas

🤖 VBA — Auto-Generate Payslips

VBA
Sub GenerateAllPayslips()
    Dim wsSalary As Worksheet, wsSlip As Worksheet
    Set wsSalary = Sheets("MonthlySalary")
    Set wsSlip = Sheets("Payslip")

    Dim lastRow As Long
    lastRow = wsSalary.Cells(Rows.Count, 1).End(xlUp).Row
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\Payslips\" & Format(Date, "MMMM-YYYY") & "\"
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath

    Dim i As Long
    For i = 2 To lastRow
        ' Fill Payslip template
        wsSlip.Range("C3").Value = "INFOSYS TECHNOLOGIES PVT. LTD."
        wsSlip.Range("C4").Value = "Payslip for " & Format(Date, "MMMM YYYY")
        wsSlip.Range("C6").Value = wsSalary.Cells(i, 1).Value  ' EmpID
        wsSlip.Range("E6").Value = wsSalary.Cells(i, 2).Value  ' Name
        wsSlip.Range("C7").Value = Application.VLookup(wsSalary.Cells(i, 1).Value, _
            Sheets("EmpMaster").Range("A:E"), 3, False) ' Department
        wsSlip.Range("E7").Value = Application.VLookup(wsSalary.Cells(i, 1).Value, _
            Sheets("EmpMaster").Range("A:E"), 4, False) ' Designation

        ' Earnings
        wsSlip.Range("D10").Value = wsSalary.Cells(i, 3).Value  ' Basic
        wsSlip.Range("D11").Value = wsSalary.Cells(i, 4).Value  ' HRA
        wsSlip.Range("D12").Value = wsSalary.Cells(i, 5).Value  ' DA
        wsSlip.Range("D13").Value = wsSalary.Cells(i, 6).Value  ' Sp Allow
        wsSlip.Range("D14").Value = wsSalary.Cells(i, 7).Value  ' Gross

        ' Deductions
        wsSlip.Range("F10").Value = wsSalary.Cells(i, 8).Value   ' PF
        wsSlip.Range("F11").Value = wsSalary.Cells(i, 9).Value   ' ESI
        wsSlip.Range("F12").Value = wsSalary.Cells(i, 10).Value  ' PT
        wsSlip.Range("F13").Value = wsSalary.Cells(i, 11).Value  ' TDS
        wsSlip.Range("F14").Value = wsSalary.Cells(i, 12).Value  ' Total Ded

        ' Net Salary
        wsSlip.Range("D17").Value = wsSalary.Cells(i, 13).Value  ' Net

        ' Export PDF
        wsSlip.ExportAsFixedFormat Type:=xlTypePDF, _
            fileName:=savePath & wsSalary.Cells(i, 1).Value & "_Payslip.pdf"

        Application.StatusBar = "Payslip " & i - 1 & "/" & lastRow - 1
    Next i

    Application.StatusBar = False
    MsgBox lastRow - 1 & " payslips generated at:" & vbCrLf & savePath, vbInformation
End Sub

✅ Final Deliverables

• EmpMaster sheet with 30+ employees across 5 departments
• MonthlySalary sheet with all components and deductions auto-calculated
• Payslip template sheet with professional layout
• DeptSummary sheet with department-wise salary totals and averages
• Pivot Table: Department-wise headcount, average salary, total salary bill
• Charts: Department salary comparison bar, deduction breakdown pie
• VBA macro to batch-generate PDF payslips

📋 Assessment Rubric

📊 Sales Analytics Dashboard

📊 Dataset Description

📐 Key Formulas

📊 Pivot Tables (5 Required)

1. Monthly Revenue Trend: Rows = Month, Values = Sum of Revenue, Sum of Profit
2. Category Performance: Rows = Category, Values = Sum of Revenue, Count of Orders, Average Order Value
3. Regional Analysis: Rows = Region, Values = Sum of Revenue, Sum of Profit, Profit Margin
4. Salesperson Leaderboard: Rows = Salesperson, Values = Sum of Revenue (sorted descending), Count of Orders
5. Product × Region Matrix: Rows = Product, Columns = Region, Values = Sum of Qty

📈 Charts (6 Required)

• Line Chart: Monthly revenue trend with forecast line
• Stacked Bar: Category-wise revenue comparison (Revenue vs Cost stacked)
• Map Chart: Region-wise revenue heat map (South India cities)
• Doughnut: Category contribution to total revenue
• Bar Chart: Top 10 products by revenue
• Combo Chart: Monthly orders (bars) + revenue (line) on dual axis

🖥️ Dashboard Layout

🤖 VBA — One-Click Dashboard Refresh & PDF Export

VBA
Sub RefreshAndExport()
    ' Refresh all pivot tables
    Dim pt As PivotTable
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' Update dashboard date stamp
    Sheets("Dashboard").Range("A1").Value = "Last Updated: " & Format(Now, "DD-MMM-YYYY HH:MM")

    ' Export Dashboard as PDF
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\Reports\Dashboard_" & Format(Date, "YYYYMMDD") & ".pdf"
    Sheets("Dashboard").ExportAsFixedFormat Type:=xlTypePDF, _
        fileName:=filePath, Quality:=xlQualityStandard

    MsgBox "Dashboard refreshed and exported to:" & vbCrLf & filePath, vbInformation
End Sub

Sub EmailDashboard()
    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Dim filePath As String
    filePath = ThisWorkbook.Path & "\Reports\Dashboard_" & Format(Date, "YYYYMMDD") & ".pdf"

    With OutMail
        .To = "manager@reliancedigital.com"
        .Subject = "Weekly Sales Dashboard - " & Format(Date, "DD-MMM-YYYY")
        .Body = "Please find the weekly sales analytics dashboard attached."
        .Attachments.Add filePath
        .Display  ' Use .Send for auto-send
    End With
End Sub

✅ Final Deliverables

• SalesData sheet with 1000+ transaction rows (12 months)
• 5 Pivot Tables with slicers and timeline controls
• 6 professional charts with consistent formatting
• Executive Dashboard sheet with KPIs and charts
• FORECAST.LINEAR-based revenue projection
• VBA macros: Refresh, PDF Export, Email functionality

📋 Assessment Rubric

🔬 Research Data Analyzer

📊 Dataset Description

📐 Key Formulas

📈 Visualizations Required

• Histogram: Age distribution of respondents (bins: 20-25, 26-30, 31-35, 36-40, 41-50)
• Stacked Bar: Likert response distribution for each question (shows agreement patterns)
• Scatter Plot: WFH Days vs Satisfaction score with trendline and R² value
• Box & Whisker: Productivity scores by company (shows median, quartiles, outliers)
• Radar Chart: Average scores across all 5 questions by company
• Correlation Matrix: Heat map of correlations between all question pairs

🤖 VBA — Auto-Run Analysis & Generate Report

VBA
Sub RunFullAnalysis()
    Dim wsData As Worksheet, wsAnalysis As Worksheet
    Set wsData = Sheets("SurveyData")
    Set wsAnalysis = Sheets("Analysis")

    Dim lastRow As Long
    lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
    Dim n As Long: n = lastRow - 1  ' Sample size

    ' Header
    wsAnalysis.Range("A1").Value = "RESEARCH ANALYSIS REPORT"
    wsAnalysis.Range("A2").Value = "Generated: " & Format(Now, "DD-MMM-YYYY HH:MM")
    wsAnalysis.Range("A3").Value = "Sample Size (N): " & n

    ' Descriptive Statistics for Q1-Q5 (Columns H-L)
    Dim qNames As Variant
    qNames = Array("Productivity", "Focus", "Work-Life Balance", "Stress", "Satisfaction")
    Dim col As Long, row As Long: row = 6
    wsAnalysis.Range("A5").Value = "DESCRIPTIVE STATISTICS"
    wsAnalysis.Range("A" & row).Value = "Variable"
    wsAnalysis.Range("B" & row).Value = "Mean"
    wsAnalysis.Range("C" & row).Value = "Median"
    wsAnalysis.Range("D" & row).Value = "Mode"
    wsAnalysis.Range("E" & row).Value = "Std Dev"
    wsAnalysis.Range("F" & row).Value = "Min"
    wsAnalysis.Range("G" & row).Value = "Max"

    For col = 8 To 12  ' Columns H to L
        row = row + 1
        Dim rng As Range
        Set rng = wsData.Range(wsData.Cells(2, col), wsData.Cells(lastRow, col))
        wsAnalysis.Cells(row, 1).Value = qNames(col - 8)
        wsAnalysis.Cells(row, 2).Value = Round(Application.Average(rng), 2)
        wsAnalysis.Cells(row, 3).Value = Application.Median(rng)
        wsAnalysis.Cells(row, 4).Value = Application.Mode(rng)
        wsAnalysis.Cells(row, 5).Value = Round(Application.StDev(rng), 2)
        wsAnalysis.Cells(row, 6).Value = Application.Min(rng)
        wsAnalysis.Cells(row, 7).Value = Application.Max(rng)
    Next col

    ' Correlation Matrix
    row = row + 3
    wsAnalysis.Cells(row, 1).Value = "CORRELATION MATRIX"
    row = row + 1
    Dim i As Long, j As Long
    For i = 0 To 4
        wsAnalysis.Cells(row, i + 2).Value = qNames(i)
        wsAnalysis.Cells(row + i + 1, 1).Value = qNames(i)
    Next i
    For i = 0 To 4
        For j = 0 To 4
            Dim r1 As Range, r2 As Range
            Set r1 = wsData.Range(wsData.Cells(2, 8 + i), wsData.Cells(lastRow, 8 + i))
            Set r2 = wsData.Range(wsData.Cells(2, 8 + j), wsData.Cells(lastRow, 8 + j))
            wsAnalysis.Cells(row + i + 1, j + 2).Value = Round(Application.Correl(r1, r2), 3)
        Next j
    Next i

    MsgBox "Full analysis complete! Check the Analysis sheet.", vbInformation
End Sub

✅ Final Deliverables

• SurveyData sheet with 200+ respondent records
• Descriptive statistics table for all variables
• Correlation matrix (5×5) with conditional formatting heat map
• Regression analysis output (slope, R², p-value interpretation)
• 6 professional charts/visualizations
• VBA macro for automated analysis and report generation
• Written interpretation of findings (1-page summary)

📋 Assessment Rubric

🏢 Business Intelligence Dashboard

📊 Multi-Source Data Structure

Source 1: Sales Data

Source 2: Inventory Data

Source 3: HR Data

Source 4: Finance Data

📐 Key Formulas & Techniques

🖥️ Dashboard Architecture

The workbook has a navigation-driven structure with 7 sheets:

🤖 VBA — Navigation Menu & Auto-Refresh

VBA
' === Navigation Module ===
Sub GoToHome(): Sheets("Home").Activate: End Sub
Sub GoToExecutive(): Sheets("ExecutiveSummary").Activate: End Sub
Sub GoToSales(): Sheets("SalesDashboard").Activate: End Sub
Sub GoToInventory(): Sheets("InventoryDashboard").Activate: End Sub
Sub GoToHR(): Sheets("HRDashboard").Activate: End Sub
Sub GoToFinance(): Sheets("FinanceDashboard").Activate: End Sub

' === Auto-Refresh All Data ===
Sub RefreshAll()
    Application.ScreenUpdating = False

    ' Refresh Power Query connections
    Dim conn As WorkbookConnection
    For Each conn In ThisWorkbook.Connections
        conn.Refresh
    Next conn

    ' Refresh all pivots
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' Update timestamp
    Sheets("ExecutiveSummary").Range("B1").Value = "Last Refreshed: " & Format(Now, "DD-MMM-YYYY HH:MM")
    Application.ScreenUpdating = True
    MsgBox "All data sources and pivot tables refreshed!", vbInformation
End Sub

' === Email Executive Report ===
Sub EmailExecutiveReport()
    ' Export Executive Summary as PDF
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "\ExecutiveReport_" & Format(Date, "YYYYMMDD") & ".pdf"
    Sheets("ExecutiveSummary").ExportAsFixedFormat xlTypePDF, pdfPath

    ' Create Outlook email
    Dim olApp As Object, olMail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    With olMail
        .To = "ceo@tatagroup.com; cfo@tatagroup.com"
        .Subject = "Weekly Executive Dashboard - " & Format(Date, "DD-MMM-YYYY")
        .HTMLBody = "<h2>Weekly Executive Report</h2>" & _
                    "<p>Please find the latest executive dashboard attached.</p>" & _
                    "<p>Key Highlights:</p><ul>" & _
                    "<li>Revenue: " & Sheets("ExecutiveSummary").Range("C5").Text & "</li>" & _
                    "<li>Profit: " & Sheets("ExecutiveSummary").Range("C6").Text & "</li>" & _
                    "</ul>"
        .Attachments.Add pdfPath
        .Display
    End With
End Sub

✅ Final Deliverables

• Multi-source data integration (4 data sources consolidated)
• Power Query connections (or structured references) for data refresh
• Home page with VBA-powered navigation buttons
• Executive Summary with 4 KPI scorecards (traffic lights)
• 4 department-specific dashboard sheets with charts and slicers
• VBA navigation, auto-refresh, and email report macros
• Professional formatting with consistent color scheme
• Sheet protection with navigation-only access for end users

📋 Assessment Rubric

📖 Glossary of Excel Terms (A–Z)

A comprehensive glossary of 100+ essential Excel terms every student and professional must know.

A

B

C

D

E–F

G–H

I–K

L–M

N–O

P–Q

R–S

T–U

V–Z

🎓 Excel Certification Preparation (MOS)

About Microsoft Office Specialist (MOS) Certification

The MOS Excel certification is globally recognized and validates your proficiency in Excel. There are three levels:

MOS Excel Associate (MO-210) — Topics

Domain 1: Manage Worksheets and Workbooks (10-15%)

• Import data from .txt, .csv files
• Navigate within workbooks, customize Quick Access Toolbar
• Format worksheets (page setup, themes, headers/footers)
• Configure print settings, manage sheet visibility

Domain 2: Manage Data Cells and Ranges (20-25%)

• Manipulate data in worksheets (cut, copy, paste special)
• Format cells (number formats, alignment, styles)
• Create named ranges, define data validation
• Summarize data using SUBTOTAL, structured references

Domain 3: Manage Tables and Table Data (15-20%)

• Create and format Excel tables
• Modify tables (add/remove rows, columns, calculated columns)
• Filter and sort table data

Domain 4: Perform Operations Using Formulas and Functions (30-35%)

• Insert references (relative, absolute, mixed)
• Calculate and transform data using functions (SUM, AVERAGE, MAX, MIN, COUNT)
• Format and modify text using functions (UPPER, LOWER, LEFT, RIGHT, MID, CONCAT)
• Apply logical functions (IF, AND, OR, NOT, IFS, SWITCH)
• Apply lookup functions (VLOOKUP, HLOOKUP, INDEX, MATCH)

Domain 5: Manage Charts (20-25%)

• Create charts (column, bar, line, pie, scatter)
• Modify charts (elements, layout, styles)
• Format charts (axis, titles, legends, data labels)

Practice Tips for MOS Exam

💼 Top 50 Excel Interview Questions with Answers

Questions 11-50 cover: IFERROR vs IFNA, Circular References, Named Ranges, Data Tables, Scenario Manager, Solver, Array Formulas, INDIRECT, OFFSET, TEXT function, Custom Number Formats, Conditional Formatting with formulas, VBA basics (Sub vs Function, loops, error handling), UserForms, Events, Workbook_Open, Application.ScreenUpdating, Pivot Table Calculated Fields, Slicers vs Report Filters, GETPIVOTDATA, Power Pivot Data Model, DAX basics, Measures vs Calculated Columns, Dynamic Arrays (FILTER, SORT, UNIQUE, SEQUENCE), LET function, LAMBDA, XLOOKUP match modes, Structured References, Tables vs Ranges, Flash Fill patterns, Chart trendlines, Sparklines, Map Charts, Waterfall Charts, Combo Charts, Print Area management, Page Break Preview, Header/Footer codes, AGGREGATE function, SUBTOTAL visibility, Consolidation, 3D References, and Workbook Links management.

📁 Downloadable Dataset Descriptions

Descriptions of 20 practice datasets to build with or generate using RANDBETWEEN and lookup tables.

🔑 Answer Keys for All Parts

Project 1: School Management — Key Answers

• Class 10-A student count: =COUNTIFS(Students!C:C,10,Students!D:D,"A") → 2 students (GFS001, GFS002)
• Total fee collected: =SUMIFS(Fees!C:C,Fees!F:F,"Paid") → ₹50,000
• GFS001 attendance %: 1 Present / 2 Total = 50%
• GFS002 Mid-Term Rank: Total 423 = Rank 1

Project 2: Result Management — Key Answers

• Arjun's English Grade (89 marks): A2 (81-90 range)
• Sneha's Maths Grade (74 marks): B1 (71-80 range)
• Vikash's CGPA: (6+5+5+5+6+5)/6 = 5.3
• Class topper: Arjun (Total 521, Rank 1)

Project 3: Attendance — Key Answers

• PE001 attendance: 22/26 = 84.6% → ✅ Safe
• PE002 attendance: 18/26 = 69.2% → ⚠️ Shortage
• PE004 attendance: 15/26 = 57.7% → 🔴 Critical

Project 4: Inventory — Key Answers

• PRD001 current stock: 50 in - 20 out = 30 units → ✅ OK (reorder level: 15)
• PRD002 current stock: 20 in - 18 out = 2 units → 🔴 REORDER (reorder level: 8)
• July purchase total: 6,40,000 + 1,70,000 + 8,90,000 = ₹17,00,000

Projects 5-10: Key Reference Values

• P5 (Finance): Savings Rate = (73,833 - 58,000) / 73,833 = 21.4%
• P6 (GST): C001 (UP→UP): CGST+SGST; C002 (Delhi→UP): IGST. Salt GST: 5% = CGST 2.5% + SGST 2.5%
• P7 (Payroll): EMP001 Gross = 45000+18000+5400+8000 = ₹76,400; PF = ₹5,400
• P8 (Sales): ORD001 Profit = 119998-89998 = ₹30,000; Profit margin = 25%
• P9 (Research): Q1-Q5 Correlation expected range: 0.65-0.85 for related constructs
• P10 (BI): Jan Revenue vs Target: 12.5/14.0 = 89.3% → 🔴 Red

💡 90+ Additional Excel Project Ideas

Brief descriptions of additional projects to reach 100+ total project ideas. These can serve as assignments, mini-projects, or self-practice exercises.

Education & Academic (11-25)

Business & Finance (26-45)

Healthcare & Science (46-55)

Operations & Logistics (56-65)

Marketing & HR (66-80)

Personal & Lifestyle (81-90)

Advanced & Specialized (91-100+)

🎉 Congratulations!

You've completed the Microsoft Excel Mastery series. From basic cell formatting in Part I to building enterprise-grade BI dashboards with VBA in Part XI — you now possess the skills that companies like TCS, Infosys, Flipkart, and Reliance look for. Keep practicing, get MOS certified, and remember: Excel is not just a tool — it's a superpower.