Build a Driver-Based Planning Model

This guide builds a planning model from business drivers instead of copied spreadsheet values.

Before You Start

Choose one decision and one owner.

Examples include:

• How does hiring affect cash over the next 12 months?
• What revenue follows from customer growth and pricing?
• How does a collections delay affect working capital?
• What operating plan reaches a target gross margin?

Collect only the actuals and assumptions required for that decision.

1. Define the Output

Write the primary output first.

Examples:

• Ending cash by month
• Monthly recurring revenue
• Headcount cost by department
• Gross margin percentage
• Receivables balance and DSO

Add one or two supporting outputs. Do not begin with every metric the business tracks.

2. Draw the Driver Chain

Work backward from the output.

For a simple revenue plan:

1. Beginning customers
2. New customers
3. Churned customers
4. Ending customers
5. Average revenue per customer
6. Revenue

For a hiring plan:

1. Beginning headcount
2. Planned hires
3. Departures
4. Ending headcount
5. Loaded cost per employee
6. Payroll expense

Each relationship should be explainable in one sentence.

3. Create or Choose the Model

1. Open Scenario planning → Models.
2. Select a template that matches the driver chain, or start blank.
3. Name the model for the subject and period.
4. Assign an accountable owner.
5. Save it in the appropriate folder.

Use a template only when its structure helps. Delete or rename a template variable only after checking what depends on it.

4. Set the Timeline

Open Model settings and choose:

• granularity
• start period
• end period
• last actual date
• fiscal-year display when needed

Use the same period detail as the decision. Avoid daily detail for a quarterly decision.

5. Add Actuals

Import or link the actuals that establish the starting point.

1. Refresh the planning source.
2. Import or link the required actuals.
3. Compare one known period with the source.
4. Confirm the last actual date.
5. Keep actual rows distinct from assumptions.

Do not continue until the starting actuals are credible.

6. Create Driver Variables

Create one variable for each driver.

For every variable, set:

• a specific label
• the number format and unit
• input or formula behavior
• the relevant dimensions
• an owner or rationale

Use sections and groups to separate assumptions, operating calculations, and outputs.

7. Add Formulas in Order

Build from upstream drivers to downstream outputs.

1. Add the simplest relationship.
2. Save it.
3. Change one input.
4. Confirm the result changes.
5. Continue to the next relationship.

Use formula suggestions and templates instead of guessing function names. Create helper variables when one formula becomes difficult to explain.

For the revenue chain above, a reproducible monthly sequence is:

Create the four input variables before saving these formulas. Select each variable from Formula Explorer, then change New customers per month and confirm all three calculated rows respond.

8. Add Dimensions Only Where Needed

Apply a dimension when the decision needs a breakdown such as department, region, entity, or cohort.

After applying it:

1. Confirm the members.
2. Check member-level inputs.
3. Review the aggregate.
4. Confirm source mappings.
5. Test one member independently.

A dimension should make the model more useful, not only larger.

9. Create a Base Case

The base case should represent the team's current operating expectation.

Confirm:

• actuals are current
• assumptions have owners
• formulas calculate without unresolved errors
• units and signs are consistent
• the model status is Up to date
• the owner can explain the key driver chain

Save a named version such as Initial reviewed base.

10. Add One Scenario

Create one alternative case by changing only the drivers that define it.

Examples:

• Hiring delayed one quarter
• Conversion 15% lower
• Price increase starts in July
• Collections move 30 days later

Compare the output with the base. If the result does not change, trace the driver path before adding more overrides.

11. Check the Model

A reviewable driver model should pass these checks:

• one known actual period matches its source
• one formula can be reproduced independently
• each key input has an owner
• each scenario override has a rationale
• outputs respond in the expected direction
• dimension totals reconcile
• time settings match the decision
• calculation status is current

12. Prepare the Review

1. Save a focused view.
2. Include the key drivers and outputs.
3. Select the intended scenario.
4. Add comments to unresolved assumptions.
5. Share the view with the lowest required access.
6. Export a point-in-time copy when the review needs a file.

Troubleshoot

The model has too many inputs

Replace copied period values with formulas and keep only the assumptions that a person must own.

A formula is difficult to audit

Split it into named helper variables and validate each intermediate result.

Dimension totals do not reconcile

Check missing members, aggregation settings, and source-to-member mappings.

The scenario produces an unexpected direction

Trace the changed driver through each dependent formula and check signs, units, and active periods.

Next Steps

• Planning Models and Formulas
• Configure Planning Time and Actuals
• Compare and Share Planning Scenarios
• Run Planning Uncertainty Analysis