We created a way to consolidate multiple forecast processes which would otherwise blow up your Anaplan model - this is the story of how we did it...

During our early time with Anaplan we wanted to design a better way to consolidate the numerous actuals, budget and forecast data that all FP&A models generate.

We wanted to create a grand unifying theory and a single consolidation source from which we could map data from into all our reports.

Existing process involved referencing every single data source within each line of each report, mapping them using a vast array of manual mapping tables and then embedding these calculations within a massive nested IF THEN ELSE formula.

Anything else would be a vast improvement.

Our grand plan was to create a single consolidation point, complete with every possible report dimension which could be mapped into our reports using just one table.

The result was a massive module taking up over 20% of the model cell count. It was big but it provided a single data source which we could use for all subsequent reports. It was a vast improvement on the status quo.

But it created other problems.

Despite our best efforts to control sparsity and reduce the load on the model we couldn't.

Back to work.

We wanted to retain the concept of a single source but reduce the impact of sparsity as this was the contributing the most to excess size.

We came up with Sequential Consolidation.

The aim was to create a dedicated consolidation route for each report where data moved through a sequence before it reached its final consolidation.

From here reports could reference the data directly with no need for additional mapping.

Wait, will this not create more inefficiencies?

No!

The consolidation is dedicated to a single report. Therefore, we can remove irrelevant dimensionality as we move through the consolidation sequence.

The reason the first approach created such large consolidation modules was it added dimensionality as its aim was to act as a single data source to ALL reporting. By consolidating for specific reports we were able to be laser focused and only include relevant dimensionality for that final report.

Also, if we wanted to add a new forecast process which included additional dimensionality such as project planning we could do so while also better managing the impact on model cell count, size and speed.

We will still need to manage sparsity but this approach ensures we spend our sparsity dollars on parts of the model which add value rather than squandering them on ineffective and inefficient model design.

Sequential consolidation is core to how we design, develop and deploy fit for purpose Anaplan for FP&A models.