How to Build an Amazon 3P Seller Profit Calculator

What to Think About As You Prepare to Create Your Profit Calculator

Creating a robust Amazon profit calculator is essential for sellers aiming to understand and enhance their profitability on the platform. This tool should encompass various data points and calculations to provide a comprehensive view of your financial performance at the SKU level. Here's how to build a Amazon profit calculator that delivers real insights:

• Incorporate Item-Level Transaction and Fee Data: Start by integrating detailed transaction data from Amazon, which includes item-level fees, sales revenue, and other related costs. This data is critical for calculating the direct costs associated with each SKU, providing a foundation for your profitability analysis.

• Develop a Mental Model for Allocating Non-Item-Level Costs: Not all costs can be directly attributed to specific SKUs, such as marketing expenses or general administrative costs. Create a model for appropriately allocating these overhead costs to your products. This could be based on each SKU's revenue contribution, volume sold, or other relevant factors, ensuring a fair distribution of general expenses.

• Input Your Own Landed Product Costs: Factor in the actual costs of getting your products ready for sale on Amazon. This includes manufacturing costs, shipping, handling, and any other expenses incurred in preparing the product for the marketplace. These are your 'landed' costs and are crucial for understanding the true cost of selling each item.

• Add Advertising Spend Data: To assess the true profitability of your products, include advertising costs in your Amazon profit calculator. This should account for the cost of Amazon ads and any other promotional activities related to your products.

• Incorporate Amazon’s Fulfillment Fees: If you’re using FBA, include Amazon’s fulfillment fees in your calculations. These fees vary depending on the size and weight of the product and include picking, packing, shipping, and handling costs.

• Regular Updating Mechanism: Ensure your Amazon profit calculator can be regularly updated with the latest data. The market dynamics and costs on Amazon can change rapidly, so your calculator should be able to reflect these changes promptly.

By building an Amazon profit calculator with these components, Amazon sellers can gain a clearer understanding of their product-level profitability. This tool will aid in making informed decisions about product selection, pricing strategies, ad spending, and portfolio optimization, ultimately guiding you towards increased profitability on Amazon.

Now let’s go through the practical steps to build an Amazon Profit Calculator. We will provide a step-by-step approach to gathering necessary data from Amazon Seller Central, structuring this data for SKU-level profitability analysis, and incorporating various costs including ad spends into your profit calculations. By the end, you will have the tools you need to create an Amazon profit tracker that gives you the insights needed to take insightful and strategic action to benefit your business.

How to Build an Amazon Seller profit tracker in Excel:

Building a Profit and Loss (P&L) statement at the SKU-level is a process that requires a solid grasp of the data available to Amazon sellers. This initial P&L statement will serve as the core of your Amazon Profit Calculator. Here’s a step-by-step guide to creating a SKU-level P&L using Amazon Seller Transactions data:

1. Download transactions data

• Navigate to Payments > Reports Repository > (Detailed) Transaction Reports in Seller Central to gather transaction data. This data is fundamental for analyzing item-level fees and revenues.

2. Building a P&L

• Open the CSV file in Excel and create a pivot table

• Add the following columns to the table to start analyzing the data:

  • SKU and/or ASIN

  • Product sales / OPS

  • Promotional rebates

  • Shipping credits

  • Gift wrap credits

  • Selling fees

  • FBA fees

  • Other transaction fees

  • Other

• Add the type field to the columns panel, to see a breakout of each metric by orders vs. refunds. This should help clarify when a transaction includes debits or credits.

3. Getting to per-unit profitability

• Build a second pivot just like the first, filtered to the same SKU, with just sum of {quantity} as a value and filtered to Type = {Order} or {Refund}

• Determine the net units by subtracting refund units from order units

• Divide the all-SKU table by the net units number to generate a per-net-unit P&L. Create a new table that references the original pivot table (Copy, Paste as Link), and then dividing each cell by the net unit value

4. Incorporating COGS and Advertising Costs

• Create a table with two columns - SKU and COGS per-unit, and then bring that per-unit value into your Excel transactions table for all rows by SKU.

• Calculate COGS for any row by multiplying {quantity} * COGS per unit, with logic that makes it a credit or debit. For instance: Type = ‘Order’ → COGS is a debit → {quantity} * COGS per unit * -1 Type = ‘Refund’ → COGS is a credit → {quantity} * COGS per unit

• Advertising spend is captured as an expense in your transaction data. However, the spend might be inaccurate because transactions are captured at the time of settlement. We recommend using actual spend data from each of Amazon’s sponsored ad reports for maximum accuracy. Make sure to frequently refresh this data to capture rolling changes and updates from Amazon.

5. Develop a Model for Allocating Non-Item-Level Costs

• Many fees and SG&A expenses are billed at the account level, not the product or transaction level. This makes it more complicated to include those fees in your analysis.

• Create a method to apportion overhead costs like marketing or administrative expenses. Allocate these based on factors like revenue share or sales volume per SKU.

• For example:

  • If you have $5,000 in overhead expenses, and sold 500 units, you can attribute $10 in expenses per unit.

  • If you have $5,000 in overhead expenses, and sold $50,000 of product, you can attribute $1 in expense for every $10 in revenue.

  • Experiment with different allocation models to see which makes sense for your business.

6. Input Your Own Landed Product Costs

• Add a table with SKU and per-unit landed costs, encompassing manufacturing, shipping, and handling expenses

• Adjust your P&L by including these costs to get a more accurate picture of profitability

7. Multi-Month Analysis

• Extend your analysis to cover multiple months. Repeat the above steps, with a different time-bound export from Seller Central. This will help in identifying trends and making comparisons over time, which is invaluable for making informed decisions.

8. Create Regular Updating Mechanisms

• Automate and Schedule Data Retrieval: Utilize automation tools to regularly extract transaction data from Amazon Seller Central. Set a consistent schedule for data retrieval, such as weekly or monthly, to ensure your P&L reflects the most current information.

• Standardize and Streamline Data Processing: Develop a standardized template or process in Excel for integrating new transaction data. This process should include updating costs, recalculating metrics, and incorporating non-item-level expenses like overheads and advertising.

• Perform Regular Reviews and Adjustments: Allocate time for a thorough review and reconciliation of your P&L. This step is crucial for verifying data accuracy, adjusting for any changes in Amazon’s fee structure or policies, and analyzing financial performance to inform business decisions.

9. Refining Your P&L

• Continuously enhance your P&L by adding more categories or data. This could include finer cost categorization or integrating new data sources for deeper insights.

This process is a step towards gaining a deeper understanding of your business’s financial health. A well-structured SKU-level P&L is a great tool to measure your Amazon profitability, aiding in making data-driven decisions that could significantly impact your bottom line.

---

There’s help available.

—

Do you have time for all that?

Our Seller Profit Toolkit simplifies and automates the process of tracking, analyzing, and optimizing your Amazon profits.

• Real-time view of your SKU-level profitability, enabling immediate decision-making

• Automated tracking of various costs including Cost of Goods Sold and advertising expenses, providing a clear picture of your actual profits

• Quickly spot trends, unprofitable selection, and optimize ads for profitability

In a marketplace like Amazon, having precise, real-time insights into your profitability is crucial. The Seller Profit Toolkit is about moving beyond mere data collection to actionable insights that propel your Amazon business towards higher profitability and sustained success.