Concept: Calculation Scope for Modeled and Cube Sheets

Calculations in Sheets

By default, formulas calculate data in sheets for the entire version. When modeled and cube sheets have many formulas, the performance of the sheet might suffer. Formulas include:

Calculated accounts.

Formulas users have entered into the cells of the sheet.

Using Scope for Calculations

To improve the performance, you can set a time range for all calculations within the

Scope

tab of the

Sheet Properties

. Reducing the time range of calculations improves the performance by narrowing the scope of calulcated accounts. We don't narrow the scope for formulas that users entered into the cells.

Time Ranges for Scope

The time ranges are dynamic because you offset them according to the time settings of the versions. You can set scope for both actual versions and plan versions or either.

The scope for:

Actuals is relative to the

Completed Values Through

period of the actuals version.

Plan versions is relative to the

Start of Plan

period of each plan version.

Typically, the

Start of Plan

for unlocked versions is the period directly after the

Completed Values

period of actuals. When the

Start of Plan

is more than 1 period later, we use the

Completed Values Through

period as the point of reference for the plan version.

Example:

Start of Plan	Completed Values Through	Point of Reference for the Scope of Plan Versions
Jan '25	Dec '24	Jan '25
Jan '25	Jun '24	Jun '24

Calculations Out of Scope

The scope for calculations doesn't include formulas that users enter into cells. These formulas always calculate for the entire version.

From the sheet view, cells with calculated accounts that are out of scope have:

A blue triangle to indicate the data is formula-driven.

Gray background to indicate that the cell is read-only because it has a formula.

Blank to indicate that the sheet isn’t calculating the data because it’s out of range.

Example of Dynamic Scope

The 2020 Budget
is a plan version that starts January 2020. The 2021 Budget
is a plan version that starts in January 2021.

You set a scope of the Revenue
cube sheet.

For

Start calculating

, you leave the default, Left Scroll Limit
. For

Stop calculating,

you select Month
and enter a 12
.

Revenue
cube sheet calculates from January to December of 2020 when you select the 2020 Budget version. When you select the 2021 Budget
version, the sheet calculates cells between January and December 2021.

When you adjust the

Start of Plan

in any plan version, the calculations for that version in the sheet adjusts, too.