Capstone: Business Analytics Dashboard

🎯 Mục tiêu dự án

TB5 min

Trong capstone này, bạn sẽ xây dựng Sales Performance Dashboard hoàn chỉnh:

✅ Data import & cleaning (Power Query)

✅ Data model (Tables + Relationships)

✅ Analysis (PivotTables + Formulas)

✅ Visualization (Charts + KPI Cards)

✅ Interactivity (Slicers + Form Controls)

✅ Professional formatting & protection

Thời gian: 60 phút | Độ khó: Advanced | Yêu cầu: Hoàn thành Bài 1-11 | Type: Project

Task 0

📖 Bảng Thuật Ngữ Tổng Hợp

TB5 min

Thuật ngữ	Tiếng Việt	Mô tả
ETL Pipeline	Đường ống ETL	Extract → Transform → Load automated
Data Model	Mô hình dữ liệu	Tables + Relationships
KPI Card	Thẻ chỉ số	Value + Trend + Target
Combo Chart	Biểu đồ kết hợp	Column + Line cùng chart
Report Connection	Kết nối báo cáo	Slicer → nhiều PivotTables
Camera Tool	Công cụ chụp	Live snapshot cells
Achievement Rate	Tỷ lệ đạt	Actual / Target × 100%
MoM Change	Thay đổi tháng/tháng	Month-over-Month comparison
Wireframe	Bản phác thảo	Layout sketch trước build
3-Layer Design	Thiết kế 3 lớp	Data → Calc → Dashboard

Checkpoint

Capstone kết hợp tất cả kỹ năng 12 bài: Power Query ETL, Tables, PivotTables, Charts, Slicers, Form Controls, KPI Cards, Protection. Bạn sẵn sàng chưa?

Task 1

📋 1. Project Brief

TB5 min

Business Context

Bạn là Data Analyst tại công ty bán lẻ điện tử. Manager yêu cầu dashboard để:

Track doanh thu hàng tháng vs target
Compare performance giữa các regions
Identify top products và customers
Monitor profit margins
Filter theo period, region, product category

Data Sources

File	Description	Rows
`sales_2026.csv`	Transaction data	5,000
`products.xlsx`	Product master	50
`targets.xlsx`	Monthly targets by region	48

Đây là dự án portfolio-ready — hoàn thành tốt có thể show cho nhà tuyển dụng. Focus vào chất lượng, không chỉ hoàn thành!

Checkpoint

Dashboard cho Sales Manager: track revenue, compare regions, identify top performers, monitor margins. 3 data sources cần merge. Portfolio project — chất lượng là ưu tiên!

Task 2

📥 2. Phase 1: Data Preparation

TB5 min

2.1 Import with Power Query

Sales Data:

fxImport Sales CSV

Data → Get Data → From CSV → sales_2026.csv

1. Promote Headers

2. Change types (Date, Number)

3. Remove nulls/blanks

4. Trim text columns

5. Add columns: Month, Quarter, Year

Products:

fxImport Products

Data → Get Data → From Workbook → products.xlsx

Change types, Rename columns to match

Targets:

fxImport Targets

Data → Get Data → From Workbook → targets.xlsx

1. Unpivot month columns → Long format

2. Rename: Month_Name, Target_Revenue

2.2 Merge Queries

fxMerge Queries

Sales ←LEFT JOIN→ Products (on ProductID)

→ Add: Product Name, Category, Unit Cost

Sales (Grouped by Region, Month) ←LEFT JOIN→ Targets

→ Add: Target Revenue

2.3 Calculated Columns

fxCalculated Columns

Revenue = Quantity × Unit_Price

Cost = Quantity × Unit_Cost

Profit = Revenue - Cost

Margin_Pct = Profit / Revenue

Achievement = Revenue / Target

2.4 Load to Data Model

fxLoad to Data Model

Close & Load To

☑ Only Create Connection

☑ Add to Data Model

→ Create relationships

Unpivot targets trước khi merge! Targets thường ở wide format (columns = months). Unpivot → long format thì mới JOIN được với sales data.

Checkpoint

Phase 1: Import 3 sources → Clean → Merge Sales+Products+Targets → Calculate Revenue, Profit, Margin → Load to Data Model. Power Query handles entire ETL!

Task 3

📊 3. Phase 2: Analysis Layer

TB5 min

3.1 PivotTables (Sheet "Calc")

fx6 PivotTables

PT1: Monthly Revenue Trend

Rows: Month | Values: Sum Revenue, Sum Target

PT2: Revenue by Region

Rows: Region | Values: Sum Revenue, % of Total

PT3: Revenue by Product Category

Rows: Category | Values: Sum Revenue, Sum Profit

PT4: Top 10 Products

Rows: Product Name | Values: Sum RevenueFilter Top 10

PT5: Top 10 Customers

Rows: Customer Name | Values: Sum Revenue, Count Orders

PT6: KPI Summary

Values: Sum Revenue, Sum Profit, Sum Cost, Count Orders

3.2 KPI Calculations

fxKPI Calculations

Từ PivotTable

=GETPIVOTDATA("Revenue", PT6)Total Revenue

=GETPIVOTDATA("Profit", PT6)Total Profit

=GETPIVOTDATA("OrderID", PT6)Total Orders

Calculated KPIs

=Revenue / OrdersAvg Order Value

=Profit / RevenueProfit Margin

=Revenue / Total_TargetTarget Achievement

=(Current - Previous) / PreviousMoM Change

Checkpoint

Phase 2: 6 PivotTables cho different views + KPI Calculations với GETPIVOTDATA. Tất cả trên sheet "Calc" — sẽ hide khỏi users. Bạn đã tạo PivotTables chưa?

Task 4

🎨 4. Phase 3: Dashboard Layout

TB5 min

4.1 Wireframe

4.2 Design Specs

fxColor Palette & Typography

Color Palette

Primary: #2563EB (Blue)

Positive: #10B981 (Green)

Negative: #EF4444 (Red)

Neutral: #6B7280 (Gray)

Background: #F9FAFB

Cards: #FFFFFF

Typography

Dashboard Title: Calibri Bold 16pt

KPI Value: Calibri Bold 28pt

KPI Label: Calibri Regular 9pt

Chart Title: Calibri Bold 11pt

Wireframe trên giấy trước! Đừng mở Excel ngay. Sketch layout 5 phút → tiết kiệm hàng giờ rearranging sau. Professional analysts luôn plan trước khi build.

Checkpoint

Dashboard layout: KPI row trên cùng → Main charts giữa → Filters + Detail dưới. Z-Pattern eye flow. Color palette + typography defined trước khi build. Bạn đã sketch chưa?

Task 5

🔨 5. Phase 4: Build Dashboard

TB5 min

5.1 KPI Cards

📊 Dashboard KPI Cards

📈$2.8MRevenue

▲15% vs prev month

🎯$2.5MTarget

▲Achieved ✅

💰32%Profit Margin

▼-2% vs prev

5.2 Charts

Chart 1: Revenue vs Target (Combo)

Clustered Column (Revenue) + Line (Target)
X-axis: Months | Connection: PT1

Chart 2: Revenue by Region (Donut)

Data: PT2 | Center text: Total Revenue

Chart 3: Top Products (Horizontal Bar)

Data: PT4 | Sort: Descending

Chart 4: Profit by Category (Stacked Column)

Data: PT3 | Data labels: values

5.3 Slicers & Filters

fxSlicers & Filters

Slicer 1: Region → Connected to PT1-PT6

Slicer 2: Product Category → Connected to PT1-PT6

Timeline: Order Date → Connected to PT1-PT6

Combo Box: Year selector

Checkpoint

Build: KPI Cards (Camera Tool) + 4 Charts (Combo, Donut, Bar, Stacked) + Slicers (all PTs connected) + Timeline. Test: click slicer → ALL elements update. Bạn đã build chưa?

Task 6

✨ 6. Phase 5: Polish & Finalize

TB5 min

6.1 Formatting Checklist

fxFormatting Checklist

☑ Hide gridlines, row/column headers

☑ Consistent fonts (Calibri throughout)

☑ Consistent colors (palette defined)

☑ Charts aligned (Alt + drag)

☑ Insight titles (not just "Revenue Chart")

☑ Slicers styled to match theme

☑ White space balanced

6.2 Protection

fxProtection Steps

1. Unlock slicer/control cells only

2. Protect Dashboard sheet

3. Hide Calc and Data sheets

4. Protect Workbook structure

5. Save as .xlsx

6.3 Documentation

fxSheet "README"

Dashboard purpose

Data sources & refresh instructions

Contact info

Last updated date

Checkpoint

Polish: formatting consistency, protection (hide calc sheets, lock dashboard), documentation (README sheet). Dashboard phải đẹp, bảo mật, và có hướng dẫn sử dụng!

Task 7

📝 7. Evaluation & Career Tips

TB5 min

Rubric

Criteria	Points	Description
Data Preparation	20	Power Query: clean, transform, merge
Analysis	20	PivotTables, KPIs, calculations
Visualization	25	Chart selection, formatting, clarity
Interactivity	20	Slicers, form controls, dynamic
Polish	15	Layout, consistency, protection
Total	100

Portfolio Presentation

fxKhi show cho nhà tuyển dụng

1. Start with CONTEXT: "This dashboard helps sales managers..."

2. Explain DATA: "I cleaned 5,000 rows using Power Query..."

3. Show INSIGHTS: "North region leads with 35%..."

4. Demo INTERACTIVITY: "Click here to filter..."

5. Discuss DECISIONS: "This helped identify underperforming..."

Extension Challenges

What-If Analysis: Scroll bar cho price change → projected revenue
Multi-Year Comparison: YoY growth, dual-axis charts
Automated Report: VBA capture dashboard + email

Checkpoint

Dashboard project = portfolio piece mạnh nhất. Present: Context → Data → Insights → Interactivity → Decisions. Show problem-solving process, không chỉ kết quả. Bạn sẵn sàng present chưa?

Task 8

� 8. Tổng Kết Khóa Học

TB5 min

Skills Summary

fxCourse Skills Summary

📊 Excel Fundamentals

✅ Interface, shortcuts, data entry

📐 Formulas & Functions

✅ SUM/IF/COUNT, VLOOKUP, INDEX-MATCH, TEXT, DATE

📈 Data Analysis

✅ PivotTables, PivotCharts, Grouping, Calculated Fields

🎨 Visualization

✅ Charts, Conditional Formatting, Sparklines

🗃️ Data Management

✅ Data Validation, Named Ranges, Tables

⚡ Power Query

✅ Import, Transform, Merge, Append, M Language

📊 Dashboards

✅ Design, Interactivity, Slicers, Form Controls, Protection

Câu hỏi tự kiểm tra

Dashboard nên bắt đầu thiết kế từ đâu?
Những formulas nào bạn sử dụng nhiều nhất trong dự án?
Power Query giúp gì cho quy trình phân tích dữ liệu?
Portfolio project cần có những yếu tố nào để gây ấn tượng?

Next Steps:

📊 SQL Basics — Query databases trực tiếp
📈 Statistics Fundamentals — Nền tảng thống kê
🐍 Python for Data Analysis — Lập trình phân tích dữ liệu

Key Takeaways toàn khóa học:

Excel Fundamentals: interface, shortcuts, data entry
Formulas: SUM/IF/COUNT, VLOOKUP, INDEX-MATCH, TEXT, DATE
PivotTables: drag-and-drop analysis, PivotCharts, Slicers
Visualization: Charts, Conditional Formatting, Sparklines
Data Management: Validation, Named Ranges, Tables
Power Query: Import, Transform, Merge, Append, M Language
Dashboards: Design, Interactivity, 3-Layer Architecture

Task 9