🚀 Capstone Project: AI-Powered Business Data Analysis in Excel Project Title:

Build, Clean, Analyze & Automate a Sales Performance Dashboard Using Excel + Copilot

🎯 Project Objective

Students will:

Create a structured dataset
Clean and prepare raw data
Apply formulas and functions
Use Copilot for analysis and automation
Engineer effective prompts
Build a presentable mini-dashboard
Evaluate and optimize Copilot performance

This project combines Excel fundamentals + AI-powered workflows.

📁 Scenario

You are a Data Analyst for a retail company.

The company has provided raw monthly sales data that contains:

Missing values
Incorrect date formats
Inconsistent text entries
Duplicate records
Mixed data types

Your job is to clean, analyze, and present insights using Excel + Copilot.

🧩 Part 1: Data Creation & Structuring Task 1: Generate Dataset

Using Copilot:

Generate synthetic sales data with:
- Order ID
- Date
- Region
- Product Category
- Sales Amount
- Quantity
- Sales Representative
- Discount
- Profit

Minimum: 200 rows

✅ Deliverable: Structured Excel sheet with proper headers

🧹 Part 2: Data Cleaning & Preparation Task 2: Identify Errors

Use Copilot to:

Detect missing values
Identify inconsistent formatting
Find duplicate entries
Spot incorrect data types

Write the prompt you used.

Task 3: Clean the Data

Convert text dates into proper date format
Standardize region names
Remove duplicates
Fix blank sales values
Correct negative profit entries

✅ Deliverable: Clean dataset version (new sheet)

📊 Part 3: Core Excel Skills Application Task 4: Add Formula Columns

Add:

Revenue after discount
Profit margin (%)
Month extracted from date
Sales category (High / Medium / Low using conditional statements)

Use both:

Manual formulas
Copilot-assisted formula creation

Task 5: Apply Functions

Use:

SUM
AVERAGE
MIN
MAX
IF
LOOKUP or XLOOKUP
Nested functions

Compare manual vs Copilot-generated formulas.

🔍 Part 4: Data Analysis with Copilot Task 6: Ask Copilot Analytical Questions

Examples:

Which region generated highest revenue?
Which sales rep has lowest profit margin?
Monthly sales trend?
Top performing product category?

Save the prompts and Copilot responses.

Task 7: Data Profiling

Use Copilot to:

Summarize dataset patterns
Identify anomalies
Highlight trends

🎨 Part 5: Visualization & Presentation Task 8: Formatting & Conditional Formatting

Apply color scales to profit margin
Use data bars for revenue
Highlight negative profit rows
Apply professional formatting

Task 9: Create Pivot Tables

Build pivot tables showing:

Revenue by region
Sales by month
Profit by product category

🤖 Part 6: Prompt Engineering Challenge Task 10: Write 5 Basic Prompts

Example:

“Analyze sales performance by region and highlight underperforming areas.”

Task 11: Improve Prompts

Rewrite the prompts to:

Be more specific
Include constraints
Ask for structured outputs

Compare results.

Task 12: Evaluate Copilot Performance

Answer:

Did Copilot generate correct formulas?
Where did it make mistakes?
How did refining the prompt improve results?

⚙ Part 7: Workflow Optimization Task 13: Automate Repetitive Task

Use Copilot to:

Create formula columns automatically
Generate summary insights
Reformat entire sheet

Measure time saved.

📈 Final Deliverable

Students must submit:

Raw dataset
Cleaned dataset
Formula-enhanced dataset
Pivot table summary
5 prompts (original + optimized versions)
1-page dashboard sheet
Reflection document answering:
- What worked well with Copilot?
- What didn’t?
- How can performance be improved?

🏆 Bonus Challenge (Advanced)

Create a Travel Budget sheet using Copilot
Use Copilot to simulate “What-if” analysis
Build a dynamic summary sheet

Noéma Kronenburg

--

1