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

🎯 Learning Objectives

• Understand Scenario Manager and its role in decision-making
• Create, edit, and delete scenarios with multiple changing cells
• Switch between scenarios to compare different assumptions
• Generate a Scenario Summary report for side-by-side comparison
• Generate a Scenario PivotTable report for dynamic analysis
• Apply Scenario Manager to Indian business planning contexts

📖 Theory — What is Scenario Manager?

Scenario Manager is a What-If Analysis tool that lets you save multiple sets of input values (called scenarios) and switch between them. Unlike Data Tables (which show all results at once) or Goal Seek (which finds one answer), Scenario Manager lets you name, save, and compare different "what-if" stories.

Key Concepts

How to Access

Data → What-If Analysis → Scenario Manager

When to Use Scenario Manager vs Other Tools

✏️ Solved Examples

Example 1: School Fest Budget Planning

A college fest has three budget scenarios:

Example 2: Startup Funding Scenarios

A Bengaluru edtech startup models its runway:

Example 3: Farmer's Crop Planning (Rabi Season)

A farmer in Madhya Pradesh models income from wheat vs chickpea:

Example 4: Infosys Project Staffing

An Infosys project manager models team structure scenarios for a client project:

Example 5: Personal Finance — Monthly Budget

Example 6: Café Revenue Projection

A Chennai café models daily revenue under different scenarios:

Example 7: Solar Panel ROI

Example 8–12: Additional Business Scenarios

Ex 8 — Gym Membership: Compare monthly (₹2,500), quarterly (₹6,000), annual (₹20,000) memberships for 100/200/300 members.

Ex 9 — YouTube Channel: Ad revenue scenarios with 10K/50K/200K monthly views at different CPM rates.

Ex 10 — Pharmacy: Revenue with 50/100/200 prescriptions/day at different average bill amounts.

Ex 11 — Wedding Planning: Budget scenarios: Economy (₹5L), Standard (₹15L), Luxury (₹40L).

Ex 12 — Real Estate Investment: Rent yield vs capital appreciation for 1BHK/2BHK/3BHK in Mumbai.

📋 MCQ Quiz

🎯 Mini Project: Business Investment Scenario Analyzer

🎤 Interview Questions

🎯 Learning Objectives

• Master advanced one-input and two-input data table techniques
• Combine data tables with financial functions: PMT, FV, PV, RATE, NPER
• Understand and apply each financial function with complete syntax and examples
• Create professional sensitivity analysis tables for financial models
• Build multi-function sensitivity tables (EMI, Total Interest, Total Payment in one view)

📖 Theory — Financial Functions Deep Dive

Before building advanced data tables, you need to master the five core financial functions. These functions are interconnected — they all deal with the Time Value of Money (TVM) concept: money today is worth more than the same amount in the future.

PMT — 3 Worked Examples

Example A: Home loan of ₹40,00,000 at 8.5% for 20 years

=PMT(8.5%/12, 240, -4000000)

Example B: Car loan of ₹7,00,000 at 9% for 5 years

=PMT(9%/12, 60, -700000)

Example C: Personal loan of ₹3,00,000 at 14% for 3 years

=PMT(14%/12, 36, -300000)

FV — 3 Worked Examples

Example A: SIP of ₹10,000/month for 15 years at 12% annual return

=FV(12%/12, 180, -10000)

Example B: Lump sum ₹5,00,000 invested for 10 years at 10%

=FV(10%/12, 120, 0, -500000)

Example C: PPF — ₹1,50,000/year for 15 years at 7.1% (annual)

=FV(7.1%, 15, -150000)

PV — 3 Worked Examples

Example A: How much to invest today to get ₹1 crore in 20 years at 10%?

=PV(10%/12, 240, 0, -10000000)

Example B: What's the present value of receiving ₹25,000/month pension for 20 years at 7%?

=PV(7%/12, 240, -25000)

Example C: Annuity value — ₹50,000/quarter for 10 years at 8%

=PV(8%/4, 40, -50000)

✏️ Solved Examples — Advanced Data Tables

Example 1: SIP Maturity — Amount vs Return Rate (Two-Variable)

Monthly SIP amounts across top, annual return rates down left side. Duration: 15 years.

Setup: Formula in top-left: =FV(B3/12, 15*12, -B2). Row input = B2 (SIP amount), Column input = B3 (rate).

Example 2: EMI + Total Interest Sensitivity

For a ₹50L home loan, show both EMI and total interest paid at different rates and tenures.

Table A — Monthly EMI:

Table B — Total Interest Paid (₹ lakhs):

Key Insight: At 9.5% for 30 years, you pay ₹101.47L interest on a ₹50L loan — more than double the principal!

Example 3: Recurring Deposit Maturity Table

Monthly deposit: ₹10,000. How does maturity value change with rate and tenure?

Example 4: PV Sensitivity — Retirement Planning

How much to invest today (lump sum) to get ₹1 crore in N years at R% return?

Insight: With 12% return and 25-year horizon, just ₹5.88L today becomes ₹1 crore!

Example 5: NPER Table — How Long to Reach ₹50 Lakhs

Starting with ₹0, investing monthly. How many years to reach ₹50,00,000?

Example 6: Personal Loan — Rate vs EMI Table

Loan: ₹5,00,000, Tenure: 4 years. EMI at different interest rates:

Example 7: NPS Contribution Sensitivity

Monthly NPS contribution varying from ₹5,000 to ₹25,000. Expected return: 10%. Duration: 25 years.

Example 8: Two-Variable — FD vs Debt Mutual Fund

Compare post-tax returns for ₹10,00,000 investment. FD taxed at slab rate, Debt MF with indexation.

Examples 9–15: Quick Reference

Ex 9: Education loan — RATE table showing effective interest for different EMI amounts on ₹10L loan over 7 years.

Ex 10: Sukanya Samriddhi Yojana — FV table for annual deposits (₹50K to ₹1.5L) at 8% for 21 years.

Ex 11: Two-variable — Car loan EMI with varying down payment % and interest rate.

Ex 12: NPER table — How many months to pay off ₹2L credit card debt at 24% to 42% APR with different monthly payments.

Ex 13: Corporate bond — PV of ₹1,00,000 face value bond with different coupon rates and yields to maturity.

Ex 14: Two-variable — Monthly SIP needed for ₹1 Cr corpus at different return rates and different time horizons.

Ex 15: Rent vs Buy — EMI + maintenance vs Rent + Investment return comparison over 10/15/20 years.

📋 MCQ Quiz

🎯 Mini Project: SIP Investment Return Calculator

🎤 Interview Questions

🎯 Learning Objectives

• Understand what Solver is and how it differs from Goal Seek
• Enable the Solver Add-in in Excel
• Set up Solver problems: Objective cell, Variable cells, Constraints
• Understand the three solving methods: Simplex LP, GRG Nonlinear, Evolutionary
• Interpret Solver reports: Answer, Sensitivity, Limits
• Solve real-world optimization problems: production mix, resource allocation, portfolio optimization

📖 Theory — What is Solver?

Solver is an Excel add-in that finds the optimal value (maximum, minimum, or specific target) of a formula by changing multiple variables subject to constraints. Think of it as Goal Seek on steroids — while Goal Seek changes one cell to find one value, Solver can change multiple cells simultaneously and respect constraints (rules that must be followed).

Solver vs Goal Seek Comparison

Step 1: Enabling Solver

1. Go to File → Options → Add-ins
2. At the bottom, select "Excel Add-ins" from the Manage dropdown
3. Click Go...
4. Check Solver Add-in
5. Click OK

Solver will now appear in the Data tab, in the Analyze group.

Solver Dialog Box Components

The Three Solving Methods

✏️ Solved Examples

Example 1: Diet Problem (Minimum Cost Nutrition)

A hostel mess needs to plan meals meeting minimum nutrition at lowest cost:

Constraints: Min 50g protein, min 200g carbs, max 30g fat per person per day

Objective: Minimize total food cost

Solver finds: Optimal mix costing approximately ₹85 per person per day

Example 2: Investment Portfolio Optimization

An investor has ₹20,00,000 to allocate across 4 assets:

Constraints:

• Total allocation = 100%
• Each asset: minimum 10%, maximum 40%
• Bonds: minimum 20% (risk management)
• Maximum portfolio risk: 15%

Objective: Maximize expected return

Solver Result: Large Cap 30%, Mid Cap 30%, Bonds 20%, Gold 20% — Expected return: 11.6%

Example 3: Logistics — Minimize Transportation Cost

A company has 2 warehouses and 3 retail stores. Minimize shipping costs:

Variable cells: Quantities shipped on each route (6 cells)

Constraints: Supply limits, demand requirements, non-negativity

Objective: Minimize total shipping cost

Solver Result: Minimum cost = ₹17,750

Example 4: Staff Scheduling

A Noida call center needs minimum staff per shift:

Cost per person: Morning ₹800, Afternoon ₹900, Night ₹1,200

Objective: Minimize total daily staffing cost while meeting minimum requirements

Result: Minimum cost = ₹15 × 800 + 20 × 900 + 10 × 1200 = ₹42,000

Example 5: Maximize Sales Revenue with Ad Budget

A D2C brand has ₹5,00,000 ad budget across platforms:

Constraint: Total budget ≤ ₹5,00,000; each platform has max limit

Objective: Maximize total revenue

Example 6: Crop Planning for Maximum Income

A farmer has 50 acres, ₹3,00,000 capital, and 2,000 labor hours:

Objective: Maximize total profit from all crops

Example 7: Minimize Production Cost

A paint company makes 3 types of paint. Minimize total cost while meeting daily order commitments of 200L interior, 150L exterior, 100L weatherproof. Each type has different raw material requirements and costs.

Example 8: School Timetable Optimization

A CBSE school needs to schedule 8 subjects across 40 periods/week for 6 sections. Each teacher has max period limits. Minimize conflicts using Solver with Evolutionary method.

Example 9: Bakery Production Plan

A Pune bakery makes cakes, pastries, and breads. Each uses oven time, flour, and sugar differently. Oven capacity: 8 hours/day. Maximize daily profit. Use integer constraints (whole items only).

Example 10: Mutual Fund Portfolio — Risk-Return Optimization

Given 5 mutual funds with historical returns and standard deviations, find the allocation that achieves ≥12% return with minimum risk (standard deviation). This is the classic Markowitz optimization.

Example 11: E-commerce Inventory Optimization

An Amazon seller has ₹10,00,000 to invest in 5 product categories. Each has different ROI, storage cost, and minimum order requirements. Maximize total profit.

Example 12: Trucking Route Optimization

A Delhivery logistics hub needs to assign 10 trucks to 10 routes. Each truck-route combination has different costs. Find the assignment that minimizes total delivery cost (Hungarian method via Solver).

Example 13: Wedding Caterer — Menu Optimization

Plan a wedding menu serving 500 guests with budget ₹4,00,000. Multiple dishes available with different costs and satisfaction ratings. Maximize total guest satisfaction within budget.

Example 14: Factory Shift Planning

A Maruti factory has 3 shifts across 7 days. Each shift needs minimum workers. Workers can do max 5 shifts/week. Minimize total workers hired.

Example 15: Solar Panel Installation — ROI Maximization

Given roof area of 1,000 sq ft, budget ₹8,00,000, and two panel types (300W and 500W with different costs and efficiency), maximize annual energy generation.

📋 MCQ Quiz

🎯 Mini Project: Production Mix Optimization

🎤 Interview Questions