How to use Solver in Excel

What is Solver in Excel

Solver in Excel is part of the What if functions that are available. It's an add-in that you must turn on. Solver's basic purpose is to find a solution that satisfies all the constraints and return the optimal result.

In order for Solver to work properly, the worksheet must be set up in a specific way. Most importantly, you must have a solid understanding of the problem that you are trying to solve!

Solver in Excel Example

The example in the video below is for a business that assembles and sells furniture. This business currently sells two types of dining chairs. One made from maple and another from mahogany. While the maple chair is of lower quality, it is easier to manufacture and sell. The mahogany chair is of much better quality. But the price is much higher and it is more time intensive to produce.

From examining the worksheet, you can see that it takes three hours of rough carpentry to assemble the maple chair. While the mahogany chair takes four hours. After the chair has been assembled, it takes two hours of finish work to get the mahogany chair to showroom standards. It only takes one hour with the maple chair:

The problem that you need to solve is to figure out how many of each type of chair can be constructed to maximize weekly total revenue for the company. And what the maximum total revenue will be.

What complicates this problem is that the business employs one part-time finish carpenter who is only available for a total of 30 hours per week. The two rough carpenters employed by the company are full-time employees who are available for a combined total of 80 hours a week. In addition, the lumber yard that supplies the company can only provide enough mahogany for up to 10 chairs a week. All these factors are constraints

In the video below we will look at turning on Solver in Excel. Then we will work through the example above.

If you enjoyed this lesson don't forget to check out my range of Excel Courses which you can access here.

Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox

Found this article useful? Please share it with your friends and colleagues

Take A FREE course with us Today!

The Ultimate Excel Formulas Course

* includes XLOOKUP and will soon include Dynamic Arrays

Cross posted from my blog with SteemPress : https://theexcelclub.com/use-solver-excel/