Forecasted Inventory Volume/Demand - How?

[Anonymous]

Hello --

I'm a little confused how I would go about this.

I have a somewhat interesting request to develop a chart in Qlik that will represent expected on-hand inventory volumes for future months. Basically by analyzing the puts/takes but predicting the puts based on when the amount on-hand reaches a threshold. Ideally they'd like to be able to manipulate the various fields to see how it affects the graph.

They'd like to do this  by capturing forward looking demand for inventory, comparing this to our leadtime for the purchased inventory item. The material required would be replenished within the necessary leadtime.

The graph would show the expected ups/downs of the material demand

The data I have to work with looks something like this:

Forecast Data:

Item #	Qty Required	Date Required
1234	500	8/1/2015
1234	500	8/15/2015
1234	500	8/30/2015
1234	500	9/5/2015
1234	500	9/30/2015

Inventory Item Master:

Item #	Leadtime	Min Order Qty
1234	10	1000

The expected graph they're hoping to get is something that looks like this:

[Anonymous]

Hello,

Are you able to apply the scenario without the functionality of user input? Or you are at the start of things?

BR

Serhan

[Anonymous]

Hello --

I am at the very start.

[Anonymous]

Hello,

If I understand correctly, you have the Forecast Data already which are basically something like orders that should be shipped on future dates. Plus I assume you know the current inventory.

So, if you treat forecast data exactly like stock data and do something like this:

Stock Data + Forecast Data

You would get something like this:

Item #	Qty	Date
1234	100	07/20/2015
1234	200	07/21/2015
1234	150	07/22/2015
1234	300	07/23/2015
1234	500	8/1/2015
1234	500	8/15/2015
1234	500	8/30/2015
1234	500	9/5/2015
1234	500	9/30/2015

So if you draw the chart of this data, you would get peak points but not the descending points. So what is needed now is to fill the gaps. It would require some advanced scripting but it is possible to calculate for instance daily inventory consumption (not sure what it is called in English, maybe inventory turnover?). Day by day you can deplete your inventory accordingly and fill the dates.

As I said this is hard work but I'm sure it's possible. I did something like that before but it was for stock replenishment, calculating average weekly sales of each item and replenishing the stocks according to min/ max value business logic etc.

Hope that helps. At least the approach may help since I'm afraid it is full time work on coding side and may exceed the objective of this community.

BR

Serhan

[Anonymous]

Thanks for your reply.

Yes, I understand I'm just wondering if I can do this within a chart or if I have to prepopulate the data in the load script?

My requirement was to be able to place an input field and have the data automatically change for simulation purposes.

[Anonymous]

Hello,

You should definitely do some work on script level. You can't handle it on front end. Even if you do, would be terrible performance.

Actually, with a second thought, if you just need to draw this line chart and nothing else is required, you may only need to add to my example table above, the missing dates of future with no corresponding value.And then you should be able to get your line without a problem. Attached is a small example.

The point is you just need the dates in your table, you don't even need the relevant values of these dates. You don't need to calculate daily depletion for example. You just need to have a minimum quantity value on the day before the shipping date so that line would drop at a certain level before replenished. My test app may give you more idea.

Item #	Qty	Date
1234	100	07/20/2015
1234	200	07/21/2015
1234	150	07/22/2015
1234	300	07/23/2015
1234	500	8/1/2015
1234	null()	8/2/2015
1234	null()	8/3/2015
1234	100 (min value)	8/14/2015
1234	500	8/15/2015
1234	null()	8/16/2015
etc.	null()	etc.
1234	500	8/30/2015
1234	500	9/5/2015
1234	500	9/30/2015

BR

Serhan

[Anonymous]

Hi Serhan --

Thanks again for your reply. Perhaps I haven't clearly explained the data I'm attempting to generate.

The forecast data shows the amount of inventory outs we will be experiencing for the given dates. So, these will be out transactions. This will deplete stock inventory. When stock gets closest to 0, they will trigger a new order for no less than the minimum order qty.

The graph is an attempt to show based on demand, how long the minimum order qty will last us with the anticipated forecast demand.

For simulation purposes, they'd like to be able to  change the minimum buy to see how long it affects their ability to cover demand.

So the graph is essentially a timer for the depletion of inventory based on demand. The low points will be when we start to run out of inventory and the upswings will be when we should purchase more material based on minimum inventory buys.

Hopefully that explains more clearly what we're looking for.

[Anonymous]

Hi Joe,

I understand your problem.

My approach was that since these will be outs, you must have them first to out them. So your inventory quantity must be at least 500 on the day 8/1/2015 in order to meet the order on the day.

On the next day, it drop to 0 or null().

So, what is missing is the daily depletion then. You need to calculate that such as let's say average daily stock outs of last 1 month per product. And start subtracting these values daily from the current inventory and see if it can cover the outage days. 

The last part of your question about simulations is similar what-if scenarios you can find in the community. I think you should first construct the model without the simulation and then see how simulation comes in. At least that would be my approach.

I will try to do a sample for you, if i can find time.

BR

Serhan

[Anonymous]

Thank you!

[Anonymous]

Hello,

This should solve things.

BR

Serhan