Distributing Cell Values

Distribute operations can help when you need to assign a value to a group of cells, or distribute a value from a source cell to a group of cells.

Context

For example, if your travel expenses are higher than budgeted partway through the year, you might want to assign the variance to the upcoming forecast periods. This way, you’ll set new targets that reduce your costs enough to meet the budget.

Procedure

Select a source cell that contains the amount to distribute. It doesn’t need to be an editable cell.

(If you want to type your own source value, you can do that after opening the panel.)

In this case, you’ll select the variance for travel expenses, which is calculated by a formula.
Open the planning panel by right-clicking the cell and selecting Distribute Value.
There are a few other ways to open the panel, too:
- Select Distribute Values.
- Press Ctrl + Alt + D .
- Type ? in the source cell, on its own or followed by a value, and press Enter.
The Planning Panel opens, showing the location and value of the source cell.
Configure the source value as necessary with the following options:
- Typing a new source value: This lets you assign any amount without referencing a source cell.
- Lock the source cell: If you have a value booked directly to a parent member, this option lets you spread it to its leaf members. It’s enabled by default in this case. See Entering Values with Multiple Hierarchies for details.
- (Select source cell): Select to pick a different source cell from the table.
- Distribute: Open this list to switch to a Redistribute operation.
- Book as additional amount: With this option enabled, the operation won’t deplete the source cell. So instead of moving values from one place to another, you’ll just book the value to your targets.
In this example, Book as additional amount is mandatory because the variance is calculated by a formula and isn’t editable.
Choose your target cells.

You might also see some recommendations for common operations that you can set up with one click.

Otherwise, activate the (Add Target) button next to Where to? and select cells in the grid. You can also use the keyboard to navigate through the grid and type values into your target cells.

The target area can include cells on different levels of a hierarchy, or from different members of multiple dimensions.
Note
If you select a group of dependent cells, such as EMEA, Germany, and Frankfurt, or Gross Margin and Total Revenue, you’ll only be able to enter values for one of them.

To assign the variance, you’ll select travel expenses for the forecast quarters for the rest of the year.
From the Cell list, choose whether to overwrite existing target cell values, or append the new values.

In this case, you’ll use Append to add the variance to the existing forecast.
Use the Driver list to choose how to set the target cell values.
- Input Values: Add the exact target values, either in the table or in the panel.
- Input Weights: Set proportional weights for each cell. For example, if you want to get weights from a different time period, you could copy and paste those values from the table.
- Equally: Divide the source value equally among the cells.
- Proportionally: Use the existing proportions between the cells.
For distributing variance, it makes sense to divide the values proportionally between the remaining quarters.
Set values for the target cells.

If you chose Input Values or Input Weights, you can now set the target values by typing or copying and pasting. For Input Values, you can also change values directly in the table. Simple formulas such as +10% are available in the table or panel, too.

When you select a target, the table will highlight the cell. You can also hover over the cell coordinate to see its members. If you need to remove any targets, use the (Remove target) button.
Check the figures and then select Apply.

You can see a preview of the values in the table, and the percentage distribution next to the targets in the panel. If you used input values that don’t add up to the total amount, you’ll see a warning beneath the targets. You can still apply the operation in this case, though.

The table is updated to show your changes. The forecast travel expenses are reduced, bringing the variance back to 0.