Theory
Working the formula backwards
Normally a spreadsheet runs forwards: you enter a price and cost, it computes the margin. But Meera asks the reverse: 'What price do I need to charge to get a 20% margin?'
That is running the formula backwards, from a desired answer to the required input. Guessing prices until the margin hits 20% is tedious.
What-If Analysis does it instantly. Goal Seek finds the input for a target output; Scenario Manager compares whole plans; Data Table shows an output across many inputs. These are the tools of planning and decision-making, and they close Unit 2.
Theory
Aiming, not just firing
A normal formula is firing an arrow: you set the angle (inputs) and see where it lands (output). Goal Seek is aiming: you say 'I want to hit that target' and it works out the angle for you. Instead of trying angles until you hit, you name the target and let Excel solve for the input. That backwards-solving is the essence of What-If: start from the answer you want, find what produces it.
At a glance
The three What-If tools
| Tool | Question it answers | Varies |
|---|---|---|
| Goal Seek | What input gives this exact result? | One input, one target |
| Scenario Manager | How do best/worst plans compare? | Whole sets of inputs |
| Data Table | How does output change across inputs? | One or two inputs, a range |
Theory
Goal Seek: find the input for a target
Data > What-If Analysis > Goal Seek needs three things:
- Set cell: the formula cell (the margin).
- To value: the target result (20%).
- By changing cell: the input to adjust (the price).
Excel then searches for the price that makes the margin exactly 20% and fills it in. One input, one target, solved backwards.
Goal Seek shines for break-even ('what sales cover my costs?'), target-setting ('what score do I need in the final exam for 60% overall?'), and pricing, real questions with a single unknown.
Quiz
Aryan wants to know what monthly sales are needed to reach a yearly profit target. He has a profit formula. Which tool fits best?
- Goal Seek, it finds the one input (sales) that produces the target output (profit)
- A PivotTable, to summarise the sales
- Conditional formatting, to colour the profit
- VLOOKUP, to find the sales elsewhere
Show the answer
Goal Seek, it finds the one input (sales) that produces the target output (profit)
This is a classic Goal Seek problem: one target output (the profit target) and one input to solve for (required sales), with a formula linking them. Goal Seek works the formula backwards to find the exact sales figure. Pivots summarise, conditional formatting colours, VLOOKUP fetches, none of them solve for an input. Goal Seek is the backwards-solver.
Think first
Goal Seek or Scenario Manager?
Meera wants to compare THREE full plans for next month, an optimistic one (high sales, low costs), a pessimistic one, and a realistic one, each with several different input values. Is Goal Seek the right tool? If not, which is?
Show the answer
Not Goal Seek, that solves for one input toward one target. Comparing whole sets of inputs is Scenario Manager: Aryan saves three named scenarios ('Optimistic', 'Pessimistic', 'Realistic'), each with its own values for sales, costs, etc., then switches between them or views a summary comparing their outcomes side by side. Goal Seek finds a single number; Scenario Manager compares entire what-if worlds. Matching the tool to 'one unknown' vs 'many plans' is the key judgement.
Watch out
Where marks leak
Confusing the three: Goal Seek = solve for one input to hit one target (backwards); Scenario Manager = save and compare sets of inputs (plans); Data Table = tabulate an output across a range of inputs. Thinking Goal Seek can change multiple inputs (it changes one, use Solver for many). And forgetting these live under Data > What-If Analysis. Match the tool to the shape of the question, exactly as with charts.
Theory
Unit 2 complete: you can analyse anything
You can now summarise the past (pivots, dashboards) and explore the future (What-If). That is the full analyst toolkit. Unit 3 changes gears entirely: from analysis to automation. When a task repeats every month, you stop doing it by hand and teach Excel to do it, with macros and a little VBA code. Your BCA104 C skills are about to meet Excel. Next: recording your first macro.
Summary
Key takeaways
- What-If analysis explores hypotheticals: run formulas backwards or across many inputs.
- Goal Seek finds the one input value that produces a desired target output (works backwards).
- Scenario Manager saves and compares whole named sets of inputs (best/worst/realistic).
- Data Table tabulates how an output changes as one or two inputs vary across a range.
- All live under Data > What-If Analysis; match the tool to the question's shape.
- Memory hook: Goal Seek is aiming (name the target, solve the input), not just firing.