ElyxAI

Product Margin Calculation in Excel

Brand ManagerMargin CalculationFree Template

This margin sheet is for a product range. One row is one SKU: the selling price, the unit cost, the margin in money (price minus cost), and the rate, that margin divided by the price when the price is not zero. A menu dish is a different sheet. A price list by channel is the pricing grid. This page answers what you keep on the SKU you already sell. Elyx can add the margin and the rate on the rows you paste. The trial lasts 7 days, with no card.

The Problem

A price and a unit cost often live in two files. You cannot see, on the SKU, what is left after the cost. Price minus cost is the margin in money. That margin divided by the price is the rate. The sheet does not choose a new price.

Benefits

One row is one SKU, with selling price and unit cost.

Margin in money is price minus cost.

The rate is that margin divided by the price, when the price is not zero.

A missing cost stays blank.

The channel price list stays on the pricing grid.

Step-by-Step Tutorial

1

Create the table structure

Set up a new Excel workbook with column headers for tracking product margins by brand and category. Create columns for: Product Name, SKU, Category, Cost Price, Selling Price, Units Sold, and Revenue. This foundation will organize all margin calculation data in a single, easy-to-reference table.

Use Ctrl+T to convert your data range into a structured table for automatic formula updates

2

Add realistic sample data

Populate your template with example products from different categories (e.g., Skincare, Haircare, Supplements). Include realistic cost prices, selling prices, and unit sales volumes. This sample data helps you test formulas and demonstrates how the template works for actual brand management scenarios.

Use at least 10-15 product rows to ensure your SUMIF and SUMPRODUCT formulas work correctly across multiple categories

3

Calculate gross profit per product

Create a new column called 'Gross Profit Per Unit' to show the profit earned on each product sale. Subtract the Cost Price from the Selling Price to determine the per-unit margin. This metric is essential for brand managers to identify which products are most profitable individually.

=D2-C2

Format this column as currency (e.g., $0.00) for clarity in financial reports

4

Calculate total profit by product

Add a 'Total Profit' column that multiplies the Gross Profit Per Unit by Units Sold. This shows the absolute profit contribution of each product, accounting for both margin and sales volume. Brand managers use this to prioritize which products drive the most revenue.

=G2*F2

Use conditional formatting (green for high profit, red for low) to instantly identify top performers

5

Calculate profit margin percentage

Create a 'Profit Margin %' column to show the percentage return on each product's cost price. Divide Gross Profit Per Unit by Cost Price and multiply by 100. This percentage metric is critical for comparing profitability across products with different price points.

=(G2/C2)*100

Add a conditional format rule to highlight margins above 50% in green and below 30% in orange for quick analysis

6

Create category-level summary using SUMIF

Build a summary section below your main table to calculate total profit and average margin by category. Use SUMIF to aggregate profits for each category (Skincare, Haircare, etc.). This helps brand managers understand which product categories are most profitable.

=SUMIF($C$2:$C$50,"Skincare",$H$2:$H$50)

Reference your category names from the main table using a dropdown list to make the summary dynamic

7

Calculate weighted average margin using SUMPRODUCT

Create an advanced metric that calculates the overall profit margin weighted by sales volume. Use SUMPRODUCT to multiply each product's margin percentage by its units sold, then divide by total units sold. This reveals the true average margin of your brand portfolio.

=SUMPRODUCT((G2:G50/C2:C50)*F2:F50)/SUM(F2:F50)

This formula accounts for the fact that high-volume products have more impact on overall margin than low-volume products

8

Add conditional logic for margin alerts

Use an IF statement to flag products that fall below your target margin threshold (e.g., 35%). Create a new 'Status' column that displays 'Review Pricing' for low-margin products and 'Healthy' for products meeting targets. This alerts brand managers to pricing or cost issues.

=IF(J2<35,"Review Pricing","Healthy")

Combine IF with conditional formatting to automatically color-code the Status column for visual impact

9

Create a brand performance dashboard

Build a summary dashboard at the top of your workbook that displays key metrics: Total Revenue, Total Profit, Overall Profit Margin %, Number of Products, and Average Units Sold. Use SUMPRODUCT and SUMIF to pull these metrics from your detailed table. This gives brand managers an instant overview of performance.

=SUMPRODUCT(D2:D50,F2:F50) for Total Revenue; =SUM(H2:H50) for Total Profit

Use large, bold formatting and cell colors for dashboard metrics so executives can grasp performance at a glance

10

Add scenario analysis with data validation

Create a 'What-If' section where brand managers can test pricing changes. Use data validation dropdowns to select a product, then create formulas that show how margin changes if they adjust the selling price by 5%, 10%, or 15%. This helps with pricing strategy decisions.

=IF(L2="5%",D2*1.05,IF(L2="10%",D2*1.10,IF(L2="15%",D2*1.15,D2)))

Use named ranges for your price adjustment percentages to make the formula more readable and easier to modify

Template Features

Multi-level Margin Analysis

Calculates gross margin, operating margin, and net margin simultaneously to track profitability at each business level. Solves the problem of understanding which margin level is declining and why.

=((Revenue-COGS)/Revenue)*100 for Gross Margin; =((Revenue-COGS-OpEx)/Revenue)*100 for Operating Margin

Product Line Comparison Dashboard

Compares margin performance across different product lines or SKUs in a single view, enabling quick identification of underperforming products that need strategic attention.

=MAXIFS(Margin, ProductLine, A2) to highlight top performer; =MINIFS(Margin, ProductLine, A2) for bottom performer

Variance to Target Alert System

Automatically flags when actual margins fall below target thresholds with color coding, helping brand managers prioritize corrective actions before margins deteriorate further.

Scenario Modeling for Pricing Decisions

Tests impact of price increases or cost reductions on margins without modifying actual data, solving the challenge of deciding whether to raise prices or negotiate supplier costs.

=((Revenue*(1+PriceIncrease%)-COGS)/Revenue)*100 for price scenarios

Year-over-Year Margin Trend Analysis

Tracks margin evolution over multiple periods to identify seasonal patterns and long-term trends, revealing whether margin erosion is temporary or structural.

=(CurrentMargin-PriorYearMargin)/PriorYearMargin for YoY % change

Channel Profitability Breakdown

Isolates margins by sales channel (direct, retail, e-commerce, wholesale) to identify which channels are most profitable and guide distribution strategy decisions.

=SUMIF(Channel, A2, Margin)/SUMIF(Channel, A2, Revenue) for channel-specific margin

Concrete Examples

Product Line Profitability Analysis

Sophie, Brand Manager for a cosmetics company, needs to evaluate which product lines are generating the highest margins to inform her marketing budget allocation and promotional strategy.

Premium Skincare: COGS €12/unit, Selling Price €45/unit, Volume 5,000 units; Mass Market Foundation: COGS €3.50/unit, Selling Price €12/unit, Volume 18,000 units; Luxury Fragrance: COGS €18/unit, Selling Price €75/unit, Volume 2,200 units

Result: A margin comparison table showing Premium Skincare (73.3% margin, €165,000 profit), Mass Market Foundation (70.8% margin, €152,100 profit), and Luxury Fragrance (76% margin, €125,400 profit). Sophie identifies that Premium Skincare delivers the best ROI and decides to increase its marketing spend by 30%.

Seasonal Campaign Profitability Tracking

Thomas, Brand Manager for a fashion retailer, launches three seasonal collections and needs to monitor actual margins against projected margins to assess campaign effectiveness and adjust inventory decisions.

Spring Collection: Projected margin 45%, Actual COGS €8/item, Actual Selling Price €18/item, Units sold 12,000; Summer Collection: Projected margin 42%, Actual COGS €7.50/item, Actual Selling Price €16.50/item, Units sold 15,500; Fall Collection: Projected margin 48%, Actual COGS €9/item, Actual Selling Price €19.50/item, Units sold 9,800

Result: A variance analysis showing Spring Collection achieved 44.4% (vs 45% projected, -0.6% variance), Summer Collection achieved 54.5% (vs 42% projected, +12.5% variance due to lower-than-expected COGS), and Fall Collection achieved 53.8% (vs 48% projected, +5.8% variance). Thomas identifies the Summer Collection as unexpectedly profitable and plans to increase its production for next year.

Competitor Pricing vs. Margin Strategy

Isabelle, Brand Manager for a premium electronics brand, conducts quarterly competitive analysis to ensure her brand maintains price positioning while protecting margins against competitor pricing pressure.

Our Product: COGS €220, Our Price €599, Competitor A Price €549, Competitor B Price €629; Similar Product B: Our COGS €85, Our Price €249, Competitor A Price €199, Competitor B Price €279

Result: A competitive matrix showing Our Product has 63.3% margin at €599 (vs Competitor A's estimated 60% at €549 and Competitor B's estimated 60% at €629), while Product B has 65.9% margin at €249 (vs Competitor A's estimated 70% at €199 and Competitor B's estimated 65% at €279). Isabelle decides to maintain pricing on Product A (justified premium) but reviews costs for Product B to remain competitive against Competitor A.

Pro Tips

Build a Dynamic Margin Dashboard with Conditional Formatting

Create a visual margin tracking system that instantly flags underperforming products. Use conditional formatting to color-code margins by threshold (red <20%, yellow 20-30%, green >30%). This allows you to spot margin erosion at a glance across your entire brand portfolio without manual review. Pair this with slicers to filter by product category, channel, or time period for rapid decision-making.

=((Sales_Price-COGS)/Sales_Price)*100

Implement Scenario Analysis with Data Tables

Use Excel's Two-Way Data Table feature (Data > What-If Analysis > Data Table) to instantly model how changes in pricing or COGS impact margin across multiple scenarios. This eliminates the need to manually recalculate formulas and lets you present stakeholders with 5-10 pricing scenarios in seconds. Critical for promotional planning and competitive positioning decisions.

Create a Margin Variance Analysis Template with Index-Match Lookups

Automate month-over-month or year-over-year margin comparisons using INDEX-MATCH to pull historical data dynamically. This prevents copy-paste errors and ensures your variance analysis always reflects the latest data. Add a simple IF statement to flag variances exceeding your threshold (e.g., >5 percentage points) for investigation.

=INDEX(HistoricalMargin,MATCH(ProductID,ProductList,0))-CurrentMargin

Use Named Ranges for Brand-Specific Margin Targets

Define Named Ranges (Formulas > Define Name) for each brand's target margin, COGS baseline, and pricing floor. This makes formulas readable (=ActualMargin-BrandTarget_Luxury) and allows you to update targets once in a central location without editing multiple worksheets. Particularly valuable when managing multi-brand portfolios with different margin requirements.

=IF(CalculatedMargin>=BrandTarget_Premium,"On Track","Below Target")

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Product margin calculation, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Frequently Asked Questions

See also