Formula for Minimum Purchase Qtys

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I was wondering if there's a way to create a formula
based on what I do manualy which is:

I get pricing for items that make up kits and then
they're resold, the problem I have is when a supplier
tells me I have to buy a minimum quantity. What I do is
say I have 10 kits each one needing 3 pieces, I put the
cost of 3 pieces in one column called "Cost" and the cost
of the other 7 in a column called "Min Buy" into the
first kit and then manualy not include any cost in kits 2
& 3 and then in kit 4 I add the cost of 2 pieces into
the "Cost" column (because I have 1 left over from the
first buy) and the balance in the "Min Buy" column. Is
there a way to somehow create a formula that takes the
minimum qty and distribute it over the kits but then put
the cost in when the quantity is exhausted?

I hope I explained this clearly, if not please let me
know and I try differently.

TIA
Joe
 
Joe,
Could you explain this a little more?

In your example, what is the quantity that is the minimum buy? I assume you need a total of 30 peices since you are making 10 kits with 3 pcs each.

Why is it "the other 7"?

Why do you manully not include any cost in kits 2 and 3? Don't they each include 3 pcs?

Why do you have one left over from the first buy? Is this from a previous purchase for a different set of kits? And if so, should it have zero cost since it was covered in the previous set of kits?

I used to purchase components for valve assemblies and had to deal with this same issue. I'm sure with a little more info on how you want to address the extra material we can come up with something.

Regards,
Mark Graesser
(e-mail address removed)

----- Joe wrote: -----

I was wondering if there's a way to create a formula
based on what I do manualy which is:

I get pricing for items that make up kits and then
they're resold, the problem I have is when a supplier
tells me I have to buy a minimum quantity. What I do is
say I have 10 kits each one needing 3 pieces, I put the
cost of 3 pieces in one column called "Cost" and the cost
of the other 7 in a column called "Min Buy" into the
first kit and then manualy not include any cost in kits 2
& 3 and then in kit 4 I add the cost of 2 pieces into
the "Cost" column (because I have 1 left over from the
first buy) and the balance in the "Min Buy" column. Is
there a way to somehow create a formula that takes the
minimum qty and distribute it over the kits but then put
the cost in when the quantity is exhausted?

I hope I explained this clearly, if not please let me
know and I try differently.

TIA
Joe
 
Correct the min buy is 10 pieces.
The other 7 are the excess from the initial purchase that
get placed into stock. Since the government pays for
these we charge them upfront for all 10 since we aren't
sure they will buy the balance, if they do we can't go
back and charge them again for the other 7 (reason kits 2
& 3 don't include price). At kit 4 we have 1 left over
from the first buy (which is at no cost to them) but we
need to purchase 10 again for the min buy and add the
cost of 2 additional pieces (balance of 8 goes into min
buy column).

Sometimes we make 1 kit sometimes more it usally varies.

The excess material I would like to calculate in and put
in the min buy column but only when we need to repurchase

My spreadsheet summarizes this way qty, unit cost, total
cost, min buy.

The qty is how many are required, unit cost is... and so
on

Initial buy 10
kit qty reqd unit cost total min buy cost
1 3 1.00 3.00 7.00
2 3 1.00 0.00 0.00
3 3 1.00 0.00 0.00
4 3 1.00 2.00 8.00 (1 left
over from intial buy (no charge) plus 2 for total and 8
in the min buys)
5 3 1.00 0.00 0.00
6 3 1.00 0.00 0.00
7 3 1.00 1.00 9.00 (2 left over
from intial buy (no charge) plus 1 for total and 9 in the
min buys)
8 3 1.00 0.00 0.00
9 3 1.00 0.00 0.00
10 3 1.00 0.00 0.00

Hope this helps
Joe


-----Original Message-----
Joe,
Could you explain this a little more?

In your example, what is the quantity that is the
Click to expand...
minimum buy? I assume you need a total of 30 peices
since you are making 10 kits with 3 pcs each.
Why is it "the other 7"?

Why do you manully not include any cost in kits 2 and
Click to expand...
3? Don't they each include 3 pcs?
Why do you have one left over from the first buy? Is
Click to expand...
this from a previous purchase for a different set of
kits? And if so, should it have zero cost since it was
covered in the previous set of kits?
I used to purchase components for valve assemblies and
Click to expand...
had to deal with this same issue. I'm sure with a little
more info on how you want to address the extra material
we can come up with something.
 
I'm out for a meeting but will be back in a couple of
hours, but did give more info I hope. If more information
is required.

Joe
-----Original Message-----
Joe,
Could you explain this a little more?

In your example, what is the quantity that is the
Click to expand...
minimum buy? I assume you need a total of 30 peices
since you are making 10 kits with 3 pcs each.
Why is it "the other 7"?

Why do you manully not include any cost in kits 2 and
Click to expand...
3? Don't they each include 3 pcs?
Why do you have one left over from the first buy? Is
Click to expand...
this from a previous purchase for a different set of
kits? And if so, should it have zero cost since it was
covered in the previous set of kits?
I used to purchase components for valve assemblies and
Click to expand...
had to deal with this same issue. I'm sure with a little
more info on how you want to address the extra material
we can come up with something.
 
Hi Joe
I put together a spread sheet which will do what you want

Basically you need to add a column to track the available leftovers, and a cell to input the minimum lot size. After you manually fill in the "total" for kit 1, the rest of the table will fill automatically

The following formulas are based on your example, with the column header in row 3 and the dat in cells A1 through F13, The minimum lot size is entered in G

Total column (excluding kit 1, so entered on kit 2
=IF(F4>B5,0,B5-F4
If "available" from previous line is more the "QTY Per" then zero, otherwise "QTY per" minus "available" from previous lin

Min Buy Column (entered on kit 1
=IF(D4=0,0,$G$4-D4
If "total" equals zero then zero, otherwise "lot size" minus "total

available column (entered on kit 1
=IF(E4=0,F3-B4,E4
If "min buy" equals zero then "available" from previous line minus "QTY per", otherwise "min buy

If you would like me to send you the spread sheet just e-mail me directly

Regards
Mark Graesse
(e-mail address removed)

----- Joe Gieder wrote: ----

I'm out for a meeting but will be back in a couple of
hours, but did give more info I hope. If more information
is required

Jo
-----Original Message----
Joe
Could you explain this a little more
Click to expand...
minimum buy? I assume you need a total of 30 peices
since you are making 10 kits with 3 pcs eachthis from a previous purchase for a different set of
kits? And if so, should it have zero cost since it was
covered in the previous set of kitshad to deal with this same issue. I'm sure with a little
more info on how you want to address the extra material
we can come up with something
 
Back
Top