Pricing decreases as quantity increases- how to do that
 

When I purchase raw material, the more I buy the less it costs per
pound. I would like to be able to enter historic data and then have
Excel give me a price that would be in line with that data. Meaning
if
I bought 100 lbs at $3.50/lb and 127 lbs at $3.02/lb what would the
cost be of 185 lbs using those numbers?

Pricing decreases as quantity increases- how to do that
 

Depends. In inventory management there is FIFO, LIFO and Average Cost.
Assuming your first purcahse was 100lb and the second 127lb

FIFO First In First Out
100 * 3.50 + 85 * 3.02

LIFO Last In First Out
127 * 3.02 + 58 * 3.50

Average Cost
((100 + 127) / ((100 * 3.50) + (127 * 3.02))) * 185

Check with your accountant as to which one they recommend. Regarless which
one you choose it gets a bit messy in a spreadsheet...
--
HTH...

Jim Thomlinson


"rudyeb" wrote:

When I purchase raw material, the more I buy the less it costs per
pound. I would like to be able to enter historic data and then have
Excel give me a price that would be in line with that data. Meaning
if
I bought 100 lbs at $3.50/lb and 127 lbs at $3.02/lb what would the
cost be of 185 lbs using those numbers?

.

Pricing decreases as quantity increases- how to do that
 

I think the OP was looking for the slope of the line formed by the two
purchases to project a third point.

Jim Thomlinson wrote:

Depends. In inventory management there is FIFO, LIFO and Average Cost.
Assuming your first purcahse was 100lb and the second 127lb

FIFO First In First Out
100 * 3.50 + 85 * 3.02

LIFO Last In First Out
127 * 3.02 + 58 * 3.50

Average Cost
((100 + 127) / ((100 * 3.50) + (127 * 3.02))) * 185

Check with your accountant as to which one they recommend. Regarless which
one you choose it gets a bit messy in a spreadsheet...