Breakback Loop in Calculation
  • 8 Minutes to read
  • Dark
    Light
  • PDF

Breakback Loop in Calculation

  • Dark
    Light
  • PDF

Article summary

Breakback Loop in Calculation

You can set up a Breakback Loop comprising Breakback Scope to generate data for any model based on the available data instead of creating multiple calculation steps.

The Breakback Loop helps to achieve the following:

  • Breakback Loop will leverage the existing Breakback engine and enhance it to support complex use cases with a simple setup.

  • Breakback Loop will help customers configure complex top-down processes, make top-side adjustments, push the changes across multiple dimensions, and allocate the amounts based on historical trends.

  • Breakback Loop will simplify the configuration of complex top-down processes.

  • Breakback Loop will reduce the ongoing model and calculations maintenance.

  • Breakback Loop will merge multiple breakback steps into one, simplify the calculations and improve their maintenance.

  • Breakback Loop will collect all leaf members falling within the scope range for each loop dimension and iterate through them.

You can set up a Breakback Loop comprising a Breakback Scope to generate forecast data for any model based on the available data instead of creating multiple calculation steps.

For example, previously, if you want to generate a forecast for 24 months for 100 customers having multiple dimensions based on the actual data, you have to define all dimension attributes in the Breakback manager and create 2400 Breakback steps. This took a lot of effort and time. With the Breakback Loop, you can generate the same forecast for 100 customers by just creating one Breakback Loop calculation comprising one Breakback Scope. The functionality saves time and effort and increases flexibility.

To enable this functionality, application admin users have to set the Enable Breakback Loop property to Yes . There is no need to contact the customer support team; application admin users can enable this functionality directly from the application.

DynamicPlanningJuly21Enablebreakback.png

Once you set the value of this property to “Yes”, you will be able to see “Breakback Loop” in the Type drop-down list for creating a calculation step.

DynamicPlanningJuly21Enablebreakback2.png

You will also see the Breakback Scope column enabled in the Scope sheet.

DynamicPlanningJuly21Breakbackscope.png

Breakback Scope property is an optional property in the Scope.

  • Users will be using Scope for various reasons, and it is applicable for Aggregation and Formula types in the calculation.

  • If users are using Scope for any other type in the calculation other than the ‘Breakback Loop’, there is no need to populate or select any values in the Breakback Scope property.

  • Breakback Scope property is only applicable for the Scopes users will use in the

  • the Breakback Loop type.

  • Breakback Scope property should not impact the Scopes that are already created in the application.

  • If users want to use Breakback Loop, they need to create the Scope with appropriate selections in the Breakback Scope property.

  • Breakback Scope property drop-down contains Breakback Cell, Spread: Reference, Value: Reference, and Hold values. It is not mandatory that all these values have to be selected for a given scope. Based on the use case and need, users can select a combination of these values. For example, a Scope may only have a Breakback Cell, or a Scope may have a Breakback Cell and Spread Reference, etc.

  • For the dimensions that users want to loop through, they need to set up the Scope and select the appropriate Breakback Scope property. For example, if users want to configure Breakback for 24 months, then one option is to use Breakback type in the calculation and create a 24 step calculation. Another option is to use the Breakback Loop type in the calculation and define the Scope. In the Scope, set the Time dimension. Based on the Scope setup, the Breakback engine will loop through for each month, that is, 24 months.

  • Any dimension that is defined in the Scope will work as a loop dimension for the Breakback engine. The only exception is for dimensions where the Breakback Scope property is set to Hold.

  • Hold primarily holds the dimension members from the execution. The Hold in the Breakback Loop works the same as how Hold works in Breakback. The benefit users will have from defining Hold in the Scope is to make updates from one place.

  • For users to use Hold, they have to define the hold dimension members in the Breakback configuration when they configure Breakback in the calculation.

  • Hold is primarily to hold the dimension members from Breakback during the execution.

  • Currently, defining the hold members in the Breakback manager in the calculation is mandatory. Hold in the Scope will only override the values. In future releases, this limitation will be relaxed.

  • Hold dimension and the dimension on which users want to loop should not be the same. For example, if a user is looping through the Time dimension, defining the Hold on Time dimension is not supported.

  • Dimensions in Breakback Scope must be limited to those that are loop parameters. There is no need to define all dimensions in the Scope.

How to Create a Breakback Loop Calculation Step?

  1. In SpotlightXL, navigate to Model > Scope.

  2. In the Scope sheet, set the Dimension, Filter Type, Filter Value, and Breakback Scope values. The Breakback Scope drop-down list will contain Breakback Cell, Spread: Reference, Value: Reference, and Hold values.

  3. Save the scope.

  4. Navigate to the Calculation sheet.

  5. Select Breakback Loop from the Type drop-down list.

  6. Enter the required Breakback Scope name in the Name column and add the required variables in the Variables column.

DynamicPlanningJuly21BBLoop2.png

Sample Use Case

Let's say we have a sample model where salespeople are forecasting the revenue by region, and there are other dimensions in the model such as department, business unit, time, scenario, and measures.

There are four regions in the model (i.e., East, West, North, and South), and salespeople forecast the revenue for 24 months. Once the sales forecast is inputted, department managers have to plan for expenses in each department based on the sales forecast.

In this use case, revenue is forecasted at the region level, and expenses are forecasted at the department level.

To solve these types of use cases, users can consider multiple options. One option is to create separate models with corresponding dimensions and then consolidate the data for analysis and reporting purposes. Essentially the revenue is forecasted in a separate model, and expenses are forecasted in another model. By leveraging the Map functionality, users can move required data into these models.

Another option is to have both revenue and expense forecasted in the same model by leveraging the Breakback engine.

For example:

Step 1 - Let salespeople input their forecast for the regions that they are responsible for.

Step 2 - Create breakback steps in the calculation to push the revenue forecast data, which is at the region level to departments and business units level so that expenses forecast can be based on the sales targets.

Step 3 - Have department managers input the expenses at the department level by considering the sales targets.

The aspect of this process is how to achieve Step 2 simply and straightforwardly. Leveraging Breakback is an ideal approach; however, as the salespeople input the data for each region for 24 months, the engine then has to take that data and push the data into all the departments and business units based on the previous year's forecast, or current actuals, or a combination of both. So in practice, users have to create at least 24 breakback steps for each region. So to solve this use case, we need 24 * 4 = 96 steps.

If there are more regions and if there are more months, then the complexity increases drastically.

Also, suppose users want to make changes every month, like adjusting the time horizon or adding more regions. In that case, there will be a significant amount of changes needed in the breakback steps and calculation.

The Breakback loop will simplify these kinds of complex use cases by leveraging the looping mechanism.

So, we want the breakback to loop through 4 regions and 24 months in this use case, which means both Time and Region are the loop dimensions.

The first step in creating the Breakback loop step is to determine the dimensions and dimension members that qualify for the looping. Every use case will be different, so based on the use case, users must define the loop dimensions and dimension members.

One simple assumption that users can make here is to solve the use case with Breakback step, and understand what's changing (i.e., dimension and dimension members) for each step in the calculation, and then consider configuring those dimensions and dimension members loop dimensions.

Steps involved in creating the breakback loop for the above use case.

Step 1 - Create the Scope

As we have identified Time and Region as the loop dimensions, we need to include only these two dimensions in the Scope.

While defining the Breakback step in the calculation, users must provide Breakback Cell, Values Reference, and Spread Reference. Similarly, the Scope will also accept the same information.

Note:

It's not mandatory to include all Breakback Cell, Value Reference, and Spread Reference in the Breakback Scope. Based on the use case, users can define the required option.

We will discuss these options below.

In the above use case, we want both Region and Time dimensions to be included in the loop, and we also want the same for both Breakback cell and Value Reference.

Users can use FixedMember or MemberandBelow as the filter type options.

This Scope will result in 4 regions * 24 months = 96 breakback steps internally.

DynamicPlanningJuly21Sampleusecase1.png

Step 2 - Create Breakback Loop step in the calculation

Creating Breakback Loop step in the calculation is exactly the same as how users create the Breakback step in the calculation. The only additional step is to select the Scope that was created in the previous step.

DynamicPlanningJuly21Sampleusecase2.png

Notes:

  • Users have to select all the Breakback properties from the Breakback manager in the calculation step. This is exactly the same as creating a regular breakback step.

  • Anything that is defined in the Breakback Scope will override the selections made in the Breakback manager.

  • Time and Region dimensions are included in the Scope, and those dimensions and dimension members will override the selections made from the Breakback manager in the calculation.

Breakback Loop with Spread Reference

Below Scope will consider running the breakback for 4 regions * 24 months by leveraging the 2020 spread.

DynamicPlanningJuly21Sampleusecase3.png

Breakback Loop with MemberAndBelow for Region dimensions vs. Fixed Member

Below scope will loop through all the regions under ‘All Regions’ rollup, for example, 4 * 24 months = 96 steps.

An additional benefit in configuring the Breakback loop is that when a new Region gets added, it automatically gets included in the loop without any manual intervention from admin users.

DynamicPlanningJuly21Sampleusecase4.png

Breakback Loop for 3 quarters in a year

Below Scope will loop through 9 months * 4 regions = 36 steps.

DynamicPlanningJuly21Sampleusecase5.png

Breakback Loop for 3 months in a year

DynamicPlanningJuly21Sampleusecase6.png


Was this article helpful?