Excel Solver is a tool, also referred to as Data Analysis add-in, used to find the optimal solution in the decision-making process mostly for business and engineering related problems. For example, we can find the optimal advertising campaign with regard to budget to maximize profitability. We will learn how to use Solver to find the optimal solution by adding Solver to Excel.
Figure 1. Solver Examples Optimization
How to Add Solver to Excel
Excel Solver add-in is available in all the Excel versions but it is not included by default. Therefore we have to add it manually in our Excel version by going through the following steps.
- In Excel 2007, go to the Microsoft Office button and then click on Excel Options. But in Excel 2010 and later versions, go to File and then click Options.
- On the left sidebar of the Excel Options, select Add-in > Manage box and select Excel Add-in and press Go button.
Figure 2. Add-in Options
- From the available Add-ins dialog, select Solver Add-in and click OK button. After getting installed the Solver Add-in is available in the Analysis section of Data tab.
Figure 3. Adding Solver to Excel
How to Use Solver in Excel
We need to set up the data in the worksheet before using the Solver function to find the optimal solution. Suppose we need to run an advertising campaign for a defined budget of $45000 for a defined time frame of 10 weeks. We have planned to run 100 adds per week and we need to calculate the minimum cost per add using Microsoft.Solver.
Figure 4. Data Formulation
As we have set up the data as per our problem description, now we need to run the Solver by clicking Data tab > Solver in Analysis section. The Solver parameters window will open having the following sections;
We first need to set the object by choosing the objective cell C6 which calculates the payment terms having the formula =C3/(C4*C5) in terms of the number of weeks to run an advertisement for. Then we need to choose the optimal objective of maximum, minimum or a set value to achieve. We’ll use 10 weeks in our example.
Figure 5. Setting Objective Parameters
In this section, we need to select the variable cells in our problem model having data to change to meet our set objective We have cells C4 and C5 contain the variable data of adds per week and cost per add. If these cells are non-adjacent then manually enter the cells references separated by commas.
Figure 6. Selecting the Variable Cells
Setting Up Constraints
Solver constraints are limitations or conditions applicable to the problem’s solution. In the Subject to Constraints section of the parameters window, click on Add button to add the constraints.
Figure 7. Solver Constraints Parameters
In the Add Constraints dialog, enter the constraints and press Add button. When we are done with adding all the constraints then we press the OK button. Like, we have added the constraints for the total budget amount and total adds per week.
Figure 8. Adding Constraints
After setting up the parameters, we need to click on the Solve button.
Figure 9. Run Solver
When Solver solves the problem a Solver Results window appears where we need to select Keep Solver Solution option and press OK button.
Figure 10. Solver Example Problems