Forum Discussion
Finding the best combination of values using solver
Lost_inExcel I'm not entirely sure, without seeing what went wrong. If you could save and attach the file (or provide a screenshot), I could possibly offer something more concrete. What version of Excel are you using? The formulas I used will only work with Excel for MS365. This is what it looks like when I input $1 for all parts for Sup1:
Min Cost via Data Table
As long as you input any Row_ID with the lowest cost (1 thru 6) in cell F13, it should assign all 19 parts to Sup1.
To use Excel's Solver tool to find the best combination of values for purchasing parts at the lowest cost, follow these steps:
- Set Up Your Worksheet:
- Enter the supplier names, part numbers, and costs into your Excel worksheet as shown in your input data.
- Define Your Variables:
- Create a table to represent the quantities of each part purchased from each supplier. Each cell in the table will represent the quantity of a specific part purchased from a specific supplier. You can vary these quantities to minimize the total cost.
- Define the decision variables (quantities) in your Solver model. These will be the cells in your worksheet that represent the quantities of parts purchased.
- Define the Objective Function:
- Determine the objective function that you want to minimize. In this case, you want to minimize the total cost of purchasing the parts. The total cost can be calculated by summing the product of each part's quantity and its corresponding cost.
- Create a formula in a cell to calculate the total cost based on the quantities of parts purchased and their costs.
- Set Up Constraints:
- Define any constraints that need to be satisfied. In this case, you want to ensure that the total quantity of parts purchased from each supplier does not exceed the total available quantity of parts, and that each part quantity is non-negative.
- Create formulas or use Excel functions to set up these constraints in your worksheet.
- Run Solver:
- Open the Solver tool in Excel. You can find it under the "Data" or "Analysis" tab, depending on your version of Excel.
- Set up Solver to minimize the objective function subject to the defined constraints.
- Specify the decision variables, objective function, and constraints in the Solver Parameters dialog box.
- Run Solver to find the optimal solution.
- Review the Results:
- Once Solver has finished running, review the results to see the optimal quantities of parts to purchase from each supplier that minimize the total cost.
- Solver will adjust the quantities in the decision variable cells to achieve the optimal solution.
By following these steps and using Excel's Solver tool, you can find the best combination of values to minimize the cost of purchasing parts from multiple suppliers while satisfying any constraints you may have. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.