Lesson ProgressPhase 4 of 6
Phase 4Independent Practice
Independent Practice: Goal Seek: Hit Your Profit Target
Build the CVP model and use Goal Seek in Excel.
🚀 Phase 4: Independent Practice
Build Sprint: CVP Workbook with Goal Seek
It's time to build the CVP model from scratch and use Goal Seek to answer the investor's question. By the end of this sprint, you'll have a workbook that can find any target price in seconds.
Reference Model — Sarah's Completed CVP Workbook
This is the exact structure you'll build. The yellow cells are inputs, the green cells are formulas.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sarah's CVP Model - Target Profit Analysis | |||
| 2 | ||||
| 3 | INPUTS | |||
| 4 | Price per Project: | $1,350 | ||
| 5 | Projects (Volume): | 25 | ||
| 6 | Fixed Costs: | $12,000 | ||
| 7 | Variable Cost/Project: | $880 | ||
| 8 | ||||
| 9 | CALCULATIONS | |||
| 10 | Contribution Margin: | #VALUE! | ||
| 11 | Total Revenue: | #VALUE! | ||
| 12 | Total Variable Cost: | #VALUE! | ||
| 13 | Total Profit: | #VALUE! | ||
| 14 | ||||
| 15 | GOAL SEEK RESULT | |||
| 16 | Target Profit: | $15,000 | ||
| 17 | Required Price: | $1,388 |
Build Sequence — Excel Instructions
Step 1: Set Up the CVP Model
- Open a new Excel workbook, rename Sheet1 to "CVP Model"
- Create the INPUTS section with labels and input cells for Price, Volume, Fixed Costs, Variable Cost
- Create the CALCULATIONS section with formulas for Contribution Margin, Revenue, Variable Costs, and Total Profit
- Verify the Profit formula works: it should calculate correctly when you change Price or Volume
Step 2: Run Goal Seek
- With the workbook open, go to Data → What-If Analysis → Goal Seek
- Set Cell: Click your Total Profit cell (the formula cell, not the label)
- To Value: Type
15000 - By Changing Cell: Click your Price input cell
- Click OK and watch Excel find the answer
Step 3: Test Different Scenarios
- Run Goal Seek again to find the price needed for different target profits: $10,000, $20,000
- Try changing the approach: keep Price at $1,350, find the volume needed for $15,000 profit
- Document each scenario in your notes with the target, the result, and the business meaning
Verification Checkpoints
Before you move on, verify each of these:
- Changing the Price input updates the Profit formula automatically
- Goal Seek successfully changes the Price to approximately $1,388
- The resulting price makes mathematical sense (higher than variable cost + proportional to target)
- You can run Goal Seek again with a different target and get a new answer
Workbook Build Rubric
| Category | Emerging | Proficient | Advanced |
|---|---|---|---|
| CVP Model Setup | Formulas don't calculate correctly. | All formulas work, Profit updates with inputs. | Clear labeling, organized sections, easy to read. |
| Goal Seek Execution | Can't find the tool or wrong cells selected. | Goal Seek runs and finds correct price. | Tests multiple scenarios and documents results. |
| Business Interpretation | Can't explain what the result means. | Explains the price in business context. | Connects results to investor scenario and pricing strategy. |