Excel Solver for Optimization

Solver finds optimal solutions for complex problems with multiple variables and constraints. It's perfect for resource allocation, scheduling, budgeting, and linear programming.

Enabling Solver

1. File → Options → Add-ins
2. Manage: Excel Add-ins → Go
3. Check "Solver Add-in" → OK
4. Now available: Data tab → Analyze group → Solver

Example 1: Production Optimization

Problem: Maximize profit with limited resources

Setup:
Product A: $40 profit, 2 hours labor, 3 units material
Product B: $30 profit, 1 hour labor, 2 units material

Constraints:
- Maximum 100 hours labor available
- Maximum 120 units material available

Cells:
B1: Units of A (to optimize)
B2: Units of B (to optimize)
B3: =40*B1 + 30*B2  // Total profit (objective)
B4: =2*B1 + 1*B2    // Labor used
B5: =3*B1 + 2*B2    // Material used

Solver Settings:
Objective: B3 (maximize)
By changing: B1:B2
Constraints:
- B4 <= 100 (labor limit)
- B5 <= 120 (material limit)
- B1 >= 0, B2 >= 0 (can't produce negative units)

Result: Optimal production mix for maximum profit

Example 2: Budget Allocation

Problem: Allocate $10,000 marketing budget across channels

Channels: Social Media, Email, PPC, Content
Each has different cost per lead and conversion rates

Objective: Maximize total conversions
Constraint: Total spend <= $10,000
Each channel minimum $500, maximum $4,000

Solver finds optimal allocation across all channels

Example 3: Staff Scheduling

Problem: Minimum staff to meet hourly demand

Requirements:
Mon-Fri 9am-5pm: 10 employees needed
Weekends: 5 employees needed
Employees work 5 consecutive days

Objective: Minimize total employees
Constraints: Meet minimum requirements each day

Solver determines optimal hiring schedule

Setting Up Solver

1. Define Objective Cell

What to optimize (maximize, minimize, or target value)
Example: Maximize profit, minimize cost

2. Variable Cells

Cells Solver can change to reach objective
Example: Production quantities, budget allocations

3. Constraints

Limitations and requirements
Examples:
- Budget <= $10,000
- Inventory >= 100
- Labor hours <= 40
- Variables must be integers
- Variables >= 0 (non-negative)

Solver Parameters

Solving Method:
- Simplex LP: Linear problems (best for most cases)
- GRG Nonlinear: Smooth nonlinear problems
- Evolutionary: Complex, discontinuous problems

Options:
- Max Time: How long to search for solution
- Iterations: Maximum calculation iterations
- Precision: How close to target
- Integer Constraints: Force whole numbers

Common Use Cases

Portfolio Optimization

Maximize returns while:
- Meeting risk tolerance
- Diversification requirements
- Minimum/maximum position sizes

Transportation Problem

Minimize shipping costs from:
- Multiple warehouses
- To multiple destinations
- With capacity constraints

Product Mix

Maximize profit given:
- Limited raw materials
- Production capacity
- Minimum order quantities
- Storage limitations

Solver vs Goal Seek

Goal Seek	Solver
Changes 1 variable	Changes multiple variables
No constraints	Handles many constraints
Reaches specific value	Maximizes/minimizes
Simple problems	Complex optimization

Tips for Success

• Start with reasonable initial values
• Add constraints one at a time and test
• Use Simplex LP for linear problems (fastest)
• Make integer constraints only when necessary
• Save your model to rerun with different parameters