Lesson ProgressPhase 4 of 6
Phase 4Independent Practice
Independent Practice: Data Tables: Sensitivity Analysis
Build the Sensitivity Matrix in your Excel workbook.
🚀 Phase 4: Independent Practice
Open Your CVP Workbook
The Data Table builds on your Lesson 5 CVP model. That model calculates profit based on price, volume, fixed costs, and variable cost. We'll now use Data Tables to test many price/volume combinations at once.
- Open your PriceLab_GOALSEEK.xlsx workbook from Lesson 5.
- Verify your CVP model is working (Profit should calculate correctly when you change Price or Volume).
- Save As to create PriceLab_DataTables.xlsx so your Lesson 5 work stays intact.
Build Sprint: Sensitivity Analysis
You have the CVP engine. Now you'll build the Sensitivity Matrix—a tool that shows Sarah (and her investors) every possible profit outcome across a range of prices and volumes.
Reference Model — Your CVP Engine
Your existing CVP model should look something like this. Verify these cells exist and calculate correctly:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | PriceLab CVP Model | |||
| 2 | ||||
| 3 | Input Variables | Values | ||
| 4 | Selling Price | 1350 | $/project | |
| 5 | Variable Cost | 880 | $/project | |
| 6 | Fixed Costs | 12000 | $/month | |
| 7 | ||||
| 8 | Calculated Results | Formulas | ||
| 9 | Contribution Margin | 470 | Price - VC | |
| 10 | Break-Even Units | 1 | units | |
| 11 | Target Profit | 15000 | $ | |
| 12 | Target Units | 0.34814814814814815 | units |
Build Step 1: One-Variable Data Table (Price Sensitivity)
This table shows profit at different prices while holding volume constant.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | One-Variable Data Table: Price Sensitivity | ||||||
| 2 | |||||||
| 3 | Formula Cell: | Profit Formula | -892000 | ||||
| 4 | |||||||
| 5 | Price → | 1000 | 1100 | 1200 | 1300 | 1400 | 1500 |
| 6 | Profit ↓ | -7200 | -1200 | 4800 | 10800 | 16800 | 22800 |
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 |
Excel Instructions:
- In a row above, enter your price range (e.g., $1,000 to $1,600 in increments of $100)
- In the cell immediately to the left of the first price, enter the profit formula = (B4-880)*B5 - 12000 (or reference your Profit cell)
- Select the range including the formula and all prices
- Go to Data → What-If Analysis → Data Table
- For Column Input Cell, select your Price input cell (e.g., B4)
- Click OK—Excel fills in all profit values
Build Step 2: Two-Variable Data Table (Price × Volume Matrix)
This matrix shows profit for every combination of price AND volume.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Two-Variable Data Table: Price × Volume Matrix | |||||
| 2 | Price → | |||||
| 3 | 1000 | 1200 | 1400 | 1600 | ||
| 4 | Volume | 15 | -4200 | 4800 | 13800 | 22800 |
| 5 | ↓ | 25 | 3800 | 14800 | 25800 | 36800 |
| 6 | 35 | 11800 | 24800 | 37800 | 50800 | |
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Excel Instructions:
- In the first row, enter your price range (across columns)
- In the first column, enter your volume range (down rows)
- In the corner cell (where row and column headers meet), enter the profit formula
- Select the entire range including the corner formula
- Go to Data → What-If Analysis → Data Table
- For Row Input Cell, select your Volume input cell (e.g., B5)
- For Column Input Cell, select your Price input cell (e.g., B4)
- Click OK—Excel fills the entire matrix
Important: Data Table Behavior
- Do not try to edit individual cells in the results table—it's an array formula!
- To delete a Data Table, select the entire range and press Delete.
- If results show #VALUE!, check that your input cell references are correct.
Deliverable: Pricing Risk Analysis
In a text box next to your Data Table, write a brief analysis:
- Best Case: At what price and volume do you maximize profit? What is that profit?
- Break-Even Zone: What price range keeps profit above $0?
- Risk Signal: If volume drops to 15 units, what is the minimum price needed to avoid a loss?
Data Table Build Rubric
| Category | Proficient | Advanced |
|---|---|---|
| One-Variable Table | Price range produces correct profit values | Table is clearly labeled with units and formatted professionally |
| Two-Variable Matrix | Price × Volume matrix calculates all combinations correctly | Matrix uses conditional formatting to highlight profit/loss zones |
| Analysis | Identifies best case and break-even zone | Provides specific pricing recommendations with risk caveats |