Forecast shortages

[BrutalDawg]

Hello,

I am trying to be able to better identify shortages without as many steps for other users that are not as fluent in excel. Currently, all I do is take my sales order filter down to 3-4 weeks and run a combination script:

Sub CombineRows()
'Updated 20150511
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Stock-INSERT-DATE-HERE"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

Then I utilize a simple conditional formatting rule that highlights any item that is less than Stock. This creates a lot of leg work of verifying when the PO will deliver compared to the sales order. I am trying to develop a tool, where I can have the Sales Orders on Sheet1, Stock on Sheet2, and better identification of shortages. For example stock:

[TABLE="width: 160"]
<tbody>[TR]
[TD]Item Number[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1a1[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]2b2[/TD]
[TD="align: right"]305[/TD]
[/TR]
</tbody>[/TABLE]


Example Sales Orders:
[TABLE="width: 663"]
<tbody>[TR]
[TD]Location[/TD]
[TD]PurchaseOrderNumber[/TD]
[TD]Part Number[/TD]
[TD]Timing[/TD]
[TD]Quantity[/TD]
[TD]When[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]A437B[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD]A8683B[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD]A893[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/8/2016[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]11/15/2016[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]11/15/2016[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]A437B[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/15/2016[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]A7893[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]11/22/2016[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]B7820[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/22/2016[/TD]
[/TR]
</tbody>[/TABLE]

with an example output of:
[TABLE="width: 760"]
<tbody>[TR]
[TD]Location[/TD]
[TD]PurchaseOrderNumber[/TD]
[TD]Part Number[/TD]
[TD]Timing[/TD]
[TD]Quantity[/TD]
[TD]When[/TD]
[TD]Short Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]A437B[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/8/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD]A8683B[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/8/2016[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD]A893[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]11/8/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]11/15/2016[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]11/15/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]A437B[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/15/2016[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]A7893[/TD]
[TD]2b2[/TD]
[TD]Planning[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]11/22/2016[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]B7820[/TD]
[TD]1a1[/TD]
[TD]Planning[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]11/22/2016[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible with the SO's having split quantities?

Thanks for any suggestions!

 

[AlphaFrog]

Formulas accounting for optional -exact suffixes on Stock

Short Qty
=IF(VLOOKUP(C2&"*",Sheet2!A:B,2,0)< SUMIF($C$1:C2,C2,$E$1:E2),SUMIF($C$1:C2,C2,$E$1:E2)-VLOOKUP(C2&"*",Sheet2!A:B,2,0),"")

Rolling Stock
=VLOOKUP(C2&"*",Sheet2!A:B,2,0)-SUMIF($C$1:C2,C2,$E$1:E2)


Note: if you use the rolling stock formula, the Short Qty formula could be
=IF(H2<0,ABS(H2),"")