1. Modeling
  2. Work with data
  3. Breakback

Breakback is similar to 'goal-seek' in spreadsheets: you type into a total and all the cells that make up that total are changed to meet a target result. It's used as a method of top-down allocations and the allocation method is always pro-rata, which means that it's simple to allocate using seasonality patterns or last year's data as a profile.

It's easy to import, say, last year's data (or whatever profile, percentage split you want), and then enable Breakback. When you overtype a total it will allocate pro-rata across rows, columns, and pages based on the profiles or data that were there before.

Enable Breakback

In Blueprint mode, go to the Breakback column and select the line item(s) for which you want breakback to be enabled. It can be turned on or off for each line item individually.

You can also enable Breakback so that it's turned on automatically for all new line items added to the module at a later date.

The default Breakback setting for the module can also be edited in Model Settings, Modules.

Use Breakback

To use Breakback, open a module and type over any total.

For example, suppose the start point contains seasonal data loaded into a grid as shown and breakback has been enabled for the Gross Sales line item. To change the Q1 FY14 total for Gross Sales for London from 10000 to 11000, simply type over the total.

This triggers a breakback that allocates pro-rata across Jan, Feb and Mar based on the seasonality pattern that was there in the original data.

Breakback works across multiple dimensions. For example, if you change the Q1 FY14 total for Gross Sales for the UK, then not only does breakback use the seasonality pattern to allocate across months, it also uses the geographical split to allocate across UK cities.

Changing the UK total triggers a Breakback across 6 cells, allocating pro-rata across 3 months and 2 cities.

How does Breakback allocate if the total was originally zero?

If the original leaf-level items are all zero, changing a total allocates evenly across its children.

For example, change the Berlin Q1 FY14 total from zero to 3000, and Breakback allocates evenly across the 3 months, assigning 1000 to each month.

There are cases where breakback won't work. Suppose you have a total summing to zero such as this for Munich:

If you try to enter a new total for breakback, a warning message appears - Cannot breakback across non-zero cells which total zero.

There are too many possible solutions to this allocation problem for the program to pick a sensible answer. Change the leaf-level data so that the total is non-zero, then repeat the allocation.

Is it possible to tell how many cells are affected by a Breakback?

Yes. Hover the mouse over any Breakback-enabled total and a tooltip will appear showing the number of cells that will be affected. Here, six cells will be changed as a result of a single Breakback.

Can I turn off the Breakback markers?

Yes. To hide the blue triangles and tooltips, on the Help menu click Hide Breakback Markers.

This removes the blue indicators in the top left corner of the cells for which breakback is permitted. Breakback is still enabled - this just removes the markers from the grid. To reveal the blue triangles and breakback tooltips again, on the Help menu click Show Breakback markers.

Can I use the copy commands to change several totals at once?

Yes. You can copy and paste into several Breakback-enabled totals at once. All leaf-level cells affected by each Breakback total will be updated accordingly for the change in total values.

If your paste into an existing total results in an unsolvable Breakback, the paste will be rejected and you will get an error

Is Breakback permitted across line items?

Breakback is restricted to totals on simple hierarchies and totals on the time dimension and is not permitted in line items, even if they contain simple subtotals. However, it is possible to breakback across line item subsets. Line item subsets effectively convert line items so they act as simple hierarchies.

How are read-only cells affected by Breakback?

Read-only cells are held at their previous values when a Breakback is triggered. They could be read-only because of selective access settings or, in the case of a rolling forecast, because the early months contain actual historic data that can't be changed:

Suppose the switchover date for the rolling forecast is set to Mar 14. The Forecast version will consist of 2 months of read-only actuals and 10 months of editable forecast.

Now change the Q1 FY14 cell for the Forecast version—here an increase from 11,000 to 11,500—and trigger a Breakback:

Jan 14 and Feb 14 remain unchanged, but the Mar 14 value is changed to meet the new target.

In a second example, we have a module showing Gross Sales for Actual and Forecast versions:

Switchover for the Forecast version is set at Mar 14:

Any change to the FY14 total for the Forecast version will Breakback over the 10 cells for Mar 14 to Dec 14, with Jan 14 and Feb 14 values unaffected because they are read-only actuals:

Breakback and change history

Breakback can affect millions of cells. Therefore, the change history for a module only shows the cell change that originally triggered the Breakback, together with the total number of affected cells.


Hold is a Breakback feature that allows you to temporarily ‘hold' values in cells, if you want to update totals without impacting a value for a line item, or if you want to change values for a particular line item without changing the overall total. Find out more: Breakback - Hold.