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:

Item Number	Quantity
1a1	200
2b2	305

<tbody>
</tbody>

Example Sales Orders:

Location	PurchaseOrderNumber	Part Number	Timing	Quantity	When
80	0	1a1	Planning	50	11/1/2016
72	0	1a1	Planning	50	11/1/2016
80	0	2b2	Planning	100	11/1/2016
80	A437B	1a1	Planning	50	11/8/2016
72	A8683B	1a1	Planning	100	11/8/2016
72	A893	2b2	Planning	50	11/8/2016
65	0	1a1	Planning	75	11/15/2016
80	0	2b2	Planning	75	11/15/2016
65	A437B	2b2	Planning	100	11/15/2016
80	A7893	2b2	Planning	120	11/22/2016
65	B7820	1a1	Planning	100	11/22/2016

<tbody>
</tbody>

with an example output of:

Location	PurchaseOrderNumber	Part Number	Timing	Quantity	When	Short Quantity
80	0	1a1	Planning	50	11/1/2016
72	0	1a1	Planning	50	11/1/2016
80	0	2b2	Planning	100	11/1/2016
80	A437B	1a1	Planning	50	11/8/2016
72	A8683B	1a1	Planning	100	11/8/2016	50
72	A893	2b2	Planning	50	11/8/2016
65	0	1a1	Planning	75	11/15/2016	0
80	0	2b2	Planning	75	11/15/2016
65	A437B	2b2	Planning	100	11/15/2016	20
80	A7893	2b2	Planning	120	11/22/2016	0
65	B7820	1a1	Planning	100	11/22/2016	0

<tbody>
</tbody>

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),"")