Need help writing formula

[DKRbella0814]

I am trying to develop a model which illustrates when our company (manufacturer) should reorder material or parts based on predetermined reorder quantities.

If, for example the starting inventory count for our company and our supplier is as follows:

Us= 1,000
Supplier = 2,500

And our company's average weekly usage is 250 pcs/week, then our on-hand inventory would be modeled as such:

<table width="777" border="0" cellpadding="0" cellspacing="0"><col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="11"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 55pt;" width="73" height="17">Week:</td> <td style="width: 48pt;" width="64" align="right">0</td> <td style="width: 48pt;" width="64" align="right">1</td> <td style="width: 48pt;" width="64" align="right">2</td> <td style="width: 48pt;" width="64" align="right">3</td> <td style="width: 48pt;" width="64" align="right">4</td> <td style="width: 48pt;" width="64" align="right">5</td> <td style="width: 48pt;" width="64" align="right">6</td> <td style="width: 48pt;" width="64" align="right">7</td> <td style="width: 48pt;" width="64" align="right">8</td> <td style="width: 48pt;" width="64" align="right">9</td> <td style="width: 48pt;" width="64" align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td style="background: none repeat scroll 0% 0% lime;" align="right">1000</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">750</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">500</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">0</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-500</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-750</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1000</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1500</td> </tr> </tbody></table>
The colors above indicate that when Inventory meets the following criteria, it should fall within the above color-coded buffers:

1000>= Inventory >=750 --- GREEN
750>= Inventory >=250 --- YELLOW
250>= Inventory -- RED

Since the quantity 750 marks the start of the Yellow buffer, this is our predetermined reorder point.


If the variables to be modeled are in the following cells:

A2 = Supplier Starting Inventory
A3 = our company's starting inventory
A4 = our company's average weekly usage

E2 = Top of Green Qty (1000)
E3 = Top of Yellow Qty (750)
E4 = Top of Red Qty (250)

H2 = Lead Time (Weeks) - Amount of time in weeks it would take for our company to receive a new order of our predetermined reorder amount
H3 = Reorder Qty (predetermined)

QUESTION:

Is there a formula or macro I can write to do the following:

1) Based on the color-coded demand schedule - Recognize when a new order needs to be placed (when our inventory count is 750 (cellE3))

2) Update our company's on-hand inventory when a new order is placed based on the number of weeks that we expect the new parts to be delivered (cells (H2 and H3))

 

[DKRbella0814]

Can someone help me out with this please?

 

[turtle_solver]

When I get some time I will review your question.

Can you answer the following?

How will you input that the item has been ordered?

Is this going to be for a full 52 wk period?

Do you want to control the orders for each item individually? Or Will anything in yellow or below always be ordered?

 

[DKRbella0814]

To answer your questions, 1) Yes, this is for a full 52 week period. and 2) The reorder point is always going to be the Top of YELLOW (TOY) quantity. So, as soon as we hit 750 pcs in on-hand inventory at our shop, this triggers us to place a new order with our supplier for 250 pcs.

*While the 250 pcs on order is actually counted as part of our total inventory, it should not actually "show up" or be reflected in our total inventory until the shipment date (or end of the lead time for our supplier to make and/or ship the product or material to us).

In the example below, we would reorder 250 pcs during week 1 (when our inventory reaches 750), but since the supplier's lead time is 2 weeks (in this example), we actually wouldn't receive the 250 pcs until week 3. At that time, our on-hand inventory has been depleted to 250 pieces (since we use 250 pcs/ week), but when the order should be shipped to us, so we would actually want to see 500 in week 3.

Originally, I tried to write a formula beneath the buffer timeline which says

=if the on-hand inventory = TOY, "Reorder", "0"
=if(B8=$e$3, "Reorder", " ")

BUT, I could not figure out a way to write a formula or program to recognize that when the "Reorder" trigger shows, that the reorder qty (250) should be added to the on-hand inventory IN THE WEEK THAT WE EXPECT THE ORDER TO BE SHIPPED TO US. So, in effect, the formula written above is a more of a visual signal to us that a order needs to be placed, though the actual reorder qty should not be reflected in our total inventory count until it is actually delivered to us (based on our supplier's lead time).

My purpose in doing this is to build a standard model to illustrate when we would need to reorder throughout the year and how this would affect our buffers and on-hand inventory for ANY PART that we buy/manufacture.

So, I should be able to enter any numbers into the variable quantities below and have it update for both on-hand inventory and when we should place new orders.

<table width="649" border="0" cellpadding="0" cellspacing="0"><col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="9"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 55pt;" width="73" align="right" height="17">2500</td> <td colspan="2" style="width: 96pt;" width="128"> Supplier Starting Inv</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64" align="right">1000</td> <td style="width: 48pt;" width="64">TOG</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64" align="right">2</td> <td colspan="2" style="width: 96pt;" width="128">Lead Time (Weeks)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1000</td> <td colspan="2" style="">Bowden Starting Inv</td> <td>
</td> <td align="right">750</td> <td>TOY</td> <td>
</td> <td align="right">250</td> <td colspan="2" style="">Reorder Qty</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">250</td> <td colspan="2" style="">Avg Weekly Usage</td> <td>
</td> <td align="right">250</td> <td>TOR</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

I am not able to figure out a way to transfer the work I've already done into this text box, as it gets distorted, but if you can make sense of it, this is what I have.

<table width="777" border="0" cellpadding="0" cellspacing="0"><col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="11"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 55pt;" width="73" align="right" height="17">2500</td> <td colspan="2" style="width: 96pt;" width="128">Supplier Starting Inv</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64" align="right">1000</td> <td style="width: 48pt;" width="64">TOG</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64" align="right">2</td> <td colspan="2" style="width: 96pt;" width="128">Lead Time (Weeks)</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1000</td> <td colspan="2" style="">Bowden Starting Inv</td> <td>
</td> <td align="right">750</td> <td>TOY</td> <td>
</td> <td align="right">250</td> <td colspan="2" style="">Reorder Qty</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">250</td> <td colspan="2" style="">Avg Weekly Usage</td> <td>
</td> <td align="right">250</td> <td>TOR</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Week:</td> <td align="right">0</td> <td align="right">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">4</td> <td align="right">5</td> <td align="right">6</td> <td align="right">7</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td style="background: none repeat scroll 0% 0% lime;" align="right">1000</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">750</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">500</td> <td style="background: none repeat scroll 0% 0% yellow;" align="right">250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">0</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-500</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-750</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1000</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1250</td> <td style="background: none repeat scroll 0% 0% red;" align="right">-1500</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24"> </td> <td class="xl25">Reorder</td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">0</td> <td class="xl25">250</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> <td class="xl25">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td align="right">1000</td> <td align="right">1000</td> <td align="right">500</td> <td align="right">250</td> <td align="right">0</td> <td align="right">-250</td> <td align="right">-500</td> <td align="right">-750</td> <td align="right">-1000</td> <td align="right">-1250</td> <td align="right">-1500</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</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 style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td align="right">1000</td> <td align="right">1000</td> <td align="right">500</td> <td align="right">250</td> <td align="right">0</td> <td align="right">-250</td> <td align="right">-500</td> <td align="right">-750</td> <td align="right">-1000</td> <td align="right">-1250</td> <td align="right">-1500</td> </tr> </tbody></table>

 

[Tinus Bergakker]

I wasn't going to write something, but then I thought of using an HLOOKUP for your problem.

Something like
IF(Current Week Num = HLOOKUP(750, /*Select and quantities*/, 1) + 2, previous value - 250 + order value, previous value - 250)?

Its difficult to give you an exact formula because you paste values without clear cell references.

Hope it helps.