Math for Business Operations

Applied Accounting with Excel

UNIT06 - Lesson 7

Data Tables for Sensitivity Analysis

45 minutes
Lesson Overview

Lesson Focus

Build comprehensive what-if analysis using one- and two-variable data tables

Key Unit Objectives

Enduring Understandings:

  • Pricing decisions require balancing profitability with market competitiveness
  • Cost-Volume-Profit relationships govern business viability and strategic planning
  • Data-driven analysis provides objective foundation for subjective business decisions
  • Sensitivity analysis reveals business vulnerabilities and opportunities

Lesson Activities

Activity 1: The Sensitivity Analysis Need
5 minutes

Why businesses need to understand multiple scenario outcomes

Details:

  • Problem: Business conditions change - costs, volumes, competitive prices
  • Risk: Single-point estimates don't show vulnerability to changes
  • Solution: Data tables show profit sensitivity to multiple variable changes
Activity 2: One-Variable Data Table Construction
20 minutes

Build data tables showing profit sensitivity to single variables

Details:

  • Create data table showing profit at different price levels
  • Build data table showing profit at different volume levels
  • Construct data table showing profit at different variable cost levels
  • Format tables professionally with conditional formatting for visual insights
Data Table Setup Requirements

Proper structure is critical for data table functionality

  • Input values: List possible values for variable being tested
  • Formula reference: Single cell reference to profit calculation
  • Table command: Data > What-If Analysis > Data Table
  • Variable reference: Must match input cell in original model
Activity 3: Two-Variable Data Table Analysis
15 minutes

Advanced sensitivity analysis with two variables simultaneously

Details:

  • Build data table with price on rows and volume on columns
  • Create data table with variable cost on rows and price on columns
  • Use conditional formatting to highlight profitable scenarios
  • Interpret results to identify optimal pricing sweet spots
Activity 4: Sensitivity Insights & Documentation
5 minutes

Extract business insights from sensitivity analysis

Details:

  • Identify price ranges that maintain profitability under various conditions
  • Document which variables have greatest impact on profit sensitivity
  • Note scenarios where competitive pricing might be unprofitable
  • Preview Day 8: Integrating analysis into pricing recommendation
Required Materials
  • Data table construction guides
  • Sensitivity analysis templates
  • Conditional formatting examples
  • Business insight documentation forms
Differentiation Strategies

For Struggling Students

  • • CVP Templates: Pre-built model structures with guided formula entry
  • • Visual Calculators: Step-by-step markup vs. margin calculation guides
  • • Peer Partnerships: Pair with students strong in Excel or business analysis
  • • Alternative Assessment: Focus presentation on business insights rather than technical details

For Advanced Students

  • • Advanced Modeling: Incorporate seasonality and demand elasticity into CVP models
  • • Leadership Roles: Facilitate peer critique sessions and support struggling teams
  • • Extended Analysis: Research actual competitor financial statements for deeper insights
  • • Presentation Enhancement: Create interactive Excel dashboards for town hall debate

For English Language Learners

  • • Business Vocabulary: Glossary of CVP and pricing terms with visual definitions
  • • Formula Explanations: Mathematical concepts explained with universal symbols
  • • Cultural Context: Discussion of pricing practices in different economic systems
  • • Collaborative Support: Mixed-language teams with peer translation assistance