Cell reference

[leebauman]

My model contains daily inventory data (begin inventory less sales = end inventory)

I can manipulate the number of days to restock

I want a formula in B "Start Inventory" which references G "Restock Order Qty", but the restock quantities only add to inventory the number of restock days after the restock order is placed.

In this example on day 2 end inventory is (80) but is projected to fall below zero before the restock period (4 days). So the model flags that a restock order needs to be placed on day 2. I need the (50) restock items to add to inventory (4) days later (day 6).

So, day 6 starts with day 5's end inventory (10) and the (50) restock items from day 2 are received, leading to total day (6) starting inventory being (60).

Book1
	A	B	C	D	E	F	G
1	Restock Days	4
2	Restock Qty	50
3
4	Day	Start Inventory	Sales	End Inventory	Restock period rolling sales	Projected Inventory	Restock Order Qty
5	1	100	5	95	85	10
6	2	95	15	80	85	-5	50
7	3	80	20	60	75	-15
8	4	60	35	25	50	-25
9	5	25	15	10	40	-30
10	6	60	15	45	35	10
11	7	45	10	35	25	10
12	8	35	10	25	15	10
13	9	25	5	20	10	10
14	10	20	10	10	0	10
Sheet1

Cell Formulas
Range	Formula
D5:D14,F5:F14	D5	=B5-C5
E5:E14	E5	=SUM(TAKE(C6:C14,$B$1))
G5:G14	G5	=IF(AND(F5<=0,F4>0),$B$2,"")
B6:B9,B11:B14	B6	=D5
B10	B10	=G6+D9

Appreciate any help. Thanks!

 

[Skybluekid]

Try the below:

Book1
	A	B	C	D	E	F	G	H
1	Restock Days	4
2	Restock Qty	50
3
4	Day	Start Inventory	Sales	End Inventory	Restock period rolling sales	Projected Inventory	Restock Order Qty
5	1	100	5	95	85	10
6	2	95	15	80	85	-5	50
7	3	80	20	60	75	-15
8	4	60	35	25	50	-25
9	5	25	15	10	40	-30
10	6	60	15	45	35	10
11	7	45	10	35	25	10
12	8	35	10	25	15	10
13	9	25	5	20	10	10
14	10	20	10	10	0	10
15
Sheet1

Cell Formulas
Range	Formula
D5:D14,F5:F14	D5	=B5-C5
E5:E14	E5	=SUM(TAKE(C6:C14,$B$1))
G5:G14	G5	=IF(AND(F5<=0,F4>0),$B$2,"")
B10:B14	B10	=IF(OFFSET(B10,-4,5)<>"",OFFSET(B10,-4,5)+D9,D9)

 

[leebauman]

Works perfectly. Much appreciated!

 

[Peter_SSs]

Works perfectly. Much appreciated!

If so, then wouldn't this simple non-volatile option for column B also work?

23 11 19.xlsm
	A	B	C	D	E	F	G
1	Restock Days	4
2	Restock Qty	50
3
4	Day	Start Inventory	Sales	End Inventory	Restock period rolling sales	Projected Inventory	Restock Order Qty
5	1	100	5	95	85	10
6	2	95	15	80	85	-5	50
7	3	80	20	60	75	-15
8	4	60	35	25	50	-25
9	5	25	15	10	40	-30
10	6	60	15	45	35	10
11	7	45	10	35	25	10
12	8	35	10	25	15	10
13	9	25	5	20	10	10
14	10	20	10	10	0	10
Inventory

Cell Formulas
Range	Formula
D5:D14,F5:F14	D5	=B5-C5
E5:E14	E5	=SUM(TAKE(C6:C14,$B$1))
G5:G14	G5	=IF(AND(F5<=0,F4>0),$B$2,"")
B6:B14	B6	=SUM(D5,G2)

Or if you want to relate it to the value in B1 ..

23 11 19.xlsm
	A	B	C	D	E	F	G
1	Restock Days	4
2	Restock Qty	50
3
4	Day	Start Inventory	Sales	End Inventory	Restock period rolling sales	Projected Inventory	Restock Order Qty
5	1	100	5	95	85	10
6	2	95	15	80	85	-5	50
7	3	80	20	60	75	-15
8	4	60	35	25	50	-25
9	5	25	15	10	40	-30
10	6	60	15	45	35	10
11	7	45	10	35	25	10
12	8	35	10	25	15	10
13	9	25	5	20	10	10
14	10	20	10	10	0	10
Inventory (2)

Cell Formulas
Range	Formula
D5:D14,F5:F14	D5	=B5-C5
E5:E14	E5	=SUM(TAKE(C6:C14,$B$1))
G5:G14	G5	=IF(AND(F5<=0,F4>0),$B$2,"")
B6:B14	B6	=SUM(D5,IF(ROW()>B$1,INDEX(G:G,ROW()-B$1),0))