Useful Excel Solver Functions for Optimal Data Analysis

This article will provide a comprehensive exploration of the following topics –

Whether you’re a novice or a seasoned Excel user, this blog will provide you with the information you need to fully utilize Excel Solver.

Download the Excel Workbook below to follow along and understand all about Excel Solver –

download excel workbookSolver.xlsx

 

What is Excel Solver?

Excel Solver is an add-in that allows users to solve complicated optimization issues with relative ease. It applies mathematical techniques to identify the optimum solution based on the given limitations and objectives.

Users can utilize Solver to identify optimal solutions by maximizing or minimizing a specific objective for various scenarios subject to a set of constraints.

The different elements required to run a solver are –

• Decision Variables – These are the unknown quantities that are expected to be estimated as an output of the LPP solution.
• Objective Function – It is the objective for which we are making the model.
• Constraints – The restrictions or limitations on the total amount of a particular resource that is available to us.

For example – Solver can help you with product planning by determining the optimal quantity that can be produced in a manufacturing industry. This optimal level will be calculated keeping in mind the two constraints – minimizing cost and maximizing profit.

 

How to set up an Excel solver?

Before you can start using Solver in Excel, you need to install the Solver add-in. Follow the steps below to execute the same –

Step 1: Go to the File tab and click on Options.

Step 2: In the pop-up window, click on Add-ins.

Step 3: Select Excel Add-in and click on Go.

Step 4: Check the Solver Add-in box and click OK.

You can now find the Solver on the Data tab, in the Analyze group. Now we can freely use the Excel Solver for our problems!

Now let’s see the Solver in action!

 

Example of Excel Solver

In the manufacturing unit, there are 3 types of products – Product 1, Product 2, and Product 3. Our objective is to find the most optimum mix of products that should be made to maximize our profit. Now comes the different elements of the model.

• Decision Variables -These will be the quantities of the products that need to be calculated to arrive at a maximum profit.
• Objective Function – It is to maximize profit.
• Constraints – We have 3 constraints that we need to keep in mind –
  • The maximum number of labor hours is 1200 hours.
  • The total cost cannot exceed $100,000.
  •  The minimum requirement for each product is at least 50 units.

We can try to arrive at a solution by applying a hit-and-trial method, but that will not give us an optimum result. So here, we can use the Excel Solver to arrive at a conclusion.

This is what the Excel Worksheet looks like –

Follow the step-by-step tutorial to arrive at a solution using Excel Solver –

STEP 1: Calculate the total cost by multiplying the optimum units and cost per unit using the SUMPRODUCT formula.

=SUMPRODUCT(B4:D4,B7:D7)

STEP 2: Calculate the total labor hours by multiplying the optimum units and labor hours per unit using the SUMPRODUCT formula.

=SUMPRODUCT(B5:D5,B7:D7)

STEP 3: Calculate the profit by applying the same technique – we have multiplied each unit by its respective profit per unit.

Great! Now comes the Solver magic!

STEP 4: Let’s Go to Data > Solver.

STEP 5: Let’s select the objective. Here it is to maximize total profit, which is in cell G7.

STEP 6: Since we want to maximize it, we should select the Max option.

STEP 7: Select the cells that we want to change to arrive at our answer. These are our optimum units of products, ie, the array B7:D7.

STEP 8: To add constraints, we just need to click on the Add button.

STEP 9: To set the total cost limit to $100,000, select cell reference as F2, constraint as H2, and operator as less than equal to (<=).

STEP 10: To set the maximum labor hours to 1200, select cell reference as F3, constraint as H3, and operator as less than equal to (<=).

STEP 11: To set the minimum demand requirement, select cell reference as B7:D7, constraint as 50, and operator as greater than equal to (>=).

STEP 12: Select the solving method as Simplex LP.

STEP 12: Click on Solve button.

Now the solver will run iterations till it finds the optimal solution! 

To optimize the use of Solver:

• It is essential to structure your spreadsheet appropriately, with clear labeling of objective cells, decision variables, and constraints.
• Starting with simpler models before tackling complex problems helps build familiarity with Solver’s functionalities.
• Taking an iterative approach and refining constraints can lead to improved solutions.
• Understanding and interpreting Solver results, as well as performing sensitivity analysis, allows for a more comprehensive evaluation of the problem and potential alternative scenarios.

 

Conclusion

Excel Solver is a sophisticated tool that allows users to efficiently solve difficult optimization problems. You may unleash a world of possibilities within Excel by learning its operations, effectively configuring it, and exploring its advanced features.

Whether you work in finance, operations, logistics, or marketing, Solver can help you make better decisions and allocate resources. So, the next time you face an optimization problem, use Excel Solver to translate your data into meaningful insights and make informed decisions for success.