Excel formula for calculating selling price with cost
Often, calculations that are easy on paper and with a calculator can become challenging when transferring them to Excel. However, mastering this skill can save a substantial amount of time. Let's learn how to calculate product prices while taking a specified margin into account.
What Is Margin?
Essentially, margin is a synonym for the term "markup" expressed in monetary terms. However, when dealing with percentages, margin and markup have different meanings due to their definitions. Let's compare them:
Denominators in the margin calculation formula.
- Margin is the ratio of the price minus the cost to the price.
- Markup is the ratio of the price minus the cost to the cost.
That's where the difference lies.
Calculating Price with Margin and Cost
Now that we understand the distinction between margin and markup, we can derive the price formula. How do we calculate the price knowing the margin and the cost?
Notation of the formula's arguments:
- M - Margin.
- P - Price.
- C - Cost.
If M = (P - C) / P, then P = C / (1 - M).
Essentially, it's quite simple. You just need to input the margin and cost values into Excel and then enter the price formula. However, this formula is for a specific case. In the economy, costs that determine margin constantly change. Therefore, let's prepare a comprehensive calculation considering other variables.
We'll create a table for calculations and specify the parameters beforehand (in mathematical terms, we'll provide the known values).
| Date | margin | percentage of managers | deferment, calendar days | bank rate | wholesaler bonus | VAT rate |
| 10/28/2023 | 40% | 5% | 30 | 10% | 15% | 7% |
| Product Name | Estimated price per piece. VAT included | Estimated price per piece. without VAT | Cost of 1 piece. without VAT | Gross income excluding VAT | Markup, % | Manager's percentage | Markup, % | Postponement | Wholesaler retro bonus | Total costs | Marginal income | Margin, % |
| Product1 | 44.64 | |||||||||||
| Product2 | 100 | |||||||||||
| Product3 | 600 | |||||||||||
| Product4 | 900 |
The table contains only the values that we currently know - the costs of the products. The set parameters are constant. Let's proceed step by step.
We'll calculate the deferral according to the formula: multiply the cost by the bank interest rate, divide by the number of days in a year, and by the deferral days.
=D6*$E$3/DAYS(DATE(YEAR($A$3)+1,1,1),DATE(YEAR($A$3),1,1))*$D$3
We see that the cells where the interest rate and deferral are specified are absolute, denoted by the dollar signs. This is done to keep these values constant when extending the formula.
Now, we can calculate the calculated price without VAT using the economic formula.
=(D6+H6)/(1-$C$3-$F$3-$B$3)
Next, the price with VAT (7% VAT for states like Mississippi, Indiana, Rhode Island, and Tennessee).
Next, the gross income (the price difference and the cost).
Now, we calculate the markup. In our case, the markup formula is simplified since the gross income is calculated earlier.
The markup indeed remains the same, indicating we're on the right track. Next is the manager's percentage: 5% of the calculated price.
Similarly, the retro-bonus, which is 15% of the calculated price.
Total expenses, which are the sum of three components (manager's percentage, wholesaler's percentage, and deferral).
Calculating marginal revenue as the difference between gross income and expenses.
Done. Now we need to verify whether the calculations were performed correctly. To do so, we'll calculate the margin using another formula: the marginal revenue divided by the price.
The margin indeed becomes 40%, which means everything is correct. With this table, you can change the margin or cost values and instantly see all the indicators. Moreover, all markups will remain equal, and the margin will be consistent for cross-checking.
Let's try changing the margin to 15% (as per the set parameters). Let's see what happens.
Or let's change the cost of the first product.
DownloadAs we can see, all the indicators automatically change while economic principles remain intact. Similarly, you can adjust other set parameters.
