fx Foundations

Capstone

30 min1 lessons6 quiz questions

Lessons

1. Capstone: clean a 2,000-row messy sales export + build a summary block30 min

Practice file

Foundation capstone practice workbook

m7-capstone-practice.xlsx contains exactly 2,000 synthetic sales rows, excluding the header. Complete the yellow cells and save your finished workbook. Budget 30 minutes for the guided workflow.

Sheet	Purpose
Read Me	Task, business rules, timing and handover instructions.
Raw Export	Original 2,000-row export; keep unchanged.
Clean Work	Copy of the export plus ten empty helper columns for normalization, duplicate checks and net sales.
Summary	Empty KPI, regional and monthly summary cells, plus space for observations.
Clean Answer	Reference: 1,900 unique cleaned orders, including 20 Review rows.
Answer Key	Formulas, expected totals and completion criteria.

Each order has one line item. Deduplicate by normalized ID; keep valid returns and promotional zero prices. Missing quantities stay flagged for review and excluded from Ready sales metrics. The lesson includes all required formulas and instructions for naming the cleaned Table Sales.

हिंदी: Raw Export बचाएँ। Clean Work में helper formulas भरें, values paste करें, Clean ID से duplicates हटाएँ और Sales Table बनाएँ। Summary भरकर row counts और totals मिलाएँ; गायब quantities का अनुमान न लगाएँ।

The workbook uses synthetic data and contains no macros or external connections. XLOOKUP and dynamic arrays are not required. Formula examples use commas; adapt argument separators to your Excel locale if needed.

Module quiz (6)

1. Should a missing quantity be replaced with zero?

No — retain the order as Review and exclude it from Ready sales metrics.

2. Which key removes duplicate imports in this exercise?

The normalized Clean ID, because each order has one line item.

3. Why must regional and monthly revenues both equal the overall net revenue?

Each grouping partitions the same Ready orders; a mismatch reveals missing, duplicated or differently filtered data.

4. Why keep a negative quantity?

It represents a valid return that reduces net units and net revenue.

5. Does a zero-price order belong in the Ready order count?

Yes, if its quantity is present; it contributes zero revenue.

6. How many orders remain after deduplication, and how many are Ready?

1900 unique orders remain: 1880 Ready and 20 Review.