PROJECT 01Beginner
Plan–Fact Analysis for Orion Retail Stores
Business scenario
The Head of Retail reviews every store monthly against the sales plan approved by Finance. Until now the comparison has been assembled by hand and nobody agrees which stores are really behind.
Problem statement
Produce a reliable monthly and year-to-date plan-versus-fact view for 2025 by store and identify which deviations matter.
Learning objectives
- Compute net revenue at the correct grain
- Calculate absolute and percentage variance with a consistent sign convention
- Separate one-off deviations from persistent under-performance
Instructions
Net revenue = quantity × unit_price × (1 − discount_pct) for completed orders, by order date. Plan = sales_targets.target_revenue. Use the convention variance = fact − plan (positive = ahead of plan). Treat a store as persistently behind if it is below 95% of plan in at least 3 of the last 6 months.
Tasks
- Write SQL returning store, month, fact, plan, variance and variance % for 2025.
- Add year-to-date fact, plan and variance per store with a window function or cumulative sum.
- Reconcile: total fact across stores must equal total completed-order revenue for 2025.
- Flag persistently behind stores using the rule above.
- In Excel, build a variance table with conditional formatting (red below 95%, green above 105%).
- Write 5–8 sentences for the Head of Retail.
Deliverables
- SQL file
- Excel workbook with variance table
- Short written summary
Evaluation rubric
| Criterion | Weight | Excellent | Proficient | Developing | Insufficient |
|---|---|---|---|---|---|
| Data preparation & validation | 20% | Correct filters and grain; validation checks documented and totals reconciled. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Variance calculations | 25% | All variances correct, sign convention stated, YTD correct. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Identification of persistent issues | 15% | Rule applied correctly and explained. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Interpretation & recommendations | 25% | Clear, quantified conclusions tied to the business question, with limitations stated. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Presentation | 15% | Concise, well-labelled outputs a manager can act on without explanation. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
Guided solution
The guided solution unlocks after you submit your own attempt.