Calculate lowest cost combination of packages

Question

We're trying to find a way to automate in Excel the selection of the lowest total cost combination of bulk-purchase packages to achieve a specific quantity of two variables.

Say we have a pricing schedule such as this:

Bundle Qty  /  Cost
======================
200         /  $46.00
400         /  $52.00
800         /  $68.00

Let's say that we are constrained to 2000 transactions and we need to achieve a minimum total bundle quantity of 850,000 (overage is ok). I need to figure out the cheapest way to allocate those transactions across these bundles.

I can manually play around with the numbers and figure out that buying these quantities results in what appears to be the lowest price:

400 x Bundle 200
1275 x Bundle 400
325 x Bundle 800

However, I need to automate this. So far my attempts have been frankenstein Excel formulas, and I have to believe there is an elegant solution here.

Answer 1

Let C be the total cost. $X_1, X_2, X_3$ be the transactions.

Now what you have is a minimization problem with

$Min\space C = 46X_1+52X_2+68X_3$

with constraints:

$200X_1+400X_2+800X_3\ge 850,000$

$X_1+X_2+X_3 \le 2000$

$X_i \ge $ for all i's

Put it into EXCEL you will get the answer using solver. I tried but it is skewed to $X_3$. If you add minimum of each trasactions, it may work to your liking.

Regards Satish

Answer 2

Notice that the bigger the bundle is, the more cost efficient it is.

To get $850,000$ quantity, first get $1060$ bundles of $800$.

That gives $848,000$. We can squeeze it to $849,600$ by getting $1062$ bundles of $800$.

Now, if we get $1$ bundle of $400$, we are done. Most cost efficient.

Therefore, the cost is $1062(68)+52=\boxed{\$72268.00} $

---

Don't know what this is for, but sounds really expensive.

---

Here is the algorithm.

Let's say you need X quantity.

First, get $\displaystyle \left\lfloor \frac{X}{800} \right\rfloor$ bundles of $800$.

Then you will have between $1-799$ quantity left. Use casework here.

Let $X-800\displaystyle \left\lfloor \frac{X}{800} \right\rfloor=A$.

If $0<A\le200$, buy $1$ more $200$ bundle.

If $200 \le A \le 400$, buy $1$ more $400$ bundle.

If $400 \le A \le 800$, it is no longer worth buying a combination of $200$ and $400$ bundles, so buy $1$ more $800$ bundle.