Supplier selection problem
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
To download the data for this example and the trial version of XLOPTIM, please click on the links below:
For each product we have:
- The price depending on the supplier
- The current stock
- The safety stock
- The storage costs
- 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
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.
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
Feel free to contact us if you have any questions regarding the use of our XLOPTIM® tool.