About this lesson
Goal Seek: break-even volume and the price for a target margin
Goal Seek finds the input that makes a formula hit a target. Go to Data, What-If Analysis, Goal Seek. Set the profit cell to 0 by changing the volume cell to find break-even, or set the margin cell to 0.15 by changing the price cell to find the price for a 15% margin. The formula cell must depend on the cell you change.
=B1*B4What you'll practise
Model a new product's profit, then let Goal Seek find the cases needed to break even and the price that gives a 15% margin.
4 steps · 40 points · the cells outlined in gold below
- Goal Seek
- break-even analysis
- target margin pricing
- What-If Analysis
The data
| A | B | |
|---|---|---|
| 1 | Price per case ($) | $54.00 |
| 2 | Variable cost per case ($) | $31.20 |
| 3 | Fixed costs ($) | $412,000.00 |
| 4 | Cases sold | 15,000 |
| 5 | ||
| 6 | Revenue ($) | |
| 7 | Variable costs ($) | |
| 8 | Profit ($) | |
| 9 | Profit margin |