Cases: Guided Business Investigations
The controller, sales coordinator, operations planner, and design lead need explanations they can act on. These eight cases take you from their question to a reproducible analysis and a supported recommendation.
Each case contains a fully worked investigation in Excel and SQL, several explanatory figures, session stopping points, and an independent challenge. Choose either tool to address the shared business question and expected results. Keep a record of your scope, checks, source evidence, and what remains uncertain.
Start with Resolve a Customer Account Review. If you are still learning to filter, join, or summarize records, complete the relevant Learn and Practice lessons first. The Excel and SQL setup guides remain available throughout.
Allow 60–90 minutes per session as a planning estimate. Basic cases have two sessions, intermediate cases three, and advanced cases four. The eight cases total 23 sessions, or 23–34.5 estimated hours at that rate, excluding setup, tutorials, and reading. These are planning estimates, not observed completion times. Complete the prerequisites for your selected case without needing to study all eight.
Basic
Follow a defined population, reconcile evidence, and explain a result.
Case 1 · 2 sessions · Excel or SQL
Resolve a Customer Account Review
What can we tell the customer about delivery, billing, and the amount still unsettled?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Keep the order cohort fixed while changing event cutoffs. Distinguish receipt from application and balance each complete voucher.
- Deliverable
- A document timeline, two-date invoice settlement schedule, and a customer response with an action owner.
Case 2 · 2 sessions · Excel or SQL
Review Design Work and Billing
How does design effort become approved work, billed amounts, and recorded cost?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Distinguish work dates, approval dates, billing coverage, and invoice dates when summarizing hours and costs at engagement/month grain.
- Deliverable
- An engagement-period reconciliation, an hours/billing/cost bridge, and a design review memo.
Case 3 · 2 sessions · Excel or SQL
Explain Supplier Obligations at Month End
Which selected obligations are uninvoiced receipts, supplier payables, or remaining estimates?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Aggregate receipt, invoice, payment, and estimate evidence separately before explaining GRNI, AP, and accrued-service obligations at each cutoff.
- Deliverable
- Separate goods and expense-obligation schedules, selected posting checks, and a quarter-end review note.
Intermediate
Compare measures, investigate competing explanations, and recommend an action.
Case 4 · 3 sessions · Excel or SQL
Choose a Product and Pricing Priority
Which product group deserves a focused review, and what evidence supports that priority?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Preserve invoice-line grain and state the limits of current standard costs and separately dated credits in historical comparisons.
- Deliverable
- A product comparison, selected pricing evidence, a simple analytical chart, and a recommendation memo.
Case 5 · 3 sessions · Excel or SQL
Explain Production Cost and Delivery Pressure
Do completion, recorded cost, and financial close tell the same production story?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Aggregate issue, labor, completion, and close independently by work order, and compare capacity at one row per center/day.
- Deliverable
- A work-order cost bridge, completion/close comparison, capacity evidence, and a production review memo.
Case 6 · 3 sessions · Excel or SQL
Explain Staffing, Overtime, and Payroll Cost
What do staffing plans, approved work, and paid cost actually establish?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Preserve approved-time detail, distinguish identified from unassigned centers, and keep employee-period pay separate from center workload.
- Deliverable
- A coverage comparison, employee-period pay reconciliation, settlement schedule, and staffing recommendation.
Advanced
Reconcile processes and periods, evaluate uncertainty, and defend priorities.
Case 7 · 4 sessions · Excel or SQL
Explain Profit, Cash, and the Year-End Close
How can the company report profit while cash falls, and what does the year-end close change?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Retain full ledger history for openings and distinguish pre-close performance, cash movements, balance snapshots, and complete closing vouchers.
- Deliverable
- A full-history balance rollforward, profit/cash bridge, selected source reconciliation, and management review memo.
Case 8 · 4 sessions · Excel or SQL
Investigate Exceptions and Recommend Controls
Which query flags become supported findings, and what control could be assessed?
- Preparation
- Ready when you can
- Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
- Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
- Define an explicit criterion and denominator, distinguishing time entries from people and an exception flag from a proven control failure.
- Deliverable
- An exception register, two source-supported workpapers, and prioritized control recommendations.
Check Readiness for the Chosen Module
Start with the focused module routes. Each case links its prerequisites individually so students in shorter modules can establish the skills its investigation requires. Before a case, show one source trace with an explicit grain, valid key and match-count check, reporting cutoff, independently checked amount, and bounded conclusion. Use the Lesson 3, Lesson 6, and advanced-case gates as appropriate to the route.
Use the blank evidence template and public method rubric to retain this evidence. The public method-repair exercise provides method feedback before assessment while keeping independent challenge answers private.
Keep Evidence, Then Make a Decision
At every stage, predict what you expect, run the analysis, compare the worked checkpoint, and investigate differences. Save the intermediate outputs before continuing. Return to the management request: what can you now explain, which evidence supports it, and what should the team do next?
A complete submission explains the population, calculation, result, source check, limitation, and proposed next action. The worked core shows this method openly. The separate challenge asks you to transfer it to a new population without a public model answer.
After a case, use Reports and Analytics and the Query Library as extensions. Their broader measures may use different populations, dates, or definitions, so compare those before comparing totals.