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

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
Anaplan Report page issue in the UX
Hello community, I have an issue with anaplan report (functionality). The issue I have is within couple of slides. The data in that report is scrolling into a new page. There is a hierarchy in the rows and line items and time in the columns. My problem is, the hierarchy is getting broken in the second page. One of the node…
ADO - S3 incremental load vs. full replace
I want to create an S3 connection that will have monthly data stored in individual csv files that is connected to ADO. Around month end, data will be uploaded multiple times, replacing existing csvs. When this happens I want to replace the existing data for that month with the new file. I don't want to have to create a…