Excel Sales Dashboard

Import the CSV File

• Import sales_data.csv into a new Excel workbook
• Name this worksheet "Raw Data"
• Do NOT modify this sheet - it serves as your backup

Create a Copy for Cleaning

• Copy the Raw Data worksheet and name it "Cleaned Data"
• All cleaning operations will be performed on this sheet

Remove Duplicate Transactions

• Use Remove Duplicates feature on Transaction_ID column
• Document how many duplicates were found (add a note or comment)

Fix Date Formats

• Standardize all dates to MM/DD/YYYY format
• Use Text to Columns or formulas if needed
• Apply Date formatting to the entire Date column

Clean Text Columns

• Customer_Name, Product, Sales_Rep: Use TRIM() to remove extra spaces
• Customer_Name, Sales_Rep: Use PROPER() for proper capitalization
• Category, Region: Use UPPER() for consistent uppercase
• Create helper columns if needed, then paste values and delete helpers

Fix Numeric Columns

• Convert Unit_Price and Cost from text to numbers (remove $ symbols)
• Delete rows where Quantity is zero or negative
• Use Find & Replace to remove $ symbols or VALUE() function

Calculate Missing Values

• Total_Sale: = Quantity * Unit_Price
• Profit: = Total_Sale - (Quantity * Cost)
• Use IF() to only calculate where values are missing (don't overwrite existing correct values)

Handle Blanks

• Check for blank cells in Product, Category, and Region columns
• Either fill with "Unknown" or delete those rows (document your choice)

Add Data Validation (Optional Bonus)

• Apply Data Validation to Category column (list of valid categories)
• Apply to Region column (list: NORTH, SOUTH, EAST, WEST)

Create Analysis Worksheet

• Create a new worksheet named "Analysis"
• This sheet will contain calculated metrics and summary tables

Monthly Sales Summary

Create a table showing sales by month with these columns:

• Month: Jan 2025, Feb 2025, etc.
• Total Revenue: Use SUMIFS() referencing Cleaned Data
• Total Profit: Use SUMIFS()
• Transactions: Use COUNTIFS()
• Profit Margin %: = (Total Profit / Total Revenue) * 100

Category Performance

Create a summary table by category:

• Category: List unique categories
• Total Revenue: Use SUMIF()
• Total Profit: Use SUMIF()
• Avg Transaction Value: Use AVERAGEIF()
• Units Sold: Use SUMIF() on Quantity column

Regional Performance

Create a summary table by region (same structure as Category Performance)

Top 10 Products

• Create a table showing top 10 products by revenue
• Use SUMIF() to calculate revenue per product
• Sort manually or use advanced techniques

Sales Rep Rankings

• Create a table showing each sales rep's total sales and profit
• Include commission calculation: = Total_Sale * 0.03 (3% commission)

Pivot Table 1: Sales by Category and Month

• Rows: Category
• Columns: Month (from Date field)
• Values: Sum of Total_Sale
• Format as currency
• Add a Slicer for Region

Pivot Table 2: Sales Rep Performance

• Rows: Sales_Rep
• Values: Sum of Total_Sale, Sum of Profit, Count of Transaction_ID
• Sort by Total_Sale (descending)
• Add Calculated Field: Profit Margin % = Profit / Total_Sale

Pivot Table 3: Product Performance by Region

• Rows: Region, then Product (nested)
• Values: Sum of Total_Sale, Sum of Quantity
• Show top 5 products per region

Create Charts from Pivot Tables

• Chart 1: Column chart for Sales by Category
• Chart 2: Line chart for Monthly Sales Trend
• Chart 3: Bar chart for Top 10 Sales Reps
• All charts must have titles, axis labels, and legends

Create Executive Dashboard

Design a one-page dashboard that includes:

• KPI Cards (4): Total Revenue, Total Profit, Transactions, Avg Transaction Value
• Charts (3-4): Copy charts from Pivot Tables worksheet
• Summary Tables: Top 5 Products, Top 5 Regions, Top 5 Sales Reps
• Design: Use colors, borders, and formatting for professional appearance
• Interactive: Link slicers from pivot tables if possible

Create Repository

• Go to GitHub and create a new public repository
• Name it exactly: techmart-sales-dashboard
• Initialize with README

Upload Files

• Upload your Excel workbook
• Upload original CSV file
• Create screenshots folder and upload images
• Edit README.md with all required information

Submit Link

• Copy your repository URL
• Submit via the course submission form
• Format: https://github.com/your-username/techmart-sales-dashboard