Hierarchy and Demand planning(like MRP)

D0nmac

Hi folks,

 

I was wondering if you might be able to help point me in the right direction  as I seem to have hit a wall. I am building a model which appears to have a linear hierarchy SKU-> Product -> product Family  and this is working as expected.

 

However in part of the model I need to work out  component requirements in which we currently take the monthly  figure then multiply it by a percentage per component. The problem I'm having is that to be able to do this I feel I need to change my hierarchy but as soon as I do that the levels below can't map he current level and I need the figures from the existing hierarchy to get my solution. e.g. Jan 10,000 is made up of part A 60% and part B 40%  I want to be able to show that at product family level we need 6000 Part A and 4000 Part B 

 

I'm just a bit stumped and I'm looking for possible suggestions that might get me moving on it.

 

Thanbks David 

ArunManickam

You can achieve this without the hierarchy. You need a list and module to define the relation between Product and component, thats all, as it is Many-Many, it is ideal to be a numbered list.

 

 

Independent forecast @ SKU level as follows,

Calculate the exploded forecast for each relation line as follows,

 

You could consolidate the exploded forecast by component as follows,

 

Hope it helps. Here my Component list and PRD-COMP list are independent of the product hierarchy.

 

Thanks

Arun

DavidSmith

@D0nmac 

Your hierarchy of the SKUs to Product will enable you to get the 10,000

 

1. Create a module by SKU (SKU Details) and add a line item called Product. Format the line item as the Product list and enter the formula: PARENT(ITEM(SKU))

2. Add another line item called %, to hold the respective splits

3. You can then use the line item from 1. to LOOKUP the parent value.  The formula, in a module by SKU will be something like Source module.Demand[LOOKUP:SKU Details.Product]*SKU Details.% 

 

If you are unsure about LOOKUP, see the attached

I hope that helps

David

 

D0nmac

Thanks for that,  I've attached a sample spreadsheet with a rough layout of what I'm trying to achieve. My main issue is that we need the SKU total from the calculation  but the formula is assigned to a level or 2 up. Also in our live data we have multiple SKUs rolling into the same product hierarchy with the same formula.

 

If I make my hierarchy unique for the formula layer which I need to do to get the formula onto Anaplan, I don't then have the hierarchy to roll up. I was thinking of creating a  module with a unique reference for each recipe, but have a hierarchy component part in it e.g. unique1, product1, unique2, product1  with a list for the unique code and then create a module using this and time then use the lookup  to get the Product total and then the  correct percentage for my calculation.

 

does this sound correct or am I wasting my time? 

 

Thanks again David 

D0nmac

Thanks @ArunManickam   I'll try that today and let you know how I get on but it looks exactly what I'm trying to do 

 

D0nmac

@ArunManickam  

I've been trying what you said but I'm getting a lookup issue with a data mismatch,   

Data type mismatch: format other than LIST or DATE being referenced as a relation

 

so I think I may have misunderstood you when I've executed it.

I've created a numbered list for the items I have, 1-106.  I've then created a module and imported  a unique reference 1-106, products, components and percentages. 

 

then when I goto do my lookup referencing back to the model I get teh above error.

 

am i doing the correct thing or should the numbered list contain all of the propertys we are using.

 

Thanks again David

ArunManickam

Hello David ( @D0nmac ),

 

The format for Product and Component should be List formated to the respective lists. I believe you thats why you are getting this error.

 

 

Thanks

Arun

D0nmac

Thats exactly what it was, I've made that change and its working as you discribed. 

 

Thanks a lot Arun