Excel
All Top 50 Excel & Business Modeling Questions#46
Solving Break-Even Volume and Target Net Income with Goal Seek
EasyDeloitteInterview Question #46
Asked at DeloitteA manufacturing model has Unit Price ($50) in `B1`, Variable Cost per Unit ($30) in `B2`, Fixed Costs ($100,000) in `B3`, and Sales Volume in `B4`. Operating Profit in `B5` is computed as `=(B1-B2)*B4 - B3`. Use Goal Seek to find the exact Sales Volume needed to break even (Operating Profit = $0).
Input Table: ModelParameters (B1:B5)
5 rows preview| Parameter | Current Value | Cell |
|---|---|---|
| Unit Selling Price | $50.00 | B1 |
| Variable Cost / Unit | $30.00 | B2 |
| Fixed Overhead Costs | $100,000 | B3 |
| Sales Volume (Units) | 2,500 units | B4 |
| Operating Profit | ($50,000) | B5: =(B1-B2)*B4 - B3 |
Expected Output Structure1 rows
| Goal Seek Settings | Target Input (B4) | Resulting Profit (B5) |
|---|---|---|
| Set: B5 | To Value: 0 | By Changing: B4 | 5,000 units | $0.00 (Break-Even) |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Parameter | Current Value | Cell | |||||||
| 2 | Unit Selling Price | $50.00 | B1 | Target [Enter Formula] | ||||||
| 3 | Variable Cost / Unit | $30.00 | B2 | |||||||
| 4 | Fixed Overhead Costs | $100,000 | B3 | |||||||
| 5 | Sales Volume (Units) | 2,500 units | B4 | |||||||
| 6 | Operating Profit | ($50,000) | B5: =(B1-B2)*B4 - B3 | |||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.