Objectives

  • Create and title a column chart and a pie chart.
  • Use VLOOKUP (or XLOOKUP) to pull prices from a lookup table.
  • Build a one-page summary for a manager.

Theory: Application software, IT for business.

Prerequisite: complete Lab 4 or bring an equivalent sales sheet.

Tasks (55 minutes)

Workbook Surname_IT231_Lab5.xlsx:

  1. Sheet PriceList: Product, UnitPrice (at least five products).
  2. Sheet Orders: OrderID, Product, Qty; UnitPrice via VLOOKUP/XLOOKUP; LineTotal = Qty * UnitPrice.
  3. Insert a column chart of Product vs LineTotal; give it a clear title.
  4. Insert a pie chart of share of LineTotal by Product (or by Status if you add one).
  5. Sheet Dashboard: total revenue, average order value, count of orders, and a short note (2–3 sentences) for a manager.
  6. Optional: conditional formatting on LineTotal ≥ 10000.

Deliverable

The .xlsx file with both charts visible.

Viva

  1. When is a pie chart a poor choice?
  2. What must be true about the lookup table for VLOOKUP to work?
  3. Why separate PriceList from Orders?
  4. What KPI would you add next for a retail shop in Nepal?