Assignments

Lab 1 — Excel Basics with Product Inventory

Due

Sunday of Week 2, 23:59

Released

Week 2

Weight

Part of 20% Participation & Homework

CLOs

1, 6

Goal

Work with a 50-row Product Inventory dataset (two sheets: Inventory and Suppliers) to build an analysis workbook. The exercises cover aggregation, conditional logic, lookup, and cell reference types. The dataset is small enough that you can verify every answer manually.

Data

Product-Inventory.xlsx — provided in class or uploaded to the LMS.

Two sheets:

• Inventory: 50 products with Product ID, Product Name, Category, Supplier Code, Unit Price, Quantity in Stock, Reorder Level, Supplier Name.
• Suppliers: Lookup table mapping Supplier Code → Supplier Name.

Tasks

Part A — Aggregation

In a new Analysis sheet, compute:

1. Total inventory value (Unit Price × Quantity, summed across all products) using SUMPRODUCT.
2. Average unit price using AVERAGE.
3. Total product count using COUNTA.
4. Highest and lowest unit prices using MAX and MIN.
5. Total inventory value per category using SUMPRODUCT with a boolean mask or a helper column.

Part B — Conditional logic

1. In the Inventory sheet, add column I (Stock Status): =IF(F2<G2,"Reorder","OK") — flag products where Quantity < Reorder Level.
2. Add column J (Inventory Value): =E2*F2.
3. Add column K (Priority): classify as High/Medium/Low based on Inventory Value (>5000/2000–5000/<2000) using nested IF.
4. In the Analysis sheet: count how many products need Reorder using COUNTIF. Count by category using COUNTIF. Count Electronics that need Reorder using COUNTIFS.

Part C — VLOOKUP

1. In the Analysis sheet: create a lookup where the user types a Product ID in one cell, and a formula retrieves the Supplier Name from the Inventory sheet.
2. Add a new column L in the Inventory sheet that looks up the Supplier Name from the Suppliers sheet using VLOOKUP(D2, Suppliers!A2:B5, 2, FALSE). Compare it with column H — they should match.

Part D — Cell references

1. In the Inventory sheet, confirm column J (=E2*F2) uses relative references — verify that J3 shows =E3*F3 after copy-down.
2. In Analysis!B3, compute the average price. In column M of Inventory, compute =E2-$B$3 (price deviation from average). Confirm that $B$3 stays fixed when the formula is copied down.

Part E — Summary table

Build a 5-row summary table in the Analysis sheet covering all five categories (Electronics, Clothing, Food, Books, Sports), with columns: Total Inventory Value, Average Price, Product Count, Products Needing Reorder. Use SUMPRODUCT, AVERAGEIF, COUNTIF, COUNTIFS.

Submission

Submit one Excel workbook (.xlsx) to the LMS by the deadline. The workbook must have:

• The Inventory sheet with Stock Status, Inventory Value, and Priority columns filled.
• An Analysis sheet with all summary tables.
• Clear labels on all computed cells.

Include your full name and student ID in a comment in cell A1 of the Analysis sheet.

Group Progress Report 1 is also due this week. Submit separately to the LMS:

• Selected project topic and dataset justification
• Problem statement and 3–5 research questions
• Initial data exploration plan and group work plan (1–2 pages, Word or PDF)