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

M

mighty mouse

Board Regular
Joined
Mar 17, 2005
Messages
140
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

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td]
Case Qty​
[/td][td]
Size​
[/td][td]
​
[/td][td]COGs/Container[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][td]Logistics Costs[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td]
1568​
[/td][td]40' Container[/td][td][/td][td]
($13,563)​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][td]
-$7,710​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td]
​
[/td][td][/td][td][/td][td][/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
​
[/td][td=bgcolor:#000000]
Year #1 ​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#000000]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td]
200 Accounts​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
Capital Flows​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
​
[/td][td]
Week Number​
[/td][td]
# of Accts​
[/td][td]
STRs Per Account​
[/td][td]
Weekly Depletions​
[/td][td]
Monthly Depletions​
[/td][td]
Cumulative Depletions​
[/td][td]
Inventory Position​
[/td][td=bgcolor:#000000]
Distr Starting Inventory​
[/td][td]
Container Arrival @ Distributor​
[/td][td]
Avg Throughput Per week​
[/td][td]
Container Ships from Supplier​
[/td][td]
Container Payment to Supplier (2 Weeks)​
[/td][td]
BOM Pays Logist & Taxes​
[/td][td]
Distrib Pays BOM​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td]
1​
[/td][td=bgcolor:#FFFF00]
25​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#FFFFFF]
200​
[/td][td]
50​
[/td][td=bgcolor:#F2F2F2]
1518​
[/td][td]
200​
[/td][td]
​
[/td][td]
2.00​
[/td][td]
​
[/td][td]
​
[/td][td]
-$7,710​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td]
2​
[/td][td=bgcolor:#FFFF00]
25​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
100​
[/td][td=bgcolor:#F2F2F2]
1468​
[/td][td]
​
[/td][td]
​
[/td][td]
2.00​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td]
3​
[/td][td=bgcolor:#FFFF00]
25​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
150​
[/td][td=bgcolor:#F2F2F2]
1418​
[/td][td]
​
[/td][td]
​
[/td][td]
2.00​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td]
4​
[/td][td=bgcolor:#FFFF00]
25​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
200​
[/td][td=bgcolor:#F2F2F2]
1368​
[/td][td]
​
[/td][td]
​
[/td][td]
2.00​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td]
5​
[/td][td=bgcolor:#FFCC66]
50​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#FFFFFF]
400​
[/td][td]
300​
[/td][td=bgcolor:#F2F2F2]
1268​
[/td][td]
400​
[/td][td]
​
[/td][td]
1.20​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td]
6​
[/td][td=bgcolor:#FFCC66]
50​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
400​
[/td][td=bgcolor:#F2F2F2]
1168​
[/td][td]
​
[/td][td]
​
[/td][td]
1.33​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td]
7​
[/td][td=bgcolor:#FFCC66]
50​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
500​
[/td][td=bgcolor:#F2F2F2]
1068​
[/td][td]
​
[/td][td]
​
[/td][td]
1.43​
[/td][td=bgcolor:#FFC000]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td]
8​
[/td][td=bgcolor:#FFCC66]
50​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
600​
[/td][td=bgcolor:#F2F2F2]
968​
[/td][td]
​
[/td][td]
​
[/td][td]
1.50​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td][/td][td]
9​
[/td][td=bgcolor:#FF8000]
75​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#FFFFFF]
600​
[/td][td]
750​
[/td][td=bgcolor:#F2F2F2]
818​
[/td][td]
600​
[/td][td]
​
[/td][td]
1.11​
[/td][td][/td][td=bgcolor:#FFC000]
-$13,563​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td][/td][td]
10​
[/td][td=bgcolor:#FF8000]
75​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
900​
[/td][td=bgcolor:#F2F2F2]
668​
[/td][td]
​
[/td][td]
​
[/td][td]
1.20​
[/td][td]
​
[/td][td][/td][td][/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td][/td][td]
11​
[/td][td=bgcolor:#FF8000]
75​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
1050​
[/td][td=bgcolor:#F2F2F2]
518​
[/td][td]
​
[/td][td]
​
[/td][td]
1.27​
[/td][td][/td][td][/td][td][/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td][/td][td]
12​
[/td][td=bgcolor:#FF8000]
75​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
1200​
[/td][td=bgcolor:#F2F2F2]
1936​
[/td][td]
​
[/td][td=bgcolor:#FFC000]
1​
[/td][td]
1.33​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#FFC000]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td][/td][td]
13​
[/td][td=bgcolor:#FF0000]
100​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#FFFFFF]
800​
[/td][td]
1400​
[/td][td=bgcolor:#F2F2F2]
1736​
[/td][td]
800​
[/td][td]
​
[/td][td]
1.08​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td][/td][td]
14​
[/td][td=bgcolor:#FF0000]
100​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
1600​
[/td][td=bgcolor:#F2F2F2]
1536​
[/td][td]
​
[/td][td][/td][td]
1.14​
[/td][td=bgcolor:#EEECE1]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td][/td][td]
15​
[/td][td=bgcolor:#FF0000]
100​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
1800​
[/td][td=bgcolor:#F2F2F2]
1336​
[/td][td]
​
[/td][td]
​
[/td][td]
1.20​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td][/td][td]
16​
[/td][td=bgcolor:#FF0000]
100​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
2000​
[/td][td=bgcolor:#F2F2F2]
1136​
[/td][td]
​
[/td][td]
​
[/td][td]
1.25​
[/td][td][/td][td=bgcolor:#EEECE1]
-$13,563​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td][/td][td]
17​
[/td][td=bgcolor:#FF0000]
125​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#FFFFFF]
1000​
[/td][td]
2250​
[/td][td=bgcolor:#F2F2F2]
886​
[/td][td]
1000​
[/td][td]
​
[/td][td]
1.06​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td][/td][td]
18​
[/td][td=bgcolor:#FF0000]
125​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
2500​
[/td][td=bgcolor:#F2F2F2]
636​
[/td][td]
​
[/td][td]
​
[/td][td]
1.11​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td][/td][td]
19​
[/td][td=bgcolor:#FF0000]
125​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
2750​
[/td][td=bgcolor:#F2F2F2]
1954​
[/td][td]
​
[/td][td=bgcolor:#EEECE1]
1​
[/td][td]
1.16​
[/td][td=bgcolor:#D9D9D9]
x​
[/td][td]
​
[/td][td=bgcolor:#EEECE1]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td][/td][td]
20​
[/td][td=bgcolor:#FF0000]
125​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
3000​
[/td][td=bgcolor:#F2F2F2]
1704​
[/td][td]
​
[/td][td]
​
[/td][td]
1.20​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td][/td][td]
21​
[/td][td=bgcolor:#FF0000]
150​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#FFFFFF]
1200​
[/td][td]
3300​
[/td][td=bgcolor:#F2F2F2]
1404​
[/td][td]
1200​
[/td][td][/td][td]
1.05​
[/td][td]
​
[/td][td=bgcolor:#D9D9D9]
-$13,563​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td][/td][td]
22​
[/td][td=bgcolor:#FF0000]
150​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
3600​
[/td][td=bgcolor:#F2F2F2]
1104​
[/td][td]
​
[/td][td]
​
[/td][td]
1.09​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td][/td][td]
23​
[/td][td=bgcolor:#FF0000]
150​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
3900​
[/td][td=bgcolor:#F2F2F2]
804​
[/td][td]
​
[/td][td]
​
[/td][td]
1.13​
[/td][td=bgcolor:#92D050]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td][/td][td]
24​
[/td][td=bgcolor:#FF0000]
150​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
4200​
[/td][td=bgcolor:#F2F2F2]
2072​
[/td][td]
​
[/td][td=bgcolor:#D9D9D9]
1​
[/td][td]
1.17​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#D9D9D9]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td][/td][td]
25​
[/td][td=bgcolor:#FF0000]
175​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
350​
[/td][td=bgcolor:#FFFFFF]
1400​
[/td][td]
4550​
[/td][td=bgcolor:#F2F2F2]
1722​
[/td][td]
1400​
[/td][td]
​
[/td][td]
1.04​
[/td][td]
​
[/td][td=bgcolor:#92D050]
-$13,563​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td][/td][td]
26​
[/td][td=bgcolor:#FF0000]
175​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
350​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
4900​
[/td][td=bgcolor:#F2F2F2]
1372​
[/td][td]
​
[/td][td]
​
[/td][td]
1.08​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
35​
[/td][td][/td][td]
27​
[/td][td=bgcolor:#FF0000]
175​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
350​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
5250​
[/td][td=bgcolor:#F2F2F2]
1022​
[/td][td]
​
[/td][td]
​
[/td][td]
1.11​
[/td][td=bgcolor:#C5D9F1]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
36​
[/td][td][/td][td]
28​
[/td][td=bgcolor:#FF0000]
175​
[/td][td=bgcolor:#FFFD78]
2​
[/td][td=bgcolor:#F2F2F2]
350​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
5600​
[/td][td=bgcolor:#F2F2F2]
2240​
[/td][td]
​
[/td][td=bgcolor:#92D050]
1​
[/td][td]
1.14​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#92D050]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
37​
[/td][td][/td][td]
29​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
1800​
[/td][td]
6050​
[/td][td=bgcolor:#F2F2F2]
1790​
[/td][td]
1800​
[/td][td]
​
[/td][td]
1.04​
[/td][td]
​
[/td][td=bgcolor:#B8CCE4]
-$13,563​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
38​
[/td][td][/td][td]
30​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
6500​
[/td][td=bgcolor:#F2F2F2]
1340​
[/td][td]
​
[/td][td]
​
[/td][td]
1.08​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
39​
[/td][td][/td][td]
31​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
6950​
[/td][td=bgcolor:#F2F2F2]
890​
[/td][td]
​
[/td][td]
​
[/td][td]
1.12​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
40​
[/td][td][/td][td]
32​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
7400​
[/td][td=bgcolor:#F2F2F2]
2008​
[/td][td]
​
[/td][td=bgcolor:#C5D9F1]
1​
[/td][td]
1.16​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#DCE6F1]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
41​
[/td][td][/td][td]
33​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
1800​
[/td][td]
7850​
[/td][td=bgcolor:#F2F2F2]
1558​
[/td][td]
1800​
[/td][td]
​
[/td][td]
1.19​
[/td][td]
​
[/td][td]
-$13,563​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
42​
[/td][td][/td][td]
34​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
8300​
[/td][td=bgcolor:#F2F2F2]
1108​
[/td][td]
​
[/td][td]
​
[/td][td]
1.22​
[/td][td]
x​
[/td][td][/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
43​
[/td][td][/td][td]
35​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
8750​
[/td][td=bgcolor:#F2F2F2]
658​
[/td][td]
​
[/td][td]
​
[/td][td]
1.25​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
44​
[/td][td][/td][td]
36​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.25​
[/td][td=bgcolor:#F2F2F2]
450​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
9200​
[/td][td=bgcolor:#F2F2F2]
1776​
[/td][td]
​
[/td][td]
1​
[/td][td]
1.28​
[/td][td]
​
[/td][td]
-$13,563​
[/td][td]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
45​
[/td][td][/td][td]
37​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
2000​
[/td][td]
9700​
[/td][td=bgcolor:#F2F2F2]
1276​
[/td][td]
2000​
[/td][td]
​
[/td][td]
1.31​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
46​
[/td][td][/td][td]
38​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
10200​
[/td][td=bgcolor:#F2F2F2]
776​
[/td][td]
​
[/td][td]
​
[/td][td]
1.34​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
47​
[/td][td][/td][td]
39​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
10700​
[/td][td=bgcolor:#F2F2F2]
1844​
[/td][td]
​
[/td][td]
1​
[/td][td]
1.37​
[/td][td]
​
[/td][td]
-$13,563​
[/td][td]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
48​
[/td][td][/td][td]
40​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
11200​
[/td][td=bgcolor:#F2F2F2]
1344​
[/td][td]
​
[/td][td]
​
[/td][td]
1.40​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
49​
[/td][td][/td][td]
41​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
2000​
[/td][td]
11700​
[/td][td=bgcolor:#F2F2F2]
844​
[/td][td]
2000​
[/td][td]
​
[/td][td]
1.43​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
50​
[/td][td][/td][td]
42​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
12200​
[/td][td=bgcolor:#F2F2F2]
1912​
[/td][td]
​
[/td][td]
1​
[/td][td]
1.45​
[/td][td]
​
[/td][td]
-$27,126​
[/td][td]
-$7,709.86​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
51​
[/td][td][/td][td]
43​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
12700​
[/td][td=bgcolor:#F2F2F2]
1412​
[/td][td]
​
[/td][td]
​
[/td][td]
1.48​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
52​
[/td][td][/td][td]
44​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.5​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
13200​
[/td][td=bgcolor:#F2F2F2]
912​
[/td][td]
​
[/td][td]
​
[/td][td]
1.50​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
53​
[/td][td][/td][td]
45​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
2200​
[/td][td]
13750​
[/td][td=bgcolor:#F2F2F2]
3498​
[/td][td]
2200​
[/td][td]
2​
[/td][td]
1.53​
[/td][td]
​
[/td][td]
​
[/td][td]
-$15,419.72​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
54​
[/td][td][/td][td]
46​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
14300​
[/td][td=bgcolor:#F2F2F2]
2948​
[/td][td]
​
[/td][td]
​
[/td][td]
1.55​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
55​
[/td][td][/td][td]
47​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
14850​
[/td][td=bgcolor:#F2F2F2]
2398​
[/td][td]
​
[/td][td]
​
[/td][td]
1.58​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
56​
[/td][td][/td][td]
48​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
15400​
[/td][td=bgcolor:#F2F2F2]
1848​
[/td][td]
​
[/td][td]
​
[/td][td]
1.60​
[/td][td]
​
[/td][td]
-$27,126​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
57​
[/td][td][/td][td]
49​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
2200​
[/td][td]
15950​
[/td][td=bgcolor:#F2F2F2]
1298​
[/td][td]
2200​
[/td][td]
​
[/td][td]
1.63​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td=bgcolor:#F2F2F2]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
58​
[/td][td][/td][td]
50​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
16500​
[/td][td=bgcolor:#F2F2F2]
748​
[/td][td]
​
[/td][td]
​
[/td][td]
1.65​
[/td][td=bgcolor:#FFFF00]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
59​
[/td][td][/td][td]
51​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
17050​
[/td][td=bgcolor:#F2F2F2]
3334​
[/td][td]
​
[/td][td]
2​
[/td][td]
1.67​
[/td][td]
​
[/td][td]
​
[/td][td]
-$15,419.72​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
60​
[/td][td][/td][td]
52​
[/td][td=bgcolor:#FF0000]
200​
[/td][td=bgcolor:#FFFD78]
2.75​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td]
17600​
[/td][td=bgcolor:#F2F2F2]
2784​
[/td][td]
​
[/td][td]
​
[/td][td]
1.69​
[/td][td]
​
[/td][td=bgcolor:#FFFD78]
-$27,126​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
61​
[/td][td][/td][td]
​
[/td][td]
End of Year #1 : 200 Accounts​
[/td][td]
​
[/td][td=bgcolor:#000000]
17,600 ​
[/td][td]
​
[/td][td]
​
[/td][td]
2784​
[/td][td]
​
[/td][td]
12​
[/td][td]
​
[/td][td]
​
[/td][td]
- 189,885 ​
[/td][td]
- 100,228 ​
[/td][td]
- ​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
62​
[/td][td][/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][td][/td][td][/td][td]
Ending Inventory​
[/td][td]
​
[/td][td]
Total Container Shipments​
[/td][td][/td][td][/td][td][/td][td][/td][td]
- 290,113 ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Dado Vol A[/td][/tr][/table]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td][/td][td]Starting Inventory:[/td][td]
1000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td]
Week​
[/td][td]
Quantity Sold​
[/td][td]
Inventory Position​
[/td][td]
New Inventory Arrival​
[/td][td]
Place Order​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td]
1​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#F2F2F2]
950​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td]
2​
[/td][td=bgcolor:#F2F2F2]
50​
[/td][td=bgcolor:#F2F2F2]
900​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td]
3​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#F2F2F2]
800​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td]
4​
[/td][td=bgcolor:#F2F2F2]
100​
[/td][td=bgcolor:#F2F2F2]
700​
[/td][td]
​
[/td][td]
x​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td]
5​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#F2F2F2]
550​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td]
6​
[/td][td=bgcolor:#F2F2F2]
150​
[/td][td=bgcolor:#F2F2F2]
400​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td]
7​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td]
​
[/td][td]
x​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td]
8​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td=bgcolor:#F2F2F2]
1000​
[/td][td]
1000​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td]
9​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#F2F2F2]
750​
[/td][td]
​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td]
10​
[/td][td=bgcolor:#F2F2F2]
250​
[/td][td=bgcolor:#F2F2F2]
500​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td]
11​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#F2F2F2]
200​
[/td][td]
​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td]
12​
[/td][td=bgcolor:#F2F2F2]
300​
[/td][td=bgcolor:#F2F2F2]
900​
[/td][td]
1000​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
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
ABCDEF
1
2Starting Inventory:1000Reorder inventory:1000
3
4WeekQuantity SoldInventory PositionNew Inventory ArrivalPlace Order
5150950  
6250900
73100800X
84100700
95150550
106150400
117200200X
12820010001000
139250750
1410250500
1511300200
16123009001000
Sheet15
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Extracting data from multiple sheets
2
Replies
12
Views
1K
DanteAmor
DanteAmor
M
  • Question Question
Speeding Up and Optimizing My Code
Replies
2
Views
1K
mwinning8
M
D
  • Question Question
A sizable, hopefully well explained, project
Replies
1
Views
1K
Smitty
Smitty

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top