Solver for optimization in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Maximize profit with Solver

You have a product that sells for $50 each. The cost to make one product is $30. You want to find how many products to make to maximize profit, but you can only make up to 100 products. Which cell should you set as the objective in Solver?

A The cell calculating profit (=total revenue - total cost)

B The cell calculating total cost (=30 * quantity)

C The cell calculating total revenue (=50 * quantity)

D The cell containing the quantity of products

💡 Hint

Think about what you want to maximize: revenue, cost, or profit?

In Solver, the objective cell is the one you want to maximize or minimize. Here, profit is revenue minus cost, so you want to maximize the profit cell.

📊 Formula Result

intermediate

Solver changing variable cell

You use Solver to find the best number of items to produce. The quantity is in cell B2. After running Solver, what will happen to cell B2?

A It will stay the same as before running Solver

B It will be cleared and become empty

C It will show an error because Solver cannot change input cells

D It will change to the value that maximizes or minimizes the objective

💡 Hint

Solver changes certain cells to reach the goal. Which cells does it change?

Solver changes the variable cells (called 'changing cells') to find the best solution for the objective.

❓ Function Choice

advanced

Choosing constraints in Solver

You want to use Solver to ensure the number of products made is at least 10 and at most 100. Which constraints should you add?

A "B2 = 10" and "B2 = 100"

B "B2 > 10" and "B2 < 100"

C "B2 >= 10" and "B2 <= 100"

D "B2 != 10" and "B2 != 100"

💡 Hint

Think about how to include the boundary values 10 and 100.

Constraints with >= and <= include the boundary values, so the quantity can be exactly 10 or 100.

❓ data_analysis

advanced

Interpreting Solver results

After running Solver to minimize cost, the status message says "Solver found a solution. All constraints and optimality conditions are satisfied." What does this mean?

A Solver could not find any solution that meets the constraints

B Solver found a solution that meets all constraints and is the best possible

C Solver found a solution but some constraints are violated

D Solver stopped before completing the optimization

💡 Hint

Look for words like "all constraints" and "optimality" in the message.

This message means Solver successfully found the best solution that meets all your rules.

🧠 Conceptual

expert

Why use integer constraints in Solver?

You are using Solver to decide how many whole products to make. Why should you add an integer constraint to the quantity cell?

A To ensure the quantity is a whole number because you cannot make part of a product

B To prevent Solver from changing the quantity cell

C To allow Solver to use decimal numbers for more precise results

D To speed up Solver calculations by limiting options

💡 Hint

Think about what it means to produce a fraction of a product.

Integer constraints force Solver to pick whole numbers only, which makes sense for countable items like products.

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