A finance team calls us with a simple question. Two business units should get the same allocated expense. One unit shows an extra amount in January. The other does not. Nobody knows where the difference comes from.
This happens a lot with allocation models. The number on the report is only the end of the chain. The real reason sits a few steps upstream.
Here is how we trace it.
First, understand the flow
In this model, the allocation runs in four steps.
Survey input. A cube sheet holds survey data for each business unit. The users enter annual values for two drivers, and the model copies them across the months.
Share calculation. A separate calc sheet runs for each business unit. It takes each survey value and divides it by the total to get a percentage share.
Source pool. A modeled sheet pulls the cost pools to allocate. Here it is SG&A, overhead, and indirect overhead.
Target. The allocated amount lands on a company expense allocation account, and we see it at the group level before eliminations.
The key point: the target is not the cause
The account where we see the difference is the result of the allocation. It does not drive anything. So fixing or checking that account alone does not help. We go back up the chain, one step at a time.
How we check each step
We start with the survey cube. We compare the survey values for both business units, month by month. Even a small difference here changes the split in every step after it.
Then we open the calc sheet for each business unit. We check the input values and the percentage share. If the percentages do not match, the allocated amounts do not match either.
Then we check the source pool. We confirm which accounts feed the allocation and whether both business units get the same input amounts.
Only after that do we go back to the target account and confirm where the extra amount comes from.
Adaptive features that help
Adaptive has a few built-in ways to see where a number comes from.
In the Excel Interface for Planning, Explore Cell shows the account details, related formulas, and other sheets where the cell’s data appears. It works on data cells in standard and cube sheets, and on time-span cells like Year or Quarter in modeled sheets. For modeled sheets, Row Details shows information about the data in the whole row.
The formatting also tells a story. Italic text means someone entered the value with a formula. Bold text is an automatic total, and a light blue background is a rollup value. Neither of those has a manually entered formula, so we use Cell Explorer to see where they come from.
What we learn from this
Allocation differences are almost never in the place where they show up. When we follow the order of the model, survey to share to pool to target, we stop guessing and find the real cause faster. We also suggest that teams keep a simple flow diagram of each allocation model, so the next person knows where to start.