Yet another Inventory Calculation Question

[dLand]

It seems like there are a lot if inventory related questions here, so I hope I'm not repeating a post.

I think I need a recursive function (which I'm not too great with) but any guidance will help.

I have the following fields:

QTYONHAND ( must be >= 0 )

QTYCOMMITTED

QTYAVAILABLE ( qtyOnHand-qtyCommitted ) can be negative

QTYPENDINGpo4CUSTOMER ( After pulling a part, the qtyCommitted is reduced and this field increases )

MINIMUMPURCHASE ( multi-use - aka unitsPerCase )

MINIMUMSTOCK ( Like a Reorder Point + 1 )

ROUNDQUANTITIES ( switch indicating that the purchase quantity must be in increments of the minimumPurchase - i.e. if value of minimumPurchase is 10 and this is checked, purchases must be 10, 20, 30, etc. If not checked, then 10, 11, 12, etc. This is like 'broken cases allowed')

=====================================

I need to determine the QTYTOPURCHASE.

=====================================

Purchase Orders are cut weekly. The qtyPendingPO4Customer field is the value that is accumulating during the week and will be on the PO when it is cut.

The qtyCommitted and the qtyPendingPO4Customer must both be considered.

Here is probably a worst case example:

qtyOnHand = 15

qtyCommitted = 25 (could be greater than qtyOnHand)

qtyAvailable = -10 (calculation)

qtyPending4Customer = 12

SUBTOTAL required so far is 22 (10 + 12)

minimumStock = 13 (like Reorder Point + 1)

SUBTOTAL required so far is 35 (22 + 13)

minimumPurchase = 10 (aka Case Qty and vendor minimum)

result is qtyToPurchase = 40 if roundQuantities or

result is qtyToPurchase = 35 if not roundQuantities

I hope I was clear enough on this. Thank you for any help.

Edited December 6, 2007 by Guest

[comment]

Could you pinpoint your difficulty? It seems you're saying you know how to get the SUBTOTAL required, and the problem is to calculate the qtyToPurchase.

[dLand]

Thanks for the response Comment.

I can do this in my head, but can't figure out how to put it into a calculation when considering various scenarios. I've tried it multiple times. My attempts work sometimes, then are incorrect at other times. Frankly, I'm a bit of a hack at math.

I think my question lies in 3 places. Part of my challenge is that I'm not even sure of that.

1) Considering that the qtyAvailable can be positive or negative, how do I calculate the first subtotal? i.e. (-10 + 12) = 2 but the desired and required result is 22. If the qtyCommitted is > qtyOnHand then the calculation would be accurate. In my confusion, it doesn't seem like Abs() would do the trick.

2) I'm not sure how to 'round up' to the minimumPurchase when cases can't be broken (i.e. require purchase of 10, 20, 30, etc.) This is where I think a recursive function may be required but it could just be a ceiling() or other simple function.

3) Understanding that the minimumPurchase is in theory 1 case, then 1 case must be purchased but depending on the roundQuantities switch the purchase must be 1 whole cases or can be broken cases so long as it is more than 1 case in total.

1/2 of my question in #3 is addressed in #2 but while I'm addressing it here, rather than posting a second time, I want to confirm that both options in the If() (based on roundQuantities) is addressed.

In summary, the problem lies in the various values that may occur (positive vs. negative) and in rounding up to case quantites.

I hope that helps. Thanks again.

[comment]

These appear to be basic math questions, not really related to Filemaker. My problem is I don't understand what your labels actually mean in real life, so I will have to answer in general terms:

1.

The Abs() function should do the trick, e.g. Abs ( -10 ) + 12 = 22.

2.&3.

Given these three:

• required - (number) the number of units you'd order, if there were no restrictions;

• minimum - (number) the minimum number of units that can be ordered;

• multipleOf - (number) the number by which the ordered quantity must be divisible,

you can calculate the quantity to order as:

Let ( 

q = Case ( multipleOf ; multipleOf * Ceiling ( required / multipleOf ) ; required )

;

Max ( q ; minimum )

)

I am not convinced that if there is a 'multipleOf' restriction, it must always be a multiple of the minimum. But if you're sure of that, you could use:

Let ( 

q = Case ( multipleOf ; minimum * Ceiling ( required / minimum ) ; required )

;

Max ( q ; minimum )

)

where multipleOf means: true/false - the ordered quantity must be divisible by the minimum.

[dLand]

Thanks Comment.

I made some other changes but basically your second example did the trick.

I appreciate it.