NovaMart Distributors
Supply Chain Data Cleaning & Analysis Assignment
Background
You have just joined the analytics team at NovaMart Distributors, a fast-growing wholesale and e-commerce company that sources products from multiple vendors and delivers to various customer regions. Management requires detailed Excel reports that combine data from sales orders, vendor shipments, and delivery tracking systems to better understand vendor performance and delivery timeliness.
Your role is to clean and organize the data, apply provided formulas, and generate key insights that highlight sales trends, delivery delays, and their impact on the business. These reports will help both the Finance and Supply Chain teams track vendor reliability, measure late deliveries, and assess compliance with Service Level Agreements (SLAs). Your analysis will directly support decision-making aimed at improving operational efficiency and customer satisfaction.
Overview of the Task
You will work with four main datasets:
-
Vendor Table: Delivery companies handling orders
-
Product Table: Product master data
-
Sales Orders: Customer orders including the assigned delivery company
-
Delivery Tracking: Delivery dates and customer city data for orders
Your goals are to:
-
Clean and prepare each dataset for accurate analysis
-
Match data across tables to consolidate all relevant details into the Sales Orders table
-
Perform key calculations and analyses related to supply chain performance
-
Create an Analysis sheet summarizing key metrics and insights
Task Stages
1. Data Cleaning:
Review and correct inconsistencies, errors, or missing values in each dataset to ensure the data is reliable for analysis.
2. Data Matching:
Use lookup functions (e.g., VLOOKUP, XLOOKUP) to combine related data points across tables, creating a comprehensive Sales Orders dataset.
3. Data Analysis:
Apply Excel formulas such as SUMIFS, COUNTIFS, AVERAGEIFS, and IF statements to calculate totals, measure delivery performance, identify late deliveries, and extract meaningful business insights.
Part 1: Data Cleaning — Detailed per Column
| Table | Column | Cleaning Task |
|---|---|---|
| Vendor Table | Vendor_ID | Ensure consistent format (e.g., uppercase, no extra spaces using TRIM and UPPER functions) |
| Vendor_Name | Remove extra spaces, apply proper case (PROPER function) | |
| Contact_Name | Trim spaces and apply proper case | |
| City | Remove extra spaces and apply proper case | |
| Country | Remove extra spaces and apply proper case | |
| Contract_Start/End | Standardize date format (e.g., DD/MM/YYYY) | |
| Product Table | Product_ID | Remove spaces (TRIM) |
| Product_Name | Remove extra spaces, proper case | |
| Unit_Price | Remove commas, convert text to numbers | |
| Category | Remove extra spaces, proper case | |
| Units_in_Carton | Assume clean | |
| Sales Orders | Order_ID | Remove spaces, ensure consistent format |
| Delivery Company | Ensure consistent IDs matching Vendor_ID | |
| Order_Date | Standardize date format | |
| Product_ID | Trim spaces, ensure consistency with Product Table | |
| Quantity_Ordered | Assume clean | |
| Customer_City | Remove extra spaces, proper case | |
| Delivery Tracking | Order_ID | Remove spaces, consistent formatting |
| Use Text to Column to split | Customer_City | Remove extra spaces, proper case |
| Delivery Date | Standardize date format |
Part 2: Data Matching — Enrich Sales Orders Table
Use lookup functions to bring the following columns into Sales Orders:
| Target Column | Lookup From | Lookup Key | Notes |
|---|---|---|---|
| Product_Name | Product Table | Product_ID | Product description |
| Unit_Price | Product Table | Product_ID | Needed to calculate sales value |
| Category | Product Table | Product_ID | For category analysis |
| Vendor_Name | Vendor Table | Delivery Company | Delivery company name |
| Delivery Date | Delivery Tracking Table | Order_ID | Actual delivery date |
Part 3: Calculated Columns in Sales Orders
Add these calculated columns with formulas:
| Column Name | Formula/Logic Description | Purpose |
|---|---|---|
| Delivery_Days | = Delivery Date – Order_Date | Measure delivery time in days |
| Late_Delivery_Flag | = IF(Delivery_Days > 5, “Late”, “On Time”) | Flag late deliveries (>5 days) |
| Total_Sales_Value | = Quantity_Ordered * Unit_Price | Revenue per order |
| Order_Month | = TEXT(Order_Date, “MMM-YYYY”) | Month for time trend analysis |
| Delivery_Delay_Days | = IF(Late_Delivery_Flag=”Late”, Delivery_Days – 5, 0) | Days late beyond threshold |
Part 4: Metrics Sheet — Key Summary Metrics
Use formulas to create quick insights:
| Metric | Example Formula | Description |
|---|---|---|
| Total Number of Orders | = COUNTA(SalesOrders!A:A) | Count of all orders |
| Number of Late Deliveries | = COUNTIF(SalesOrders!Late_Delivery_Flag, “Late”) | Count of late deliveries |
| Number of On-Time Deliveries | = COUNTIF(SalesOrders!Late_Delivery_Flag, “On Time”) | Count of on-time deliveries |
| Total Sales Value | = SUM(SalesOrders!Total_Sales_Value) | Total revenue |
| Number of Unique Vendors | = COUNTA(UNIQUE(SalesOrders!Delivery_Company)) | Count of distinct vendors |
Part 5: Analysis Sheet — Summary Tables & Insights
Create tables using these formulas to answer key supply chain questions:
-
Total Sales per Vendor and Category
=SUMIFS(Total_Sales_Value, Vendor_ID, Selected_Vendor, Category, Selected_Category)
Summarizes sales by vendor and product category. -
Number of Late Deliveries by Vendor and Month
=COUNTIFS(Vendor_ID, Selected_Vendor, Delivery_Status, "Late", Delivery_Month, Selected_Month)
Tracks late delivery counts monthly per vendor. -
Average Delivery Days per Vendor and Category
=IFERROR(AVERAGEIFS(Delivery_Days, Vendor_ID, Selected_Vendor, Category, Selected_Category), 0)
Measures average delivery speed by vendor and category. -
Total Orders and Average Order Value per Vendor
Total Orders:=COUNTIF(Vendor_ID, Selected_Vendor)
Average Order Value:=AVERAGEIF(Vendor_ID, Selected_Vendor, Total_Sales_Value)
Insights on vendor sales volume and order size. -
On-Time Delivery Rate (%) per Vendor
=COUNTIFS(Vendor_ID, Selected_Vendor, Delivery_Status, "On Time") / COUNTIF(Vendor_ID, Selected_Vendor)
Percentage of orders delivered on time. -
Sales by Category per Month
=SUMIFS(Total_Sales_Value, Delivery_Month, Selected_Month, Category, Selected_Category)
Sales trends by product category over time. -
Late Delivery Impact on Sales (Value Lost)
=SUMIFS(Total_Sales_Value, Vendor_ID, Selected_Vendor, Delivery_Status, "Late")
Revenue affected by late deliveries. -
Customer City Order Distribution
Orders:=COUNTIF(Customer_City, Selected_City)
On-Time:=COUNTIFS(Customer_City, Selected_City, Delivery_Status, "On Time")
Late:=COUNTIFS(Customer_City, Selected_City, Delivery_Status, "Late")
Geographic analysis of delivery performance.