topic Re: Forecasted Inventory Volume/Demand - How? in QlikView

Forecasted Inventory Volume/Demand - How?

Anonymous — Tue, 21 Jul 2015 13:47:38 GMT

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:

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Tue, 21 Jul 2015 14:06:36 GMT

Hello,

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

Serhan

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Tue, 21 Jul 2015 14:11:00 GMT

Hello --

I am at the very start.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Tue, 21 Jul 2015 14:32:03 GMT

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.

Serhan

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Wed, 22 Jul 2015 18:12:58 GMT

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.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 02:19:30 GMT

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

Serhan

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 11:55:24 GMT

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.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 12:13:42 GMT

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.

Serhan

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 12:24:39 GMT

Thank you!

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 13:42:07 GMT

Hello,

This should solve things.

Serhan

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 14:02:58 GMT

Serhan --

This is very helpful. Thank you.

However, the "replenishment" amount needs to be dynamically calculated. I think I am closing to devising how to do this within the chart, but I am a little hung up.

Here's my current expression within the chart.

ITEM_QTY_ONHAND - RangeSum(Above(Sum(QTY_REQ), 0, NoOfRows(TOTAL)))

This gives me the rolling "depletion" of stock - which ultimately continues to drive into a negative value. Now, I need to say when that expression is less than the "minimum required on-hand", add the minimum order qty to that value to get the upswing - that is what I am struggling with.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 14:12:20 GMT

Here's the data to help explain what I'm seeing.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 14:24:21 GMT

The "Test" field is my attempt to replenish when the running total dips below the min inventory level - however this is flawed because it will continue to drive negative as the running total of depletion keeps declining. I essentially need to add this back to itself - but I can't figure out how to do that.

Re: Forecasted Inventory Volume/Demand - How?

Anonymous — Thu, 23 Jul 2015 14:34:43 GMT

Hello Joe,

My advice is that, if possible, do not try to replenish auto; just point out the levels to be taken action, replenished. Because you may get lost at front-end easily. Instead, try to get the min levels to the background and make the user play with them with a what-if scenario as I tried to do.

That's all I can offer I guess

Serhan