Need help writing formula

[DKRbella0814]

Ok, I am re-posting my question to avoid all of the confusion in the previous threads. I have changed the formatting of my spreadsheet from rows to columns of data, which should hopefully make things easier.

I am looking to write a formula that will adjust on-hand inventory levels based on available on-hand inventory and orders of material/parts that have been delivered by suppliers.

If our company hits a set trigger (Top of Yellow), which indicates the level of inventory that we would need to place a new order, then the order qty should not be added into on-hand inventory until it is delivered and in our possession.

I am looking to build a formula to calculate the levels of on-hand inventory so that I can model our inventory buffers for multiple parts.



b1= Our company's starting inventory (1000)
b2= Our company's average weekly usage (100)
b3= Top of Green (100)
b4= Top of Yellow (Qty at which we should reorder parts) (500)
b5= Top of Red (100)
b6= Lead time in weeks to receive new parts/material from our supplier (2)
b7= Reorder Qty (500)

a10-a19 = week# (0,1,2,3,4,5,6,7,8,9,10)

b10-b19 = on-hand inventory levels

So, what should be displayed is as follows:

col A B
Week Inv
Row#
10 0 1000
11 1 900
12 2 800
13 3 700
14 4 600
15 5 500 (Reorder 500 parts; delivered in 2 weeks)
16 6 400
17 7 800
18 8 700
19 9 600
20 10 500 (Reorder 500 parts; delivered in 2 weeks)


If I were to do a simple formula using our weekly avg demand, the inventory counts would look like the following:

col A B =b10-$b$2 (100-avg weekly usage (100))
Week Inv
Row#
10 0 1000
11 1 900
12 2 800
13 3 700
14 4 600
15 5 500 (Reorder 500 parts; delivered in 2 weeks)
16 6 400
17 7 300
18 8 200
19 9 100
20 10 0

*I want the formula to subtract the average weekly usage as normal, unless the on-hand inventory is = the TOY top of yellow reorder qty (500), then it should add the reorder quantity to the on-hand inventory level and return the updated value in the week that the parts/material is delivered.

I tried a number of the following formulas, but they are not working. Can someone help me?


IF((a10-$a$1)=$a$4,VLOOKUP(a11+a6,$a$10:$b$18,2,FALSE)+$a$7,a10-$a$2
<table width="64" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> </tbody></table>

 

[shg]

Maybe like this:

       -----A----- --B-- ---C--- ---------------------------------D----------------------------------
   1   Start Inven  1000                                                                             
   2         Usage   100                                                                             
   3           TOG  1000                                                                             
   4           TOY   500                                                                             
   5           TOR   100                                                                             
   6     Lead Time     2                                                                             
   7       Reorder   500                                                                             
   8                                                                                                 
   9      Week     Inven Reorder                                                                     
  10             0  1000         B10 and down: =$B$1 - $B$2 * A10 + SUM(C$9:INDEX(C:C, ROW() - $B$6))
  11             1   900       0 C11 and down: =(B10>$B$4) * (B11<=$B$4) * $B$7                      
  12             2   800       0                                                                     
  13             3   700       0                                                                     
  14             4   600       0                                                                     
  15             5   500     500                                                                     
  16             6   400       0                                                                     
  17             7   800       0                                                                     
  18             8   700       0                                                                     
  19             9   600       0                                                                     
  20            10   500     500                                                                     
  21            11   400       0                                                                     
  22            12   800       0

 

[DKRbella0814]

Thank you. This seems to work for the quantities that I have provided.

Can you please explain what the formulas mean? This appears to be an array formula if I'm not mistaken. Thanks for your help.

 

[shg]

No array formulas.

Inventory is

(initial inventory) - (weekly usage) * (weeks elapsed) + (orders received up to lead time weeks ago)

Order when

(last week's inventory) > (min qty on hand) and (this week's inventory) <= (min qty on hand)