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.