Days of Supply logic

[luizcdepaula]

Hello experts,

I am facing a challenge and I hope you can help. I need to build a days of supply logic that look into the future forecast. I found a similar post, however it gave a partial solution. Find post below.

Days of Supply Problem

The main difference on my logic is that we usually keep around 6 days of supply, so we need to use the Total Stock (Total Receipts + Stock on Hand) and check how many days based on forecast we will be able to cover. Find example below.

Test:

LOAD * INLINE [

    date, Forecast, Receipts, Stock on Hand, Total Stock

    01-01-13, 1217, 5550, 2670, 8220

    02-01-13, 1197, 1230, 7023, 8253

    03-01-13, 1101, 2320, 7152, 9472

    04-01-13, 1161, 1259, 8311, 9570

    05-01-13, 1431, 1010, 8139, 9149

    06-01-13, 1226, 970, 9723, 8893

    07-01-13, 2330, 1323, 6563, 7886

    08-01-13, 3002, 1789, 4884, 6673

    09-01-13, 1277, 1323, 5396, 6719

    10-01-13, 1045, 1456, 5674, 7130

    11-01-13, 879, 1232, 6251, 7483

Expected result

    date, Forecast, Receipts, Stock on Hand, Total Stock, DOS

    01-01-13, 1217, 5550, 2670, 8220, 5.9

    02-01-13, 1197, 1230, 7023, 8253, 5.33

    03-01-13, 1101, 2320, 7152, 9472, 5.25

    04-01-13, 1161, 1259, 8311, 9570, 5.29

    05-01-13, 1431, 1010, 8139, 9149, 5.3

    06-01-13, 1226, 970, 9723, 8893, 5.87

    07-01-13, 2330, 1323, 6563, 7886, 6.1

    08-01-13, 3002, 1789, 4884, 6673, 5.98

    09-01-13, 1277, 1323, 5396, 6719, 5.32

    10-01-13, 1045, 1456, 5674, 7130, 5.56

    11-01-13, 879, 1232, 6251, 7483, 5.89

So the calculation of DOS is as follows: Stock on Hand 8253 as   { 8220-1197 +1230 = 8253 and then 8253 -1101=7152 and then 7152-1161 = 5991 and then 5991 -1431=4560 and then 4560 -1226=3334 and then 3334 -2330=1004. So when it gets to the point where SOH is lower than forecast, it is divided 1004/3002=0.33} at this situation we consider 5.33 days for days of supply for day 02-01-13.

Please help.

Thanks in advance.

LD

[sunny_talwar]

So essentially there is not upper limit to how many days in the future you might need to look at? If your inventory can cover up until 100 days, you will have to go 100 days ahead to find the DOS?

[Anonymous]

may be try with Previous() from back end .

or

Rangsum() in the pivot table ..

[luizcdepaula]

Hello Sunny,

Exactly. The horizon has no limit. We don't keep the DOS that high, but if we did, we would calculate as it is.

The logic I am using right now from the other post that gives a partial answer is below

IF(Peek([Forecast],RowID,'Temp') < [Total Stock],

        1+ (([Total Stock] - Peek([Forecast],RowID,'Temp')) / Peek([Forecast],RowID+1,'Temp')),

        [Total Stock] / Peek([Forecast,RowID,'Temp')) as [Days of Supply]

[sunny_talwar]

Can you share the complete script you are using?

[luizcdepaula]

Our planning system uses three main paramenters, Plant, Material (SKU), and Date.

[Temp]:

LOAD

    %Plant as "Plant ID",

    "%Material ID" as "Material ID",

    %Date as "Date",

    "Forecast",

    “Receipts”,

    “Stock on Hand”,

    "Total Stock",

    Rowno() as RowID

[DOS_final]:

LOAD

    *,

     IF(Peek([Forecast],RowID,'Temp') < [Total Stock], 1+ (([Total Stock] - Peek([Forecast],RowID,'Temp')) / Peek([Forecast],RowID+1,'Temp')),

[Total Stock] / Peek([Forecast,RowID,'Temp')) as “Days of Supply”

Resident Temp

Order by “Date”

;

[sunny_talwar]

I don't think that this script is even close to what you are looking to get. I am going to try another way and I am going to make an assumption that the max DOS will always be less than 7 days. We can try to look to expand on to the assumption example later on.

[luizcdepaula]

I like that idea. The one that I using right now it is not close to what I am trying to get, as you said.

Thanks,

LD

[sunny_talwar · Accepted solution]

Check this out

The values are correct for only first two rows, but I guess as we add more data, you should start to see correct numbers

Script used:

Temp:

LOAD * INLINE [

    date, Forecast, Receipts, Stock on Hand, Total Stock

    01-01-13, 1217, 5550, 2670, 8220

    02-01-13, 1197, 1230, 7023, 8253

    03-01-13, 1101, 2320, 7152, 9472

    04-01-13, 1161, 1259, 8311, 9570

    05-01-13, 1431, 1010, 8139, 9149

    06-01-13, 1226, 970, 9723, 8893

    07-01-13, 2330, 1323, 6563, 7886

    08-01-13, 3002, 1789, 4884, 6673

    09-01-13, 1277, 1323, 5396, 6719

    10-01-13, 1045, 1456, 5674, 7130

    11-01-13, 879, 1232, 6251, 7483

];

For i = 1 to 7

  Left Join (Temp)

  LOAD Date(AddMonths(date, -$(i))) as date,

  Forecast as Forecast$(i)

  Resident Temp;

NEXT

Table:

CrossTable(Forecast_Type, Value, 5)

LOAD *

Resident Temp;

TempTable:

LOAD date,

  Forecast,

  Receipts,

  [Stock on Hand],

  [Total Stock],

  If(date = Previous(date), RangeSum(Peek('CumValue'), Value), Value) as CumValue,

  Value,

  Forecast_Type

Resident Table

Order By date, Forecast_Type;

FinalTable:

LOAD *,

  If([Total Stock] - CumValue > 0, KeepChar(Forecast_Type, '0123456789') + ([Total Stock] - CumValue)/Peek('Value')) as DOS

Resident TempTable

Order By date, Forecast_Type desc;

Right Join (FinalTable)

LOAD date,

  Min(DOS) as DOS

Resident FinalTable

Where Len(Trim(DOS)) > 0

Group By date;

DROP Table Temp, Table, TempTable;

[sunny_talwar]

Now to expand this, I think all you need to do is change this part of the script

For i = 1 to 7

Make this as big as your realistically want this to be

For i = 1 to 100