Complete TargetProfit and PriceSensitivity sheets using your group's data.
Build Your Target Profit and Sensitivity Analysis
In Phase 1, you reopened your workbook and reviewed what you completed in Lesson 08. Now you will build the TargetProfit sheet to solve for your profit target, and the PriceSensitivity sheet to test how profit changes as you adjust price. These two sheets form the core of your pricing recommendation evidence.
Your business has a target profit from your scenario card. This sheet answers the question: "What price or volume do we need to hit that target?"
Step 1: Enter Your Known Values
- Fixed Costs: Copy the total from your CostSetup sheet
- Variable Cost per Unit: Copy from your CostSetup sheet
- Target Profit: From your group scenario card
- Capacity: Maximum units you can produce per month
Step 2: Calculate Required Contribution Margin
Use this formula: Required CM = (Fixed Costs + Target Profit) ÷ Units
Or solve for units: Required Units = (Fixed Costs + Target Profit) ÷ (Price - Variable Cost per Unit)
Step 3: Test Different Price-Volume Combinations
Try at least 3 different prices. For each price, calculate how many units you need to sell to hit your target profit. Flag any combination that exceeds your capacity.
Common mistake: Forgetting to add fixed costs to target profit before dividing. Your contribution margin must cover BOTH fixed costs AND your target profit.
This sheet shows how profit changes when you adjust price while holding volume constant. It helps you understand which prices are most sensitive to change.
Step 1: Set Your Base Case
- Choose a starting price from your PriceOptions sheet
- Use your expected volume (units you plan to sell)
- Calculate baseline profit
Step 2: Test Price Changes
Test at least 5 different prices around your recommended price:
- Two prices below your recommendation (e.g., -$2, -$5)
- Your recommended price
- Two prices above your recommendation (e.g., +$2, +$5)
Step 3: Calculate Profit for Each Price
Use this formula for each row: Profit = (Price × Units) - (Fixed Costs + Variable Cost per Unit × Units)
Step 4: Identify the Sweet Spot
Which price gives you the best balance of profit and risk? Note where profit drops sharply if you go too low or too high.
Check your work: If profit goes UP when you LOWER the price, something is wrong. Lower prices should generally mean lower profit (unless you sell significantly more units).
- TargetProfit sheet has at least 3 price-volume combinations tested
- TargetProfit sheet clearly shows which combinations hit your target profit
- PriceSensitivity sheet has at least 5 different prices tested
- PriceSensitivity sheet shows profit for each price with clear calculations
- Both sheets have clean labels and organized layout
These two sheets will provide the evidence for your recommendation statement. When you write your claim in Phase 4, you will cite numbers from these sheets:
- The exact price you recommend
- The projected profit at that price
- The break-even units or capacity utilization
- How sensitive profit is to price changes (from PriceSensitivity)