Anaplan Use Case 4: Linear Interpolation
Linear Interpolation is a mathematical method used to estimate values between two known numbers. While not common, it does occasionally pop up in business planning. The upcoming article focuses on a specific use-case involving Linear Interpolation.
NOTE: This article is a segment of the Anaplan Use Cases series. If you haven’t already, I encourage you to read through the introductory blog Why Anaplan Use Cases Series? to understand the background behind this endeavor.
Context
A hypothetical pharmaceutical company Bio Well markets and sells drugs to counter imbalances of nutrients in human body to achieve long-term wellness. Bio Well successfully launched an anti-diabetic drug a few years back. If an adult human takes one pill a year, he/she can, very well, keep any existing/potential diabetic problem at bay, without any side effects.
Every year end, the Sales, Marketing, and Manufacturing departments seek R&D department’s help in estimating quantity of anti-diabetic drug required for 9 future years, so that they can prepare for required personnel and infrastructure to support the expected future supply.
It is the month of November in 2024. The R&D department already has information about Total Addressable Market (TAM) for 9 future years in each of the country the firm operates in.
R&D Head gets partial market share information from sales and marketing departments.
1) Actual market share (for the year 2024) from sales department, and
2) Expected market share (for the year 2033) from marketing department.
R&D department has been tasked to fill in the intermediate years (2025 through 2032) with an assumption that the company can linearly increase its market share each year to reach the target set for 2033, setting up stage for a classic linear interpolation problem.
With TAM and Market Share information for 9 future years, R&D Head can then project Customer Base for the upcoming years, and hence, the supply of anti-diabetic drug Bio Well needs to provide and support for.
Customer Base = TAM x Market Share
User Story 1
As R&D Head, I need to input Market Share for the current year and the 9th future year, so that the system can linearly interpolate information for the intermediate years (8 future years).
User Story 2
As R&D Head, I need the system to linearly interpolate the market share of all the intermediate years, so that we can project customer base for future years later.
Also, I need to review the market share information for all the years.
Analysis & Design
User requirements call for user input and linear interpolation of market share information.
In an ideal world, if Anaplan permitted, it would be great to see the user input values under first and last years and the system populate (interpolated) values for all the intermediate years, both in the same data grid. However, in the real world (of Anaplan), we cannot force a line-item to accept both user input (for some years) and calculated values (for the other years). The line-item can either be formula-based or user input, but not both. Hence, we need to create two different line-items — one for the user input and the other for the calculated values.
For linear interpolation, we would need to subtract the first value from the last value and divide the difference by the number of intervals between the two years (i.e., 9) to get the increment amount. For example, the increment amount for USA is 4.44%, as shown below.
The increment amount would then be added to the value of the prior year to calculate the value for a given year.
To extract the first and the last values for each country, we would need TIMESUM function. The other aggregation methods wouldn’t be applicable here because those methods work on regular dimensions/lists, but not on Time dimension.
Implementation
For linear interpolation, we start off with a simpler solution and see if it has any drawbacks.
a) We create a time range ‘CY + 9FY’ to include 9 future years along with the current year (2024).
b) For User Story 1, we create an input module for registering user inputs for the first and the last years in a line-item ‘Input %’.
c) We create a new module (without time dimension) for extracting first and last years’ values to calculate increment amount for each country.
First Value: TIMESUM(‘INP01 Market Share’.Input %, 0)
Last Value: TIMESUM(‘INP01 Market Share’.Input %, 9)
Increment: (Last Value — First Value) / 9
Notice that we specified 0 or 9 in TIMESUM function to extract value of a specific year. We also hardcoded the number of intervals as 9 in the formula of Increment line-item.
d) We create a new line-item ‘Calc %’ in the input module to calculate values for all the intermediate years, by using the increment amount calculated above.
Voila! There we go with the result of linear interpolation between the two values.
However, there is a problem with this approach. The solution works well until the time range is changed. If the business users decided to lengthen/shorten the planning range (9 future years), all the hardcoding (0 or 9) in the calculation module would need to be changed accordingly, else the increment value (and hence, the interpolated numbers) would be calculated incorrectly.
For a more robust solution, we need to remove the hardcoding.
a) Create Time system module (for the time range ‘CY + 9FY’) and identify the first and the last years using formulae.
Cumulate: CUMULATE(1)
Min: TIMESUM(Cumulate, -100, 100, MIN)
Max: TIMESUM(Cumulate, -100, 100, MAX)
First Year?: Cumulate = Min
Last Year?: Cumulate = Max
b) Create a new line-item ‘First Input %’ in the Input module that leverages the time system module to save first year’s input only.
c) Create a new line-item ‘Last Input %’ in Input module that leverages the time system module to save the last year’s input only.
d) Change all the formulae in the calculation module that calculates increment amount.
First Value: TIMESUM(‘INP01 Market Share’.First Input %)
Last Value: TIMESUM(‘INP01 Market Share’.Last Input %)
# Intervals: TIMESUM(‘SYS01 Time’.Cumulate, -100, 100, MAX) — 1
Increment: (Last Value — First Value) / # Intervals
Bingo! Now, if the requirements changed, we would just need to change the number of years of the time range. Everything else would automatically work.
Conclusion
While a solution might be simple and quick to implement, we need to ask ourselves whether it is easier to change in the wake of evolution of user requirements. Only then we could move forward to guarantee a more stable, sustainable and auditable system for the users.
Before we end the topic, there is one more thing worth pointing out. We could enhance the user experience by not allowing the user to be able to input numbers in the intermediate years. Anyways, those numbers wouldn’t be of any use anywhere. We can achieve this by setting up DCA (Dynamic Cell Access) using a new line-item in the time system module — Fringe Year?
