How to Use Solver in Excel – Shiksha Online

The article covers the Solver in Excel. Learn how to use Solver in Excel through examples and facilitate decision-making.

A solver is an optimization tool of Excel that performs calculations under certain given conditions or Constraints. It changes the variable cells by using Sum constraints. The solver allows us to facilitate decision-making. For example, purchasing at the supermarket, we want to spend the minimum, considering things cannot be left aside. How to make this decision? A solver facilitates problem-solving from the data that we introduce in Excel.

Components

Objective function: Measure of performance of the decision. It measures how well we are doing given what we want to achieve.

Parameters: Numbers that do not depend on the decisions we make

Variables: Numbers that correspond to our findings or will be affected by them.

Restrictions: Possible limitations that my decision presents.

The solver tool is available with Excel but is not upfront, and hence we need to activate it via the below steps.

Go to File and then to Options.

You will see a dialogue box. Click on Add-ins and then on Solver Add-in. 

Choose the Excel Add-ins and click go.

Now Click on Solver Add-in and select OK.

Windows will configure the Solver Add-in to your spreadsheet.

You can locate Solver in –

Data ⇒ Analyze ⇒ Solver

How to Use Solver in Excel – Example 1

We want to know how much a student will secure in his final term to get his honors. He has already achieved 75% on his assignment and is yet to write midterm and finals. But he needs to get 90% to earn honors in this subject. Let’s calculate –

To achieve this, click the Solver button on the Data tab.

Keep in mind that the cell displaying Total marks should be a formula. Here –

Select the Total

Set objective – Change the value. We will put 90 in Value of and then move on By Changing Variable Cells. 

Click on the Midterm and Final values, 0, and again click on the total marks.

Add some constraints 

The highest possible mark is 100%. Hence we will move to Add. Go to Cell reference and click on marks for the Midterm. You will see that it shows many options in the dropdown.

In this case, we will choose <=, and in the Constraint section, we will add the maximum value, which is 100. Click Add to add the constraint.

Again click on the click on marks that the students need to secure for the Final term, choose <= and in the Constraint section, add the maximum value, which is 100, and click OK.

Now click on Solve

Excel throws recommendations on the marks he needs to secure his Honors.

How to Use Solver in Excel – Example 2

Another example is of estimating how a pizza shop can make a benefit. The example is the following. I have a pizza shop that offers two types of traditional pizza, Pepperoni ($30) and Vegetarian ($35), as well as the special Supreme pizza ($45). We don’t know what the revenue potential of the establishment is, and we don’t know what emphasis should be given to each type of pizza to maximize sales.

We must consider the following conditions. Given our production capacity, we can only produce 150 pizzas per day. Another condition is that we cannot exceed 90 traditional pizzas (Pepperoni and Vegetarian). Also, we estimate to sell 25 vegetarian pizzas per day. Another condition is that we can only buy 60 Supreme pizzas per day.

With this information, I will prepare the following Excel sheet:

Data for Excel Solver

Note that all the business rules of the establishment are represented in the data. For each type of pizza, I have placed the total number of pizzas to be sold (zero for now), the subtotal of each one, and the total sales formed by the sum of the subtotals. Also, I have placed the previously mentioned conditions under the heading Restrictions.

Something significant is to establish the equivalences for the restrictions. For example, one constraint is that total pizzas can’t exceed 150. Still, Excel doesn’t necessarily know what “Total Pizzas” means, so I’ve designated a cell to specify that total pizzas are the sum of cells B2+B6+B10. The same happens to explain what Traditional Pizzas mean.

The data is now ready to use Solver, so you need to go to the Data tab and click on the Solver command, which will display the Solver Parameters dialog.

Solver Parameters

In our example, we want to maximize the total sales, so in the Set target text box, the cell $E$1 is specified, and of course, I selected the Max option. The other important parameter is the variable cells, which in our example are the pizzas to be sold for each of the different types.

Finally, observe how the establishment’s sale conditions are reflected in the restrictions table. Pay special attention to how the equivalences of cells $E$10 and $E$11 have been used.

Everything is ready to continue. You just have to click on the Solve button and Excel will start calculating different values ​​for the variable cells until finding the maximum value for the total sales. The Solver Results dialog box will be displayed at the end of the calculation.

Solver Results

Just click OK to see the results in the Excel sheet.

Excel Solver Solution

Excel has done the calculations. With the restrictions in place, we will have a maximum total sales value of $5,525. Now you could easily change the values ​​of the constraints and perform the calculation again with Solver to observe the behavior in sales.

---