Goal Seek in Excel - Dashboard Guide

Dashboard Mode - Goal Seek

Goal of the Dashboard

Find the sales price needed to reach a target profit using Goal Seek.

Sample Data

Item	Cost per Unit ($)	Sales Price ($)	Units Sold	Profit ($)
Widget A	20	30	100	=(C2 - B2) * D2
Widget B	15	25	150	=(C3 - B3) * D3
Widget C	10	20	200	=(C4 - B4) * D4
Widget D	12	22	120	=(C5 - B5) * D5
Widget E	18	28	130	=(C6 - B6) * D6

Dashboard Components

• Total Profit Card: Shows total profit from all items.
  Formula: =SUM(E2:E6)
  Result: (30-20)*100 + (25-15)*150 + (20-10)*200 + (22-12)*120 + (28-18)*130 = 1000 + 1500 + 2000 + 1200 + 1300 = 7000
• Target Profit Input: Cell where user enters desired profit goal.
  Example: 9000
• Goal Seek Setup: Use Excel Goal Seek to change sales price of Widget A (cell C2) to reach target profit.
  Set cell: Total Profit cell (e.g., E7)
  To value: Target Profit input cell (e.g., G2)
  By changing cell: Sales Price of Widget A (C2)
• Sales Price After Goal Seek: Shows new sales price for Widget A after Goal Seek.
  Formula: =C2 (updated by Goal Seek)

Dashboard Layout

+----------------------+---------------------+
| Total Profit Card     | Target Profit Input |
|       (E7)            |       (G2)          |
+----------------------+---------------------+
| Sales Price After Goal Seek (C2 updated)    |
+---------------------------------------------+

Interactivity

User enters a target profit in the Target Profit Input cell. Then runs Goal Seek to adjust the sales price of Widget A. The Total Profit Card updates to show the new total profit matching the target. The Sales Price After Goal Seek cell shows the new sales price needed.

Self Check

If you change the target profit to 8000 and run Goal Seek again, what will be the new sales price for Widget A? Check that the Total Profit Card updates to 8000.

Key Result

Dashboard to find the sales price needed for Widget A to reach a target total profit using Goal Seek.