Part VII: Advanced Excel

🎯 Learning Objectives

• Understand What-If Analysis and its three tools: Data Tables, Goal Seek, Scenario Manager
• Create one-variable data tables with row input and column input cells
• Build two-variable data tables to test combinations of two inputs
• Apply What-If Analysis to real Indian financial scenarios — EMIs, GST, FD returns
• Set up and interpret sensitivity analysis tables professionally
• Combine data tables with financial functions like PMT, FV, and PV

📖 Theory — What is What-If Analysis?

What-If Analysis is a powerful set of tools in Excel that allows you to explore different outcomes by changing one or more input values in your formulas. Instead of manually editing cells one at a time, you can see the results for multiple input values simultaneously in a structured table.

Think of it like a restaurant menu: instead of ordering one dish at a time to taste it, you get a "tasting plate" with all options at once. What-If Analysis is your tasting plate for numbers.

The Three What-If Analysis Tools

Understanding Data Tables

A Data Table is a range of cells that shows the results of changing one or two variables in a formula. It automatically calculates the output for each value you specify — no need to write multiple formulas.

One-Variable Data Table

A one-variable data table tests how changing one input affects the result. You can arrange it in two ways:

• Column Input: Input values are listed in a column (vertically), and the formula is in the cell one row above and one column to the right of the first input value.
• Row Input: Input values are listed in a row (horizontally), and the formula is in the cell one column to the left and one row below the first input value.

Two-Variable Data Table

A two-variable data table tests how changing two inputs simultaneously affects the result. The formula goes in the top-left corner, one set of values goes across the top row, and another set goes down the left column. Excel fills in the entire grid with results.

📋 Step-by-Step: Creating a One-Variable Data Table

Scenario: EMI at Different Interest Rates

You want to calculate the EMI for a ₹50,00,000 home loan for 20 years at different interest rates from 7% to 10%.

Step 1: Set up your base data in cells:

Step 2: Create the input values list. In cells D3:D10, enter interest rates: 7.0%, 7.5%, 8.0%, 8.5%, 9.0%, 9.5%, 10.0%, 10.5%

Step 3: In cell E2 (one row above, one column right of first input), enter the formula: =B5 (this links to your EMI formula)

Step 4: Select the entire range D2:E10 (input values + formula cell)

Step 5: Go to Data → What-If Analysis → Data Table

Step 6: In the Data Table dialog box:

• Row input cell: Leave blank (we're using column input)
• Column input cell: Click on B3 (the cell containing interest rate)

Step 7: Click OK. Excel fills the table with EMIs for each interest rate!

📋 Step-by-Step: Creating a Two-Variable Data Table

Scenario: EMI Varying Both Interest Rate AND Tenure

Step 1: Use the same base data (Loan = ₹50,00,000, Rate = 8.5%, Tenure = 20 years)

Step 2: In cell D2, enter the formula: =B5

Step 3: In cells E2:I2 (top row), enter tenure values: 10, 15, 20, 25, 30

Step 4: In cells D3:D10 (left column), enter interest rates: 7.0%, 7.5%, 8.0%, 8.5%, 9.0%, 9.5%, 10.0%, 10.5%

Step 5: Select the entire range D2:I10

Step 6: Go to Data → What-If Analysis → Data Table

Step 7: In the dialog box:

• Row input cell: B4 (tenure cell)
• Column input cell: B3 (interest rate cell)

Result: A grid showing EMI for every combination:

✏️ Solved Examples

Example 1: FD Interest Comparison

Compare the maturity amount for a ₹10,00,000 Fixed Deposit across different banks with varying interest rates and tenures.

One-Variable Table: Column input rates: 6%, 6.5%, 7%, 7.5%, 8%, 8.5%

Insight: A mere 2.5% rate difference (6% vs 8.5%) results in ₹1,80,926 more — almost 18% additional return!

Example 2: GST Impact on Product Price

A Jaipur handicraft seller sells products at ₹2,500 base price. How does the final customer price vary with different GST slabs?

Formula used: =B2*(1+D3) where B2=2500 and D3 has GST rates

Example 3: SIP Returns at Different Monthly Amounts

How does your corpus grow if you invest different amounts via SIP for 10 years at 12% annual return?

Example 4: Profit at Different Selling Prices

A Surat textile manufacturer has a cost of ₹350 per meter. Fixed costs are ₹2,00,000/month. Expected sales volume: 2,000 meters. How does profit vary with selling price?

Insight: Break-even at ₹450/meter. Below that, the manufacturer loses money!

Example 5: CBSE Percentage with Different Maths Marks

A student scored: English 85, Hindi 78, Science 92, Social 88. What percentage will they get with different Maths marks?

Example 6: Two-Variable — Rental Yield Analysis

Property value ranges from ₹40L to ₹80L. Monthly rent ranges from ₹15,000 to ₹30,000. Calculate annual rental yield = (Annual Rent / Property Value) × 100

Example 7: Car Loan EMI — Varying Down Payment

Car price: ₹12,00,000. Rate: 9%. Tenure: 5 years. How does EMI change with different down payments?

Example 8: Flipkart Seller — Profit vs Discount %

Product MRP: ₹1,999. Cost: ₹800. Platform fee: 15% of selling price. How does profit change with different discount percentages?

Example 9: Two-Variable — Zomato Delivery Revenue

Revenue per order depends on average order value and number of orders/day. Monthly revenue = Orders/Day × 30 × Commission Rate × Avg Order Value.

Commission rate assumed at 15%.

Example 10: Electricity Bill — Unit Slab Analysis

BSES Delhi charges: 0–200 units = ₹3/unit, 201–400 = ₹4.50/unit, 401–800 = ₹6.50/unit, 800+ = ₹7/unit. Build a data table showing monthly bill for 100 to 1000 units.

Example 11: TCS Employee — Tax Under Old vs New Regime

Salary: ₹12,00,000. Use data table to compare tax payable at different income levels under new regime slabs.

Example 12: Two-Variable — Reliance Retail Franchise Revenue

Monthly revenue depends on footfall and average billing. Revenue = Footfall × Conversion Rate × Avg Bill.

Conversion rate assumed at 30%.

Example 13: PPF Maturity Value Over Different Years

Annual deposit of ₹1,50,000 in PPF at 7.1% interest. Maturity value at different tenures:

Example 14: Petrol Bunk Daily Revenue

Petrol price: ₹105/litre. Daily sales vary. How does daily revenue change?

Example 15: Two-Variable — Wedding Budget Analysis

Total wedding cost depends on guest count and per-plate cost.

📝 Practice Exercises

Exercise 1: Education Loan EMI Table

Create a one-variable data table for an education loan of ₹8,00,000 at rates from 8% to 12% (0.5% increments) for 7 years. Use =PMT(rate/12, years*12, -loan).

Exercise 2: Mutual Fund SIP Comparison

Create a two-variable data table showing the maturity value of a ₹10,000/month SIP with return rates (10%, 12%, 14%, 16%) and tenures (5, 10, 15, 20, 25 years).

Exercise 3: GST Collection Projection

A state's estimated taxable turnover is ₹50,000 crore. Create a data table showing GST collection at rates 5%, 12%, 18%, 28% and compliance rates 60%, 70%, 80%, 90%, 100%.

Exercise 4: Kirana Store Break-Even

A kirana store has fixed costs of ₹45,000/month and average margin of 12%. Create a data table showing profit for monthly sales from ₹2,00,000 to ₹10,00,000 (₹1,00,000 increments).

Exercise 5: Agricultural Yield Revenue

A Punjab farmer grows wheat. Create a two-variable table with yield per acre (15, 20, 25, 30 quintals) and MSP prices (₹2,015, ₹2,125, ₹2,275) to show revenue per acre.

Exercise 6: Tata Motors Showroom

Create a data table for Tata Nexon (₹9,00,000) with down payments from 0% to 40% and interest rates 7%, 8%, 9%, 10% for a 5-year loan. Show EMI for each combination.

Exercise 7: RD Maturity Calculator

Create a one-variable data table for a Recurring Deposit of ₹5,000/month at interest rates from 5% to 8.5% for 5 years.

Exercise 8: Restaurant Revenue Sensitivity

An Udupi restaurant has average bill ₹250 and seats 60 customers. Create a two-variable table varying table turnover rate (2, 3, 4, 5 times) and average bill (₹200, ₹250, ₹300, ₹350).

📋 MCQ Quiz

🎯 Mini Project: Loan EMI Sensitivity Analyzer

🎤 Interview Questions

🎯 Learning Objectives

• Understand the concept of Goal Seek — working backward from a desired result
• Use the Goal Seek dialog box: Set cell, To value, By changing cell
• Apply Goal Seek to financial, academic, and business scenarios
• Understand the limitations of Goal Seek (single variable, iterative solver)
• Combine Goal Seek with formulas like PMT, SUM, AVERAGE, and IF

📖 Theory — What is Goal Seek?

Goal Seek is like asking Excel: "I know what answer I want — now tell me what input I need." While normally you change inputs to see outputs, Goal Seek reverses the process. You specify the desired output, and Excel calculates the required input.

The Three Parameters

How to Access Goal Seek

Navigate to: Data → What-If Analysis → Goal Seek

How Goal Seek Works Internally

Goal Seek uses an iterative algorithm. It starts with the current value in the "By changing cell," then tries different values — adjusting up and down — until it finds a value that makes the formula produce the target result (within a small tolerance). It typically finds the answer in milliseconds.

Limitations of Goal Seek

• Can only change one variable at a time
• The "Set cell" must contain a formula (not a hardcoded value)
• The "By changing cell" must contain a value (not a formula)
• Goal Seek may not find a solution if the relationship is non-monotonic or discontinuous
• For multiple variables or constraints, use Solver instead (Chapter 31)

✏️ Solved Examples

Example 1: Required Marks in Maths for 90% Overall (CBSE)

A Class X student has scored: English 88, Hindi 82, Science 95, Social Science 91. What marks does she need in Maths for 90% overall?

Goal Seek: Set cell = B8, To value = 90, By changing cell = B6

Result: She needs 94 marks in Maths to achieve 90% overall.

Example 2: Break-Even Units for a Chai Stall

A chai stall in Mumbai has fixed costs of ₹15,000/month. Each cup costs ₹8 to make and sells for ₹20. How many cups must be sold to break even?

Goal Seek: Set cell = B8, To value = 0, By changing cell = B5

Result: 1,250 cups/month (approximately 42 cups/day) needed to break even.

Example 3: Loan Amount for ₹25,000 EMI

Car loan: Rate 9.5%, Tenure 5 years. Maximum EMI budget: ₹25,000. Maximum affordable loan?

Goal Seek: Set cell = PMT formula, To value = 25000, By changing cell = Loan Amount

Result: Maximum car loan = ₹12,05,341

Example 4: Required Revenue to Achieve Target Profit

A Bengaluru startup has fixed costs of ₹8,00,000/month and variable cost ratio of 40%. Target profit: ₹5,00,000. What revenue is needed?

Goal Seek: Set cell = B5, To value = 500000, By changing cell = B2

Result: Revenue needed = ₹21,66,667

Example 5: Interest Rate for ₹1 Crore FD Maturity

You invest ₹70,00,000 in an FD for 5 years (quarterly compounding). What interest rate gives ₹1,00,00,000 maturity?

Formula: =FV(B3/4, 5*4, 0, -7000000)

Goal Seek: Set cell = FV formula, To value = 10000000, By changing cell = Rate

Result: Required rate ≈ 7.18%

Example 6: Discount % to Clear Inventory

A retailer has 500 units at ₹1,200 MRP. Target revenue: ₹4,50,000. What discount % is needed?

Goal Seek: Set cell = Revenue formula, To value = 450000, By changing cell = Discount %

Result: Discount = 25% (Selling at ₹900 each)

Example 7: Required Salary Hike for Target Take-Home

Current CTC: ₹6,00,000. Tax: 10%. Deductions: ₹60,000. Target monthly take-home: ₹50,000.

Goal Seek: Set cell = B6, To value = 50000, By changing cell = B2

Result: Required CTC = ₹7,33,333 (22.2% hike needed)

Example 8: Minimum Price for 20% Profit Margin

Cost of goods: ₹5,60,000. Operating expenses: ₹1,40,000. What selling price gives 20% profit margin?

Result: Selling price = ₹8,75,000

Example 9: CIBIL Score Target — Required On-Time Payments

A simplified credit score model: Score = 300 + (On-time payments/Total payments) × 600. Current: 52 on-time out of 70 total. What on-time payment count gives score of 750?

Result: Need 53 on-time payments out of 70 (already close!)

Example 10: Class Average Target

A teacher has 30 students. Current average: 72.5. If 5 students retake the exam, what average do they need to bring class average to 78?

Result: The 5 students need to average 105 — which is impossible (max 100), so the teacher needs to adjust the target or allow more retakes.

Example 11: Manufacturing — Units for Target Revenue

Product sells at ₹450/unit. Target monthly revenue: ₹15,00,000. How many units to sell?

Result: 3,334 units/month

Example 12: SIP Amount for ₹1 Crore Corpus

Expected return: 12% p.a. Duration: 15 years. What monthly SIP creates ₹1 crore?

Formula: =FV(12%/12, 15*12, -B2)

Goal Seek: Set cell = FV, To value = 10000000, By changing cell = SIP amount

Result: Monthly SIP = approximately ₹21,002

📋 MCQ Quiz

🎯 Mini Project: Break-Even Analysis Tool

🎤 Interview Questions