Assign and Distribute Values with the Planning Panel

In SAP Analytics Cloud, you can use the planning panel to quickly move values in a table.

Context

The panel can help if you’re working on a task that’s too complex for basic data entry and copying and pasting, but doesn’t need a structured data action or allocation process.

For example, you might have an unassigned overhead cost that you want to spread to a few different cost centers, or maybe you need to redistribute sales volumes among different regions without changing the overall volume. These tasks are difficult with basic spreadsheet applications, but you can handle them quickly with the planning panel.

There are two types of operations:

  • Distributing a source value to one or more target cells.

  • Redistributing the values of a group of cells.

You can switch between operation types as you work with the panel.

Options such as custom source values and different driver types let you carry out more scenarios with the panel. You can also allocate along multiple dimensions in one step, and pick target cells from different hierarchy levels.

You can also interact with the table while the planning panel is open by collapsing or expanding dimension members, setting the drill level of a dimension, showing or hiding unbooked members, and showing or hiding rows and columns.

If you like to use your keyboard for planning tasks, you can use shortcuts to navigate in the grid and do all your data entry and distribution without the mouse. See Keyboard Shortcut List for Tables for details.

You can see previews of the new values as you work, and then apply all the changes together when you finish the operation.

Distributing Cell Values

Context

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.

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

  1. 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.

  2. 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 Start of the navigation path Next navigation step Distribute ValuesEnd of the navigation path.

    • 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.

  3. 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.

  4. 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, from different members of multiple dimensions, and from different measures.

    If you’re choosing different accounts, you won’t be able to select account types that are incompatible with the source or with other targets, such as income and expenses.

    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. Cells that are dependent on the source cell are greyed out and can’t be picked as targets unless Book as additional amount is selected.

    • The planning panel doesn’t carry out currency conversion when your operation involves source and target cells with different currencies. Only the numeric values are distributed. In a model with measures, you can use conversion steps in data actions to copy data with currency conversion. For details, see Convert Currencies in a Data Action.

    To assign the variance, you’ll select travel expenses for the forecast quarters for the rest of the year.

  5. 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.

  6. 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.

  7. 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.

    Note

    If your operation exceeds the number of decimal places or value range of a target measure, you’ll see an info icon in the panel and some values might be rounded off when booked to the target cell. For details, see Limits on Value Ranges and Decimal Places for Measures.

  8. 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.

Redistributing Cell Values

Context

Redistribute operations in the planning panel let you change the proportions among a group of cells.

Say you’ve just copied values for marketing expenses from 2020 to your 2021 plan, but as part of a new marketing strategy, you want to change how the expenses are spread to different promotion channels.

Procedure

  1. Select a group of source cells that includes the entire amount you want to redistribute.

    The source area can include cells on different levels of a hierarchy, or from different members of multiple dimensions and measures.

    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.

    • The planning panel doesn’t carry out currency conversion when your operation involves cells with different currencies. Only the numeric values are redistributed. In a model with measures, you can use conversion steps in data actions to copy data with currency conversion. For details, see Convert Currencies in a Data Action.

    For example, you can select the marketing expenses account for different promotion channels in 2021, such as Online, Printed Media, TV, and Radio.

  2. Open the planning panel by right-clicking the cells and selecting Redistribute Values.
    There are a few other ways to open the panel, too:

    • Select Start of the navigation path Next navigation step Distribute ValuesEnd of the navigation path.

    • Press Ctrl + Alt + D .

    • Type ? in the source cell, on its own or followed by a value, and press Enter. (This starts a Distribute operation.)

    The Planning Panel opens. In the source area, it shows the aggregate value of the cells that you selected.

    To change the target cells, activate the (Add target) button and select or deselect cells in the grid.

    You can also select Redistribute to switch to a Distribute operation.

  3. From the Cell list, choose whether to overwrite existing target cell values, or append the new values.

    In this case, you’ll use Overwrite to set new target values.

  4. Use the Driver list to choose how to set the 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.

    You just want to set out a ratio between the different promotion channels, so you choose Input Weights.

  5. 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.

    Note

    If your operation exceeds the number of decimal places or value range of a target measure, you’ll see an info icon in the panel and some values might be rounded off when booked to the target cell. For details, see Limits on Value Ranges and Decimal Places for Measures.

  6. 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.