How to Calculate Months of Supply (MOS) in SAP IBP

[former_member1363065]

Hi All,

I am working on a requirement where I need to calculate months of supply (MOS) in SAP IBP. Lets have a look at the following example.

months-of-supply-mos-example.png

If you look at the above snapshot you will see three KFs Demand, Projected Inventory and MOS. All three KFs are custom. Now I want to calculate how much demand can be covered through the projected inventory and populate the same in MOS KF.

I know you might be having question in your mind that why Demand and Projected Inventory KFs are custom. This we had to do it for a specific case. Keeping the fact in mind that Demand and Projected Inventory KFs are custom can anyone please suggest how this requirement can be achieved in SAP IBP or through EPM using MOS as a local member.

Regards,

VG

[Irmi_Kuntze · Accepted solution]

So, it is not projected stock in SAP speech, but stock at the end of the initial period

If you want to populate MOS the way you layer it out, it is an incomplete picture

So when you use EPM anyhow, I would calculate projected stock with EPM

Alternatively, if you want to do in backend instead of EPM, in the „old days“ until release 1902, you would have needed LCode for that. Since 1905 we do have a new operator IBP_CAGGR. I just tested that successfully on another use case and love it already a lot! Very powerful, but a bit tricky as well. It should work for those kind of consumption calculations and is described in the latest model config guide. It can cumulate across all periods even if you have not displayed them in Excel, so you’ll need a helper if in which you copy the relevant horizon of all relevant Input KF first. Than you calculate the projected stock in the first period for the first period, and in the second (and all following) period you calculate the same but not based on period-by- period, but an cumulated values, in which the calculation result of the previous period is re-calculated in the new period.

—————-

So, you would need

1) a calculated kf for each input kf, e.g. HDEMAND, HTOTALRECEIPT on base planning level checking on relevant horizon, by checking on PERIODIDn compared to $$PERIODIDnCU$$

2) a calculated cumulated kf on base planning level for each input kf such as HDEMANDCUM, HDEMANDTOTALRECEIPTCUM with IBP_CAGGR

Instead of step 1, with the right settings in IBP_CAGGR you could just do a forward calculation instead, but that would not consider open demand from the past. But IBP_CAGGR has issues with initial values, so having a kf upfront that places a 0 everywhere instead of NULL is helpful as well

3) calculate projected stock period over period, but taking the relevant cumulated into consideration, such as = INITAILINVENTORY + INITIALINTRANSIT + HDEMANDTOTALRECEIPTCUM - HDEMANDCUM.

REQUEST level is a simple sum then

4) calculate MOS based on projected stock from 3) in a similar manner

With that, you transform a cross-period calculation into a period-by-period calculation 🙂

It takes quite some time understanding how IBP_CAGGR works and how you can use it , so EPM will be MUCH faster.

Good luck

Irmi

[former_member1363065 · Accepted solution]

Hi Irmi,

Thanks for your reply. WIll try this out and see how it works. Mostly I will try to achieve this requirement through EPM.

Lets see how it goes.

Regards,

VG

[Irmi_Kuntze]

My very first question: why does projected inventory only have a value in the current week, why is it not populated for the remaining weeks ? How did you calculate that if it is a custom if?

[former_member1363065]

Hi Irmhild,

Thanks for your question. The reason why I have put the projected inventory in the current week is because I wanted to put the requirement in a simple manner.

But in reality it will be populated in remaining weeks as well. So the intention here is to derive the MOS value based on demand and projected inventory KFs. I hope this answers your first question.

Now coming to your second question where you want to know how custom projected inventory is calculated.

The formula that I have used to calculate custom projected inventory is (INITIALINVENTORY + INITIALINTRANSIT + TOTAL RECEIPT) - DEMAND

I hope I have answered all your questions. If you have any more questions please let me know.

If at all you find any solution to calculate MOS please do let me know.

Regards,

VG

[piyush_parekh]

Hi VG,

If I understood the requirement correctly, to achieve the desired result using local member is a bit difficult. You can try following approach using key figure configuration (not very intuitive, will post if I could think of a better approach)

If M1 is current period, you need to bring future period demand in M1 for which use attribute transformation. To consider demand in M3, use another transformation to bring M3 demand in M1. Create one more key figure (sum of future two periods) to sum up above offset demand M2 and M3 in current period. This sum key figure can be used to compare with projected inventory using nested IFs in key figure expression.

Number of attribute transformations and future periods sum key figures will further increase with number of periods you want to consider and will complicate nested IF expression. Consider configuring all intermediate key figures as helper key figures. All the best!

Regards,
Piyush

[former_member1363065]

Hi Piyush,

Thanks for your reply. I know how to create a local member. Thanks for your detailed explanation on local member creation.

What I want to know is how do I calculate Months of Supply (MOM). In the example that I have shared if you see Projected Inventory in first period is 30 which covers three periods of Demand. So with that logic MOS in the first period is 3.

So my requirement is how to derive the MOS value (in this case it is 3) based on Demand and Projected Inventory in IBP or through EPM.

Sharing details on the planning levels for KFs Demand and Projected Inventory.

Demand is at WKPRODLOC (IBP KF)

Projected Inventory is at WKPRODLOC (IBP KF)

MOS (Local Member as of now)

Regards,

VG

[former_member1363065]

Hi Piyush,

Once again thanks for your reply. I will think over on the solution that you have proposed.

This will actually make the design very complicated and also not sure whether we will get the desired results or not. I am not sure whether this requirement will be possible in IBP.

If at all you find anything better please do let me know.

Regards,

VG