Product and Service Pricing Strategy: Building a Profitability Analysis Model in Excel

Introduction

Most Australian SMEs set prices based on one of three methods: matching competitors, adding a standard markup, or gut feel. All three leave money on the table or, worse, lead to unprofitable work.

The problem is that true profitability is hard to see. A product might look profitable when you only consider direct costs, but disappear into red once you allocate overhead, sales time, and support. A service line might be your biggest revenue driver and your smallest profit contributor.

This article walks through building a pricing and profitability model in Excel that reveals the real picture-so you can price with confidence.

Step 1: Understand Your Cost Structure

Before you can set a price, you need to know what something actually costs to deliver. This means breaking costs into three categories:

Direct Costs

Costs that are clearly attributable to a specific product or service:

• Materials and supplies
• Labour directly involved in delivery
• Subcontractor costs
• Shipping and fulfilment
• Payment processing fees

Indirect Costs (Overhead)

Costs that support the business but aren't tied to a specific product:

• Rent and utilities
• Salaries for management and admin
• Software and tool subscriptions
• Marketing and advertising
• Insurance and professional fees

Semi-Variable Costs

Costs that change with activity but not proportionally:

• Sales commissions (tied to revenue but not per-unit)
• Customer support (scales with customer count, not sales volume)
• Training and onboarding

The goal is to determine the fully loaded cost of each product or service. This is the direct cost plus a fair allocation of overhead.

Step 2: Build a Cost Allocation Model

Overhead allocation is where most pricing models go wrong. The simplest defensible method is to allocate overhead based on the percentage of total revenue each product line generates.

More sophisticated methods allocate based on labour hours, machine time, or transaction volume. But for most SMEs, the revenue-based method is a good starting point-it's simple, transparent, and easy to explain.

Step 3: Calculate Unit-Level Profitability

Once you have fully loaded costs, calculate profitability per unit or per service delivered:

This is where surprises emerge. A product with a healthy 60% gross margin might have a net margin of only 12% once overhead is properly allocated. Another product with a 40% gross margin but low overhead absorption could be more profitable overall.

Step 4: Set Price Floors and Targets

With full cost visibility, you can establish pricing boundaries:

• Price floor - the minimum price where you break even on fully loaded costs. Never price below this except for strategic reasons (loss leader, market entry).
• Target price - the price that achieves your desired net margin. For most SMEs, a 15-25% net margin is a healthy target after all costs.
• Aspirational price - the price that reflects your full value proposition. This is where you want to be when you have strong differentiation.

Pricing Sensitivity Table

Model how changes in price affect volume and profit:

Run this table for each product line. The relationship between price and volume is rarely linear, but this exercise forces you to think about it explicitly rather than assuming current pricing is optimal.

Step 5: Consider Market Position

Cost-plus pricing ignores the market. A complete pricing model includes:

• Competitor prices - what are the 3-5 main competitors charging for comparable offerings?
• Value perception - does your offering deliver more, less, or the same value?
• Price elasticity - how sensitive is your market to price changes? A 10% price increase that loses 5% of customers is a win. One that loses 20% is not.

Competitive Positioning Table

If your price is below the market average and your costs are under control, you have room to increase. If your price is above average, you need a clear justification-better service, faster delivery, stronger warranty.

Step 6: Scenario Analysis

Combine your cost model and pricing sensitivity into scenario analysis:

Use Excel's Scenario Manager (Data > What-If Analysis > Scenario Manager) to switch between these quickly, or simply build them side by side in a dedicated sheet.

Putting It Into Practice

A pricing model is only useful if you act on it. Common actions that come out of this analysis:

• Raise prices on products with high value perception and low price sensitivity. This is usually where you're leaving money on the table.
• Cut or restructure unprofitable products that don't cover fully loaded costs. Every dollar of revenue from a loss-making product costs you more than you earn.
• Adjust overhead allocation if one product line is subsidising another. This is surprisingly common and almost always invisible without a model.
• Introduce tiered pricing that captures value from both price-sensitive and premium customers.

Conclusion

Pricing is one of the highest-leverage decisions an SME owner makes. A 5% price increase that holds volume flows almost entirely to profit. But making that decision confidently requires knowing your true costs, your margins, and your market position.

A well-built Excel model gives you that visibility. The work is in the upfront analysis-once the model is built, pricing decisions become data-driven rather than emotional.

For more hands-on financial tools and templates, visit ExcelWiz.com.au.