Creating Data Action Allocations

Objective

After completing this lesson, you will be able to create data action allocations

Data Action Allocation Steps

Source Context

After you create the data action and add an allocation step, you define the source context.

  • Data Source: The planning model is inherited from the data action.
  • Filters: Filters allow you to restrict the dimension members and measures for the allocation step.
  • Write Mode: 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.

Data Action Allocation Step - Source Context

Driver Context

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

Data Action Allocation Step - Driver Context

Target Context

  • Booking Account: You can use one set of accounts to calculate the values to allocate, and then send (book) those values using a different account. To do this, you can select a Booking Account to send the values. If no booking accounting is selected, the original accounts are used as the sending accounts.
  • Write Mode: To delete existing values of the target members at the start of the step, in Write Modeselect, Overwrite Target.
Data Action Allocation Step - Target Context

Note

To allocate expenses based on prior-year actual data, you can use a calculated account (a dimension member formula) 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. For example:

  • Account PriorYearActualHeadCount = LOOKUP([Number of People].[d/Version]="public.Actual" AND[d/Date]="2023")
  • Account PriorYearPCs = LOOKUP([Number of PCs],[d/Date]=Previous("Year",1)AND[d/Version]="public.Actual")

Allocation Rules - Fixed

In the following example, the member selections for Source Dimension, Driver Dimension, and Target Dimension are fixed.

  • 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.
  • Driver Dimension: This provides determines how much each target dimension receives. For example, use kilowatt hours to distribute the cost of electricity.
  • Target Dimension: After specifying the Source Dimension and Driver 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 an Organization type dimension; for example, Entity or Cost Center. You can select more than one target dimension if necessary.

You can also use a parameter instead of hard coding dimension members.

Data Action Allocation Step with fixed Allocation Rules highlighted

Allocation Rules - Number of Repetitions

The Number of Repetitions field is set by default to 1. However, 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 service department and reassign them to departments based on the number of service hours that they used. This is known as the reciprocal (repetitive) method of cost allocation. Because 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.

Allocation Rules - Number of Repetitions is set to 9, indicating that HR and IT will use reciprocal cost allocation.

In this example, 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. You can see in the following example, the data before the allocation was executed and after 7 iterations.

The data before the allocation was executed and after 7 iterations.

Create a Data Action for an Allocation

Business Scenario

You want to create a data action that will allocate overhead costs based on number of people, allowing planners to allocate using a push button in the story.

You need to create data action steps for electric, water, and telephone allocation. You also need to allocate human resources data (based on number of people) using a separate sending HR account.

Task 1: Create the Data Action and Add the Parameters

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create a data action for allocations
  • Add the parameters to the data action

Task 2: Create Data Action Steps for an Allocation

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create the electric allocation step
  • Create the water and telephone allocation step
  • Create the HR allocation step

Log in to track your progress & complete quizzes