Best way to handle validity of products prices
Hello Anaplan community,
I'd like to create a model that stores prices for my items, and the evolution of these prices.
A few constraints though:
- Prices can evolve with time, and the applicable price at one date can be different from the applicable price at another date.
- Products don't change prices at the same time, and changes in prices don't happen so often (perhaps once a year or once every two years).
- The active price is the latest price input.
- I'd like to be able to easily gather the current price for one or more products, or their prices at a specific date.
- I'd also like to keep track of the history of price changes to have a neat view of the price evolution of a product.
- It must be quite simple to input the prices for many products in bulk.
I thought about a few solutions:
- Create a module where time is a dimension, and products also, with a line item with the product price, but:
- I find it really heavy to maintain the price for each day/week/month/year (depending on the time granularity) for each product
- The price may change at a specific date/time, in the middle of the week/month/year
- Create a numbered list where each item would be the n-th price of the product, and create a module with that list and the list of products and two line items (one with the product price, one with the effective date), but:
- Getting the current active price, or the price at a specific date seems complicated, as it may be on a different item of the n-th change list.
- Changing latest prices in bulk seems complicated too.
What would be your recommendation on how to handle this?
Thanks a lot,
FX
Best Answer
-
I personally prefer to store data in a dimensional way, not transactional, I think that is just easier to use in Anaplan. That means solution one.
You don't need to update price every month. You can create a logic with a line item "Final Price" that take previous price unless the line Item "Price Change" is updated. And you can add another LI called "Price Change Date" to specify a particular date within the month. This way you only need to update if there is a change, otherwise price is carried forward.
2
Answers
-
Hello FX
First of all it is good to know more details like:
* How many SKU's you have?
* How frequently they have updated prices?
* What is your granularity of planning (day/weeks/months?)
* Do you to review actuals to somehow compare Plan vs Actuals?
* How many history periods do you want to store?
As initial thoughts, If you dont have a really big amount of SKU's (<<10k) and your price changes occurs less then 5 times a year for each of SKU you might consider following data structure to store your price changes:
* Numbered list with properties: SKU, Date of change
This list will represent transactional nature of changes a price. You would need to have a code to distinguish a date.
* Data storage module which will import your changed prices for each of the record
* System Module which will have relevant relationship with products and necessary mappings (i.e with time periods)
I would suggest to think about potential conflicts like: in a given period "Sep 2020" could occur 2 price changes for specific SKU.
Keep in mind that storing data in a list is more "expensive" storage (200b) rather then store in a module cell (8b), so design of this structure is linked to the "shape" of your data. If your prices changes quite often, it might be even better to store price changes as a module dimensioned by daily timescale.
1 -
Please don't embed the Date into a list members code as you have above. Instead, use a transactional module where the module is dimensionalized by the List and by Time. As @nathan_rudman stated, keep it multidimensional. In the your example, a better way to do this is have a line item where you load the price (Price Input). Another line item for Final Price having the formula : previous(Price Input). This keeps the list members to a minimum while still having all of the functionality of the data changing over time.
For more information on this, you can read this article: https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-Peak-Performance/ta-p/48866#M509
Rob
2 -
Hello Nathan,
Thanks for your reply.
I understand the benefits of such a method indeed. It's much easier afterwards to get the pricing at a specific date, week, month... etc...
As @nikolay_denisov states, I really have very few pricing changes (most products will never have), so adding the time dimension creates quite a huge amount of cells, that's the only drawback.0 -
Agree with @nathan_rudman
Slowly changing dimensions are handled best in Anaplan when you use time as a dimension (and as @rob_marshall mentioned, not in embedded in the UID of the list). Yes, your module will be large which is the tradeoff here, so you may want to consider using a time dimension that is more aggregate like, month, or quarter. Also, think very hard about why you need this information and the value it brings. You can avoid any issues with prices that change within your aggregate time dimension by associating the price to your receipts. Since the receipt has value (say USD) and there is a respective unit you can calculate the price (Receipt Dollars / Receipt Units). In olden times, this is how retail inventory method for valuing inventory was accomplished, using cumulative markup (CMU) based on receipts. The cost of goods sold was calculated from the retail and the CMU. No units.
Another use case for slowly changing values on time is in-transit inventory, as it changes day to day but it's a snapshot in time. This requires two time dimensions. That ought to give you a challenge!
0 -
unless you are very short on space, a few milion cells really don't matter
0
