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.