Pricing is one of the most consequential decisions a small business makes and one of the least systematically approached. Most small business owners set prices based on what competitors charge, what feels reasonable, or what customers seem willing to pay — without ever calculating whether those prices actually produce a viable margin after all costs are accounted for.
Excel makes pricing analysis straightforward. You can calculate the true margin on every product or service you sell, find your break-even point, model different pricing scenarios, and build a pricing tool that tells you immediately whether a new price or discount is financially viable. Here is how to build it.
Margin and markup are often confused and using the wrong one leads to serious pricing errors. They are both ways of expressing the relationship between cost and price but they mean different things and produce very different numbers.
Profit as a percentage of selling price. A 40% margin on a $100 product means $40 profit and $60 cost. Margin is calculated on the sale price.
Profit as a percentage of cost. A 67% markup on a $60 cost produces a $100 selling price. Markup is calculated on the cost price.
The same product. The same cost. The same selling price. But 40% margin and 67% markup. Confusing the two — especially when communicating with an accountant or setting pricing targets — leads to mistakes. This guide uses margin throughout because it is the more useful number for profitability analysis.
These are the four formulas that form the foundation of any pricing analysis in Excel. Set them up once in a simple calculator layout and you can test any price or cost scenario instantly.
Gross profit:
Gross margin percentage:
Selling price needed to achieve a target margin:
Markup percentage:
For businesses with multiple products or services, a pricing analysis sheet shows the margin on everything you sell side by side. This view often reveals pricing problems that are invisible when you look at products individually — services priced below cost, products with margins too thin to survive a cost increase, or a pricing structure that looks consistent but is actually wildly inconsistent in profitability.
| Column | What to Enter or Formula |
|---|---|
| Product or Service | Name of what you sell |
| Direct Cost | Materials, cost of goods, or direct labor cost |
| Overhead Allocation | Your share of fixed costs allocated to this product |
| Total Cost | =Direct Cost plus Overhead Allocation |
| Current Price | What you currently charge |
| Gross Profit | =Current Price minus Direct Cost |
| Gross Margin % | =Gross Profit / Current Price |
| Net Profit | =Current Price minus Total Cost |
| Net Margin % | =Net Profit / Current Price |
| Suggested Price | =Total Cost / (1 - Target Margin %) |
| Price Gap | =Suggested Price minus Current Price |
Here is what this looks like for a typical small product or service business:
| Product | Total Cost | Current Price | Net Margin | Suggested Price | Gap |
|---|---|---|---|---|---|
| Basic Service | $42 | $75 | 44% | $70 | -$5 |
| Premium Service | $85 | $120 | 29% | $142 | +$22 |
| Add-on Package | $28 | $35 | 20% | $47 | +$12 |
| Consultation | $95 | $100 | 5% | $158 | +$58 |
This table tells a clear story. The consultation service is severely underpriced at only 5% net margin. The add-on package is also below target. The basic service is slightly over-priced relative to its cost. The premium service has the biggest pricing gap and the best opportunity for a price increase.
Break-even analysis tells you how many units you need to sell, or how much revenue you need to generate, to cover all your costs and reach zero profit. Knowing your break-even point tells you whether your business model is viable at your current price and cost structure, and what volume you need to hit to start making money.
Break-even in units:
Break-even in revenue:
One of the most practically useful pricing tools in Excel is a discount impact calculator. It shows you exactly how many additional units you need to sell to maintain the same total profit when you offer a discount. This number is almost always higher than people expect, which is why unplanned discounting is one of the fastest ways to erode profitability.
Most small business owners offering a 10% discount think they need to sell a little more to make up for it. The math says they need to sell 33% more. A 20% discount on a 40% margin product requires selling 100% more units — double the volume — just to break even on profit. This calculator makes those numbers visible before the discount is offered rather than after.
Pricing is not a one-time decision. Costs change, competitor prices change, your value proposition evolves, and inflation erodes margins gradually over time. A pricing analysis that made sense two years ago may be quietly losing you money today.
Building your pricing analysis in Excel and reviewing it quarterly — updating your costs and comparing your current prices against your target margins — is one of the highest-value financial habits a small business owner can develop. Most businesses that do this discover at least one or two products or services that need a price increase, and implementing even one of those increases typically more than pays for the time spent on the review.
If you would like a custom pricing analysis workbook built for your specific products and cost structure, that is exactly what we do at HelpMyData.
Tell us about your products and cost structure and we'll build a complete Excel pricing calculator — margins, break-even analysis, and discount impact modeling. Starting at just $75.
📧 Get Started Today