Excel for Data Analysis and Reporting

Prepare a reporting table from related files

Prepare a reporting table from related files

The existing Foundations Excel module already teaches cleaning, formulas and lookups; use it as a prerequisite rather than repeating its workbook. This exercise adds a multi-table reporting contract. Import orders.csv and order_lines.csv locally, preserve text keys and retain cancelled status until the reporting filter is applied. A lookup should enrich each line from a unique order key. Document matched and unmatched counts before deriving values. Keep raw source tabs separate from working calculations and preserve integer cents until presentation.

Follow a concrete example

Use an exact-match lookup keyed by order_id and test the unmatched case deliberately in a separate copy. A lookup that returns the first of two parent rows can appear successful while hiding ambiguity. Keep a parent-key uniqueness check beside the merge/lookup procedure. Preserve the original imports so a correction can be traced rather than merely remembered.

Further reading: Microsoft model relationships, Microsoft CALCULATE, W3C complex images. The case and exercises are authored for DataCareerHub.

Your task

Create a working line table with order date and status from an exact order lookup. Record key uniqueness, unmatched count and completed line count.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Create PivotTables and compatible measures

Create PivotTables and compatible measures

A PivotTable groups its underlying rows; it does not automatically know that a repeated order ID represents the same order. Use the data model’s distinct count where available, or first create a verified order-grain table for order counts and average order value. Sum line values for monetary totals. A conventional row count on the line table returns 42, not 21 orders. Feature availability varies by Excel platform/version, so an order-grain summary is a transparent fallback. The included CSV and formulas are practice inputs, not a supplied live PivotTable object.

Follow a concrete example

If distinct-count support is unavailable, build an order-grain intermediate: one row per order with its summed line value and status. The monthly order count then counts those rows. Do not mix a line-value PivotTable with a separately filtered order-count table unless their populations are deliberately aligned and reconciled.

Inspect the reference implementation

Language: Python 3.12. The complete downloadable file includes the named helpers and the fresh-output command-line runner. Trace the source, transformation and result against this lesson’s contract before running it locally.

def bi_measures(source,out):
 data=sales(source);groups={}
 for month in ['2026-04','2026-05','2026-06']:
  subset=[r for r in data if r['order_date'].startswith(month)]
  n=len({r['order_id'] for r in subset});value=sum(r['value_cents'] for r in subset)
  groups[month]={'completed_orders':n,'line_value_eur':value/100 if subset else None,'average_order_eur':value/n/100 if n else None,'coverage':'available' if subset else 'no source coverage'}
 return {'months':groups,'grand_average_eur':1190/21,'arithmetic_mean_of_monthly_aov':(600/11+590/10)/2,'warning':'Grand AOV must use grand value/grand orders, not mean of monthly averages.'}

Further reading: Microsoft model relationships, Microsoft CALCULATE, W3C complex images. The case and exercises are authored for DataCareerHub.

Your task

Build monthly completed value and order-count summaries. State whether you used a distinct count or an order-grain intermediate.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.

Reconcile formulas and write a reporting note

Reconcile formulas and write a reporting note

Use visible formula steps so a reader can trace the measure. Convert cents to EUR at presentation, and compute grand AOV from grand value divided by grand orders. The arithmetic mean of two monthly AOVs is unweighted and differs when counts differ. Check the source population, monthly sum and grand total independently. Keep formulas editable and label literal expected results separately so they cannot be mistaken for calculated cells. Record the Excel platform and any changed function names or separators in your handover.

Follow a concrete example

Calculate April AOV as 600/11 and May as590/10. A grand total should use1190/21, whereas their unweighted arithmetic mean treats each month as equally sized. Document which calculation the stakeholder asked for. A formula displaying the expected number without a traceable source/denominator is not an adequate solution.

Further reading: Microsoft model relationships, Microsoft CALCULATE, W3C complex images. The case and exercises are authored for DataCareerHub.

Your task

Write the grand AOV formula and compare it with the unweighted monthly average. Prepare a short report note with exclusions.

Use learner_worksheet.txt and the provided source/code files. Save your own artifact and compare the separate solution after attempting the task.