A reliable price is more than a cost plus a percentage. This product pricing and profitability workbook for Excel helps you combine bill-of-materials costs, labour, allocated overhead, discounts and VAT, then test margin, markup, break-even volume and alternative scenarios before you publish a price or approve a promotion.
Overview
Pricing decisions become easier to review when the assumptions are separated from the calculations. Build the workbook with one input area and several calculation sheets rather than entering figures directly into formulas. That structure makes it possible to update a supplier cost, wage assumption or VAT setting once and see the effect throughout the model.
A practical workbook can contain these sheets:
- Setup and controls: currency, VAT treatment, target margin, expected discount and scenario labels.
- Unit costs and BOM: materials, packaging, purchased components and other costs incurred per unit.
- Labour and overhead: production time, labour cost per hour and the chosen method for allocating fixed costs.
- Pricing calculator: recommended price, discounted price, VAT-inclusive price, markup and margin.
- Break-even: contribution per unit, break-even units and break-even revenue.
- Scenarios: price, cost and volume sensitivity for launch, base, downside and promotion cases.
Keep the model consistent about whether calculations use prices before or after VAT. For a VAT-registered business, profitability is commonly analysed using amounts exclusive of recoverable VAT, while customer-facing prices may need to be displayed inclusive of VAT. The correct treatment depends on the business and transaction, so record the chosen approach clearly and check current HMRC guidance when maintaining a UK model. The VAT Calculator UK guide provides a separate workflow for checking VAT on sales and purchases.
How to estimate
Start with the fully loaded unit cost. In the workbook, use one row per cost item with columns for description, quantity per finished unit, unit cost, waste allowance and calculated cost. A simple formula is:
Extended cost = Quantity per unit × Unit cost × (1 + Waste rate)
Sum the extended costs to obtain the material and component cost. Add packaging, payment fees, fulfilment or other variable costs that change with each sale. If labour is required to make or prepare the product, calculate it separately:
Direct labour cost = Minutes per unit ÷ 60 × Labour cost per hour
The labour rate should reflect the cost relevant to the business decision. Depending on the purpose of the model, that may be an employee cost, a contractor rate or an internal management rate. If employment cost is being modelled, include relevant employer costs and use assumptions that are reviewed against current guidance. The UK payroll cost calculator can help with a broader employment-cost view.
Next, allocate fixed overhead. Divide the fixed cost pool by a rational activity base, such as expected units, production hours or machine hours:
Allocated overhead per unit = Fixed cost pool ÷ Allocation base
Label this as an allocation, not as a cash cost that necessarily changes with one additional unit. This distinction matters for break-even analysis. For break-even, use fixed costs in the fixed-cost input and variable costs in the unit contribution calculation. Avoid adding the same overhead to both places.
For target-margin pricing, calculate the selling price before VAT with:
Price before VAT = Unit cost ÷ (1 − Target gross margin)
For example, if the unit cost is £24 and the target margin is 40%, the price before VAT is £24 ÷ 0.60, or £40. The resulting gross profit is £16, which is 40% of the selling price. This is different from applying a 40% markup, which would produce £33.60 and a lower margin.
| Measure | Formula | What it tells you |
|---|---|---|
| Markup | (Price − Cost) ÷ Cost | Profit as a percentage of cost |
| Gross margin | (Price − Cost) ÷ Price | Profit as a percentage of selling price |
| VAT-inclusive price | Price before VAT × (1 + VAT rate) | The displayed price when VAT is added |
| Price before VAT | VAT-inclusive price ÷ (1 + VAT rate) | The net sales value when VAT is included in the input |
Use a VAT toggle or dropdown rather than hard-coding a rate in multiple formulas. Store the rate in one control cell, add a note showing when it was last checked, and update it against current official guidance. Also distinguish VAT-inclusive customer prices from discounts: if a discount is applied to the customer price, calculate the discounted net price first, then calculate VAT according to the selected treatment.
Inputs and assumptions
Make every material assumption visible. A useful input sheet includes:
- Component name, supplier, quantity and unit cost.
- Expected waste, damaged stock or yield loss.
- Packaging, delivery, payment processing and other per-order costs.
- Minutes per unit and labour cost per hour.
- Monthly or annual fixed costs and the allocation basis.
- List price, expected discount and any channel commission.
- VAT status, rate and whether the displayed price is inclusive.
- Expected sales volume and the period covered by fixed costs.
Use named cells or structured table references where practical. Protect formula cells, colour input cells consistently and include an assumptions note beside unusual entries. Do not hide uncertainty inside a single number. If a supplier cost could vary, create low, base and high values or use the scenario sheet to vary it.
Break-even requires a clear contribution definition:
Contribution per unit = Net selling price per unit − Variable cost per unit
Then:
Break-even units = Fixed costs ÷ Contribution per unit
Break-even revenue = Break-even units × Net selling price per unit
Use IFERROR or a validation message when contribution is zero or negative. A negative contribution means that selling more units would worsen the result under the current assumptions; the workbook should flag this rather than return a misleading break-even figure. For a fuller explanation, link the model to the break-even analysis calculator guide.
Worked examples
Suppose a product has materials and packaging of £14.50 per unit, direct labour of £5.50 and variable fulfilment costs of £2.00. The total variable cost is £22.00. If the target gross margin is 35%, the target net selling price is:
£22.00 ÷ (1 − 0.35) = £33.85
If the business expects a 10% promotional discount, do not simply ignore it. If the discounted price is required to retain the target margin, the list price needs to be solved as:
Required list price = Target net price ÷ (1 − Discount rate)
Using the example:
£33.85 ÷ 0.90 = £37.61
The workbook can then add VAT to the relevant customer-facing price using the VAT rate stored in the control cell. Do not treat VAT collected as profit. Display both the net price used for margin calculations and the VAT-inclusive price used for communication.
For break-even, assume fixed costs allocated to the period are £4,000 and the net selling price is £33.85. Contribution is £11.85 per unit, so:
£4,000 ÷ £11.85 = 337.55 units
Because units are whole items, the practical threshold is the next whole unit. The scenario sheet should test what happens if unit cost rises by 5%, the realised price is reduced by a promotion, or volume is below plan. Use a two-variable data table where supported, with price on one axis and volume on the other, or create a clearly labelled scenario grid with formulas. Show contribution, gross margin, operating result and break-even units for each case.
For management review, pair the workbook with a budget or variance process. The monthly variance analysis template can help compare planned assumptions with actual cost and volume after launch.
When to recalculate
Recalculate the model whenever a pricing input changes, not only at the annual planning point. Important triggers include a supplier price revision, a change in packaging, altered labour costs, a new sales channel, a different payment fee, a revised VAT treatment, a planned promotion or a material change in expected volume.
Before a launch or quarterly review, complete a short checklist:
- Confirm that all component and labour costs use the same unit and VAT basis.
- Compare expected quantities and waste with recent purchasing or production records.
- Check that fixed costs have not been included twice.
- Run base, downside and upside scenarios.
- Review the margin after discounts, commissions and fulfilment costs.
- Record the date, owner and reason for each major assumption change.
Review UK-specific VAT settings against current official guidance whenever rates or business circumstances change. If the workbook uses wage examples or supplier-inflation assumptions, refresh those inputs at least annually and label them as illustrative rather than universal. Keep a dated copy of each approved pricing decision so later users can distinguish a changed assumption from a changed formula.
The result is a reusable product pricing template rather than a one-off calculation. Update the inputs, rerun the scenarios and export the key outputs—net price, customer price, margin, contribution and break-even volume—before each launch, promotion or formal review.
