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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect