Solver for optimization in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a production planner at a furniture company.

📋 Request: Your manager wants you to find the best number of chairs and tables to produce to maximize profit, given limited wood and labor hours.

📊 Data: You have data on profit per chair and table, wood required per unit, labor hours required per unit, and total available wood and labor hours.

🎯 Deliverable: Create a spreadsheet model and use Excel Solver to find the optimal production quantities of chairs and tables that maximize profit without exceeding resource limits.

Sample Data

Item	Profit per Unit ($)	Wood per Unit (board feet)	Labor per Unit (hours)
Chair	45	5	3
Table	80	20	10

Resource	Available
Wood (board feet)	100
Labor (hours)	40

1

Step 1: Enter the data into the spreadsheet as shown in the sample data tables.

No formula needed; just input the data.

Expected Result

Data is organized with profit, wood, and labor per unit for chairs and tables, and total available resources.

2

Step 2: Set up cells to input the number of chairs and tables to produce. For example, put 'Chairs' in cell B7 and 'Tables' in cell C7, and enter initial values 0 in cells B8 and C8.

No formula needed; just input labels and initial values.

Expected Result

Cells B8 and C8 are ready for Solver to change.

3

Step 3: Calculate total profit in cell D8 using the formula: Profit = (Chairs * 45) + (Tables * 80).

=B8*45 + C8*80

Expected Result

Cell D8 shows total profit based on current production quantities.

4

Step 4: Calculate total wood used in cell B10: Wood used = (Chairs * 5) + (Tables * 20).

=B8*5 + C8*20

Expected Result

Cell B10 shows total wood used.

5

Step 5: Calculate total labor used in cell C10: Labor used = (Chairs * 3) + (Tables * 10).

=B8*3 + C8*10

Expected Result

Cell C10 shows total labor used.

6

Step 6: Open Excel Solver (Data tab > Solver). Set the objective to cell D8 (total profit) and choose 'Max'.

Objective: Set cell D8 to 'Max'.

Expected Result

Solver is ready to maximize total profit.

7

Step 7: Set the variable cells to B8 and C8 (number of chairs and tables).

By Changing Variable Cells: B8,C8

Expected Result

Solver will change these cells to find the best solution.

8

Step 8: Add constraints: total wood used (B10) <= 100, total labor used (C10) <= 40, and production quantities (B8, C8) >= 0 and integers.

Constraints: B10 <= 100 C10 <= 40 B8 >= 0 C8 >= 0 B8 and C8 are integers (select 'int' constraint in Solver)

Expected Result

Solver will respect resource limits and produce whole units.

9

Step 9: Click 'Solve' and let Solver find the optimal number of chairs and tables to maximize profit.

No formula; run Solver.

Expected Result

Solver finds solution: Chairs = 13, Tables = 0, Total Profit = $585.

10

Step 10: Keep Solver solution and review results in the spreadsheet.

No formula; accept Solver solution.

Expected Result

Spreadsheet shows optimal production plan and maximum profit.

Final Result

Production Plan Optimization
----------------------------
Chairs to produce: 13
Tables to produce: 0

Resource Usage:
Wood used: 65 board feet (limit 100)
Labor used: 39 hours (limit 40)

Total Profit: $585

✓Producing 13 chairs and 0 tables maximizes profit without exceeding wood and labor limits.

✓Wood is partially used (65 board feet), labor is nearly fully used (39 hours).

✓Profit is maximized at $585 under given constraints.

Bonus Challenge

Add a new product 'Stool' with profit $30, wood 3 board feet, and labor 2 hours. Update the model and use Solver to find the new optimal production plan.

Show Hint

Add new columns for Stool data and update all formulas and Solver settings to include the new product variables.

Practice

1. What is the main purpose of Excel's Solver tool?

easy

A. To format cells with colors

B. To create charts and graphs automatically

C. To sort data alphabetically

D. To find the best values for variables to achieve a goal

Solution

1. Step 1: Understand Solver's function

   Solver is designed to adjust variables to optimize a target value.

2. Step 2: Compare options

   Only To find the best values for variables to achieve a goal describes finding best values to reach a goal, which matches Solver's purpose.

3. Final Answer:

   To find the best values for variables to achieve a goal -> Option D

4. Quick Check:

   Solver = optimization tool [OK]

Hint: Solver finds best numbers to reach your goal [OK]

Common Mistakes:

• Confusing Solver with chart tools
• Thinking Solver sorts or formats data
• Assuming Solver only works with text

2. Which of these is the correct way to set the objective cell in Solver?

easy

A. Select any empty cell

B. Select the cell with the formula you want to optimize

C. Select the entire worksheet

D. Select only cells with text

Solution

1. Step 1: Identify the objective cell role

   The objective cell contains the formula Solver tries to maximize, minimize, or set to a value.

2. Step 2: Evaluate options

   Only Select the cell with the formula you want to optimize correctly states selecting the formula cell to optimize.

3. Final Answer:

   Select the cell with the formula you want to optimize -> Option B

4. Quick Check:

   Objective cell = formula cell [OK]

Hint: Objective cell must have the formula to optimize [OK]

Common Mistakes:

• Choosing empty cells without formulas
• Selecting too many cells
• Picking cells with only text

3. Given this setup: Objective cell B5 = SUM(B1:B4), variable cells B1:B4, and constraint B1>=0, what will Solver do if you set objective to maximize B5?

medium

A. Return an error because constraints are invalid

B. Decrease values in B1:B4 to minimize the sum

C. Increase values in B1:B4 to maximize the sum

D. Ignore constraints and set B1:B4 to zero

Solution

1. Step 1: Understand objective and variables

   The objective is to maximize the sum in B5, which depends on B1:B4 values.

2. Step 2: Apply constraints

   Constraint B1>=0 means B1 cannot be negative, but no upper limit is set.

3. Step 3: Predict Solver behavior

   Solver will increase B1:B4 values (within constraints) to maximize the sum.

4. Final Answer:

   Increase values in B1:B4 to maximize the sum -> Option C

5. Quick Check:

   Maximize sum = increase variables [OK]

Hint: Maximize sum by increasing variable cells [OK]

Common Mistakes:

• Thinking Solver minimizes when set to maximize
• Ignoring constraints
• Assuming Solver returns errors without reason

4. You set Solver to minimize cell C10 by changing C1:C5 but get an error saying 'No feasible solution found'. What is a likely cause?

medium

A. Constraints are too strict or conflicting

B. Objective cell has no formula

C. Variable cells contain text instead of numbers

D. Solver add-in is not installed

Solution

1. Step 1: Understand 'No feasible solution' error

   This error means Solver cannot find values meeting all constraints.

2. Step 2: Analyze possible causes

   Too strict or conflicting constraints often cause no solution.

3. Step 3: Evaluate other options

   Objective cell without formula or text in variables cause different errors; Solver add-in missing prevents running Solver.

4. Final Answer:

   Constraints are too strict or conflicting -> Option A

5. Quick Check:

   No feasible solution = conflicting constraints [OK]

Hint: Check constraints if Solver finds no solution [OK]

Common Mistakes:

• Ignoring constraint conflicts
• Assuming Solver add-in missing causes this error
• Not verifying variable cell data types

5. You want to minimize total cost in cell D10 by changing quantities in D1:D5. Each quantity must be an integer between 1 and 10. How do you set up Solver correctly?

hard

A. Set objective to minimize D10, variable cells D1:D5, add constraints D1:D5 >=1, D1:D5 <=10, and select 'Make Integer' for D1:D5

B. Set objective to maximize D10, variable cells D1:D5, no constraints needed

C. Set objective to minimize D10, variable cells D1:D5, add constraint D1:D5 = 5 only

D. Set objective to minimize D10, variable cells D1:D5, add constraint D1:D5 text only

Solution

1. Step 1: Define objective and variables

   Objective is to minimize D10 by changing D1:D5 quantities.

2. Step 2: Add constraints for integer and range

   Quantities must be integers between 1 and 10, so add constraints D1:D5 >=1, D1:D5 <=10, and set 'Make Integer' for D1:D5.

3. Step 3: Evaluate options

   Only Set objective to minimize D10, variable cells D1:D5, add constraints D1:D5 >=1, D1:D5 <=10, and select 'Make Integer' for D1:D5 correctly sets objective, variables, and all constraints including integer requirement.

4. Final Answer:

   Set objective to minimize D10, variable cells D1:D5, add constraints D1:D5 >=1, D1:D5 <=10, and select 'Make Integer' for D1:D5 -> Option A

5. Quick Check:

   Integer constraints + bounds = correct setup [OK]

Hint: Add integer and range constraints for quantities [OK]

Common Mistakes:

• Forgetting to set integer constraint
• Using maximize instead of minimize
• Setting wrong or missing constraints