Anaplan Use-Case 10: To Select or Not To Select?

Every now and then, we stumble upon situations where we need to refer to a value against a specific member of a list. In a formula, hardcoding the reference to a specific list member would seem a straightforward way to go. But is it the right way to solve the problem at hand? Does it have any side effects that we might be overlooking? Let’s dive into such a problem and explore different solutions.

NOTE: This article is a segment of the Anaplan Use Cases Series. If you haven’t already, I encourage you to go through the introductory blog Why Anaplan Use Cases Series? to understand the background behind this endeavor.

Context

A hypothetical firm Saffire is an agricultural company in India that markets, exports and sells Saffron in USA. The firm grows and harvests four varieties of Saffron — Bunch, Sargol, Negin and Kashmiri — in different regions of India.

Pricing Analyst works on the pricing model for Saffron. Using historical prices and weight sold for each variety and month in the last three years (‘DAT01 Actuals’ module), the existing Anaplan system calculates the (3-yr) Average Price for each variety (‘CAL02 Avg Price’ module). (Refer to the use case for more details on the calculation, implementation and the existing Anaplan solution).

After reviewing the historical/average prices and with some sense of demand/supply prediction, expected inflation and projected competition, Pricing Analyst inputs and sets the pricing of every variety for the next three years (‘INP01 Forecast Price’ module).

While Bunch is regarded as a Primary variety, the other varieties are considered Secondary.

Pricing Analyst does not want to go through the exercise of setting up forecast prices for Secondary varieties anymore. He intends to simplify the process by inputting price forecast for only the Primary variety, and letting the system calculate the price forecast of each Secondary variety as follows.

1) Given Avg. Price of a Secondary variety, calculate its Relative Price — relative to that of a Primary variety, and

2) Calculate Price Forecast of a Secondary variety by applying its Relative Price on forecasted price of the Primary variety.

User Story 1

As Pricing Analyst, I need the system to calculate Relative Price of each variety, so that the system can calculate price forecast of Secondary varieties later.

Also, I would like to see both Avg. Price and Relative Price of each variety.

Relative Price is the historical average price of a variety relative to that of the Primary variety.

User Story 2

As Pricing Analyst, I need to input the Price Forecast for the Primary variety (Bunch) only, so that the system can calculate the price forecast of Secondary varieties later.

Also, I should be able to refer to the past prices while setting up forecast prices of the Primary variety.

User Story 3

As Pricing Analyst, I need the system to automatically calculate Price Forecast of Secondary varieties, so that I can conduct the planning process faster and more efficiently.

Also, I would like to see and review the forecasted price of each variety.

Analysis & Design

1. For User Story 1, Relative Price of each variety requires us to reference the Avg. Price of Primary variety. As a result, we would need something to refer to the Primary variety. Perhaps, a SELECT function?
2. For User Story 2 and 3, while price forecast for the Primary variety is through user input, price forecast for Secondary varieties needs to be calculated. As a result, we would need to manage price forecast information in two different modules — one for the user input and the other for the calculation.

Implementation

1) For User Story 1, we calculate Relative Price, in the existing module ‘CAL02 Avg Price’, using Select function, hardcoding the reference to Bunch (primary variety).

However, this poses two problems:

• Hardcoding is usually not a sustainable solution. What if the user changes his understanding of Primary variety and comes back later with a request to start considering Sargol as primary and the others as secondary? We would need to change the reference hardcoding in the Select statement above.
• If the Variety list needs to be configured as ‘Production Data’, the system won’t allow it. Think about it for a moment. On one hand, (by using Select) we expect a hardcoded reference to an item of a list, implying that we know the members of the list and which specific member to use as a reference, not just in the development environment but also in the production. On the other hand, (by configuring the list as Production Data) we expect the members of the list to be created and defined in production only, implying that we don’t know the members of the list in production yet. Both the expectations — Select function on the list member and configuring the list as Production Data — are contradictory to each other.

2) Fortunately, we could implement an alternative solution using Lookup function instead. First, we create a dimensionless module ‘SYS02 Generic’ to capture Bunch as the Primary variety. Notice this can be changed/setup easily in any environment (be it development or production).

3) In the module ‘CAL02 Avg Price’, we change the formula of Relative Price to reflect the use of Lookup function instead of Select.

4) To conclude User Story 1, we simply pull Relative Price on the existing UX page.

5) For User Story 2 and 3, let’s create a system module for Variety dimension (‘SYS03 Variety’) to calculate whether a variety is Primary or Secondary.

6) Using the ‘Primary?’ Boolean in ‘SYS03 Variety’, we edit the existing saved view off the module ‘INP01 Forecast Price’ to show/edit data on UX page for Primary variety only. This solves for the User Story 2.

7) Next, we move on to implement User Story 3. First, we create a system module ‘SYS04 Year’ to distinguish past from future years.

8) We create another module ‘CAL03 Price Forecast’ to host the calculation of forecast prices for Secondary varieties. For user convenience, we include both past and future years for all the varieties.

The formula for Price line-item is as follows. Notice the use of Relative Price to calculate Price Forecast of all the (Secondary) varieties.

We use ‘Sales?’ Boolean to calculate forecasts for only the months the given variety is usually sold in. (If you remember, we discussed the use of Sales? Boolean in the related use-case as well).

9) We conclude User Story 3 by setting up another UX page for the user to select any variety in the context selector and review its forecast prices while referring to the past prices.

Conclusion

We encountered a scenario where the use of SELECT function seemed an obvious choice. However, SELECT involves hardcoding a reference, which might make the solution unsustainable. We learnt how to use LOOKUP in lieu of SELECT, keeping the solution robust in the wake of future changes.

We usually encounter dilemmas where we ask ourselves whether to deploy Select (and solve the problem easily and immediately) or whether to avoid it (and implement a lengthier, but more sustainable method). When such a situation arises, always kick-start your thinking process and analysis with a singular goal — avoiding SELECT function. And more likely than not, you would be able to find an alternative solution, however, impossible it might appear at first.