Anaplan Job Aid: Circular References and Percentage Spreading
I’m amazed that more retailers aren’t using Anaplan. It’s a terrific, low-cost alternative to large merchandise planning solutions and it has so many advantages over Excel. In this article I will explain two recurring call-outs that retailers, especially merchandise planners, have with Anaplan. I’ll explain why its a challenge and also how to solve it.
Very consistently I hear from the merchandise planning team that:
- they can’t plan percentages, ratios, and prices and have them aggregate or spread
- that Anaplan has challenges implementing circular references
These two cases are related and in both examples, they can be accommodated in Anaplan.
There are three types of editable merchandise planning equations.
- Algebraic – calculations where there is one equation for each unknown, or editable field
- Circular – calculations where there are more unknowns, or editable fields, than there are equations
- Iterative – equations that require iterations to optimize a solution
Circular Reference Solution
Most planners are used to being able to edit any variable in an equation and have the remaining variables flex depending on how the rules are set up. Anaplan, just like Excel, only allows equations to flow one direction; meaning, you can only make one variable editable.
This is where many retail practitioners get tripped up with Anaplan. Lets take the circular example above where we want to be able to edit sales units and/or the sell thru percentage. Traditional merchandise planning tools like JDA or Oracle allow you to set up flex rules so you only need one equation. With Anaplan you need to set up one equation for each editable field. This can be achieved by using the conditional logic (IF Statements).
- Final Sales = GAFS Units – Sales Units
- Final Sales = GAFS Units * Sell Thru Percentage
To the planner it will look like one equation because the TRUE/FALSE metric is hidden. That metric is used to determine which equation was edited. For example, if I edit sales units, then the T/F metric equates to “TRUE”. The final sales calculation tests for the TRUE or FALSE and uses the first equation. Alternatively, if the sell thru percentage is edited the final sales number will use the second equation
Here’s an example.
- Beginning Units – all levels
- Receipt Units – all levels
- Sales Units – all levels
- Sell Thru % (default) – bottom level only because it is a percentage. See spreading to see how to override the sell thru % at an aggregate level.
In this example you can edit sales units or sell thru percentage at the lowest level to get final sales. This is how Anaplan handles circular equations. You can use the “use sales?” checkbox to determine if the equation should use sales units or sell thru percentage to get final sales
Here’s the final sales calculation:
IF Use Sales? = TRUE THEN Edit Sales Units ELSE Edit Sell Thru % * GAFS Units
Spreading a Percentage Solution
It’s true of Anaplan that you cannot create spreading and aggregation rules like you can in JDA or Oracle. But it’s really not necessary because once you’ve translated your percentage, ratio, or price to a whole number like units or dollars, you can easily spread and aggregate.
Lets take the circular example from above. If the planner changes the sell thru percentage at the parent level, how will that spread down to lower levels of the hierarchy? We accomplish this by creating one more list item that does a variety of lookups and weighted averages:
The TD Sales Thru % is a percentage with breakback activated. Breakback allows you to edit the value at any level. Note: for this solution to make sense, you should only allow the user to edit the sell thru % at the parent level. This can be accomplished by using dashboards to control what levels are shown. See the dashboard example at the end of this article.
When the sell thru % is changed to 30% notice the TD Final Sales change accordingly so that each sales channel gets a weighted proportion of the additional sales. Lastly the final sales thru percentage is shown. Note: TD = Top Down.
Here is the formula for TD Final Sales:
IF TD Sales Thru % > 0 THEN GAFS Units[SELECT: ‘S1 Store Types’.All Store Types] * TD Sales Thru %[SELECT: ‘S1 Store Types’.All Store Types] * Final Sales / Final Sales[SELECT: ‘S1 Store Types’.All Store Types] ELSE Final Sales
In simpler terms Sales = (Parent GAFS * Parent Sell Thru %) X (Sales Channels’ Sales / All Channels Sales). For production use, you will need to clarify the “parent” level. In this case I took a short cut and used “all store types” because there’s only two levels in the hierarchy.
With multiple hierarchies you’ll need to create a workflow in the dashboard. After all, that’s what Anaplan is so good at. Here’s a much more intuitive view for a planner using a dashboard:
Anaplan for Retail
Anaplan doesn’t have all the features of large merchandise planning solutions and, from my experience, 90% of the time they’re really aren’t needed. In fact, most challenges arise from an expectation of how traditional planning tools work and requires a bit of change management. Anaplan’s approach to planning relies very heavily on workflow, breaking up large processes into smaller, more manageable pieces. That framework also allows for some flexibility on how to meet the business requirements.