I am currently building a model for production planning, which is largely based on the Bill of Material (BOM) of the different products. I have previously discussed other topics regarding this in this thread.
I have however another question which it would be great to get some input on, namely how to build the BOM structure in Anaplan. It is a multi-level BOM, with a maximum of 9 levels. The highest level is the product itself, and then each BOM level (L1 -> L9) can be either raw material (RM) or Sub-assembly (SA). An SA always have a level below it, consisting of either another SA or RM. RM is always found at the lowest level. The hierarchy is very ragged, meaning that one product could go down to L9 level, while another only goes down to L2. Further, let's assume that product A consists of SA_1 and SA_2 on L1. It could be the case that SA_1 is only RM_1 and RM_2 on L2 level, while SA_2 is SA_3 and RM_3 on L2 level, and SA_2 is RM_4 and RM_5 on L3 level, as can be seen in the table below.
That is, different SA can go to different levels.
As far as I can tell, there are two ways of doing this structure in Anaplan, and I see issues with both of them.
Alternative 1 - Using composite list
This is the way I currently have the model set up. I have my product list, and then I have L1, L2... --> L9, with the different list being parents to the other ones. Now to the problem. Let's say that I want a module where I calculate the entire material cost for a product. I can do lookups in another list to get the cost, but as seen in the table above, the RM-items are all on different levels. So what should the dimension of my module be? If I select L9, I only capture the RMs on L9 level, and if I select L8, I only capture L8 and so on. What I would need is something like "enter a formula on L9 level and apply that formula to all levels", and as far as I can tell, that is not possible in Anaplan.
Alternative 2 - Using non-composite list
This is an option I am thinking about, namely having the entire hierarchy in the same list. There is however one problem with this, and that is that it cannot be a numbered list. As soon as I turn on the numbered list option, the hierarchy within the list disappears. And since one raw material can be used in several different levels, for example, RM_2 and RM_3 could be the same in the table above, perhaps steel, I would need to have unique names for all the items, which means it would be very strange and long names, since there will be tens of thousands of products, and multiple levels.
I am personally learning towards using the composite list, and as far as I know, this is also what Anaplan recommends. But as described, this also comes with issues. How would you guys deal with this? Any input is highly appreciated!
So, I might be wrong here, but if you have a 1:1 relationship meaning a child can only have one parent, the composite list should work. I would suggest having all members, regardless of level, in one "flat" list. Create a module where you import who the parent is like the below:
In the above module, create a line item (number format, summaries turned off) with a formula of 1. This will be used in the next module.
Create a module, using the same flat list as above, and sum the "counter" line time by the parent (Parent H1 in my above picture). This will give you the number of children each member has.
Back in the properties module (the first one), create a line item and name it is Base Member? with a Boolean format. The formula will be
You can create a module (very similar to the 305 training I did several years ago), where you find out who the parent is. I break it up into 4 sections:
finding the parent
creating the Ragged look
Balancing the hierarchy
First Occurrence section
Finding the parent: fairly straight forward in that you are looking up the previous line item's parent:
Ragged portion: It looks complicated, but really isn't as you are just looking for the next member and not repeat any of the previous members. Honestly, I think this can be optimized, but I haven't had a chance to look into it.
Level 2 formula:
Level 6 (just to give you some perspective)
Base level is the easiest:
Is First Occurrence portion:
Now, you create the views using the FC line items as the filters:
Again, the 305 training is about a 20 minute video of the above and can be found here: Video .
Let me know if you still have questions or if you would like to get on a conf call to talk this out.
Thanks a lot for your reply, I think it is very useful!
However, there is one problem - I'm not sure if the model I intend to use have a 1:1 relationship between child and parent. The way the BOM costing app by Anaplan is set up, is that each item within the list hierarchy (L1 -> L9) references an item in a flat SKU list through a list property. That means that the item "Steel" in the SKU flat list can be utilized within multiple levels in the BOM structure. All the BOM lists (L1 -> L9) are numbered list, where the SKU is used as display name, hence why "Steel" can be in multiple products, and even in multiple levels within the same product.
This is very useful, since it means that if I update the purchase price of the steel in a module which holds all the cost information, it will automatically flow through to all BOM items which are connected to the SKU flat list item "Steel".
Am I wrong in saying that this means that I cannot use your solution?
What I ultimately need is one large module which holds all the cost information for a products, for all levels, like the table below
RM_1 - Steel
RM_2 - Screw
RM_1 - Steel
RM_3 - Nuts
RM_2 - Screw
Note that the cost for Steel and Screw are the same for all places it appears in the hierarchy, since it references the same items in the Flat SKU list (SA's don't have a material cost since they consist of other materials). I would really prefer if it is possible to use numbered lists with the flat SKU items as Display Name, since that is basically what the material is. The item in the BOM structure is just there to hold the flat SKU list item within the hierarchy, and not having to have the actual material "Steel" in many different places, rather only in the flat SKU list.
Would it be possible to combine this with your solution?
I was afraid of that as my solution requires the child to have a 1:1 parent child relationship which I see in your 2nd post does not. I think the flat list of SKU will help, but let me noddle on it and see what I can come up with.