Goal Seek in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to find out what sales target is needed in the next month to reach a total revenue goal.

📊 Data: You have monthly sales data with units sold and price per unit. The total revenue is calculated by multiplying units sold by price per unit.

🎯 Deliverable: Create a worksheet that calculates the required units sold next month to meet a specific total revenue goal using Goal Seek.

Sample Data

Month	Units Sold	Price per Unit	Total Revenue
January	100	20	=B2*C2
February	120	20	=B3*C3
March	110	20	=B4*C4
April	0	20	=B5*C5

1

Step 1: Enter the sample data into the worksheet starting at cell A1. Make sure the Total Revenue column uses the formula multiplying Units Sold by Price per Unit.

In cell D2 enter =B2*C2 and copy down to D5.

Expected Result

Total Revenue for January is 2000, February 2400, March 2200, April 0.

2

Step 2: Calculate the total revenue for the first three months in cell D6.

In cell D6 enter =SUM(D2:D4).

Expected Result

Total revenue for Jan to Mar is 6600.

3

Step 3: Set a revenue goal for the first four months in cell D7. Enter 9000 as the target total revenue.

In cell D7 enter 9000.

Expected Result

Cell D7 shows 9000.

4

Step 4: Calculate the total revenue including April in cell D8 by summing D6 and April's revenue in D5.

In cell D8 enter =D6+D5.

Expected Result

Initially, total revenue including April is 6600.

5

Step 5: Use Goal Seek to find the units sold in April (cell B5) needed to reach the revenue goal in D7 by changing B5.

Go to Data > What-If Analysis > Goal Seek. Set cell: D8 To value: 9000 By changing cell: B5.

Expected Result

Goal Seek changes B5 to 120, so total revenue including April is 9000.

Final Result

Month     Units Sold  Price per Unit  Total Revenue
January   100         20              2000
February  120         20              2400
March     110         20              2200
April     120         20              2400

Total Jan-Mar Revenue: 6600
Revenue Goal (Jan-Apr): 9000
Total Jan-Apr Revenue: 9000

✓To reach a total revenue of 9000 by April, April's units sold must be 120.

✓Goal Seek helps find the exact input needed to meet a target output.

✓This method saves time compared to manual trial and error.

Bonus Challenge

Use Goal Seek to find the price per unit in April needed to reach the revenue goal if units sold are fixed at 60.

Show Hint

Set April units sold (B5) to 60, then use Goal Seek on cell D8 to reach 9000 by changing C5.

Practice

1. What is the main purpose of the Goal Seek feature in Excel?

easy

A. To create charts automatically from data

B. To find the input value needed to achieve a specific formula result

C. To sort data alphabetically

D. To filter rows based on criteria

Solution

1. Step 1: Understand Goal Seek's function

   Goal Seek changes one input cell to reach a target output in a formula cell.

2. Step 2: Compare options with Goal Seek's purpose

   Only To find the input value needed to achieve a specific formula result describes finding an input value to get a desired formula result.

3. Final Answer:

   To find the input value needed to achieve a specific formula result -> Option B

4. Quick Check:

   Goal Seek = find input for target output [OK]

Hint: Goal Seek finds input for a desired output fast [OK]

Common Mistakes:

• Confusing Goal Seek with sorting or filtering
• Thinking Goal Seek creates charts
• Assuming Goal Seek changes multiple inputs

2. Which of the following is the correct way to open Goal Seek in Excel?

easy

A. Go to Home tab > Clipboard > Goal Seek

B. Go to Insert tab > Charts > Goal Seek

C. Right-click a cell > Format Cells > Goal Seek

D. Go to Data tab > What-If Analysis > Goal Seek

Solution

1. Step 1: Recall where Goal Seek is located

   Goal Seek is found under the Data tab in the What-If Analysis menu.

2. Step 2: Match the correct menu path

   Go to Data tab > What-If Analysis > Goal Seek correctly shows Data tab > What-If Analysis > Goal Seek.

3. Final Answer:

   Go to Data tab > What-If Analysis > Goal Seek -> Option D

4. Quick Check:

   Goal Seek location = Data > What-If Analysis [OK]

Hint: Find Goal Seek under Data tab's What-If Analysis [OK]

Common Mistakes:

• Looking for Goal Seek in Insert or Home tabs
• Right-click menu does not have Goal Seek
• Confusing Goal Seek with chart tools

3. You have a formula in cell B2: =A2*10. If you use Goal Seek to set B2 to 100 by changing A2, what value will Goal Seek find for A2?

medium

A. 10

B. 1000

C. 90

D. 110

Solution

1. Step 1: Understand the formula relationship

   B2 = A2 * 10 means B2 is 10 times A2.

2. Step 2: Calculate A2 for B2 = 100

   Set 100 = A2 * 10, so A2 = 100 / 10 = 10.

3. Final Answer:

   10 -> Option A

4. Quick Check:

   100 ÷ 10 = 10 [OK]

Hint: Divide target by multiplier to find input [OK]

Common Mistakes:

• Multiplying instead of dividing
• Choosing the target value itself
• Ignoring the formula relationship

4. You tried to use Goal Seek to set cell C3 to 50 by changing B3, but Excel shows an error. Which of these is the most likely cause?

medium

A. Goal Seek only works with text cells

B. B3 is locked and cannot be changed

C. Cell C3 does not contain a formula referencing B3

D. You did not select the entire worksheet

Solution

1. Step 1: Understand Goal Seek requirements

   Goal Seek needs the target cell to have a formula that depends on the changing cell.

2. Step 2: Analyze the error cause

   If C3 does not depend on B3, Goal Seek cannot adjust B3 to reach a target in C3.

3. Final Answer:

   Cell C3 does not contain a formula referencing B3 -> Option C

4. Quick Check:

   Goal Seek needs formula link between cells [OK]

Hint: Ensure target cell formula depends on changing cell [OK]

Common Mistakes:

• Trying Goal Seek on cells without formulas
• Assuming Goal Seek works on text
• Thinking worksheet selection affects Goal Seek

5. You have a loan payment formula in cell D5: =PMT(B5/12, C5*12, -A5). You want to find the interest rate in B5 that results in a monthly payment of $500 in D5. How do you use Goal Seek to find this interest rate?

hard

A. Set cell D5 to 500 by changing cell B5

B. Set cell B5 to 500 by changing cell D5

C. Set cell A5 to 500 by changing cell C5

D. Set cell C5 to 500 by changing cell A5

Solution

1. Step 1: Identify the target and input cells

   The payment is in D5, interest rate input is in B5.

2. Step 2: Use Goal Seek to set D5 to 500 by changing B5

   This finds the interest rate that makes payment $500.

3. Final Answer:

   Set cell D5 to 500 by changing cell B5 -> Option A

4. Quick Check:

   Goal Seek target = payment cell, change = interest rate cell [OK]

Hint: Set payment cell to target, change interest rate cell [OK]

Common Mistakes:

• Swapping target and changing cells
• Trying to change loan amount or term instead
• Ignoring formula cell dependencies