5 Optimization Problems You Didn’t Know the Excel Solver Could Handle

 

Excel Solver add-in is often introduced as a simple “maximize profit” or “minimize cost” tool. But it can do much more than basic what-if analysis. Solver can handle prescriptive analytics problems where Excel does not just describe what happened or predict what may happen; it recommends the best decision under real-world limits. It supports linear programming (LP), nonlinear programming (NLP), and integer/mixed-integer programming (IP/MIP). Many users only use it for basic “what-if” scenarios, but it excels at complex resource allocation, logistics, scheduling, and blending problems.

In this tutorial, we will show 5 optimization problems you didn’t know the Excel Solver could handle. These examples are suitable for business analysts, operations managers, supply chain planners, and financial analysts.

Enable Solver in Excel

• Go to File tab >> select Options >> select Add-ins >> select Excel Add-ins >> click Go
• Select Solver Add-in >> click OK
• After loading it, Solver appears under the Data tab in the Analysis group

Key Solver Elements:

• Objective Cell: The cell to maximize, minimize, or set to a value
• Variable Cells (decision variables): Cells Solver can change
• Constraints: Limits on variables or formulas
• Solving Method: Simplex LP (linear), GRG Nonlinear, or Evolutionary (for non-smooth)

1. Resource Allocation Optimization

Consider a scenario where a small consulting company has limited analyst hours. It wants to decide how many projects to accept from different service categories to maximize profit.

Step 1: Set Up the Excel Table and Summary Formulas

• Total Profit:

=SUMPRODUCT(B2:B6,F2:F6)

• Analyst Hours Used:

=SUMPRODUCT(C2:C6,F2:F6)

• Review Hours Used:

=SUMPRODUCT(D2:D6,F2:F6)

Step 2: Use Solver

• Go to the Data tab >> select Solver
• Use the following Solver settings:
  • Set Objective: B11
  • To: Max
  • By Changing Variable Cells: F4:F8
• Click Add
  • Constraint: B12 <= 160
  • Constraint: B13 <= 40
  • Constraint: F4:F8 <= E4:E8
  • Constraint: F4:F8 >= 0
  • Constraint: F4:F8 = integer
• Solving Method: Simplex LP
• Click Solve

Use Simplex LP because this is a linear optimization problem: the profit and resource usage are calculated using straight-line formulas.

Interpret the Result:

Solver recommends the number of projects to accept in each category. The best answer may not mean accepting the highest-profit project only. A lower-profit project may use fewer hours and allow the company to complete more total work.

2. Logistics and Shipping Cost Optimization

Suppose a company ships products from three warehouses to four retail stores. Each warehouse has a limited supply, and each store has a demand. The goal is to minimize total shipping cost.

Step 1: Create the Decision Variable Table

Create a table where Solver will decide how many units to ship from each warehouse to each store.

• Warehouse A:

=SUM(B2:E2)

• Copy this formula for the other warehouses.

Add store totals below — Total Received:

• Store 1:

=SUM(B2:B4)

• Copy this formula for the rest of the stores.

Step 2: Calculate Total Shipping Cost

Assume the shipping quantity table is in B2:E4 and the cost table is in B8:E10.

=SUMPRODUCT(B2:E4,B8:E10)

This formula multiplies each shipping quantity by its corresponding shipping cost and sums everything together.

Step 3: Solver Settings

• Go to the Data tab >> select Solver
• Use the following Solver settings:
  • Set Objective: B17
  • To: Min
  • By Changing Variable Cells: B4:E6
• Click Add
  • Constraint: F4:F6 <= F9:F11
  • Constraint: B14:E14 = B15:E15
  • Constraint: B4:E6 >= 0
  • Optional: Integer shipments
• Solving Method: Simplex LP
• Click Solve

This is a classic transportation optimization problem. Solver can identify the lowest-cost shipping pattern without manually testing every possible combination.

This model can be used for distribution planning, inventory transfer between branches, delivery route allocation, supplier-to-customer shipment planning, and warehouse cost minimization.

Even though this example uses warehouses and stores, the same logic applies to many allocation problems.

3. Shift Scheduling Optimization

Consider a scenario where a retail store or call center needs enough employees at different times of the day. Customer demand changes by time block, so the company does not need the same number of employees all day. The goal is to assign employees to different shifts while meeting staffing requirements at the lowest possible labor cost.

Step 1: Create the Shift Coverage Matrix

Create a table that shows whether each shift covers each time block. Use 1 if the shift covers the time block and 0 if it does not. The Employees Assigned column is the decision variable range — Solver will change these values.

Step 2: Calculate Staff Coverage

Below each time block, calculate how many employees are available.

• 8 AM–12 PM Block:

=SUMPRODUCT(E4:E9,$I$4:$I$9)

Here, E4:E9 contains the coverage pattern for 8–12 PM, and $I$4:$I$9 contains the number of employees assigned to each shift.

• Copy the formula across the other time blocks.

Step 3: Calculate Shift and Total Labor Cost

Shift Cost:

Use this formula to calculate shift cost:

=C4*I4

• Copy the formula down.

Total Labor Cost:

Use this formula to calculate total labor cost:

=SUM(J4:J9)

This total labor cost will be the objective cell.

Step 4: Use Solver

• Go to the Data tab >> select Solver
• Use the following Solver settings:
  • Set Objective: H13
  • To: Min
  • By Changing Variable Cells: I5:I9
• Click Add
  • Constraint: B14:E14 >= B13:E13
  • Constraint: I5:I9 <= D5:D9
  • Constraint: I5:I9 >= 0
  • Constraint: I5:I9 = integer
  • Optional Constraint: H15 <= I15
• Solving Method: Simplex LP
• Click Solve

Interpret the Result:

Solver will return the number of employees assigned to each shift. For example, Solver may assign more employees to the midday and afternoon shifts because those shifts cover the busiest time blocks. It may assign fewer employees to the evening shift if the staffing requirement is lower later in the day.

The benefit of this model is that the business does not overstaff every hour. Instead, Solver finds the lowest-cost combination of shifts that still meets the required coverage.

This type of Solver model is useful for:

• Retail store scheduling
• Call center staffing
• Restaurant shift planning
• Hospital or clinic desk coverage
• Warehouse picking and packing schedules
• Customer support team planning

It helps managers answer an important question: “How many employees should be assigned to each shift so that every time block is covered without unnecessary labor cost?”

This is a strong example of prescriptive analytics because Solver recommends the best staffing decision based on demand, shift structure, and labor cost.

4. Marketing Budget Optimization

Suppose a company has a $50,000 marketing budget. It wants to divide the budget across five channels to maximize expected leads while respecting minimum and maximum spending limits.

Step 1: Add the Decision Variable Column

Calculate Expected Leads:

=B2*E2/1000

• Copy the formula down for the other channels.

Step 2: Add Total Formulas

• Total Budget Used:

=SUM(E2:E6)

• Total Expected Leads:

=SUM(F2:F6)

Step 3: Solver Settings

• Go to the Data tab >> select Solver
• Use the following Solver settings:
  • Set Objective: B12
  • To: Max
  • By Changing Variable Cells: E4:E8
• Click Add
  • Constraint: B11 <= B13
  • Constraint: E4:E8 >= C4:C8
  • Constraint: E4:E8 <= D4:D8
  • Constraint: E4:E8 >= 0
  • Optional Constraint: E4:E8 <= 0.4*B13
• Solving Method: Simplex LP
• Click Solve

Add a Realistic Business Constraint

In real marketing planning, a company may not want to concentrate too much spending in one channel. You can add this rule:

• Each channel budget <= 40% of the total budget
• If the total budget is $50,000, then each channel must be <= $20,000

This prevents Solver from placing nearly all spending into the highest-return channel.

This is prescriptive analytics because Excel is not just calculating leads — it is recommending the spending plan.

5. Product Mix with Setup Costs

Consider a manufacturer that produces five products. Each product earns a profit per unit, but the company also has limited machine hours, labor hours, and raw materials. In a basic product mix model, Solver only decides how many units of each product to produce. That type of model is usually best solved with Simplex LP.

However, real production planning is often more complex. A company may need to pay a fixed setup cost before producing a product. It may also require a minimum production batch once a product is selected.

In this table:

• Produce? is a binary decision variable
• 1 means the product is selected for production
• 0 means the product is not selected
• Units Produced is also a decision variable

Step 1: Calculate Gross Profit

Gross profit is calculated before setup costs.

=SUMPRODUCT(B2:B6,J2:J6)

Step 2: Calculate Total Setup Cost

The setup cost applies only if the product is selected.

=SUMPRODUCT(C2:C6,I2:I6)

If Product A is selected, the setup cost is counted. If Product A is not selected, the setup cost is ignored.

Step 3: Calculate Net Profit

Net profit is the objective cell:

=Gross_Profit_Cell-Setup_Cost_Cell

For example:

=F14-F15

Solver will try to maximize this value.

Step 4: Calculate Resource Usage

• Machine Hours Used:

=SUMPRODUCT(D2:D6,J2:J6)

• Labor Hours Used:

=SUMPRODUCT(E2:E6,J2:J6)

• Material Used:

=SUMPRODUCT(F2:F6,J2:J6)

These formulas calculate how much of each resource is consumed by the selected production plan.

Step 5: Add Production Rule Constraints

This is the most important part of the Evolutionary Solver model. The model needs to link the Produce? column to the Units Produced column.

Rule 1: If a Product Is Not Selected, Units Produced Must Be Zero

Use this Solver constraint:

• Units Produced <= Maximum Demand × Produce?
• For Product A, the constraint is:

J2 <= H2 × I2

This rule prevents Solver from producing a product unless it has been selected.

Rule 2: If a Product Is Selected, It Must Meet the Minimum Batch Size

Use this Solver constraint:

• Units Produced >= Minimum Batch × Produce?
• For Product A, the constraint is:

J2 >= G2 × I2

Step 6: Open Solver

• Go to the Data tab >> select Solver
• Use the following Solver settings:
  • Set Objective: $F$16
  • To: Max
  • By Changing Variable Cells: $I$6:$J$10
• Click Add
  • Constraint: $I$6:$I$10 = binary
  • Constraint: $J$6:$J$10 = integer
  • Constraint: $C$14:$C$16 <= $B$14:$B$16
  • Constraint: $B$22:$B$26 >= $C$22:$C$26
  • Constraint: $D$22:$D$26 <= $E$22:$E$26
  • Constraint: $J$6:$J$10 >= 0
  • Constraint: $J$6:$J$10 <= $H$6:$H$10
• Solving Method: Evolutionary
• Click Solve

Interpret the Result:

Solver will return two kinds of decisions:

• Which products should be produced — products with Produce? = 1 are selected; products with Produce? = 0 are not selected.
• How many units of each selected product should be produced.

The best solution may not include every product. Even if a product has a good profit per unit, it may not be worth producing if the setup cost is high or if it consumes too many limited resources.

For example, Product D has the highest profit per unit, but it also has a high setup cost and uses more machine hours, labor hours, and materials. Solver may still select it if the net profit is strong enough, but it will weigh that choice against all other possible product combinations.

Solver Methods: Which One Should You Choose?

Excel Solver provides different solving methods. The method matters because different problems behave differently. Microsoft’s Solver interface allows users to choose from solving methods such as Simplex LP, GRG Nonlinear, and Evolutionary, depending on the type of model.

Best Practices for Building Solver Models

• Separate Inputs, Decision Variables, and Outputs: This makes the model easier to audit and update.
• Use SUMPRODUCT for Optimization Models: It is one of the most useful formulas for Solver.
• Start with Zeros or Reasonable Trial Values: Solver can usually work with zeros, but for some nonlinear models, reasonable starting values may help. For linear problems, trial values are less important.
• Add Non-Negativity Constraints: Most real-world decision variables cannot be negative. Always add: Changing cells >= 0.
• Use Integer Constraints Carefully: Integer constraints make the model more realistic but can slow the solver down. Use integer constraints when the result must be a whole number.

Conclusion

The five optimization problems above represent a spectrum from pure linear programs to combinatorial optimization. Excel Solver is much more than a basic what-if tool. It can solve practical prescriptive analytics problems involving limited resources, logistics planning, scheduling, marketing budgets, and production decisions. Start with the resource allocation example on a blank sheet, then scale up. Once comfortable, model your own real business problem — Solver can handle surprisingly large and complex decisions when the model is built thoughtfully.