Assignments

Lab 3 — Exploratory Data Analysis & PivotTables

Due

Sunday of Week 6, 23:59

Released

Week 6

Weight

Part of 20% Participation & Homework

CLOs

2, 6

Goal

Use the cleaned Store Sales Orders dataset from Lab 2 (or the provided EDA dataset) to perform exploratory analysis. Produce a PivotTable-based summary that directly addresses a set of analytical questions about sales patterns.

Data

EDA dataset from Google Drive, or the StoreOrders_Cleaned table you produced in Lab 2.

Tasks

Part A — Descriptive statistics

In an Analysis sheet, compute:

1. For the Amount column: mean, median, standard deviation, min, max using AVERAGE, MEDIAN, STDEV, MIN, MAX.
2. Total orders and total revenue using COUNTA and SUM.
3. Revenue per region using SUMIF. Revenue per category using SUMIF.

Part B — PivotTables

Create three PivotTables:

1. Revenue by Category: Rows = Category, Values = Sum of Amount and Count of OrderID (rename to "Orders"). Add a calculated field or a helper column for Average Order Value.
2. Revenue by Region × Category: Rows = Region, Columns = Category, Values = Sum of Amount. Format as currency. Identify the top-performing Region × Category combination.
3. Monthly trend: Rows = OrderDate (grouped by Month), Values = Sum of Amount. Which month had the highest revenue?

Part C — Patterns and insights

Answer these questions using your PivotTables (1–2 sentences each):

1. Which category generates the highest total revenue? Which has the highest average order value?
2. Which region is the strongest performer? Is the top category consistent across regions?
3. Does monthly revenue show a seasonal pattern? Describe it.
4. Are there any categories or regions that appear to underperform? What would you want to investigate further?

Part D — Slicers (optional)

Add a slicer to PivotTable 1 for Region. Confirm that selecting "North" filters the revenue and order counts in the PivotTable. Add a timeline slicer for OrderDate if available.

Submission

Submit one Excel workbook (.xlsx) containing:

• The source data (as an Excel table)
• An Analysis sheet with descriptive statistics
• Three PivotTables on separate sheets or in a dashboard layout
• A brief text box or comment answering Part C questions

Group Progress Report 3 is also due this week. Submit separately:

• Descriptive statistics summary relevant to your research questions
• Key patterns and insights from EDA on your project data
• PivotTables summarising critical dimensions (2–3 pages + Excel file)