Objectives

Section Learning Objectives

📥 Hands-on: Reverse-Engineering Outputs

Download the provided CSV template to practice using Goal Seek to force an L&T construction model to output a specific Net Income.

Download CSV Data
4.1

Goal Seek: Reverse-Engineering One Input

📖What Goal Seek Does

Goal Seek answers: "What input value do I need to achieve a specific output?" It changes ONE input cell until a formula produces your desired result. Think of it as running your model backwards.

📝 Worked Example

Finding Target Required Revenue Growth

Scenario: You've downloaded the L&T Construction model. The management has mandated that Net Income for the next period must hit exactly ₹1,000 Cr (up from the current projection of ₹862.5 Cr).

Question: Ignoring changes to Fixed Expenses or Margin, what exact Revenue Growth Rate must sales achieve to satisfy this Net Income Target?
1
Open Goal Seek
Navigate to Data → What-If Analysis → Goal Seek.
2
Configure the Input Window
Set cell: Click the cell calculating Net Income (e.g., B15) — must be a formula.
To value: Type 1000 (your target Net Income).
By changing cell: Click the Revenue Growth Percentage assumption cell (e.g., B3) — must be a hardcoded number.
3
Execute & Verify
Click OK. Excel will rapidly iterate through growth rates until the formula in Net Income equals ₹1,000.
Result: Excel changes Cell B3 to 11.111%. You've mathematically proved L&T needs at least 11.11% top-line growth to hit the mandate.
⚠️Goal Seek Limitations

• Works with only ONE input variable at a time
• The "Set cell" must contain a formula (not a hardcoded value)
• May not find a solution if the target is mathematically impossible
• Results are not dynamic — if other inputs change, you must re-run Goal Seek

4.2

Solver: Multi-Variable Optimization

📖When Goal Seek Isn't Enough

Solver is the advanced version of Goal Seek. Where Goal Seek only changes ONE variable with NO limits, Solver can change multiple input variables simultaneously while respecting strict constraints you define (e.g. "Don't let margin drop below 20%").

📝 Worked Example

Complex L&T Target Optimization

Scenario: Management still wants ₹1,000 Cr Net Income. However, the sales team insists Revenue Growth cannot mathematically exceed 8%. You must hit the ₹1,000 Cr target by altering both Revenue Growth AND modifying the Fixed Operating Expenses (which you plan to cut).

Question: How do you configure Solver to hit the ₹1,000 Cr target by changing two variables, given the firm constraint that Revenue Growth must cap at 8%?
1
Activate Solver
Solver is hidden by default. Go to File → Options → Add-ins → Manage Excel Add-ins → Go. Check the box for Solver Add-in. It will now appear on your Data tab.
2
Set the Objective
Set Objective: Select the Net Income cell (B15)
To: Select Value of and type 1000.
3
Define the Variables & Constraints
By Changing Variable Cells: Select both the Revenue Growth cell (B3) and Fixed OpEx cell (B5) separated by a comma.
Subject to the Constraints: Click Add. Select the Revenue Growth cell (B3), choose <=, and type 8%.
Result: Solver runs complex algorithms to find that if Growth maxes at 8.0%, Fixed OpEx must be slashed exactly to ₹1,811.33 for the model to balance at ₹1,000 Cr Net Income!
💡Practical Applications

Break-even analysis: What revenue growth gets NI to $0?
Target capital structure: What debt/equity mix achieves a target D/E ratio?
Optimal pricing: What price maximizes total profit given demand elasticity?
Debt capacity: How much debt can the company take on before interest coverage falls below 2.0×?

← Previous Section Next: Sensitivity Dashboard →