Cost Analysis problem

[LACA]

I am trying to develop a spreadsheet that calculates the production cost of a certain item, based on the # of orders for the item.

One of the widgets that is part of this item costs $2 per unit, but with a minimum order of 250,000 widgets.

So let's say I have an order for 200 items.

The variable cost for the widget would be 200 x $2 = $400, but then I have an additional cost of 50 X $2 = $100, since I cannot order just 200 widgets.

But then if my order changes to 300, my variable cost would be 300 x $2 = $600, but then my additional cost would be 200 x $2 = $400.

I cannot come up with a formula for this additional cost that fits any scenario. Can someone help?

Thanks

[Bryan Hessey]

I am trying to develop a spreadsheet that calculates the production cost of a certain item, based on the # of orders for the item.

One of the widgets that is part of this item costs $2 per unit, but with a minimum order of 250,000 widgets.

So let's say I have an order for 200 items.

The variable cost for the widget would be 200 x $2 = $400, but then I have an additional cost of 50 X $2 = $100, since I cannot order just 200 widgets.

But then if my order changes to 300, my variable cost would be 300 x $2 = $600, but then my additional cost would be 200 x $2 = $400.

I cannot come up with a formula for this additional cost that fits any scenario. Can someone help?

Thanks

Assuming that H1 has the minimum order quantity, and A1 the Order qt, then cost =

=IF(A1<H1,H1,A1)*2

should help
---

[LACA]

BTW, I meant the minimum order is 250 widgets, not 250,000.

Bryan, your formula works only if my order quantity is equal to or less than 250. If it goes over, say 300, then that formula would result in $600 instead of $1000. (Once my order reaches 300, I must then order 500 widgets [increment of 250] x $2 = $1000.)

[Bryan Hessey]

BTW, I meant the minimum order is 250 widgets, not 250,000.

Bryan, your formula works only if my order quantity is equal to or less than 250. If it goes over, say 300, then that formula would result in $600 instead of $1000. (Once my order reaches 300, I must then order 500 widgets [increment of 250] x $2 = $1000.)

I did not read that the order qty was multiple (pack size) of (say) 300, but amend it to

=CEILING(IF(A1<H1,H1,A1),H1)*2
correctly
=CEILING(IF(A1<H1,H1,A1),H1)*price

hth
---