Optimization of purchases problem

Refurbished Bikes OptiBike


In this tutorial we show you how to solve an optimization of purchases problem, using XLOPTIM, the fastest and most reliable solver for Microsoft Excel.

1. Data

To download the data for this XLOPTIM use case, please click the link below:

Download data for Optimization of purchases problem

2. The Problem

The OptiBike company refurbishes bicycles, that is, it buys second-hand bicycles (functional or not), it tests them, repairs non-functional bicycles and then resells them. Some repairs fail, the bike is then valued as spare parts. But if the repair goes well, the bike joins the reconditioned functional bikes to find a second life with a new owner.

The company operates its activities on 2 types of bikes: touring and cross bikes. The company is growing strongly and has planned a schedule of orders over the next 12 months with its distributors, given in the following table:

table1

Resale prices here will be assumed constant over the period and calculated by the Pricing team. Note, the resold functional products will be better valued than the others:

table2

In the supply market, there are suppliers who offer both types of bikes. Of course, already functional products are more expensive to buy and the cost of verifications will be lower. In addition, there is no associated repair cost on these products unlike broken products.

Conversely, products purchased broken are much more affordable, but their inspection and repair will cost more than products purchased functional with uncertainty about the repair which can result in failure.

Repairs on touring bikes do a little better than on cross bikes, we get repairability rates of 75% for the former and 70% for the cross.

When the repair fails, the products must be resold as non-repairable parts in spare parts at very low prices. Assume that for these products repair and shipping costs are not to be considered. However, we pay the full purchase price and a verification cost:

table3

These costs are summarized in the following table:

table4

To meet demand, it will therefore be necessary to seek to optimize purchases of broken and / or already functional products in order to meet demand.

Of course, the company does not have an unlimited cash reserve. The Finance Department has set a monthly limit on purchases, given in the following table:

table5

The company's goal will be to achieve the best possible margin on resale.

We seek to provide a linear modeling of this purchasing optimization problem, as well as a decision support tool in XLOPTIM.

3. Modeling

We propose a modeling according to the following 5 steps:

Step #1: What are the dimensions / clues of the problem?

  • t: 1 .. 12: the months of the year
  • k: 1 .. 2: types of bikes
  • j: 1..2: the 2 states of purchase of the bikes (functional or broken)
  • i: 1..2: the 2 states of resale of the bikes (functional or not repairable)

Step #2: What are the data of the problem?

  • Monthly need for functional bicycles for each month t and each type of bicycle k (89, 51, 87 ...):

$$\small{B_{tk}}$$

  • Maximum monthly purchase budget for each month t ($31,200, $39,000, $36,400, ...):

$$M_t$$

  • Repairability rate of bicycles purchased broken for each type of bicycles k:

$$RR_k$$

  • Purchase price for each type of bicycle k and each purchase status j:

$$PA_{kj}$$

  • The repair, verification and shipping costs respectively for each type of bicycle k:

$$RC_k$$ $$CC_k$$ $$SC_k$$

  • Resale price for each type of bicycle k and each resale status i:

$$PV_{ki}$$

Step #3: What are the decision variables?

  • Number of bikes purchased for each month t, each type k and in each state of purchase j:

$$A_{tkj}$$

  • Number of bicycles reconditioned each month t, of each type of bicycle k and in each state of resale i (redundant):

$$P_{tki}$$

  • Total purchase costs for each month t and each type of bicycle k (redundant):

$$TC_{tk}$$

Step #4: What are the constraints?

  • Non-negativity of decision variables:

$$A_{tkj} \geqslant0,\;P_{tki} \geqslant0,\;TC_{tk} \geqslant0$$

  • Writing of the redundant variable on refurbished bikes number:

First, we calculate the number of functional bicycles that will leave the workshops, namely those that were already functional and that will be resold as is, then those that were broken and that could be reconditioned. Then, those that will come out in unrepairable condition and that we will resell in spare parts.

$$P_{tk1}=A_{tk1} + RR_k.A_{tk2}$$ $$P_{tk2}=( 1-RR_k ).A_{tk2}$$

  • Writing of the redundant variable on the total costs:

The total costs are the costs of reconditioning functional bikes, followed by those of bikes that are broken but could be repaired. Finally, for broken bicycles that have been sold as spare parts, only their purchase price and the cost of verification are recorded.

$$TC_{tk}=(CA_k + CC_k + SC_t).A_{tk1} + (CA_k + CC_k + RC_t + SC_t). A_{tk2} . RR_k + (CA_k + CC_k). P_{tk2}$$

Step #5: What is the objective of the problem?

The objective here is to maximize the total margin, that is to say the difference between the turnover generated by sales of functional bikes, that generated by spare parts of non-repairable bikes minus all costs. reconditioning.

$$Max\sum_{t=1}^{12}\sum_{k=1}^{2}\sum_{i=1}^{2}{(PV_{tki} . P_{tki}) - TC_{tk}}$$

4. TRANSLATION INTO XLOPTIM

xloptim1

Definition of the 4 main decision variables (with their limits). The redundant variables are not modeled in XLOPTIM but directly in the spreadsheet using a formula (see Excel):

xloptim2 xloptim3 xloptim4 xloptim5

Addition of the constraint on monthly maximal purchasing budget:

xloptim6

Then the 2 constraints that must be satisfied for both types of bikes:

xloptim7 xloptim8

Attachment of the objective:

xloptim9

Calculation of the optimum:

xloptim10

which gives a value of the following optimal objective function:

xloptim11

5. Conclusion

After making a purchasing plan for the coming months, the company is expected to achieve a total margin of € 47,948 based on forecast demand and total refurbishing costs. The displayed result is optimal and obtained after 3 seconds. This problem can be combined with stochastic optimization in order to carry out a study on several possible scenarios, in particular on uncertain demand.

This case study gives you an example of a buying problem. They are frequent in factories or in supply chain problems.

You can change the size of the problem and the assumptions to vary the problem and therefore the solutions. Do not hesitate to contact us if you have any questions following the use of our XLOPTIM® solver.

To download the 14-day free trial version of the software, go to: Download the trial version