Goal Seek in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Using Goal Seek to Find a Sales Target

You have a simple sales commission setup in Excel. Cell B1 contains the sales amount, and cell B2 calculates commission as =B1*0.1. You want to find the sales amount needed to earn exactly $500 commission.

Which steps correctly use Goal Seek to find the sales amount?

A Set cell: B2, To value: 500, By changing cell: B1

B Set cell: B1, To value: 500, By changing cell: B2

C Set cell: B2, To value: 50, By changing cell: B1

D Set cell: B1, To value: 50, By changing cell: B2

💡 Hint

Goal Seek changes the input cell to make the formula cell reach the target value.

Cell B2 calculates commission based on sales in B1. To find sales for $500 commission, set B2 to 500 by changing B1.

📊 Formula Result

intermediate

Result of Goal Seek on Loan Payment

You have a loan payment formula in cell C3: =PMT(B1/12, B2, -B4), where B1 is annual interest rate (5%), B2 is number of months (60), and B4 is loan amount ($10,000). You want to find the loan amount that results in a monthly payment of $200.

After using Goal Seek with Set cell: C3, To value: -200, By changing cell: B4, what is the approximate loan amount?

A $11,000

B $10,000

C $9,500

D $12,000

💡 Hint

Goal Seek adjusts the loan amount to match the payment.

Goal Seek changes B4 (loan amount) so that the payment in C3 equals -200. The loan amount must increase to about $11,000 to have a $200 payment at 5% interest over 60 months.

❓ Function Choice

advanced

Choosing the Correct Cell to Change in Goal Seek

You have a formula in cell D5 that calculates total cost based on quantity in cell B5 and price per unit in cell C5: =B5*C5. You want to find the quantity needed to reach a total cost of $1,000.

Which cell should you choose to change in Goal Seek?

A Change cell D5

B Change cell C5

C Change cell B5

D Change cell A5

💡 Hint

Goal Seek changes the input value to reach the target formula result.

D5 is the formula cell. To find quantity, change B5 (quantity) so D5 equals 1000.

❓ data_analysis

advanced

Analyzing Goal Seek Outcome on Break-even Point

You have fixed costs in B1 ($750), variable cost per unit in B2 ($20), and price per unit in B3 ($50). Cell B4 calculates profit as =B3*B5 - B2*B5 - B1, where B5 is units sold.

You use Goal Seek to set B4 to 0 by changing B5. What is the break-even units sold?

A 20 units

B 35 units

C 30 units

D 25 units

💡 Hint

Break-even means profit is zero.

Profit = (50 - 20) * units - 750 = 0 → 30 * units = 750 → units = 25. Goal Seek finds exactly 25 units.

🧠 Conceptual

expert

Understanding Limitations of Goal Seek

Which of the following statements about Excel's Goal Seek feature is NOT true?

A Goal Seek stops when the formula cell value is close enough to the target value.

B Goal Seek can find solutions for formulas with multiple changing cells simultaneously.

C Goal Seek requires the formula cell to contain a formula dependent on the input cell.

D Goal Seek can only change one input cell at a time to reach a target output.

💡 Hint

Think about how many cells Goal Seek can adjust at once.

Goal Seek can only change one input cell at a time. It cannot handle multiple changing cells simultaneously; that requires Solver.