Build Sarah’s café dashboard with scenario controls, linked charts, and validation
Build Sarah’s Integration Dashboard
Implement scenario controls with a driver table, link outputs to visuals, and install validation that catches missing names, stale dates, and out‑of‑range rates.
- No hard‑coded values in outputs; all driven by inputs.
- Scenario switches by exact name; IFNA/IFERROR protects demos.
- Charts bind to outputs that use Table[Column] references.
- Audit panel counts: missing keys, stale AsOfDate, rate bounds.
- Executive summary states the decision and the why.
Weekend Revenue
$107,700
Café Profit
$35,200
Food Waste
2.8%
Customer Traffic
1,240/day
Use the validation tool to practice writing rules like “Scenario name must not be blank,” “Rates between 0% and 100%,” and “AsOfDate within 30 days.” Transfer these patterns into your spreadsheet model.
Error Checking System Builder
🔍Master conditional formatting and data validation rules for business automation. Build the Excel skills needed for Unit 2's Month-End Wizard - where error checking reduces closing time from days to hours.
Business Context:
Unit 5 PayDay Simulator: Automated payroll error checking prevents costly mistakes and compliance issues.
Validation Rules:
Overtime Hours Detection
HighHighlight employees working more than 40 hours per week
Missing Employee ID
HighFlag blank or missing employee identification numbers
Gross Pay Calculation Error
HighDetect discrepancies between calculated and entered gross pay
Missing Department Assignment
MediumIdentify employees without department assignments
Select a validation rule to test it
The rule will highlight errors in the sample data using conditional formatting colors
Month-End Wizard Connection
These error checking skills are essential for Unit 2's Month-End Wizard, where students learn to:
- • Build automated validation systems that catch posting errors before they impact financial statements
- • Use conditional formatting to visually highlight discrepancies and missing data
- • Create Excel formulas that validate complex business logic automatically
- • Reduce month-end closing time from days to hours through systematic error prevention
- • Design validation rules that improve data quality and reduce manual checking
Next Step: Apply these validation concepts to build your own Month-End Wizard with automated error checking systems.
Practice linking charts to data using structured references, then build recommendations based on KPI thresholds.
Link Revenue chart to the Sales Table using structured references.
Data range: SalesTable[Revenue]
Profit Margin: 12% (threshold: 10%)
- Use
Table[Column]structured references for stable charts - Charts break when they point to fixed ranges—structured refs auto-expand
- Recommendations must cite specific KPIs and thresholds
- Never make recommendations from hard-coded values—use model outputs