Cost Optimisation Calculation

Hi All,

I need to create an optimisation calculation in excel.

Facts:
A 30 cubic square metre container costs $11,000 to ship.
A 66 cubic square metre container costs $21,000 to ship.

I need to ship x amount of cubic square metres. To complicate things more, I must use the full container at all times. ie even if I only need half the 30 csm container I will still have to pay the full price.

What is the cheapest way to do it - how many (must be a whole number) of each container should I use to ship? (make x = 150 csm for the example but i need to be able to change that variable depening on what quantity i am shipping)

Thanks

Since two 30 csm containers cost more than one 66 csm container, the math is fairly simple. Take the total and divide by 66, if the remainder is over 30 then add one more 66 csm container, and if the remainder is 30 or less, add one 30 csm container. Yes?

Try this UDF.

Enter in cell:- =Vol(A1) where "A1" is the cell with the "x" (i.e 150)

Please Login or Register  to view this content.