How To Predict Future Supply Based On Past Trends?

[xlmaniac]

Dear All,

I do have an original data-set pertaining to vendor supply spanning more than 20,000 rows.

Following is the sample data-set for the month of March 2023 against 2 SKUs. Data Set is across A1:D12. Result is needed in G & H Columns as shown below.

Based on the below, data points, I would like to predict –(a) Chance of getting supply in the next Purchase Order (b) % Supply against Purchase Order(Supply%=Supply Qty/PO Qty)

Is this possible to do with these inputs?

I am using Office 2010.

 

[Tivakaran]

You are planning inventory supply? I'm using MinMax stock planning for delivery prediction. You need to have leadtime & monthly usage data to forecast order qty & delivery qty

 

[xlmaniac]

Thank Tivakaran for your reply.
Yes, I am trying to calculate the supply probability against the past probability.
Based on the vendor supply data, I would like to predict whether one SKU could be delivered in the next immediate supply or not.
For example for the SKU#131056, out of last 6 instances where the Purchase order was generated, we got supply 4 times only. My past probability for this SKU is known.
Based on the past probability, I would like to calculate the future supply probability for SKU#131056 in the next immediate supply.
I feel that this could be done with Conditional probability but unable to frame it in excel.
Would be great, if you can help further.
Regards