Many people using Microsoft Excel need to be made aware of utilizing solver, which is a great tool for analyzing data. You can generate multiple scenarios according to your choice with this amazing feature. You might face difficulties with solver optimization in Excel. We will discuss examples using solver to optimize. In this article, I am sharing with you solver examples optimization in Excel.
What Is Solver?
Solver is an Excel add-in program that will help you find the optimal solutions. It is decorated in such a way that you can simulate and optimize multiple solutions for your data models. Mostly used for linear optimization and non-smooth operations. You just need to choose your target cell and variable points to crack every possible solution.
Details Steps with Examples for Optimization with Solver in Excel
In the following, I have described detailed steps with examples for optimizing data with solver in Excel.
Suppose we have a dataset of a company’s Available Products in stock, Unit Cost, Selling Price, Labor Cost, and Advertising Cost.
We also have the Total Sales Volume from the stock. Now we will calculate Total Revenue, Total Expenses, and Profit from the given data. Then we will use the solver feature to optimize in Excel. Stay tuned!
- First, let’s determine the Total Revenue by using the simple formula in cell (C11)-
=C6*C10
- Second, press ENTER to get the revenue result.
- Next, choose a cell (C12) and apply the below formula to get the total expenses-
=C4*(C5+C7+C8)
- Simply, hit the ENTER key to get the output.
- This time we will calculate the profit output with the formula below in cell (C13)-
=C11-C12
- Gently, click ENTER to get the profit value in your hands.
As you can see, the company has made a profit of $7500. But if the company wants to make a $10,000 profit then it can make a simulation over the dataset using the solver feature. Without wasting time let’s drop into it right now.
- While in the worksheet, click the “Solver” option from the “Data” tab.
- Now, choose cell (C13) in the “Set Objective” portion.
- Hence, press “Value of” and put “10000” as the targeted value.
- After that, choose cell (C4) and cell (C10) in the “By Changing Variable Cells” section.
- Thereafter, we will provide constraints for the selected cells. To do so hit “Add” from the “Solver Parameters” window.
- In the “Cell Reference” box choose cell (C4) and provide your desired constraint. Here I have put “1000” as the company’s stock capacity is 1000 units.
- Hence, hit “Add” to provide more constraints.
- This time we will provide constraints for cell (C10). To do that select cell (C10) in the “Cell Reference” part and type “900” in the “Constraint” box. If you want you can put your own constraints according to your choice.
- Again, press “Add” to provide more conditions.
- As units of products can only be integers thus we will add an “Integer” constraint for both cell (C4) and cell (C10).
- To finish, click OK.
- Finally, you will get to see the constraints in the “Solver Parameters” window.
- Just click “Solve”.
- A new window will pop up named “Solver Results”.
- From the appeared window click “Keep Solver Solution” and press OK.
- In summary, you will get the optimized result in your hand using the solver tool. The result stands to if the company wants to make a $10,000 profit then it has to maintain a stock of 688 units of products and sell at least 616 units of product.
Read More: Excel Optimization with Constraints
Things to Remember
- If you are not getting the “Solver” feature in the Data tab then go to “ExcelOptions” and click “Add-ins”. Therefore, checkmark the “Solver Add-in” feature to get the “Solver” option in the top ribbon.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover almost all the methods to solver examples of optimization in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.
Related Articles
- How to Perform Multi-Objective Optimization with Excel Solver
- Perform Route Optimization in Excel
- Make Price Optimization Models in Excel
- How to Solve Network Optimization Model in Excel
- Mean Variance Optimization in Excel
- Schedule Optimization in Excel
- How to Do Portfolio Optimization Using Excel Solver
