Assignment: Build a Sales Dashboard from Raw Data

What This Assignment Is About

You will use AI tools within Microsoft Excel to transform a messy sales dataset into a working dashboard. This assignment tests your ability to clean data, generate insights, create visualizations, and build an interactive dashboard using AI assistance-all skills essential for working professionals who need to turn raw data into business intelligence quickly.

Scenario

You are a sales analyst at a mid-sized retail company. Your manager has just sent you a spreadsheet containing three months of sales data from five regional stores. The data includes product names, sales amounts, dates, store locations, and sales representatives. The dataset is messy-there are inconsistent date formats, some missing values, and product names are not standardized. Your manager needs a dashboard by end of day that shows:

• Total sales by region
• Top performing products
• Sales trends over time
• Performance by sales representative

You decide to use AI tools in Excel to speed up the process and create a professional dashboard.

Tasks

Task 1: Clean and Standardize the Raw Data

Open Microsoft Excel and create a new workbook. Enter a sample sales dataset with at least 20 rows that includes columns for date, product name, sales amount, store location, and sales representative name. Intentionally make the data messy-use different date formats in the same column, include a few blank cells, and use inconsistent product names like "Laptop," "laptop," and "Laptop Computer" for the same item.

Now open the AI assistant available in your version of Excel. Use it to clean and standardize your dataset. Your goal is to have consistent date formats, no blank cells, and standardized product names.

Task 2: Generate Summary Calculations

Using the AI tool in Excel, create a new section in your workbook that calculates:

• Total sales for each store location
• Total sales for each product category
• Total sales for each sales representative

Use the AI assistant to help you build these calculations. Do not manually write formulas-instead, describe what you need to the AI tool and let it generate the necessary formulas or summary tables.

Task 3: Create Visual Charts for Key Metrics

Ask the AI tool to create three different types of charts from your summary data. Choose chart types that best represent each metric-for example, one chart might show regional performance, another might display product rankings, and a third might illustrate trends over time.

After the AI generates these charts, examine each one carefully. Observe whether the chart type the AI selected matches what you would have chosen manually. Note which visualization makes the data easiest to understand at a glance.

Task 4: Add Interactivity with Filters or Slicers

Use the AI assistant to add interactive elements to your dashboard. The goal is to allow someone viewing the dashboard to filter the data by time period, store location, or sales representative without changing the underlying data.

Once you have added these interactive elements, test them by selecting different filter options. Observe how the charts and summary tables update. Compare the experience of interacting with this AI-built dashboard versus manually clicking through different sheets or data ranges.

Task 5: Build a Complete Dashboard Layout

Create a single-page dashboard that presents all the key insights your manager requested. Arrange your charts, summary tables, and interactive elements in a clean, professional layout that someone could understand in under 30 seconds. The entire dashboard should fit on one screen without scrolling.

You may use the AI tool as much or as little as you want for this task. Your goal is a finished product that looks professional and communicates the sales story clearly.

Task 6: Reflect on Your Process

Write four to five sentences reflecting on how your approach evolved throughout this assignment. Consider which tasks felt most natural to delegate to the AI tool and which required more of your own judgment. Think about what you had to tell the AI versus what you had to decide on your own. Reflect on whether using AI made you faster, more creative, or both-and where you still needed to apply your own analytical thinking.

What You Practiced

• You used AI tools in Excel to clean and standardize messy real-world data without writing manual formulas for every correction.
• You instructed an AI assistant to generate summary calculations and observed how natural language requests translate into working Excel formulas.
• You created multiple chart types with AI assistance and evaluated which visualizations communicated insights most effectively.
• You added interactive filtering elements to a dashboard and tested how they change the user experience when exploring data.
• You built a complete, professional dashboard from scratch by combining AI-generated components with your own design and analytical decisions.
• You reflected on the balance between AI automation and human judgment when transforming raw data into business intelligence.