Business Success Skills

How To Set Up Scenario Analysis And Calculate The Breakeven Price In Your Amazon FBA Profitability Calculator




An Amazon FBA profitability calculator will help you simulate changes (e.g., price, costs, PPC spend) and determine the minimum price you need to charge to cover your costs.


1. Setting Up Scenario Analysis

Step 1: Create a Dedicated "Scenario" Section

Add a section below your main table or on a separate tab labeled Scenario Analysis.

| Variable | Original Value | Scenario 1 | Scenario 2 | Scenario 3 |
|---------------------|--------------------|----------------|----------------|----------------|
| Selling Price ($) | 30 | 28 | 35 | 25 |
| Product Cost ($) | 8 | 8 | 8 | 9 |
| FBA Fee ($) | 4.50 | 4.50 | 4.50 | 4.75 |
| Referral Fee (%) | 15% | 15% | 15% | 15% |
| PPC Cost ($) | 3 | 2.50 | 3.50 | 4 |


Step 2: Link Variables to Your Profitability Formulas

  1. Use cell references to connect the variables from your scenario table to the profitability calculator. For example:
  2. Link Selling Price in the main calculator to the corresponding cell in the scenario table: excel =ScenarioAnalysis!B2

  3. Repeat for Product Cost, FBA Fee, Referral Fee, and PPC Cost.


Step 3: Calculate Outputs for Each Scenario

Add formulas to calculate the following outputs for each scenario:

Net Profit

[ {Net Profit} = {Selling Price} - ({Product Cost} + {FBA Fee} + {Referral Fee} + {PPC Cost}) ]

Profit Margin

[ {Profit Margin (\%)} = \frac{{Net Profit}} / {{Selling Price}} * 100 ]

ROI

[ {ROI (\%)} = \frac{{Net Profit}} / {{Product Cost}} * 100 ]

| Scenario | Net Profit ($) | Profit Margin (%) | ROI (%) |
|--------------|--------------------|-----------------------|-------------|
| Scenario 1 | $8.50 | 30.36% | 106.25% |
| Scenario 2 | $14.00 | 40.00% | 175.00% |
| Scenario 3 | $6.75 | 27.00% | 75.00% |


Step 4: Visualize the Scenarios

Create a Clustered Bar Chart or Table Chart: - X-Axis: Scenarios (Scenario 1, Scenario 2, etc.).
- Y-Axis: Net Profit or Profit Margin.



2. Calculating the Breakeven Price

Step 1: Breakeven Formula

To determine the breakeven price (minimum price to cover costs):

[ {Breakeven Price} = {Total Costs} ]

Where:
[ {Total Costs} = {Product Cost} + {FBA Fee} + {Referral Fee} + {PPC Cost} ]

Referral Fee as a Percentage

Referral Fee is 15% of the Selling Price, so: [ {Referral Fee} = {Breakeven Price} * 0.15 ]

This makes the formula: [ {Breakeven Price} = \frac{{Product Cost} + {FBA Fee} + {PPC Cost}}{1 - {Referral Fee (\%)}} ]


Step 2: Example Calculation

Given:

  • Product Cost = $8
  • FBA Fee = $4.50
  • PPC Cost = $3
  • Referral Fee = 15%

Calculation:

[ {Breakeven Price} = \frac{8 + 4.50 + 3}{1 - 0.15} ] [ {Breakeven Price} = \frac{15.50}{0.85} = 18.24 ]

Your breakeven price is $18.24.


Step 3: Automate Breakeven Price in Your Spreadsheet

Add a column labeled Breakeven Price and enter the formula for automatic calculation:

Excel Formula for Breakeven Price: excel =(C2 + D2 + F2) / (1 - 0.15) Where:
- C2 = Product Cost.
- D2 = FBA Fee.
- F2 = PPC Cost.


Step 4: Compare Breakeven Price to Current Selling Price

Add conditional formatting:
- Highlight rows where Selling Price < Breakeven Price in red.
- This flags products that are losing money.



3. Final Setup Example

| Product | Selling Price | Product Cost | FBA Fee | PPC Cost | Referral Fee | Total Costs | Net Profit | Profit Margin | ROI | Breakeven Price |
|---------------------|-------------------|------------------|-------------|--------------|------------------|-----------------|----------------|-------------------|---------|---------------------|
| Stainless Bottle | $30 | $8 | $4.50 | $3 | $4.50 | $20 | $10 | 33.33% | 125% | $18.24 |
| Yoga Mat | $25 | $6 | $4.00 | $2 | $3.75 | $15.75 | $9.25 | 37.00% | 154.17% | $18.53 |
| Phone Stand | $15 | $4 | $3.50 | $1.50 | $2.25 | $11.25 | $3.75 | 25.00% | 93.75% | $13.24 |


4. Key Takeaways

  • Scenario Analysis: Test different prices, costs, and advertising spends to optimize profitability.
  • Breakeven Price: Helps ensure your selling price is always profitable.
  • Visualization: Use charts to compare scenarios at a glance.

If you liked this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy