Column Harbor
Module 05: Turn data into decisions
LESSON 20 / Advanced · ABOUT 20 MIN

Use Goal Seek, then check whether the answer makes sense

Work backward from a target profit to a required quantity, and handle fractional answers without pretending the model guarantees real sales.

Before you start

Goal Seek in Windows desktop Excel 2016, 2019, 2021, 2024 and Microsoft 365. Supported Mac desktop versions also have Goal Seek, with menus that can differ. Open the workbook in desktop Excel for this exercise if you are using Excel for the web.

Useful first: Build your first formulas from cell references · Copy formulas while keeping shared inputs fixed

By the end, you can…
  • Connect one adjustable input to a formula result.
  • Configure Set cell, To value and By changing cell correctly.
  • Check fractional quantities and the assumptions behind a solved result.
YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic inputs only. Paste at Model!A1 and enter the taught profit formula in B6.

Download practice data
View the raw practice data
Input	Value
Units	50
Unit price	12
Unit cost	7
Fixed cost	100
Profit

01Build a transparent model

Paste the sample at A1:B6 on a sheet named Model. B2 is the number of units, B3 the selling price per unit, B4 the variable cost per unit and B5 the fixed cost. Replace the empty B6 with the profit formula below. The starting result is 150: fifty units contribute five each, then fixed costs consume 100.

This deliberately simple model assumes every unit sells at the same price, variable cost is constant, and no other costs exist. The values are invented practice inputs. Solving the arithmetic does not establish that customers will buy the required quantity.

=B2*(B3-B4)-B5

Enter in B6. With B2=50, B3=12, B4=7 and B5=100, the result is 150.

02Solve for one input, then substitute it back

On Windows, choose Data > What-If Analysis > Goal Seek, sometimes within the Forecast group. Set cell is $B$6, To value is 500, and By changing cell is $B$2. Run the calculation and inspect its status before accepting. The target must be a formula cell that depends on the adjustable input.

The mathematical answer is 120 units: 120 × (12 − 7) − 100 equals 500. Allow a small numerical tolerance when checking Goal Seek's displayed solution. Confirm that B3:B5 did not change and that the result remains sensible when inserted directly into the formula.

Goal Seek changes one input; it does not enforce a whole-number quantity, a capacity ceiling or an optimal price. Solver is a different tool for constrained problems. If the contribution per unit is zero or negative, this positive-profit target has no nonnegative-quantity solution under the same assumptions.

03Separate numerical success from operational feasibility

A target of 503 would mathematically require 120.6 units in the original model. If only whole units can be sold, round the quantity upward outside the target formula and recompute profit: 121 units produce 505. Rounding the displayed profit to conceal a mismatch would answer the wrong question.

Keep the original input values on a copy of the sheet before experimenting. If Goal Seek fails, do not treat its last attempted input as a valid answer. Check the dependency, target feasibility and starting value. Even a converged answer remains conditional on the model; it is not a forecast or proof of demand.

PUT IT INTO PRACTICE

Recalculate the quantity needed after a lower selling price.

  1. Restore B2=50, B3=12, B4=7 and B5=100. Seek a B6 profit of 250 by changing B2.
  2. Change B3 to 11, then run Goal Seek again for profit 250.
  3. Put =ROUNDUP(B2,0) in C2. Enter that whole-number result in B2 and recheck B6.
Show the worked answer

At price 12, the required quantity is 70.

At price 11, the mathematical quantity is 87.5. Whole units require 88, producing profit 252 rather than exactly 250.

Check your work
  • Changing the price to 11 while quantity is still 70 makes profit 180.
  • The final 252 exceeds the target by 2; that difference is explained by the whole-unit constraint.
CHECK YOUR UNDERSTANDING

One question before you move on.

Goal Seek returns 87.5 units, but the product can only be sold in whole units. What should you do?

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.
NEXT LESSONImport CSV without damaging identifiers

Keep the skill close.

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.