Product Pricing Grid in Excel
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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)*100Competitor 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 tableConditional 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.