Running Breakback from a Calculation

Overview

You can run Breakback operations from a Calculation, thus automating your top-down allocations and making them easier to maintain.

Use SpotlightXL to define Breakback operations in a calculation. Once defined, you can run the calculation from SpotlightXL or Spotlight.

Breakback Scalability in a Calculation

Calculation Breakback Basics

Breakback requires several pieces of information:

• Model name

• Parent-level data intersection from which to allocate data to children

• Spread method

• Type of change to make: amount or percentage increase/decrease

• Value of the change to make

• Data intersection to use as a reference for the Breakback (optional)

• Hold members (optional, child members that will be skipped in the Breakback)

You can provide the required information using a Breakback button on the Calculation ribbon.

This button functions similarly to the Breakback button in Views and Reports. The main difference is that you must also specify the parent-level data intersection; in views and reports, this is the data cell that your cursor is on before you select Breakback.

Calculation Breakback, Value Input Example

This example uses similar choices as are demonstrated in the help topic for Breakback from a view or report. See Using Breakback based on Current Data with % Change (Increase or Decrease) . For 2017 Budget, we want to increase the Expense (account 6000) amounts by 3%. Here is the view before the calculation is run, with the Breakback Cell highlighted.

In Practice: Adding Breakback to a Calculation

1. Select Model > Calculation.

2. Select a model from the Model drop-down.

3. Give the Calculation a name.

4. Click Save.

5. Under Type, select Breakback.

6. Under Name, type None.

7. Click Save. Notice that the Variables section is now populated with several variables.

8. Select the cell with all the Variables.

9. Click the Breakback button on the ribbon.

   

10. Select the model from the Model drop-down. By default, the model specified in the calculation is selected.

11. In the Breakback Cell area, specify the parent-level data intersection. This is the highest level parent that you will base the allocation from. The allocation will go to the leaf-level children.

    

12. In the Spread area, select Current. For information on spread types, see Breakback Types.

13. In the Value area, select % Increase.

14. Select Input because we will manually input the % increase that we want by typing 3 in the % box.

    

15. Skip the Hold area.

16. Click Update.

    Now you are returned to the calculation where you can add more commands.

17. In the second row of the calculation, select Aggregation from the Type column.

18. Under Name, type None.

19. Click Save.

Your calculation is ready to run.

After running the calculation and refreshing the View, here is the resulting data.

Calculation Breakback, Value Reference Example

You can set up Breakback to refer to a data intersection in your model as the basis for the allocation. For example, if you do not know the exact value of the % Increase or the Value Input, or if you want the % Increase or Value to be variable, you can specify a data cell from the model to reference for the value.

In Practice: Using a Cell in the Model as the Value for the Allocation

1. Use a report to set up a form for a user to enter the amount to use or the amount to increase or decrease.

2. Use the cell intersection coordinates from the report as the reference for the Breakback in the calculation.

3. Use one Breakback row in the calculation for each amount entered by the user.

4. Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.

Example

The following example uses a report Sales Increase % to let users input values for regional sales increases. The values inputted by the user are used in the Breakback calculation to increase Sales values in the model. The report automatically runs the calculation when the user saves their input values.

1. Use a report to set up a form for a user to enter regional sales increases.

   

2. Note the data intersection from the report using Design Manager.

   

3. In the calculation, add a Breakback row, and use the cell intersection coordinates from the report as the reference for the % Increase.

   

4. Use one Breakback row in the calculation for each amount entered by the user.

   

5. Note the name of the calculation.

6. Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.

   

Using Substitution Variables in Breakback Operations from a Calculation

You can use substitution variables in place of member names in Breakback operations within a calculation.

This feature is supported both in SpotlightXL and Spotlight.

When defining Breakback operations in a calculation, you can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback cell.

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

Substitution Variables are also available in Breakback in a Calculation in Model Manager on the web.

You can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback Cell:

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

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.

To understand the Breakback functionality, see Breakback functionality.

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.

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.

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

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.

In Practice: Creating Breakback Loop Calculation Step

1. Navigate to the Scope sheet.

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.

   

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.

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.

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.

Breakback Loop with Spread Reference

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

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.

Breakback Loop for 3 quarters in a year

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

Breakback Loop for 3 months in a year