Would love to eliminate so much manual entry on this sales projection & inventory sheet

[mighty mouse]

In the sales and inventory projection sheet below, I'm trying to avoid manual entry and ideally only have to adjust the input of column D.

Column L would monitor the inventory in column H, and look ahead 5 weeks (as this is how long an order takes to arrive). When the inventory 5 weeks ahead looks to be low, an "x" shows up in column L to note an order placed and shipped from the supplier. A few things happen when the "x" shows up in column L (see sheet below; orange cells in columns J/L/M/N provide example):

1) Five weeks after the "x" shows up in column L, a number of containers shows up in column J, and inventory is updated in column H (1568 units per container).
2) Two weeks after the "x" shows up in column L, column M would reflect payment to the supplier.
3) Three weeks after column M reflects payment to supplier, another payment shows up in column N.

I'm really wanting to only have to adjust column D, and let formulas populate the rest. Any help is greatly appreciated!

Excel 2016 (Mac) 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​
1​
2​		Case Qty​	Size​	​	COGs/Container		​	​		Logistics Costs
3​		1568​	40' Container		($13,563)​		​	​		-$7,710​
4​		​					​	​
5​
6​	​	Year #1 ​	​	​	​	​	​	​	​	​	​	​	​	​	​
7​		200 Accounts​	​	​	​	​	​	​	​	​	​	Capital Flows​	​	​	​
8​	​	Week Number​	# of Accts​	STRs Per Account​	Weekly Depletions​	Monthly Depletions​	Cumulative Depletions​	Inventory Position​	Distr Starting Inventory​	Container Arrival @ Distributor​	Avg Throughput Per week​	Container Ships from Supplier​	Container Payment to Supplier (2 Weeks)​	BOM Pays Logist & Taxes​	Distrib Pays BOM​
9​		1​	25​	2​	50​	200​	50​	1518​	200​	​	2.00​	​	​	-$7,710​	​
10​		2​	25​	2​	50​	​	100​	1468​	​	​	2.00​	​	​		​
11​		3​	25​	2​	50​	​	150​	1418​	​	​	2.00​	​	​		​
12​		4​	25​	2​	50​	​	200​	1368​	​	​	2.00​	​	​
13​		5​	50​	2​	100​	400​	300​	1268​	400​	​	1.20​	​	​	​	$0​
14​		6​	50​	2​	100​	​	400​	1168​	​	​	1.33​	​	​	​	​
15​		7​	50​	2​	100​	​	500​	1068​	​	​	1.43​	x​	​	​	​
16​		8​	50​	2​	100​	​	600​	968​	​	​	1.50​	​	​	​	​
17​		9​	75​	2​	150​	600​	750​	818​	600​	​	1.11​		-$13,563​	​	$0​
18​		10​	75​	2​	150​	​	900​	668​	​	​	1.20​	​			​
19​		11​	75​	2​	150​	​	1050​	518​	​	​	1.27​				​
20​		12​	75​	2​	150​	​	1200​	1936​	​	1​	1.33​	​	​	-$7,709.86​	​
21​		13​	100​	2​	200​	800​	1400​	1736​	800​	​	1.08​	​	​	​	$0​
22​		14​	100​	2​	200​	​	1600​	1536​	​		1.14​	x​	​	​	​
23​		15​	100​	2​	200​	​	1800​	1336​	​	​	1.20​	​	​	​	​
24​		16​	100​	2​	200​	​	2000​	1136​	​	​	1.25​		-$13,563​	​	​
25​		17​	125​	2​	250​	1000​	2250​	886​	1000​	​	1.06​	​	​	​	$0​
26​		18​	125​	2​	250​	​	2500​	636​	​	​	1.11​	​		​	​
27​		19​	125​	2​	250​	​	2750​	1954​	​	1​	1.16​	x​	​	-$7,709.86​	​
28​		20​	125​	2​	250​	​	3000​	1704​	​	​	1.20​	​	​	​	​
29​		21​	150​	2​	300​	1200​	3300​	1404​	1200​		1.05​	​	-$13,563​	​	$0​
30​		22​	150​	2​	300​	​	3600​	1104​	​	​	1.09​		​	​	​
31​		23​	150​	2​	300​	​	3900​	804​	​	​	1.13​	x​	​	​	​
32​		24​	150​	2​	300​	​	4200​	2072​	​	1​	1.17​	​	​	-$7,709.86​	​
33​		25​	175​	2​	350​	1400​	4550​	1722​	1400​	​	1.04​	​	-$13,563​	​	$0​
34​		26​	175​	2​	350​	​	4900​	1372​	​	​	1.08​	​	​	​	​
35​		27​	175​	2​	350​	​	5250​	1022​	​	​	1.11​	x​	​	​	​
36​		28​	175​	2​	350​	​	5600​	2240​	​	1​	1.14​	​	​	-$7,709.86​	​
37​		29​	200​	2.25​	450​	1800​	6050​	1790​	1800​	​	1.04​	​	-$13,563​	​	$0​
38​		30​	200​	2.25​	450​	​	6500​	1340​	​	​	1.08​	​	​	​	​
39​		31​	200​	2.25​	450​	​	6950​	890​	​	​	1.12​	x​	​	​	​
40​		32​	200​	2.25​	450​	​	7400​	2008​	​	1​	1.16​	​	​	-$7,709.86​	​
41​		33​	200​	2.25​	450​	1800​	7850​	1558​	1800​	​	1.19​	​	-$13,563​	​	$0​
42​		34​	200​	2.25​	450​	​	8300​	1108​	​	​	1.22​	x​		​	​
43​		35​	200​	2.25​	450​	​	8750​	658​	​	​	1.25​	​	​	​	​
44​		36​	200​	2.25​	450​	​	9200​	1776​	​	1​	1.28​	​	-$13,563​	-$7,709.86​	​
45​		37​	200​	2.5​	500​	2000​	9700​	1276​	2000​	​	1.31​	x​	​	​	$0​
46​		38​	200​	2.5​	500​	​	10200​	776​	​	​	1.34​		​	​	​
47​		39​	200​	2.5​	500​	​	10700​	1844​	​	1​	1.37​	​	-$13,563​	-$7,709.86​	​
48​		40​	200​	2.5​	500​	​	11200​	1344​	​	​	1.40​	x​	​	​	​
49​		41​	200​	2.5​	500​	2000​	11700​	844​	2000​	​	1.43​	​	​	​	$0​
50​		42​	200​	2.5​	500​	​	12200​	1912​	​	1​	1.45​	​	-$27,126​	-$7,709.86​	​
51​		43​	200​	2.5​	500​	​	12700​	1412​	​	​	1.48​	​	​	​	​
52​		44​	200​	2.5​	500​	​	13200​	912​	​	​	1.50​	​	​	​	​
53​		45​	200​	2.75​	550​	2200​	13750​	3498​	2200​	2​	1.53​	​	​	-$15,419.72​	$0​
54​		46​	200​	2.75​	550​	​	14300​	2948​	​	​	1.55​	x​	​	​	​
55​		47​	200​	2.75​	550​	​	14850​	2398​	​	​	1.58​	​	​	​	​
56​		48​	200​	2.75​	550​	​	15400​	1848​	​	​	1.60​	​	-$27,126​	​	​
57​		49​	200​	2.75​	550​	2200​	15950​	1298​	2200​	​	1.63​	​	​	​	$0​
58​		50​	200​	2.75​	550​	​	16500​	748​	​	​	1.65​	x​	​	​	​
59​		51​	200​	2.75​	550​	​	17050​	3334​	​	2​	1.67​	​	​	-$15,419.72​	​
60​		52​	200​	2.75​	550​	​	17600​	2784​	​	​	1.69​	​	-$27,126​	​	​
61​		​	End of Year #1 : 200 Accounts​	​	17,600 ​	​	​	2784​	​	12​	​	​	- 189,885 ​	- 100,228 ​	- ​
62​			​	​				Ending Inventory​	​	Total Container Shipments​					- 290,113 ​

Sheet: Dado Vol A

 

[mighty mouse]

I've simplified down what I'm looking for, along with my explanation.

Shipments of inventory take 5 weeks to arrive from the time an order is placed with the supplier. I need to be able to find a formula or other solution to have column F look ahead 5 weeks and see when more inventory will be needed, and mark column F with an order placed. Accordingly, 5 weeks after order placement, inventory would update with the new shipment, as shown below.

While it has been all manual entry so far, I'd love to just update the projected sales in column C and have everything else adjust. Sales increase in subsequent weeks (this sheet actually goes 52 weeks with increasing sales projections), so inventory orders would increase as that happens.

I need to avoid inventory getting below 0, while avoiding having too much on hand as well.

	A​	B​	C​	D​	E​	F​
1​
2​			Starting Inventory:	1000​
3​
4​		Week​	Quantity Sold​	Inventory Position​	New Inventory Arrival​	Place Order​
5​		1​	50​	950​	​	​
6​		2​	50​	900​	​	​
7​		3​	100​	800​	​	​
8​		4​	100​	700​	​	x​
9​		5​	150​	550​	​	​
10​		6​	150​	400​	​	​
11​		7​	200​	200​	​	x​
12​		8​	200​	1000​	1000​	​
13​		9​	250​	750​	​
14​		10​	250​	500​	​	​
15​		11​	300​	200​	​
16​		12​	300​	900​	1000​	​

Sheet: Sheet1

 

[jtakw]

Hi,

Based on your description and sample in Post #2 , wouldn't the first "Place Order" (red x in your sample) be in Week #3 rather than Week #4 , since it takes 5 weeks to arrive?
My solution below accounts for 5 weeks to receive new inventory:

Book1
	A	B	C	D	E	F
1
2			Starting Inventory:	1000	Reorder inventory:	1000
3
4		Week	Quantity Sold	Inventory Position	New Inventory Arrival	Place Order
5		1	50	950
6		2	50	900
7		3	100	800		X
8		4	100	700
9		5	150	550
10		6	150	400
11		7	200	200		X
12		8	200	1000	1000
13		9	250	750
14		10	250	500
15		11	300	200
16		12	300	900	1000
Sheet15

Cell Formulas
Range	Formula
D5	=D$2-SUM(C$5:C5)+SUM(E$5:E5)
E5	=IF(SUM(D$2,E$4:E4)-SUM(C$5:C5)<=0,F$2,"")
F5	=IF(SUM(C5:C10)>=SUM(D5,E5:E9),"X","")

I've added "Reorder inventory:" in E2, and reorder amount in F2, if reorder amount will Always be the same as "Starting inventory", then change F$2 to D$2 in the formulas and remove the added fields in E2 & F2.

All formulas copied down.

 

[mighty mouse]

Ah yes you're right on the misplaced order. This is perfect; thank you!

 

[jtakw]

You're welcome.