Planning Models and Formulas

A planning model turns business relationships into a calculation that can be reviewed, changed, and reused. It keeps assumptions, imported actuals, formulas, dimensions, scenarios, and outputs in one grid.

Use this page when you need to understand how a model is structured or why a value changes.

Core Model Elements

Keep these concepts separate. A scenario is not a version, and a saved view is not a frozen copy.

Plan the Model Before You Build It

Start with a decision, not a spreadsheet layout.

1. Write the decision in one sentence.
2. Identify the outputs needed for that decision.
3. List the assumptions that drive those outputs.
4. Identify the actuals that should anchor the model.
5. Add dimensions only when the decision needs a breakdown.
6. Choose the time range and granularity.

A smaller model is easier to audit. Add detail after the first calculation path works.

Inputs, Actuals, and Formulas

A reliable model distinguishes observed data from planning judgment.

• Use imported actuals for values already recorded in a connected source.
• Use inputs for assumptions owned by a person or team.
• Use formulas for relationships that should continue across periods.
• Use scenario overrides for alternative cases.

When a cell can resolve from several layers, connected data remains authoritative for imported periods. A scenario override supplies an alternative value where it applies. The formula supplies the calculated fallback.

Do not replace an actual with a manual input only to make the output match an expectation. Correct the source, mapping, boundary, or formula instead.

Create Variables

Use names that describe the business concept and unit.

Good examples include:

• Active customers
• Average contract value
• Monthly payroll
• Gross margin percentage
• Ending cash

Avoid ambiguous labels such as Total, Other, or New variable.

For each variable, confirm:

• whether it is an input or formula
• its number format and unit
• the periods where it should calculate
• the dimensions that apply
• the source, owner, or rationale
• whether a scenario can override it

Group related variables into sections so a reviewer can follow the calculation from drivers to outputs.

Build Formulas

Open the formula editor from the variable or cell that should calculate.

The editor supports:

• references to other variables
• arithmetic relationships
• time windows such as [previous], [this], [final], and [all]
• if ... then ... else conditions, and blank to test for a missing value
• mathematical, statistical, finance, and time functions
• aggregation across a window, such as sum(Revenue[all])
• dimension-aware calculations with by <Dimension>
• probability functions for uncertainty analysis
• searchable formula templates

Formula templates insert a starting expression with placeholders. Replace each placeholder with the correct variable or value before saving.

Build formulas in dependency order. For example:

1. Calculate customer count.
2. Calculate average revenue per customer.
3. Calculate revenue.
4. Calculate direct costs.
5. Calculate gross profit.
6. Calculate operating cash movement.

This sequence makes the first upstream error easier to find.

Worked Revenue Chain

For a monthly subscription model, create these inputs first:

• Starting customers
• New customers per month
• Monthly churn rate
• Average revenue per account

Then add the formulas in dependency order:

[previous] reads the preceding model period. The first-period condition uses Starting customers because no preceding customer value exists. Select variable names from Formula Explorer so the references match the model exactly.

Test the chain by changing New customers per month. Customers, MRR, and ARR should all move in the expected direction. If they do not, check the first formula whose result stays unchanged.

Use Formula Functions Carefully

The formula library groups every function into five categories:

Use suggestions in the formula editor to confirm the accepted function name and parameters. A familiar spreadsheet function can have different parameter expectations in a planning model.

Probability functions such as normal, uniform, triangle, and sample return a stable central value in the ordinary grid. They draw different values only during an uncertainty simulation.

Link Another Planning Model

Open the model source controls when one planning model should use variables from another.

1. Select a team planning model.
2. Select Add source.
3. Wait for its materialization status to become available.
4. Reference an attached variable as Model.Variable in Formula Explorer.
5. Check the dependent output after the source model changes.

A model cannot link to itself, and a model already attached does not appear in the available-source list. Select Remove only after you check every formula that uses the source.

Use linked models to separate clear planning responsibilities, such as a headcount model feeding an operating plan. Do not split one calculation across models when it makes ownership or review harder.

Apply Dimensions

A dimension repeats a variable across meaningful members.

For example, a Headcount variable can use the Department dimension with Sales, Product, and Operations members.

Before applying a dimension:

1. Confirm that the breakdown changes the decision.
2. Confirm the dimension members.
3. Decide whether the formula should calculate each member or an aggregate.
4. Check source mappings.
5. Review totals after the dimension is applied.

Do not create a dimension only to reproduce formatting from a prior spreadsheet.

Understand Calculation State

The calculation bar can report:

• Up to date when the current model has calculated successfully
• Calculating while a calculation is running
• Stale when values are from the last successful calculation
• Failed when the latest calculation did not complete

Automatic mode recalculates as the model changes. When manual calculation is available, select Run calculation after a set of edits. A failed calculation keeps the last successful values visible, so confirm the status before using the output.

Check a Formula

Before you rely on a formula:

1. Confirm every referenced variable exists.
2. Confirm the units are compatible.
3. Check the first forecast period.
4. Change one driver and confirm the output responds.
5. Check dimension totals when dimensions apply.
6. Confirm no upstream row is stale or failed.
7. Compare one result with a simple independent calculation.

A formula can be syntactically valid and still represent the wrong business relationship.

Troubleshoot

The formula editor reports an unknown variable

Check spelling, renamed variables, linked-model references, and dimension context. Select the variable from suggestions instead of typing an uncertain label.

The formula works in one period but not another

Check the model date range, actuals boundary, time references, and missing input periods.

The output does not change after an input changes

Confirm that the input is referenced by the output path, that the active scenario is correct, and that the model calculation is up to date.

A dimension total looks wrong

Check member mappings, aggregation behavior, and whether the formula is defined at the member or parent level.

The model is slow or difficult to explain

Remove unused variables and dimensions, break a large calculation into named helper variables, and keep only the detail needed for the decision.

Related Pages

• Scenario Planning
• Planning Data and Dimensions
• Planning Time and Actuals
• Planning Uncertainty
• Build a Driver-Based Planning Model