ElyxAI

Product Pricing Grid in Excel

Brand ManagerPricing GridFree Template

This pricing grid is the product range. One row is one SKU on one channel: the unit cost, the shelf price, and the margin that price leaves, which is the shelf price minus the cost. A restaurant menu is a different page. The margin sheet is the rate on the SKU. This page is the price you show, by channel. Elyx can add the margin on the rows you paste. The trial lasts 7 days, with no card.

The Problem

The same SKU can have a different shelf price by channel, while the unit cost sits in one file. You cannot see what each price leaves. One row per SKU and channel keeps the cost next to that price. You still set the price.

Benefits

One row is one SKU on one channel.

You type the unit cost and the shelf price.

The margin on the row is the shelf price minus the cost.

A missing price stays blank.

A restaurant menu is a different page.

Step-by-Step Tutorial

1

Create the table structure for product pricing

Start by setting up the foundational columns that will organize your pricing grid. Create columns for Product ID, Product Name, Category, Base Cost, Standard Price, Premium Price, and Discount %. This structure allows you to manage multiple price tiers across different product categories.

Use row 1 for headers with bold formatting and a background color to make them stand out. Leave the first column (A) for Product ID to serve as your lookup reference.

2

Add sample product data

Populate your pricing grid with realistic product examples relevant to your brand portfolio. Include at least 8-10 products across different categories (e.g., skincare, fragrances, accessories) with their corresponding base costs and standard retail prices.

Use consistent naming conventions for products and categories. For example: 'SKN-001' for product IDs makes sorting and filtering easier for brand managers.

3

Create a category lookup reference table

Build a separate reference table on another sheet (named 'Categories') that lists each product category with its standard markup percentage. This allows you to quickly adjust pricing strategy by category without editing individual rows.

Name this sheet 'Categories' and include columns: Category Name and Standard Markup %. This will be used with VLOOKUP in later steps.

4

Calculate Standard Price using markup logic

Insert a formula in the Standard Price column that multiplies the Base Cost by a markup percentage. This ensures consistent pricing methodology across your product line and makes it easy to adjust pricing strategies globally.

=ROUND(D2*1.45,2)

The ROUND function ensures prices display with exactly 2 decimal places. Adjust the 1.45 multiplier (45% markup) based on your brand's target margin strategy.

5

Implement VLOOKUP for category-based pricing

Use VLOOKUP to automatically fetch the standard markup percentage from your Categories reference table based on the product's category. This eliminates manual entry and reduces pricing inconsistencies across your brand portfolio.

=ROUND(D2*(1+VLOOKUP(C2,Categories!A:B,2,FALSE)),2)

The FALSE parameter ensures exact category matches. If you get a #N/A error, verify that your category names in column C exactly match those in the Categories sheet.

6

Calculate Premium Price with conditional logic

Create a Premium Price column that applies a higher markup for premium-tier products. Use an IF statement to automatically assign premium pricing based on product category or base cost thresholds, allowing for tiered pricing strategies.

=ROUND(IF(C2="Premium",D2*1.65,D2*1.45),2)

This formula checks if the category is 'Premium' and applies a 65% markup; otherwise, it applies the standard 45% markup. Adjust thresholds and percentages based on your brand positioning.

7

Add dynamic discount calculations

Insert a formula that calculates the final discounted price based on the Discount % column. This allows brand managers to quickly model promotional scenarios and see the impact on final retail prices across the entire product line.

=ROUND(E2*(1-G2),2)

Enter discount percentages as decimals (e.g., 0.15 for 15% off). You can also use this column for seasonal adjustments or loyalty program pricing.

8

Create a profit margin analysis column

Add a column that calculates the profit margin percentage for each product at its current pricing. This gives brand managers visibility into profitability and helps identify products that may need pricing adjustments to meet margin targets.

=ROUND((H2-D2)/H2*100,1)

This formula shows margin as a percentage. Values above your target margin (e.g., 45%) indicate healthy profitability, while lower values flag products needing price review.

9

Format the grid for professional presentation

Apply number formatting, conditional formatting, and data validation to make your pricing grid both functional and visually professional. Use currency formatting for price columns and add color scales to highlight margin performance at a glance.

Use conditional formatting with a color scale (green for high margins, red for low margins) in the profit margin column. Format all price columns as Currency with 2 decimal places.

10

Protect and document the template

Lock formula cells to prevent accidental edits while keeping input cells (Base Cost, Discount %) editable for brand managers. Add a documentation sheet explaining each column's purpose and the pricing strategy behind the formulas.

Use Format > Cells > Protection to lock formula cells, then use Review > Protect Sheet to enable editing only for specific ranges. Create a 'Notes' sheet documenting markup percentages and pricing assumptions.

Template Features

Dynamic Price Tier Calculation

Automatically calculates tiered pricing based on volume brackets, ensuring consistent pricing across product lines and customer segments

=IF(B2<=100, B2*0.15, IF(B2<=500, B2*0.12, B2*0.10))

Margin Variance Analysis

Highlights pricing decisions that fall outside target margin ranges, helping brand managers maintain profitability standards

=((D2-C2)/D2)*100

Competitor Price Comparison Matrix

Displays your pricing position relative to competitors across product categories, enabling quick competitive positioning adjustments

Promotional Discount Simulator

Calculates real-time impact of discounts on final price and margin, preventing unprofitable promotional decisions

=E2*(1-F2%)

Price Elasticity Dashboard

Tracks historical volume and price changes to predict demand impact of future pricing decisions

=((B3-B2)/B2)/((A3-A2)/A2)

Currency Conversion & Regional Pricing

Automatically adjusts prices across regions using live or fixed exchange rates while maintaining brand consistency

=G2*VLOOKUP(H2, ExchangeRates!A:B, 2, FALSE)

Concrete Examples

Product Line Tiering Strategy

Sarah, Brand Manager at a consumer electronics company, needs to establish pricing tiers across her product portfolio (entry-level, mid-range, premium) to avoid channel conflict and maintain brand positioning.

Product: Wireless Headphones | Entry: $49.99 | Mid: $129.99 | Premium: $249.99. Product: Portable Speaker | Entry: $39.99 | Mid: $99.99 | Premium: $199.99. Product: Smart Watch | Entry: $99.99 | Mid: $249.99 | Premium: $499.99

Result: A pricing grid showing clear tier separation (minimum 2.5x between tiers), margin targets by tier (40% entry, 50% mid, 60% premium), and visual alerts when pricing violates brand positioning rules

Regional Market Adaptation

James, Brand Manager for a fashion retailer, must adjust pricing across 5 geographic markets while maintaining brand equity. Each region has different purchasing power, competitor pricing, and distribution costs.

US Base Price: $85 | Canada: +8% | UK: £65 (converted) | Australia: AUD $130 | Mexico: MXN $1,700. Cost of goods: $32. Shipping/duties by region: US $3, Canada $5, UK $6, Australia $12, Mexico $8

Result: A dynamic pricing grid showing final retail price by market, local currency conversion, margin percentage by region (flagging any below 45%), and a summary showing brand consistency (visual parity check across markets)

Promotional Campaign Impact Analysis

Lisa, Brand Manager at a luxury cosmetics brand, is planning a seasonal promotion and needs to model the financial impact across product categories while protecting brand prestige through controlled discounting.

Category: Skincare | Regular: $95 | Promo: 15% off | Units baseline: 1,200/month | Expected lift: 35%. Category: Fragrance | Regular: $125 | Promo: 10% off | Units baseline: 800/month | Expected lift: 20%. Category: Makeup | Regular: $45 | Promo: 20% off | Units baseline: 3,500/month | Expected lift: 50%

Result: A pricing grid comparing regular vs promotional revenue, profit impact by category, total margin erosion, and a recommendation dashboard showing which categories can sustain deeper discounts without brand dilution

Pro Tips

Dynamic Price Sensitivity Analysis with Data Tables

Create a two-way data table to instantly visualize how revenue changes across price points and volume scenarios. This lets you identify optimal pricing without manual recalculation. Use Data > What-If Analysis > Data Table to test multiple price-volume combinations simultaneously, revealing price elasticity patterns specific to your brand segments.

=SUMPRODUCT(price_range, volume_range) for revenue modeling within the data table

Conditional Formatting for Margin Monitoring

Apply color scales to your pricing grid to instantly spot margin compression or pricing anomalies. Set rules where margins below your brand's minimum threshold (e.g., 35%) turn red, optimal margins turn green. This provides visual governance without opening reports, enabling faster strategic decisions during competitive pricing reviews.

Index-Match for Real-Time Competitor Benchmarking

Link your pricing grid to a competitor price feed using INDEX-MATCH formulas. This automatically flags when your prices drift beyond your brand positioning (e.g., premium/value tier). Update the competitor sheet weekly; your grid recalculates instantly, showing variance % and recommended adjustments.

=INDEX(competitor_prices,MATCH(product_sku,competitor_skus,0))

Scenario Manager for Brand Strategy Testing

Use Excel's Scenario Manager (Data > What-If Analysis > Scenario Manager) to save multiple pricing scenarios (aggressive growth, margin defense, market share capture). Switch between scenarios with one click to compare P&L impact. Perfect for board presentations and strategic planning without creating duplicate sheets.

Formulas Used

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

Example request

“Build this in my workbook: Product pricing grid, 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