Selling price template in Excel: cost plus margin
Selling price template in Excel: cost plus margin
Setting a selling price is one of those decisions taken once and carried for months. When the price is worked out badly, the product sells, the invoice gets paid, and at the end of the month the business cannot explain why it worked so hard and kept so little. The problem is rarely in sales or in the supplier: it is in the formula the price was built from.
This Excel template calculates the selling price from the product cost and the margin you want, with the margin applied on the price and not on the cost. The sheet is ready for forty products, with drop-down lists, automatic columns, an editable reference margin and a summary of average profit and average margin.
⬇ Download the template (Excel .xlsx)What it is and who it is for
It is an internal spreadsheet for setting selling prices. It is not market research and not a study of the competition: it is the tidy arithmetic that goes with any pricing decision. You write what one unit costs you and what margin you want to make, and the sheet returns the price, the profit per unit and the margin that really remains.
It is meant for anyone running a small or medium business: a shop, a distributor, a workshop, a bakery, a brand selling from a catalogue, or any operation that keeps a product list with its costs. It also helps a salesperson or an administrator who receives a finished price list and needs to understand where those numbers come from.
The starting point is a simple idea that is usually overlooked: the margin is calculated on the selling price, not on the cost. It is quick to say, but it is the difference between a business that earns what it thinks it earns and one that works with smaller margins than the ones written in its notebook.
The mistake the template corrects
The usual habit is to multiply cost by a factor. If the cost is 1,100 and the wanted margin is thirty per cent, you write 1,100 × 1.30 and get 1,430. The number looks right because thirty per cent of 1,100 is 330, and 1,100 plus 330 is 1,430. The catch is that the thirty per cent was taken on the cost, not on the price.
Seen against the price, the margin on 1,430 is something else: profit is 330 and the price is 1,430, so the real margin sits near twenty-three per cent. The business wanted thirty and ended up with almost seven points less on every sale. At one hundred units a month, that gap is money that never comes back.
When the margin is taken on the price, the operation is a division: total cost divided by one minus the wanted margin. With a total cost of 1,100 and a margin of thirty per cent, the price is 1,100 ÷ 0.70 = 1,571.43. The sheet does that sum on its own and shows the profit each unit leaves.
What the template includes
| Part of the sheet | What it holds |
|---|---|
| Product register | Forty rows to write in products, with category, unit, cost and additional expenses. |
| Drop-down lists | Category and unit are chosen from a list, so the sheet does not fill up with different names for the same group. |
| Reference margin | An editable cell with the margin applied by default to products that have no margin of their own. |
| Automatic columns | Total cost, selling price, rounded price, profit per unit and real margin are all calculated for you. |
| Totals | Sum of costs, of prices and of profit, plus the average margin of the whole list. |
| Summary | Products registered, average profit per unit and average margin of the catalogue. |
The prices the sheet returns are product prices, without tax: the template is a costing and margin tool, not a billing document. If your operation charges the customer anything extra, that adjustment belongs later, in the billing system, and does not change the margin analysis per product.
The columns of the sheet
| Column | What you write or what it calculates |
|---|---|
| Product | Name that identifies the item in the price list. |
| Category | Group it belongs to, chosen from the drop-down list. |
| Unit | Whether it is sold by unit, box, dozen, kilo, litre or metre. |
| Unit cost | What one unit landed in the business costs, according to the latest purchase. |
| Additional expenses | Packaging, transport, commission or any cost added on top of the unit. |
| Total cost | Automatic. Unit cost plus additional expenses. |
| Wanted margin | The margin you want to make, written per product or taken from the reference cell. |
| Selling price | Automatic. Total cost divided by one minus the wanted margin. |
| Rounded price | Automatic. The price taken to the next hundred, for a list that is easier to read. |
| Profit per unit | Automatic. Rounded price minus total cost. |
| Real margin | Automatic. Profit divided by the rounded price; it is the margin that really remains. |
It is worth looking at the last column before publishing a list. The real margin is almost never the same as the wanted margin, because rounding the price moves it a little up or down. The sheet shows both figures side by side and reveals whether the final price drifts too far from what was intended.
How the calculation works, with an example
Take a product with a unit cost of 1,000 and additional expenses of 100. The total cost is 1,100. If the wanted margin is thirty per cent on the price, the sheet divides 1,100 by 0.70 and gets 1,571.43, shown as 1,571. The price rounded to the next hundred is 1,600, and the profit per unit, at the rounded price, is 500.
| Item | Value |
|---|---|
| Unit cost | 1,000 |
| Additional expenses | 100 |
| Total cost | 1,100 |
| Wanted margin | thirty per cent |
| Selling price calculated | 1,571 |
| Price rounded to the next hundred | 1,600 |
| Profit per unit | 500 |
| Real margin | close to thirty-one per cent |
Compare that result with the habit of multiplying: 1,100 × 1.30 = 1,430. The gap between 1,571 and 1,430 is 141 per unit, and across one hundred units a month it comes to 14,100 left on the table without anyone noticing. Rounding to 1,600 improves the margin a little further and leaves a price that is easy to remember and easy to add up.
How to use it step by step
- Download the file and save it with the year in the name, so that in the next period it is clear which price list is the current one.
- Write the reference margin in the header cell. It is the margin the sheet applies to products that have none of their own; in retail it is usual to start with a value between twenty-five and forty per cent.
- Fill in one row per product: name, category, unit, unit cost and additional expenses. Always use the latest purchase as the source of the cost and note the date you checked it.
- Review the rounded price column and decide whether that price holds in the market. The sheet calculates; the decision to publish the price is still yours.
- Look at the profit per unit and the real margin. If a product falls below the margin the business needs, correct the cost, talk to the supplier or adjust the price before publishing the list.
- Keep a copy for each period. Comparing this month's list with last month's is the fastest way to see how much costs have risen, before the loss of profit shows it.
Tips and common mistakes
- Multiplying cost by the margin. It is the most frequent and the most expensive mistake, because the price ends up lower than needed and profit is lost on every sale.
- Forgetting the additional expenses. Packaging, freight and commissions are cost: if they are not inside the unit, the margin in the sheet will be higher than the one in the real business.
- Using old costs. A cost from six months ago leaves a margin that no longer exists; the cost column is worth checking every time the purchase price changes.
- Leaving the margin blank. If the wanted margin cell is empty, the row returns no price; it is better to write the margin for every product sold on a regular basis.
- Rounding downwards. Lowering the price to make it neat cuts profit without changing what the customer thinks; rounding up is the healthier choice.
- Publishing the list without looking at the real margin. The average in the summary is the figure that says whether the whole catalogue is profitable or whether some products are carrying others.
When to move to software
The spreadsheet works very well while one person handles the price list and changes are made a few times a year. The trouble starts when costs move every week, when there are several lists by channel or by type of customer, or when the price charged at the counter does not match the one in the sheet because somebody changed it by hand.
At that point it is worth keeping costs, prices and margins in a system where the price comes from the product record and updates itself. That is the line of Kardex Tauro: alongside inventory and purchases, it shows the cost of each product and lets you check the profit before publishing a price. If your catalogue is still a few products and costs change little, the template is enough and nothing more is needed.
What changes the result is not the format, but the habit of reviewing costs and working out the margin on the price every time a list is published.
⬇ Download the template (Excel .xlsx)
