Applying Solver for Optimization Problems in Microsoft Excel

Course: Advanced Excel

Topic: Applying Solver for Optimization Problems

Course Link: Advanced Excel Training – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction to Solver in Excel

Microsoft Excel Solver is an advanced What-If Analysis tool used to find the best possible solution to a problem by changing selected input values while following specified limitations. It helps users maximize profit, minimize cost, reduce resource usage, optimize production quantities, and make data-driven business decisions.

Unlike Goal Seek, which changes one input cell to achieve a specific result, Solver can adjust multiple decision-variable cells simultaneously and apply several constraints. This makes Solver especially useful for complex business and mathematical optimization problems.

For example, a company manufacturing two products may want to determine how many units of each product to produce to maximize profit without exceeding available labor hours, raw materials, or production capacity.

2. Learning Objectives

• Understand the purpose and working principles of Excel Solver.
• Identify objective cells, variable cells, and constraints.
• Enable the Solver Add-in in Microsoft Excel.
• Configure and solve optimization problems step by step.
• Choose an appropriate solving method for different models.
• Apply Solver to profit maximization, cost minimization, budgeting, and resource allocation.
• Interpret Solver results and troubleshoot common errors.

3. What Is Optimization?

Optimization is the process of finding the best possible outcome from a set of available alternatives while satisfying specified conditions or limitations.

An optimization problem generally has three important elements:

• Objective: The result to maximize, minimize, or set to a specific value.
• Decision variables: The input values that can be changed to improve the result.
• Constraints: The rules and limitations that the solution must satisfy.

Examples of optimization include maximizing business profit, minimizing transportation expenses, selecting the best investment combination, and planning production with limited resources.

4. Understanding Excel Solver Components

5. Enabling the Solver Add-in

Solver may not appear automatically in Excel. In desktop Excel, activate it using the following steps:

1. Open Microsoft Excel and select the File menu.
2. Choose Options.
3. Select Add-ins from the left panel.
4. At the bottom, locate the Manage dropdown and select Excel Add-ins.
5. Click Go.
6. Check the box labeled Solver Add-in.
7. Click OK and wait for Excel to activate the add-in.
8. Open the Data tab and look for Solver, usually in the Analysis group.

Important: The built-in Solver Add-in is not supported in Excel for the web in the same way as desktop Excel. Open the workbook in a compatible desktop version to use the traditional Solver Add-in.

6. Types of Optimization Problems

6.1 Maximization

Maximization means finding the highest possible value for an objective, such as profit, revenue, production output, or investment return.

Example: Determine the number of products to manufacture to achieve the highest total profit while remaining within the available production capacity.

6.2 Minimization

Minimization means finding the lowest possible value for an objective, such as operating cost, transportation expenses, delivery time, or resource consumption.

Example: Determine the least-cost combination of suppliers that satisfies a company's purchasing requirements.

6.3 Target-Value Optimization

In this type of problem, Solver changes decision variables until the objective cell reaches a specified value.

Example: Find the required combination of sales quantities to achieve a target profit of ₹100,000.

7. Practical Example: Maximizing Profit for Two Products

Assume a company manufactures Product A and Product B. Each product generates a different profit and consumes different amounts of labor and raw materials. The company wants to determine the production quantities that maximize profit without exceeding its available resources.

7.1 Business Information

The company has a maximum of 100 labor hours and 80 units of raw material available.

7.2 Set Up the Worksheet

Create the following Excel table:

For a more clearly organized worksheet, use the following layout instead, where the changing cells are B2:C2:

Place the resource calculations in separate cells:

• Total Profit (B7): =SUMPRODUCT(B2:C2,B3:C3)
• Labor Used (B8): =SUMPRODUCT(B2:C2,B4:C4)
• Material Used (B9): =SUMPRODUCT(B2:C2,B5:C5)

Enter the available resource limits in separate cells, for example, C8 = 100 for available labor hours and C9 = 80 for available material units.

7.3 Define the Mathematical Model

Let:

• A = quantity of Product A.
• B = quantity of Product B.

The objective is to maximize total profit:

Maximize: Profit = 40A + 30B

Subject to these constraints:

• Labor constraint: 2A + B ≤ 100
• Material constraint: A + 2B ≤ 80
• Non-negativity: A ≥ 0 and B ≥ 0

If products must be manufactured only in whole units, add integer constraints for both production quantities.

7.4 Configure Solver

1. Open the Data tab and select Solver.
2. In Set Objective, enter the total profit formula cell B7.
3. Select Max because the goal is to maximize profit.
4. In By Changing Variable Cells, enter B2:C2.
5. Add a constraint for labor: B8 <= C8.
6. Add a constraint for materials: B9 <= C9.
7. Ensure production quantities are non-negative. You can enable the non-negative option or add explicit lower-bound constraints.
8. If whole units are required, add an integer constraint to B2:C2.
9. Select Simplex LP because the objective and constraints in this example are linear.
10. Click Solve.
11. Review the solution and choose Keep Solver Solution if you want to retain the calculated values.

7.5 Understand the Expected Result

For the continuous linear model, one optimal solution is:

This solution uses all the available labor and material. If integer quantities are required, this particular solution already satisfies that requirement. The result illustrates how Solver can identify a profit-maximizing combination while meeting multiple resource limits.

8. Adding Constraints in Solver

Constraints define the permitted values in an optimization model. Without suitable constraints, Solver may return unrealistic values, such as negative production quantities or resource usage beyond the available capacity.

To add a constraint, click Add in the Solver Parameters dialog box, specify the cell reference, choose the relationship, enter the limit or cell reference, and confirm. Repeat for each additional condition.

9. Choosing the Correct Solver Method

Selecting an appropriate solving method can improve the reliability and efficiency of the optimization process. Always check whether the formulas and constraints fit the chosen method.

10. Practical Applications of Solver

10.1 Budget Optimization

Businesses can allocate a fixed budget across advertising channels to maximize expected revenue or conversions. Decision variables represent the amount spent on each channel, while constraints limit total spending and enforce minimum or maximum allocations.

10.2 Cost Minimization

Organizations can determine the lowest-cost purchasing or production plan while meeting demand and capacity requirements. Solver can compare several combinations rather than relying on manual trial and error.

10.3 Employee Scheduling

Solver can help determine staffing levels while meeting minimum coverage requirements. Integer constraints may be needed because employee counts generally cannot be fractional.

10.4 Transportation Planning

Businesses can minimize delivery costs by allocating shipments among warehouses and destinations, subject to supply, demand, and transport-capacity limits.

10.5 Investment and Project Selection

Solver can support portfolio allocation or project selection by optimizing a target measure under budget, risk, and resource constraints. Financial assumptions should be reviewed carefully before using the results for actual investment decisions.

11. Solver vs. Goal Seek

12. Common Solver Errors and Troubleshooting

13. Best Practices for Using Solver

• Organize the worksheet into clear sections for inputs, decision variables, calculations, and constraints.
• Use formulas to calculate the objective and resource usage rather than typing calculated results manually.
• Keep decision variables directly or indirectly connected to the objective and constraint formulas.
• Check units carefully, such as hours, units, kilograms, and currency.
• Use integer or binary constraints when the business problem requires discrete decisions.
• Choose a suitable solving method based on the mathematical structure of the model.
• Validate the final solution independently by recalculating profit, costs, and resource consumption.
• Save a copy of the original worksheet before running complex optimization scenarios.
• Document assumptions and constraints so other users can understand the model.
• Do not assume every returned result is globally optimal for every nonlinear or complex model; interpret Solver's status and validate the result.

14. Practice Exercises

Exercise 1: Profit Maximization

A business manufactures two products. Product A generates ₹50 profit per unit and Product B generates ₹35 profit per unit. Product A uses 3 labor hours and Product B uses 2 labor hours. There are 120 labor hours available. Each product uses one unit of material, and 50 material units are available.

• Create decision-variable cells for the quantities of both products.
• Create formulas for total profit, labor used, and material used.
• Set the objective to maximize total profit.
• Add the labor and material constraints.
• Run Solver and explain the result.

Exercise 2: Cost Minimization

A company must purchase at least 200 units of a component from two suppliers. Supplier A charges ₹25 per unit and Supplier B charges ₹22 per unit. Supplier A can provide up to 150 units and Supplier B up to 180 units.

• Set up decision variables for the order quantities.
• Calculate total purchasing cost.
• Minimize the total cost.
• Add the minimum-demand and supplier-capacity constraints.
• Compare the optimal order plan with purchasing all units from a single supplier.

Exercise 3: Project Selection

A company has a fixed investment budget and several projects with different costs and expected returns. Use binary decision variables to represent whether each project is selected. Maximize total expected return while keeping total project cost within the budget.

15. Interview Questions and Answers

Q1. What is Solver in Excel?

Solver is an optimization add-in that changes decision-variable cells to maximize, minimize, or achieve a target value for a formula cell while satisfying specified constraints.

Q2. What is an objective cell?

The objective cell contains the formula whose result is being optimized. For example, it may calculate total profit or total cost.

Q3. What are decision-variable cells?

These are the cells Solver adjusts while searching for a solution. They might represent production quantities, budget allocations, shipment quantities, or project-selection decisions.

Q4. Why are constraints important?

Constraints represent practical limits and requirements, such as budget availability, labor capacity, demand, non-negative quantities, or integer-only decisions.

Q5. What is the difference between Simplex LP and GRG Nonlinear?

Simplex LP is intended for linear optimization models, while GRG Nonlinear is designed for smooth nonlinear models.

Q6. When should an integer constraint be used?

Use an integer constraint when decision variables must be whole numbers, such as the number of employees, machines, or units that cannot be divided.

Q7. What is a binary constraint?

A binary constraint restricts a decision variable to 0 or 1. It is useful for yes-or-no decisions, such as whether to select a project or open a facility.

Q8. Why might Solver report that no feasible solution exists?

This can happen when the constraints are mutually incompatible or the available resources cannot satisfy the stated requirements.

Q9. Can Solver optimize multiple changing cells?

Yes. Solver can change multiple decision-variable cells together to improve the objective while respecting the model's constraints.

Q10. How can you verify a Solver solution?

Recalculate the objective and all constraint formulas, confirm that the limits are satisfied, and review the Solver result status and assumptions.

16. Quick Revision Checklist

• Solver is used for optimization and advanced What-If Analysis.
• The objective cell contains a formula.
• Decision-variable cells contain the values Solver can change.
• Constraints define the permitted solution space.
• Maximize is used for goals such as profit; Minimize is used for goals such as cost.
• Simplex LP is suitable for linear models.
• GRG Nonlinear is suitable for smooth nonlinear models.
• Evolutionary can handle certain non-smooth models.
• Integer and binary constraints support whole-number and yes-or-no decisions.
• Final results should be validated against the original business requirements.

17. Conclusion

Applying Solver for optimization problems is an important Advanced Excel skill for making better decisions using structured data and mathematical models. By defining an objective cell, selecting decision variables, and adding realistic constraints, users can solve problems involving profit maximization, cost minimization, resource allocation, production planning, scheduling, and project selection.

Regular practice with realistic business datasets helps develop confidence in building Solver models, choosing the correct solving method, and interpreting the results responsibly.

Explore the course: Advanced Excel Course

Register for a demo: JustAcademy Course Demo Registration

Visit JustAcademy: www.justacademy.co