DKRbella0814
Board Regular
- Joined
- Aug 10, 2008
- Messages
- 155
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>
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>