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.

Before You Start

  • Open your Lab 4 workbook, or start a new one with three sheets named PriceList, Orders and Dashboard.
  • Check whether your Excel has XLOOKUP (Excel 2021/365). If not, use VLOOKUP; the result is the same.
  • Product names must be spelt identically on both sheets; lookups match text exactly.

Step-by-Step Guide

  1. PriceList (A1:B6):

    Product UnitPrice
    Notebook 120
    Pen 25
    Calculator 950
    Stapler 380
    File Folder 60
  2. Orders. Headers A1:E1: OrderID, Product, Qty, UnitPrice, LineTotal. Enter eight orders: O-101 Calculator 12; O-102 Pen 200; O-103 Notebook 50; O-104 Stapler 10; O-105 File Folder 75; O-106 Calculator 4; O-107 Notebook 90; O-108 Pen 120.
  3. Lookup the price in D2, then copy down to D9:
    • =VLOOKUP(B2,PriceList!$A$2:$B$6,2,FALSE), or
    • =XLOOKUP(B2,PriceList!$A$2:$A$6,PriceList!$B$2:$B$6)

    FALSE forces an exact match; without it, VLOOKUP may return a wrong price.

  4. LineTotal in E2: =C2*D2, copied down. Check: O-101 = 11,400, O-107 = 10,800.

  5. Dashboard figures:

    Measure Formula Expected
    Total revenue =SUM(Orders!E2:E9) 48,300
    Number of orders =COUNTA(Orders!A2:A9) 8
    Average order value =AVERAGE(Orders!E2:E9) 6,037.50
  6. Revenue by product (for the charts). On Dashboard, list the five products in A7:A11, and in B7 enter =SUMIF(Orders!$B$2:$B$9,A7,Orders!$E$2:$E$9), copied down. Expected: Notebook 16,800; Pen 8,000; Calculator 15,200; Stapler 3,800; File Folder 4,500.
  7. Charts. Select A6:B11 (include a header row), then Insert → Column Chart, titled “Revenue by Product”. With the same range, Insert → Pie Chart, titled “Share of Revenue”. Add data labels showing percentages (Chart Elements → Data Labels → More Options → Percentage).
  8. Manager note. Write 2–3 sentences on the Dashboard, for example: “Notebooks and calculators bring in two-thirds of revenue (66%). Staplers sell least; consider bundling them with file folders.”
  9. Optional highlight. Select Orders!E2:E9, then Home → Conditional Formatting → Highlight Cells Rules → Greater Than or Equal To 10000. O-101 and O-107 should be highlighted.

Check Your Work

Checkpoint How to verify
Lookups Every UnitPrice in Orders comes from a formula; changing a PriceList price updates Orders
Dashboard Revenue 48,300; 8 orders; AOV 6,037.50 (with the sample data)
Charts Both charts titled; pie shows percentages; one slice per product
Manager note Two or three sentences that state a finding and a suggested action

Common Errors and Fixes

Problem Fix
#N/A from the lookup The product name does not match exactly; look for extra spaces or different spelling. =TRIM(B2) helps.
Wrong price returned VLOOKUP is missing FALSE as the last argument, so it did an approximate match.
Lookup breaks when copied down The table range was not absolute. Use PriceList!$A$2:$B$6.
Pie chart has eight slices with repeated names You charted the order lines. Chart the SUMIF summary (one row per product) instead.

Extension Task

For those who finish early: Insert a PivotTable from the Orders data (Insert → PivotTable) with Product in Rows and Sum of LineTotal in Values. Compare it with your SUMIF table: which would you use for 5,000 orders, and why?

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?
  5. What does the fourth argument of VLOOKUP (FALSE) do?
  6. Give one advantage of XLOOKUP over VLOOKUP.
  7. What does SUMIF do? Write the formula for revenue from Pens only.
  8. What is average order value, and why does a shop manager care about it?