Hierarchy and Demand planning(like MRP)

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

Accepted answers

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

All comments

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

SUM_and_LOOKUP_53618.pptx

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

sample_data_53619.xlsx

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

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

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Postman/Anaplan - message "404, Not found"
Hi all, I, as an Anaplan workspace administrator, am working together with a Fabric team to export data from Anaplan to Fabric via Anaplan API and Postman. We have a problem where all Anaplan exports are visible in Postman but when trying to connect to SOME of them, we get message "404, Not found". Below screenshot showing…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…