Project: budget vs actual model
From: Ellis Park, FP&A Manager
Our fictional Targets table has one row per region and month. Sales has transaction rows. Build a 2025 budget review with an explicit target measure, variance and variance percent. Show Region by Year in a matrix, mark negative variance in red and nonnegative variance in green, and add a Demand change scenario from -20% to 20% in five-point steps. The final chart should compare actual sales with the matching target.
The finance team keeps monthly regional targets in a separate table. Actual sales remain daily transaction rows. Build a comparison that respects both grains instead of joining each target onto every sale. Start with a region-aware target, then extend it to the visible year and month before calculating variance.
Each step continues from your work. The report must remain correct when a reader changes region or year, so the checks use several filter contexts and a hidden sales variant. Later steps add conditional variance formatting and a disconnected demand assumption. The scenario changes a measure, not the source plan. Finish with a regional comparison and a declared 2025 page scope so finance can distinguish the baseline actuals, the plan and the hypothetical adjustment.
Region + Year + Month -> TREATAS -> Targets
Variance = Actual - Target
Variance % = DIVIDE(Variance, Target)
Scenario = Actual * (1 + selected demand change)
A mixed-grain budget report