Supplier selection problem

alt_text

In this tutorial we will show you how to solve a supplier selection problem with XLOPTIM, the fastest and most powerful optimization tool for Microsoft Excel.

1. Description of the purchase problem

The OptiCoffee company regularly purchases 8 products from 7 different suppliers. The prices of the products depend on the suppliers who impose some constraints for orders:

  • a minimum order quantity
  • the unavailability of some products

The goal of this problem is to determine how many products to order, from which suppliers and at what date in order to minimize the purchasing costs while respecting the constraints related to the problem.

We are going to solve the problem using the XLOPTIM® software

2. Dataset

To download the data for this example and the trial version of XLOPTIM, please click on the links below:

Download the data for the suppliers selection problem

Download the trial version

  • For each product we have:

    • The price depending on the supplier
    • The current stock
    • The safety stock
    • The storage costs

    alt_text

    alt_text

    • The demand associated with each product and the date of delivery
  • For each supplier we have:

    • The minimum order quantity
    • Transport delays
    • Fixed order cost
    • Available product quantities

alt_text

3. Solving a purchase problem with XLOPTIM in Excel

It is very difficult to make decisions based on a number of products. XLOPTIM allows us to know which products we are ordering and when to order them in order to minimize purchasing and storage costs.

To model this problem, simply open the "Purchasing" dialog box.

alt_text

alt_text

In the tabs Suppliers and Products, you just have to select the different parameters related to costs and constraints. You also have to indicate the number of products and the number of suppliers.

It is possible to take into account other variable costs such as costs that vary according to the weight or volume of a product.

4. Interpreting the results

After you click on Start, XLOPTIM will solve your problem and the results will be displayed in a new sheet. We can see the total cost of the orders, the number of orders and the number of suppliers required.

At the end of the solution, we have for each supplier, a summary table showing us :

  • the quantities of products to be ordered
  • the dates on which the order has to be placed

alt_text

Feel free to contact us if you have any questions regarding the use of our XLOPTIM® tool.