Excel What-If Analysis Class Exercises
1. Goal Seek Exercise –Sales
Scenario:
You are running a small T-shirt business. You want to calculate how many T-shirts you need
to sell to achieve a specific profit goal.
Instructions:
1. Open the "Goal Seek - Break Even" sheet in Excel.
2. Use the following formula setup:
- Total Revenue = Units Sold × Selling Price
- Total Cost = Fixed Cost + (Units Sold × Variable Cost)
- Profit = Total Revenue – Total Cost
3. Use Goal Seek to set the profit to 3,000 by changing the Units Sold.
4. Go to: Data → What-If Analysis → Goal Seek
- Set Cell: Profit
- To Value: 3000
- By Changing Cell: Units Sold
2. Scenario Manager Exercise – Event Budget Planning
Scenario:
You are organizing a student conference. Compare total cost outcomes under three
different scenarios (Best, Most Likely, Worst).
Instructions:
1. Open the "Scenario Manager - Event" sheet.
2. Create three scenarios by changing:
- Venue Cost
- Catering Cost per Person
- Sponsorship Amount
3. Calculate:
- Total Catering Cost = Attendees × Catering Cost
- Total Expense = Venue + Catering
- Net Cost = Total Expense – Sponsorship
4. Use: Data → What-If Analysis → Scenario Manager → Add scenarios → Summarize.
Scenarios to Define:
1. Best Case
o Venue: 20,000
o Catering: 500
o Sponsorship: 15,000
2. Most Likely
o Venue: 30,000
o Catering: 600
o Sponsorship: 10,000
3. Worst Case
o Venue: 40,000
o Catering: 700
o Sponsorship: 5,000
3. Data Table Exercise – Loan Repayment Sensitivity
You want to take out a loan of 100,000 BDT. You're comparing monthly payments across
various interest rates and loan durations.
Instructions:
1. Open "Data Table - Loan" sheet.
Input Value
Loan Amount 100,000
Interest Rate (annual) Variable
Duration (years) Variable
Payments per Year 12
Monthly Payment =PMT(Interest Rate/12, Years×12, –Loan Amount)
2. Create a Data Table:
One-Variable Table (Interest Rate
Interest Rate Monthly Payment
5%
6%
7%
8%
Interest Rate Monthly Payment
9%
3. Observe how the payment changes with interest rate (5% to 9%) for 5 to 15 years loan.
For this create a Two-Variable Table (Interest Rate vs Loan Duration)
4. Select the range including the formula and interest rates.
5. Use: Data → What-If Analysis → Data Table → Column Input Cell = Interest Rate Cell, Row
Input cell = Duration
4. Mini Project – Startup Planning
You are planning a startup that sells eco-friendly reusable bottles. You want to:
Analyze profit under different cost/price scenarios
Calculate the number of units needed to hit a profit target
Check sensitivity to marketing cost and conversion rate
Fixed Costs 50,000
Variable Cost per Unit 120
Selling Price per Unit 250
Marketing Budget Variable
Conversion Rate Variable
Customers (number) =Marketing Budget × Conversion Rate
Total Revenue =Customers × Price
Total Cost =Fixed + (Customers × Variable)+ Marketing Budget
Profit =Revenue – Cost
Tasks:
- Use Goal Seek to calculate the number of customers required for 80,000 profit.
- Use Scenario Manager to simulate Optimistic, Moderate, and Pessimistic scenarios.
- Use a Two-Variable Data Table to test sensitivity of profit to changes in marketing budget
and conversion rate.
