Dynamic calculation on Weeks vs Cumulatebyweek

pierreyvessautereau

Hello all,

 

I have a tricky point, and cannot succed to solve it.

 

There is Confirmed orders (already sold) and Forecast orders (we produce in advance because we think we need to have some stock for many reason).

My company will produced all the quantities, but not exactly at the correct weeks (sometimes late, sometimes in advance).

=> cf figures in green below

 

My need is to detail my production Qty : was it done for Confirmed Orders or Forecast Orders ? (or both)

No need to precise when was the orginal need, if I have delay or whatever.

=> cf figures in blue below

 

My production Week 1 is enough for all my need of week 1 and week 2 (20 Conf. Orders and 10 Fcst Orders)

My production Week 2 is enough for a part of my Fcst Orders need week 3 (the previous week needs has been already served by my production week 1).

etc

(I hope the use case is clear)

 

So to have the blue result : I cannot succeed to find any way, either using cumulate or whatever.

Is any Anaplaner has a good idea for me on this ?

 

Many thanks !

pierreyvessautereau

OK so it works for my case.

 

First I create a dimension with -10 to +15 and I populate as a parameter how many weeks before/after it represents.

It is because my production can have until 10 week delays, not more, and 16 weeks anticipation, not more.

"Pegging" is the internal name of seing what kind of need my production is covering.

 

I have my CO (confirmed orders) and ALO (Anticipated Launch Orders = Fcst)

I have my production, that can be totally un-synchronised.

I made some "cumulate" line items.

(For the ID week : it is an exemple below, I know it should be in SYS_WEEK)

Assumption is : for a given need week I produce first CO then ALO. If my week is still not fully produced (ALO included) I do not start the CO of the following week. 

The goal is to see how many weeks my production can cover.

So I do a specific module with the above dimensions plus the dimension Pegging (-10 to +15 so take care of volumetry ! In my case we are good and we still can play on subset)

 

The formula is :

IF Test Pegging.Production Cumul > OFFSET(Test Pegging.Need cumul, param pegging.Nb week, 0) THEN 999 ELSE param pegging.Nb week

Important : summary is at MIN

So while my production can fully cover my week need +1qty : I move forward to the next week.

29th of August production can cover more than 3 weeks in the future, but no more than 4, then four it is.

It can be negative because maybe my production is covering barely a need let's say -7 weeks in the past but not -6 weeks in the past.

 

Go back to my previous screen : I have now the number of weeks I cover from my actual production week.

Maybe my example is not perfect (too many 4's) but it works in any configuration. Formula : IF Prod ? THEN MIN(Pegging_Week when OK.Week until prod is used, 16) ELSE 0

 

So after that the key point is to calculate the cumulated CO produced. Everything is linked to that => I need to know for the week xxx how many CO was produced since the beginning of the first week.

It works in any cases I tested (and I tested a lot of wird configuration, largely strong enough for my need).

The key point is that if I produced for the week + 4 weeks (Line Item Nb week in future until prod is used) in the future, it means I am sure I produced enough for all my needs of the week + 3 weeks in the future. So the cumulated result should be at least "week + 3 weeks needs + 1" 

Formula :

IF

    NOT Prod ?

THEN

    PREVIOUS(Cumulated CO produced)

ELSE

    IF

        OFFSET(Need cumul, Nb week in future until prod is used, 0) <= Production Cumul

    THEN

        OFFSET(CO Cumul, Nb week in future until prod is used, 0)

    ELSE

        IF

            OFFSET(Need cumul, Nb week in future until prod is used, 0) - OFFSET('Anticipated Launched Order (ALO)', Nb week in future until prod is used, 0) <= Production Cumul

        THEN

            OFFSET(CO Cumul, Nb week in future until prod is used, 0)

        ELSE

            Production Cumul - PREVIOUS(Cumulated ALO produced)

The logic is a bit complex to prove but it works, I can give details if needed.

 

After that the rest of the line items is easy.

Ex : the CO produced this week is deduced via "Cumulated CO produced - LAG(Cumulated CO produced, 1, 0)"

 

Regards

JaredDolich

@pierreyvessautereau 

Yep. A couple of reactions. This is a real production use case, love it. One thing I help my customers with is to set up several drivers for production.

• These include minimum production quantity, round to, order point overrides, offsets (produce before the sub-assembly is produced), and leadtime overrides.
• Use a combination of POST, OFFSET, LEAD, and LAG to get the values to show up where you want. All depends on the logic/need.
• Anaplan will not allow you to create a circular reference, so if you find yourself in that situation you need to use Booleans that approve the production quantities, for example, rather than trying to dynamically post a forecast.
• Maintain a balance set (End of Period Inventory = Beginning Period Inventory + Adds - Reductions)
• Hint: for prioritization, you can set up some business rules that RANK the production work orders. Keep in mind that if you have sub-assemblies, those will need to be ranked ahead of the assembly.

Read @DavidSmith most awesome post (word document) on navigating the BOM. Also, take a look at @AdamCookson recent How To article on navigating hierarchies. Good stuff.

pierreyvessautereau

Thanks for the answer Jared.

 

One question one that :

Anaplan will not allow you to create a circular reference, so if you find yourself in that situation you need to use Booleans that approve the production quantities, for example, rather than trying to dynamically post a forecast.

What do you mean exactly ?

 

pierreyvessautereau

Hi,

I think I got a solution (pretty complex, but... seems to work)
I clean it and post it when I have some time.