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
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 |