Planning Sales or Invoices Using Tier Pricing
Summary
As an ad planner, sales representative, or merchandise planner, you might be interested in planning your sales or invoices using tier pricing. This solution will address a user story that allows you to set the prices by tier range and automatically look up the right price based on the units ordered. This solution was first introduced by @DavidSmith in his Community forum response to solve a customer satisfaction score, and inspired by a question asked by @Vignesh Mohan in this post.
This Best Practice article also addresses the issues associated with using multiple nested 'if' statements, which is typically implemented for use cases like tier pricing or use cases that use decision trees. To address these challenges, this article uses the D.I.S.C.O. methodology, which promotes the concept of calculating once and referencing often. In addition to D.I.S.C.O, we also promote the PLANS philosophy by keeping our line items simple and auditable.
Assumptions
Tier pricing can be very nuanced, introducing all kinds of variables such as preferred customer status, units or cases ordered, delivery method, products that are special-ordered, and so on. In this use case, we will only be focused on the overall logic to look up a price based on a unit quantity. Nuances such as the ones above are easily introduced in the calculation module to further improve the overall fit for your specific use case.
This use case also assumes a monthly calendar, and each unit quantity will be evaluated at that level. We will introduce the customer and product dimension, but your use case may include others. These additional dimensions can be added to the system and calculation modules. In this use case, we will keep it simple by only using an overall product and customer dimension at the month level.
Our tiers are rounded to the nearest thousand units, so we will assume that our tiers are represented in thousands. For example, 1 would represent 1,000, and 15 would represent 15,000. The reason for this will be shown when we build the list of possible unit sales. We will also assume the minimum order quantity (MOQ) is 1,000.
User Story
As a retail sales representative responsible for forecasting the demand for upcoming wholesale transactions, I need the ability to have Anaplan automatically look up the right price for the customer based on the quantity forecast to be ordered. This, in turn, will calculate my overall gross margin based on my landed cost of the product. I will then need to see my overall product margins for the upcoming months. I know this will be right when I see that for every product and customer, for every month, Anaplan is looking up the correct price. I will also know it’s right when I can roll up my assortment by month and see my overall gross margin. I expect the following scenario to be solved:
Products
- There are two products. White T-Shirts and Navy T-Shirts.
- Products are at the style-color level.
- The style colors roll up to a top level called “All Style Colors.”
Wholesale Customers
- We have two wholesale accounts: Andres Segovia T-Shirts and Fernando Sor Famous Tees.
- Our wholesale accounts roll up to a top level called “All Wholesale Accounts.”
Tier Pricing
- We have 5 tier prices, but we want the option to add more, so we want 10 tiers.
- Tier prices vary by product.
- We will stagger our 5 tiers every other one so we can easily insert new ones.
Solution
This solution will heavily use the D.I.S.C.O. methodology, outlined by @DavidSmith, and will require two lists and four modules: a list and system module for the tier pricing; a list and system module for the possible order quantities; a calculation module, and a planning module. We will assume that the customer and product dimensions each have a list and corresponding system module and that the calendar also has a system module. The calendar uses a traditional monthly year, and we will only be evaluating one year.
Calendar
The model calendar in this use case uses a traditional monthly calendar. However, any calendar will work with this solution. In addition, it is not necessary to have Quarter, Half, YTD, or Total of all Periods checked to solve the tier pricing use case, but your model may already have them checked for other purposes. In either case, the solution in this Best Practice article will work.
Summary of Steps:
The following steps will be used to demonstrate how to solve this Tier Pricing use case:
- Build a system/input module for our style colors and add the landed cost.
- Build a list and system/input module for the tier pricing.
- Build a system module for tier prices.
- Build a list and system module for the possible order quantities.
- Build a calculation module that transforms and summarizes the tier pricing and possible order quantities.
- Build a planning module that automatically looks up the price based on the order quantity forecast.
- Build a view that summarizes the gross margin by customer, product, and time.
Step 1: Build a system module for our style colors and add the landed cost.
Since we only need one landed cost per style-color, we’ll add that property to a system module that uses the style-color list. We will also add the MSRP and a Boolean filter to filter out only style-colors. We’ll use these later when we calculate the overall gross margin.
Step 2: Build a list and system/input module for tier pricing.
The next step is to load or enter our tier pricing by product. In this case, we’ll need to create a list and a system module to accommodate this part of the process. The use case requires 10 ranges, even though they will only use 5. We will then import or enter the tier pricing for these products.
List:
System/Input Module:
Note: You may notice we apply a filter on the saved view below. The filter is using the “style-color?” Boolean line item we established in the SYS001 module (above). This removes the aggregate levels of the product hierarchy from our saved view and makes the grid much easier to read. Also, the aggregate levels are zero, because we set the summary level to “None.”
Blueprint View:
Note that since this is a system module we can set the summary method to “None.”
Step 3: Build a system module for tier pricing.
We need one line item that holds the calculation for a list formatted line item for tier pricing. We will use this in the calculation modules ahead. Note the “applies to” uses one dimension: “price tiers.”
Step 4: Build a list and system/input module for the possible order quantities.
This is one step that deserves special consideration for your use case. Since our simple use case allows for tier pricing in the thousands, we can make our possible order quantities list much smaller by dividing the order quantity by 1,000. Your use case may require you to build it out by units. The good news is that if you use the D.I.S.C.O. methodology, Anaplan will handle this very efficiently.
Since the highest possible order quantity is 1 million, we’ll set our list to 1,001, one more than the maximum.
List:
Module:
Blueprint:
Again, because this is a system module, we do not need summary levels. So, our numeric line item “Value to Use” is set to “None.”
Step 5: Build a calculation module that transforms and summarizes the tier pricing and possible order quantities.
This is where the D.I.S.C.O. methodology really takes form and begins bringing everything together. The calculation module here will combine the tier pricing and the possible order quantities and try to evaluate which tier is valid for the order quantity during that month for that product.
For every possible order quantity combination, it will check two Booleans. The first is whether the order quantity is above the lower limit of the tier price. The second is whether the order quantity is below the upper limit of the tier price. If both Booleans are TRUE, then we made our match.
Let’s test our calculation module. If the order quantity is 14,000 units, what price range should be used? If we look at our SYS002 module above for White T-Shirts, it says it should be in Tier 5. We passed our unit test!
Module:
Blueprint:
Note that the “Tier List” line item references the SYS004 module. This is an important concept with the D.I.S.C.O. methodology. Calculate once, refer often.
Low = 'SYS003 Possible Order Quantities'.Value To Use >= 'SYS002 Price Tier Table'.Lower Limit
High = 'SYS003 Possible Order Quantities'.Value To Use < 'SYS002 Price Tier Table'.Upper Limit
Tier? = Low AND High
Tier List = IF Tier? THEN 'SYS004 Price Tiers'.Price Tier ELSE BLANK
Step 6: Build a planning module that automatically looks up the price based on the forecasted order quantity.
The next step of the process is to look up the wholesale price using our calculation module. We will do that from our planning module.
Note: Since we are now introducing the time dimension, be sure to turn off summaries on the line items where you do not need them. In this case, we will only leave the summary on our order quantities and our gross margin. The landed cost and MSRP are lookup values from the SYS001 module for our style-colors. “Calculate once, reuse often.”
Module:
Blueprint:
Blueprint (Summaries):
Turn off any summaries that are not required.
Step 7: Build a view that summarizes the gross margin by customer, product, and time.
The last step is the most fun since we have our tier pricing working correctly. Let’s take a look at the summary report and meet the final user story requirement!
Module:
Note: It is not obvious here, but the filter being applied in this saved view is using a time system module that limits the view to show months only. This just makes the view easier to read but is not required.
Conclusion
As part of supply chain implementation, you will likely encounter the need to implement tier pricing. This use case gives you the fundamentals of how to take advantage of the D.I.S.C.O. methodology and to efficiently architect this use case.
From here, any number of paths can be taken, like calculating how often a customer uses each tier or which customer provides the best overall gross margin dollars and/or gross margin percentage.
Special thanks to @DavidSmith for sharing his customer satisfaction solution and for his ongoing support for Best Practices. And a special shout out to @rob_marshall, to whom I owe most of my deeper understanding of Anaplan.
Comments
-
@rob_marshall @JaredDolich @DavidSmith This is easily one of the cleanest overviews of the DISCO methodology I’ve come across. The modules are organized, filters are separated, inputs and calculations are broken out...
This is also a great tutorial for converting between number/text/list values, which is often an overlooked strength of Anaplan’s handling of metadata referencing and aggregation. I’ve used a similar technique for finance and real estate classifications in prior roles, so this is a very transferable process to non-supply chain use cases.
Simply put, this is quite well done. And above all, this provides a great glimpse into supply chain/merchandise planning for those of us without expertise in this area.
2 -
thanks for the feedback
1 -
Thanks @DavidEdwards as you might have guessed I had a lot of help from @rob_marshall and I used @DavidSmith's article as a starting point. Had a lot of fun writing this.
1 -
@JaredDolich
This is really clear, organized, useful, and most importantly outlines a clean/DISCO way to implement a functionality/use-case that is quite common.
I love the idea of building on concepts introduced by others, that is growth.This type of article is what I meant by my suggestion to introduce solutions to common use cases.
Honestly... Fantastic!1 -
Thanks @einas.ibrahim
We all make a good team!
0
