Skill Workbench
← Back to course

Excel Sales Reporting: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

Excel Sales Reporting

Original practice materials · Version 1.0.0 · Synthetic business data

1. Look up a region

Join a sales row to a region using an explicit key.

  1. Look up customer_id in the mapping table below.
  2. Use exact matches; an unknown customer must not inherit a nearby row.
  3. Keep every sale.

Attach a customer region

  • Mapping: customer 1 = East; customer 2 = West.
  • Return Unknown for customer 99.
  • Keep all three sales.
order_id,customer_id,region
1,1,
2,2,
3,99,

Reference answer

order_id,customer_id,region
1,1,East
2,2,West
3,99,Unknown

An unmatched customer is not a West customer.

Apply an exact product lookup

  • Mapping: PEN = 3 whole dollars; MUG = 12.
  • Unmatched products use 0 under this exercise policy.
sku,unit_price
PEN,
MUG,
OTHER,

Reference answer

sku,unit_price
PEN,3
MUG,12
OTHER,0

Only exact product keys match.

Extension: A production workbook may use XLOOKUP; this workbench uses a small exact LOOKUP.

2. Calculate conditional totals

Use conditions before summing sales.

  1. The declared sales are East 120, East 100, West 180 and West 120.
  2. A conditional total includes only the matching region.
  3. Write a two-row summary; do not include raw rows.

Summarize regional revenue

  • Sales: East 120, East 100, West 180, West 120 (whole dollars).
  • Return region and revenue with one row per region.
region,revenue
East,
West,

Reference answer

region,revenue
East,220
West,300

Do not mix the regions or omit the 100-dollar sale.

Count paid orders

  • Statuses: paid, pending, paid, paid.
  • Return one count per status.
status,orders
paid,
pending,

Reference answer

status,orders
paid,3
pending,1

COUNTIF counts matching rows, not the dollar amounts.

Extension: Try SUMIF on a scratch grid using ranges from the same rows.

3. Build a pivot-style summary

Group by region and product without duplicating totals.

  1. Use two dimensions as the grouping key.
  2. Keep each region-product group once.
  3. The source sales are East PEN 6, East PEN 9, East MUG 12, West PEN 3.

Report revenue by region and product

  • Source: East PEN 6; East PEN 9; East MUG 12; West PEN 3.
  • One row for each region and sku pair.
region,sku,revenue
East,PEN,
East,MUG,
West,PEN,

Reference answer

region,sku,revenue
East,PEN,15
East,MUG,12
West,PEN,3

Grouping by region alone loses the product breakdown.

Separate count from revenue

  • Source: East 120; East 100; West 180.
  • Return row count and summed revenue for each region.
region,order_count,revenue
East,,
West,,

Reference answer

region,order_count,revenue
East,2,220
West,1,180

Count is the number of records; revenue is their total.

Extension: A pivot table performs the same group-and-sum operation with a graphical interface.

4. Audit a report

Reconcile totals with an independent calculation.

  1. Compare displayed totals with the source rows.
  2. Calculate quantity × unit price before summing.
  3. A checksum catches a missing line but does not prove every business rule.

Fix line item calculations

  • line_total = quantity × unit_price in whole dollars.
  • Keep both products and other cells.
sku,quantity,unit_price,line_total
PEN,2,3,5
MUG,1,12,12

Reference answer

sku,quantity,unit_price,line_total
PEN,2,3,6
MUG,1,12,12

The PEN total should be 6, not 5.

Reconcile a summary total

  • Grand total must equal East + West.
  • Preserve the correct regional totals.
metric,value
East,220
West,300
Grand total,510

Reference answer

metric,value
East,220
West,300
Grand total,520

The independent sum is 520.

Extension: Use independent counts and totals in a handoff checklist.

Final project

Deliver a regional report

  • Source: East 120; West 180; East 100; West 120.
  • Return region, order_count, revenue (whole dollars).
  • Include a Total row reconciling the four orders.
region,order_count,revenue
East,2,220
West,2,300
Total,4,520

Source objectives: https://support.microsoft.com/en-us/excel/functions/sumif-function. Independent original materials. CC BY 4.0.