Excel-Module-Assignment

Overview

This project helps me apply Excel concepts practically. I can build end-to-end Excel solutions by cleaning, analyzing, and visualizing data to create interactive dashboards that provide clear, actionable business insights.

Dataset for Analysis

The project uses the widely-used Sample Superstore Sales dataset, a business dataset that simulates sales, profit, and customer data for a global retail chain.

You can download the dataset directly via this link:
Sample Superstore Sales Excel File

Dataset Overview:

• Structure: The file is an Excel workbook (.xls) with multiple sheets that contain interrelated data.
• Key Tables: The main tables are Orders, Returns, and People. These can be linked to perform a complete analysis.
• Data Scope: It includes four years of transaction data from 2014 to 2017 across the United States, divided into four regions.
• Rich Attributes: Each record has detailed dimensions and metrics, such as:
  • Customer Details: Customer Name, Segment.
  • Product Information: Category, Sub-Category, Product Name.
  • Geographic Data: Country, Region, State, City, Postal Code.
  • Sales Metrics: Sales, Quantity, Discount, Profit.
  • Order Details: Order Date, Ship Date, Ship Mode, Order ID.

Analysis

Solution File is Sample - Superstore - Solution.xlsx

This project aimed to build and showcase basic to intermediate Excel skills through hands-on tasks with the Sample Superstore dataset which included:

• Core Operations: Learned key functions like SUM, MAX/MIN, COUNT variants, and POWER for growth calculations.
• Data Structuring: Used TRANSPOSE and practiced various techniques for cell, number, and text formatting to improve clarity.
• Data Organization: Applied multi-level sorting and filtering, including by values, text, and cell color.
• Date & Logic: Performed date calculations (DATEDIF, TODAY) and included logical tests with IF, AND, OR, and IFERROR.
• Data Cleaning: Carried out thorough cleaning using TRIM, PROPER, TEXT TO COLUMNS, FIND & REPLACE, and the Go To Special feature to manage duplicates, errors, and inconsistent text.
• Data Analysis: Created and customized Pivot Tables for dynamic summarization, grouping, and conditional formatting.
• Data Lookup & Matching: Used VLOOKUP/HLOOKUP for data retrieval and comparison across tables.
• Conditional Aggregation: Applied SUMIFS, COUNTIFS, and AVERAGEIF to perform calculations based on single and multiple criteria.

This exercise transformed raw sales data into a clean, analyzed, and well-presented dataset, reinforcing my ability to use Excel as an effective tool for business intelligence and reporting.

Dashboard Summary

This interactive dashboard provides a complete view of sales, profit, and product performance over several years.

Key Performance Indicators (KPIs):

• Total Sales: ¥2.29M; Total Profit: ¥286K.
• Product Breadth: Covers 17 Sub-Categories with 9994 units sold.
• Category Split: Sales are divided among Furniture, Office Supplies and Technology.

Visualizations & Insights:

• Sales by Sub-Category: A detailed breakdown shows top-performing items like Copiers, Phones and Chairs, along with low-margin categories.
• Segment & Region Analysis: A stacked bar chart shows that the Consumer segment leads sales in all regions; the West is the top market.
• Top 10 Products: Highlights specific high-revenue items like Canon imageCLASS Copiers and Cisco TelePresence Systems.
• Discount Impact: A critical view shows how discount percentages influence sales volume across various customer segments.
• Sales Trend: A line chart tracks monthly sales and profit over time; it reveals seasonal peaks, such as in November, and troughs.

This dashboard helps quickly identify revenue drivers, profitability by category, regional opportunities, and the effect of promotional strategies.

---

---

How to Use the Superstore Sales Dashboard

This interactive dashboard is designed for easy exploration of sales performance. Follow these steps to gain insights:

1. Open the File: Launch the Sample - Superstore - Solution.xlsx file in Microsoft Excel.

2. Navigate & Interact:

   • Review KPIs: Start by looking at the high-level metrics at the top: Total Sales, Total Profit, and Quantity Sold.
   • Apply Filters: Use the interactive slicers, such as Year and Category, to filter the entire dashboard. For example, select "2015" or "Technology" to view performance for that specific year or category.
   • Analyze Charts: Click on or hover over any chart element. Clicking a segment in the "Sales by Sub-Category" chart can filter other visuals. Hovering reveals exact values and percentages.

3. Refresh Data (If Applicable):

   • If the workbook is linked to an updated dataset, refresh the analysis by going to the Data tab and selecting "Refresh All."

Author & Contact

• Name: Pratyush Puri
• Contact: pratyushpuri17@gmail.com / LinkedIn