I am putting together a program to help me estimate the cost of
various metal parts. One variable is the material cost. The parts are
stamped from a sheet. I know how many parts one sheet will yield
(D25). I know the cost of one sheet (B26). Here's my dilemma; I need
to estimate my material cost based on various quantities. This will
also cause me me to get the material price for additional sheets. The
more sheets I buy the less they cost.
Example; 1 Sheet costs $800 and yields 500 pcs. The quantities I'm
estimating are 100, 250, 500, 1000 and 2000 pcs. This means one sheet
will cover quantity pricing for 100, 250 and 500 pieces and the cost
of material for these quantities is $800 divided by the particular
quantity. I'll need 2 sheets for 1000 pcs and 4 sheets for 2000 pcs. S
sheets cost a total od $1500 and 4 sheets costs $2900. My quantities
a 100 = E27, 250 = F27, 500 = G27, 1000 = h27 and 2000 = I27.
I need a formula that will compute how many sheets a particular
quantity requires and based on that result will utilize the proper
sheet cost. I realize I will need more cells for the different sheets
costs. I will also want the formula to divide the applicable sheet
cost by the applicable quantity to get a cost per piece at that
quantity.Sheets can only be purchased as whole sheets so I cannot buy
3 1/2 sheets, I need instead to buy 4.
I hope all this make sense and i appreciate any help anyone can offer.
Many thanks..