Build Sarah’s scenario controls, link to outputs and visuals, add validation flags
Build Sarah’s Scenario‑Driven Dashboard
Implement the scenario control, link drivers to outputs, connect visuals, and add validation. Use safe, exact‑match lookups and visible flags so investors trust what they see.
- Create a Driver table with names: Base, Stretch, Downside
- Name key ranges (e.g., Driver_Scenario, Driver_RevenueGrowth)
- Switch outputs using XLOOKUP/INDEX‑MATCH with IFNA guards
- Connect charts to Table columns for auto‑expanding visuals
- Surface validation: missing scenario, stale AsOfDate, out‑of‑range rates
Total Revenue
$116,000
Net Profit
$25,000
Cash Flow
$23,100
Profit Margin
21.6%
Use error checks to catch common integration issues before the meeting:
- Missing scenario name → show friendly message, not #N/A
- Negative or >100% growth rates → flag out‑of‑range
- Stale AsOfDate (older than 30 days) → highlight update needed
- Charts pointing to static ranges → warn about non‑expanding links
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.