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.