Business Success Skills

How To Set Up A Google Sheets Template To Calculate Product Costs, Profitability, And Other Key Metrics For Your Amazon FBA Business




Note: You can copy this structure directly into Google Sheets or Excel.

1. Template Overview

The template includes the following key sections: 1. Input Variables: Enter product details (e.g., selling price, cost, fees).
2. Calculated Metrics: Automatic calculations for total costs, net profit, profit margin, ROI, and breakeven price.
3. Scenario Analysis: Test different scenarios (e.g., changing PPC cost or price).

2. Google Sheets Template Structure

A. Input Section

| Column | Description |
|----------------------|----------------------------------------------|
| Product Name | Name of the product. |
| Selling Price ($) | The price at which the product is sold. |
| Product Cost (COGS) ($)| Supplier cost per unit. |
| FBA Fee ($) | Fulfillment fee charged by Amazon. |
| Referral Fee (%) | Typically 15% for most categories. |
| PPC Cost ($) | Average ad spend per unit. |
| Inbound Shipping ($) | Cost of shipping the product to Amazon. |

B. Calculated Metrics

| Column | Formula | Description |
|-------------------------|-------------------------------------------------------------|-------------------------------------------|
| Referral Fee ($) | =B2*C2 | Selling price × Referral Fee %. |
| Total Costs ($) | =C2+D2+E2+F2+G2 | Sum of all costs. |
| Net Profit ($) | =B2-H2 | Selling Price - Total Costs. |
| Profit Margin (%) | =(I2/B2)*100 | Net Profit ÷ Selling Price × 100. |
| ROI (%) | =(I2/C2)*100 | Net Profit ÷ Product Cost × 100. |
| Breakeven Price ($) | =(C2+D2+F2+G2)/(1-E2) | Total Costs ÷ (1 - Referral Fee %). |

C. Scenario Analysis

| Column | Description |
|-------------------------|----------------------------------------------|
| Scenario Selling Price | Test different prices (e.g., discounts). |
| Scenario PPC Cost | Adjust advertising spend for comparison. |
| Scenario Net Profit | Calculate profitability under new scenarios.|

3. Pre-Made Template Example

Here’s an example template with sample data:

| Input Variables | Value | | Calculated Metrics | Formula |
|-------------------------|-------------|-----------------------------|-------------------------|--------------------------|
| Product Name | Stainless Water Bottle | | Referral Fee ($) | =B2*C2 |
| Selling Price ($) | 30 | | Total Costs ($) | =C2+D2+E2+F2+G2 |
| Product Cost ($) | 8 | | Net Profit ($) | =B2-H2 |
| FBA Fee ($) | 4.50 | | Profit Margin (%) | =(I2/B2)*100 |
| Referral Fee (%) | 15% | | ROI (%) | =(I2/C2)*100 |
| PPC Cost ($) | 3 | | Breakeven Price ($) | =(C2+D2+F2+G2)/(1-E2) |
| Inbound Shipping ($) | 1.50 | | | |

Output Example

| Calculated Metrics | Value |
|---------------------------|------------|
| Referral Fee ($) | 4.50 |
| Total Costs ($) | 21.50 |
| Net Profit ($) | 8.50 |
| Profit Margin (%) | 28.33% |
| ROI (%) | 106.25% |
| Breakeven Price ($) | 25.29 |

Scenario Analysis

| Scenario Selling Price ($) | Scenario PPC Cost ($) | Scenario Net Profit ($) |
|--------------------------------|---------------------------|-----------------------------|
| 28 | 2 | 7.50 |
| 35 | 3.50 | 10.50 |
| 25 | 4 | 5.50 |

4. Setting Up Conditional Formatting

  1. Profit Margin (%): Highlight in green if above 30% and red if below 20%.
  2. Go to Format Conditional Formatting in Google Sheets.

  3. Add a rule: >30% = Green; <20% = Red.

  4. Net Profit ($): Highlight in yellow if below $5/unit.

5. Downloadable Template

You can copy this template into your Google Sheets by clicking the link below:

Google Sheets Profitability Calculator Template (Make a copy to edit your version)

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