How do I create a script to calculate expected inventory levels based on a reorder point and order quantity?

[#1]

Hello,

Attached is a jmp data set with consumption data for a part.  I have a beginners understanding of column formulas, but not enough to achieve the end result I want with this data set.  I'd like to be able to input a starting inventory level, a reorder point, a lead time and an order quantity into a formula or script which will output how many pieces will remain in inventory using those inputs.  In the example below I am starting with 3637 pieces in inventory.  My reorder point is 1510 pieces.  My order quantity (the amount I order when I hit the reorder point) is 600 pieces.  In row 11 my Total inventory level is below 1510 after consuming 572 so I place an order for 600 pieces.  The order takes 2 weeks to arrive so my Total continues to drop until orders start to arrive on row 13 (each row is a week).  I've been trying to use functions like lag to create a similar output in JMP but I'm at a loss.

Week	Inventory On Hand	Consumption	Total	Place order?	Order Arrives
1	3637	234	3403	No	N
2	3403	256	3147	No	N
3	3147	272	2875	No	N
4	2875	140	2735	No	N
5	2735	204	2531	No	N
6	2531	178	2353	No	N
7	2353	112	2241	No	N
8	2241	108	2133	No	N
9	2133	380	1753	No	N
10	1753	240	1513	No	N
11	1513	572	941	Yes	N
12	941	370	571	Yes	N
13	571	586	585	Yes	Y
14	585	436	749	Yes	Y
15	749	449	900	Yes	Y
16	900	432	1068	Yes	Y
17	1068	252	1416	Yes	Y
18	1416	182	1834	No	Y
19	1834	327	2107	No	Y
20	2107	176	1931	No	N
21	1931	367	1564	No	N
22	1564	498	1066	Yes	N
23	1066	225	1441	Yes	Y
24	1441	496	1545	No	Y
25	1545	1203	942	Yes	Y
26	942	1011	531	Yes	Y
27	531	525	606	Yes	Y
28	606	933	273	Yes	Y
29	273	541	332	Yes	Y
30	332	670	262	Yes	Y
31	262	146	716	Yes	Y
32	716	124	1192	Yes	Y
33	1192	107	1685	No	Y
34	1685	187	2098	No	Y

[#2 · Accepted solution]

Here is a script that creates the new data table you want;

Names Default To Here( 1 );
dt = Data Table( "Part A Inventory Data" );

dtRpt = New Table( "Output Report",
	Add Rows( N Rows( dt ) ),
	New Column( "Week", set each value( Row() ) ),
	New Column( "Inventory on Hand" ),
	New Column( "Consumption", set each value( dt:Number of pieces consumed[Row()] ) ),
	New Column( "Total" ),
	New Column( "Place Order", character ),
	New Column( "Order Arrives", character )
);
Current Data Table( dtRpt );

StartingIOH = 3637;
For( i = 1, i <= N Rows( dtRpt ), i++,
	If( i == 1,
		:"Inventory On Hand"n[i] = StartingIOH,
		:"Inventory On Hand"n[i] = :Total[i - 1]
	);
	If( :"Order Arrives"n[i] == "",
		:"Order Arrives"n[i] = "N"
	);
	:Total[i] = :"Inventory On Hand"n[i] - :Consumption[i];
	If( :"Order Arrives"n[i] == "Y",
		:Total[i] += 600
	);
	If( :Total[i] < 1510,
		:"Place Order"n[i] = "Yes";
		:"Order Arrives"n[i + 2] = "Y";
	,
		:"Place Order"n[i] = "No"
	);
);

There appears to be a discrepancy around line 22 in your report table.  You specify to order new parts, however, you report them arrived in one week, not in 2 weeks.  My script sets the arrival week based upon your specification of a 2 week order lag.

[#3]

Thank you sir this is great!