Lecture Notes: Sensitivity Tables and Business Scenario Analysis

Introduction

• Presenter: Kenji
• Topic: Utilizing Excel tools for business scenario analysis, specifically:
  • Sensitivity Tables
  • Scenario Analysis
  • Goal Seek & Solver
• Example Used: Lemonade Stand
• Resources: Excel file available for download

Goal Seek

• Purpose: Determine the required input for a desired result.
• Example Objective: Achieve $100,000 net income for the lemonade stand.
• Steps:
  1. Fill in income statement lines:
     • Revenue: Price * Quantity
     • COGS (Cost of Goods Sold): Units Sold * Cost Per Unit
     • Gross Profit: Sum of Revenue and COGS
     • G&A (General & Administrative): Fixed costs (e.g., office rent, salaries)
     • Earnings Before Tax: Sum of Gross Profit and G&A
     • Taxes: Conditional based on positive earnings.
  2. Goal Seek:
     • Net Income Target: $100,000
     • Input Variable: Units Sold
     • Process: Data > What-If Analysis > Goal Seek

Solver

• Purpose: Optimize multiple variables within constraints to achieve desired outcome.
• Example Objective: Limit Units Sold to 25,000 and adjust price.
• Steps:
  1. Enable Solver: File > Options > Add-ins > Solver Add-in > OK
  2. Set Constraints:
     • Units Sold: ≤ 25,000
     • Price: ≤ $9.99
  3. Solver Setup:
     • Set Objective: Net Income
     • Changing Variables: Units Sold and Price
     • Process: Data > Solver > Solve

Sensitivity Analysis

• Purpose: Analyze how variations in price and quantity affect net income.
• Steps:
  1. Prepare Data:
     • Enter Price and Quantity Ranges.
  2. Link to Net Income cell.
  3. Data Table Tool:
     • Data > What-If Analysis > Data Table
     • Row Input: Quantity
     • Column Input: Price
  4. Format for readability (e.g., color-coded cells).

Scenario Analysis

• Purpose: Forecast multiple potential future outcomes (best, base, worst).
• Steps:
  1. Create different scenarios with varying revenues and costs.
  2. Use Choose formula to dynamically select scenario-based data.
  3. Data Validation: Create a drop-down for scenario selection.
  4. Trace Precedents Tool: Visualize formula dependencies if needed.

Summary

• Excel tools like Goal Seek, Solver, Sensitivity Analysis, and Scenario Analysis can aid in financial modeling and decision-making.
• Next Steps:
  • Check out more Excel resources and videos linked in the presentation.