Creating Allocation Processes

Objectives

After completing this lesson, you will be able to:

  • Create allocation processes

Allocation Processes

Allocation Processes and Data Action Allocations

Watch this video to discover how the planners at ABC Corporation will use allocation processes and data action allocations in their planning.

Allocation Process Step and Rule

In the following example, the allocation rule is defined to allocate expenses from the Northeast region to the other U.S. regions based on prior year sales. To the right, in the Steps Overview panel, you can see a graphical representation of multiple steps that will run one after the other.

Creating an Allocation Process

  1. Create an Allocation Process.
  2. Create an Allocation Step:
    • Select the source and target dimensions.
    • Input the number of Repetitions if needed.
    • Choose to Keep Source or not.
    • Choose to Overwrite Target or not.
    • Filter by Account.
    • Select a Reference Dimension if needed.
    • Select a Booking Account if needed.
  3. Create an Allocation Rule:
    • Source: Select a member in the Source dimension.
    • Driver: Select a member in the Account dimension.
    • Target: Select members in the Target dimension.
Note
Allocation processes can't be scheduled directly. However, an existing allocation step can be included in a data action. Data actions can be scheduled from the calendar.

Allocation Steps

Creating a sequence of steps allows you to perform complex allocations. For example, allocating costs from accounts to different projects and products, and then further allocating costs from those dimensions to different geographical responsibility centers.

You can create an allocation step as part of a process, or create the step on its own and add it to a process later.

In the following example, the cost center dimension is the sender and the target. Electric costs are planned in the electric cost center and are then allocated to other target cost centers using the same account (Booking Account is blank).

Source Dimension

To create an allocation step, you must specify a source dimension, which holds the values that you want to distribute. This can be any dimension except Version (Version is a built-in prompt), and is typically the Organization type dimension; for example, Entity or Cost Center.

You can select more than one source dimension. If you do, the subsequent dimensions become the Overwrite Dimension and appear in the allocation rule under the Source. Overwrite Dimensions are used as conditions; for example, run this allocation only for EUR.

Target Dimension

After specifying the Source Dimension, you specify a target dimension to receive the distributed values. This can be any dimension except Version and Account (Booking Account is already an option), and is typically the Organization type dimension; for example, Entity or Cost Center. You can select more than one target dimension if necessary.

Keep Source and Overwrite Target

To keep the values of the source cells, select Keep Source. The source values will be copied and allocated to the targets, instead of being reduced to zero. Don't select Keep Source if the goal is to fully allocate the source.

To delete existing values of the target members at the start of the step, select Overwrite Target. For allocation steps with multiple repetitions, the target values are only overwritten at the beginning of the step, not during each repetition.

Note

When you select both Keep Source and Overwrite Target, you can execute the step repeatedly without depleting the source values or accumulating extra value in the target cells. For example, you can run the allocation process, and then change the values of the source cells and run it again to get a new result.

If you don't select Keep Source and you do select Overwrite Target, the source will be zero, and existing target values will be overwritten. However, if the allocation is executed a second time with these selections, the target values will be deleted.

Repetitive

It's possible to use the same dimension member as the source and target for an allocation step. In this case, values will be reallocated from a source member to one or more members of the same dimension hierarchy.

For example, you can use allocations to take costs that have already been assigned to a software support department, and reassign them departments based on the number of support hours that they used.

This is known as the reciprocal (repetitive) method of cost allocation. Since some of the original value is allocated back to the source member, you can choose to perform multiple repetitions of the step until all source values are allocated.

Filter by Accounts

You want to exclude some accounts from the allocation step. For example, you want to allocate values for all financial accounts, but leave driver accounts unchanged. In this case, you can use the Filter by Accounts feature to select only the financial accounts.

Reference Dimension

Reference dimensions enable you to use more fine-grained weights for the allocation. The Date dimension is by default a reference dimension.

For example, when allocating plan revenue from a product group to products based on last year's actual data:

  • If no reference dimension is selected:
    • Each product is allocated 20%.
    • Actual revenue by product isn't considered.
  • If Product is selected as the reference dimension:
    • Each product is allocated its share of the product group revenue in proportion to prior year actual data. For example, if the product group plan revenue is 550,000 and product A accounted for 50% of the total product group actual data, then product A will be allocated 275,000 of plan revenue.
    • Actual sales by product are considered.

Booking Account:

You use one set of accounts to calculate the values to allocate, and then book those values to a different account. To do this, you can select a Booking Account to receive the values. All changes in value for the allocation step, both for source members and target members, are carried out on the booking account.

Allocation Rules

Once you've created an allocation step, you can begin to add allocation rules to the new step.

An allocation rule provides more details pertinent to the allocation step and further defines the specific value to be allocated. To create an allocation rule, you select a member of the source dimension, choose a driver (or direct assignment), and choose a member or members of the target dimension.

When creating an allocation step, you can specify an overwrite dimension, which will allow you to create override allocation rules. An override acts as a condition. For example, perform this allocation but only if currency = EUR.

If you need to choose driver values from a specific version, you can select a calculated account that uses a LOOKUP function to refer to that version.

You can also create multiple rules by copying and pasting this information directly into the table of rules for the step.

In the following example, the utilities cost center will allocate electric expenses to the target cost centers based on kilowatt hours.

Note
Allocation rules are executed in the order of the more specific first, not in the order in which you create them.

Iterative Example

Setup of an Iterative Allocation

In the following example, you can see that HR is allocating to IT and vice versa. In a scenario like this, you can increase the number of repetitions to fully allocate both HR and IT cost centers.

Result of an Iterative Allocation

In the following example, HR is allocating to IT and then IT is allocating to HR (and other cost centers), therefore, several iterations are required to fully allocate the expenses.

The number of iterations will depend on the circumstances and is determined via trial and error.

Prior-Year Actual Example

Scenario

Allocate expense based on prior year monthly actuals.

  1. Allocate between cost centers
  2. Allocate expense accounts
  3. Use a dedicated account for the allocation
  4. Allocate from HR to other cost centers
  5. Account driver is a calculated account
  6. Dimension formula looks up 2022 actuals by month

In order to allocate expense based on prior-year actual data, you can use a calculated account that looks up values for the actual version and the prior year. The year can be hard-coded, or it can be accessed dynamically by using an offset.

Create an Allocation Process

Business Scenario

As part of the overhead costing forecast, you need to create an automated way to allocate the cost for utilities (based on kilowatt hours), water, and telephone (both based on number of people) among various cost centers. These allocations use the original accounts to send the values.

You create an allocation process to allocate the electric, water, and telephone costs.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create an allocation process
  • Create steps for electric, water, and telephone

Log in to track your progress & complete quizzes