Multidimensional expense allocation

Data input cubes enable multidimensional planning by connecting planning sheets with different dimensional granularities, allowing data to flow between summarized and detailed plans. Instead of maintaining separate planning sheets and manually reconciling changes, data input cubes automatically distribute updates to detailed planning sheets based on allocation rules and aggregate changes back to higher-level plans.

Business plans are often created at a higher level of the organization but must be allocated to lower levels for detailed planning and analysis. In this article, learn how to use cubes to allocate OPEX from the country level to cities and products using multidimensional allocations.

Prerequisites#

• The column dimension is a standard date hierarchy (for example, year > quarter > month).

Configure a cube#

When you create a data input measure, define one or more breakdown dimensions to convert it into a cube measure. The measure can then store and allocate values at multiple levels of dimensional granularity.

1. In the Planning ribbon, go to Insert Column > Number > Insert a new empty series. For more information, see Create a numeric data input measure.
2. Select +Add Breakdown from the Enable Multi-Dimensional Allocation section. This action opens the Add Breakdown window where you configure the cube.
3. To allocate values based on the weights of a driver measure, select the required measure from Reference measures. In this example, use Revenue Actual as the reference measure. To learn more, see weighted allocation.

4. Allocate plans, budgets, and forecasts across dimensions that aren't included in the current planning sheet by adding breakdowns. The row dimensions in the current planning sheet are automatically added in the first breakdown- in this example, Region_Name and Country_Name. Select additional dimensions, such as City_Name, Channel_Name, and Product_Name, to distribute values at a finer level of granularity.

In this example, the product-level planning sheet also contains region dimensions, so we've added the product-level dimensions to the same breakdown. Select Add to create new dimension breakdowns as shown in the following image.

5. Create the breakdown and the measure.
6. After you create a cube measure, you can see it under Data > From Sheets > Cube.

7. Enter a value at the grand total level. In this example, we've copied the Operating Expense Actual value so that we can use the cube to allocate this value across additional dimensions. Plan automatically distributes the value equally among child rows and columns. Select the distribution icon to choose an alternate distribution method.

8. Create a calculated measure to compute Gross Margin at the region level.

Import a cube measure into another planning sheet#

After you configure a cube measure, you can use it in other planning sheets with different granularities based on the configured breakdowns. In this example:

• You create a cube in a region-level plan.
• You configure breakdowns on city, sales channel, and product dimensions.
• You enter data for the cube measure.
• You import the cube measure into the product-level plan.

The values you enter in the region-level planning sheet allocate to the product dimensions based on the weights of the reference measure. When you import the cube into the product-level planning sheet, plan automatically populates the entered values.

1. Go to Data > From Sheets > Cube. Select the measure to import. From More options (...), select Insert as measure. Alternatively, in the Model ribbon, go to Cube > Import Cube Measure and select the measure to import.

This action imports the cube measure into the product-level planning sheet. Organizations often plan values at higher levels of the business hierarchy and allocate them to lower levels for detailed planning. In this example, a cube allocates operating expenses (OPEX) from the region level to product-level dimensions.

2. Plan distributes cube measure values equally to breakdown dimensions such as city, sales channel, product family, and product. Select the distribution icon to choose an alternate distribution method.

This action redistributes imported cube values across the product dimensions while preserving region-level allocations. The following screenshot demonstrates how the cube value remains unchanged for the Asia and China rows even after redistribution.

3. In the Planning ribbon, go to Show Columns and deselect the measures that are not required for planning - in this example, the native measure Operating Expense Actual.
4. Create a calculated measure to compute Gross Margin at the product level.

Update cube values#

Cubes support bi-directional updates between planning sheets of different granularities. In this example, update the Opex Allocation for China from 24.3m to 25m in the region-level planning sheet.

The cube allocates the updated value to the additional dimensions in the product-level planning sheet. Plan automatically recomputes calculated measures, such as Gross Margin, to reflect updates to cube values.

Similarly, the cube automatically aggregates updates made at a lower level of granularity and propagates the aggregated values to the planning sheet at the higher level of granularity.

Best practices for setting up a cube#

• Use a valid allocation driver: Always configure a single, clearly defined allocation driver (reference measure) such as prior year actuals, revenue, or units. Ensure the driver reflects real-world business weighting logic.
• Allocate only across nonblank driver cells: Allocation only occurs where the selected driver measure has valid (nonblank) values. Avoid allocations across intersections where the driver is null, as it can cause allocation errors or unintended distributions.
• Restrict input by using Allow Input – Based on Formula: To prevent users from entering or allocating values on invalid intersections, configure an input rule such as [Driver Measure]!==BLANK.

By configuring Allow Input – Based on Formula, cells that don't satisfy the specified condition are automatically locked. This configuration ensures that users can allocate only at dimension intersections that meet the defined driver criteria (for example, where the reference measure is nonblank).