To use Excel 2013 to compute markup as a percentage of wholesale cost, create a formula that divides the markup amount by the cost.
Computing Markup Percent
Enter the wholesale cost and retail price for each item into a worksheet.
Compute the markup percent by writing a formula that divides the difference between price and cost by the cost. This can be visualized as (Price-Cost)/Cost. For example, if cost is $10 and price is $12, then the markup amount is $2 ($12-$10) and the markup percent is 20 percent ($2/$10).
In Excel, assuming the cost for the first item is stored in C7, the price is stored in D7, and the markup percent needs to be computed in cell E7, enter =(D7-C7)/C7 into cell E7.
Press the Enter key on your keyboard and confirm the formula returns the expected markup percent.
If the markup percent cell is not formatted as a percentage, the displayed value may be 0.2 instead of the desired 20 percent. If this is the case, change the cell format by selecting the formula cell and then the Home > Format > Format Cells icon to open the Format Cells dialog box. Change the formatting to Percentage and specify the number of decimals to display.
Fill the markup percent formula down through the range by copying the formula cell and pasting it into the remaining cells in the range. Excel updates the markup percentage when the cost or price values change.
Computing Retail Sales Price
If you know the cost and markup percentage, you can write an Excel formula that computes the retail sales price. Enter the cost and markup percent for each item into a worksheet.
The price is equal to the cost plus the markup amount. The markup amount is computed by applying the markup percent to the cost. For example, if the cost is $10 and the markup is 20 percent, the sales price of $12 is determined by adding the cost of $10 to the markup amount of $2 ($1020%). Assuming the cost is stored in cell C7, the markup percent is stored in D7 and the price will be computed in E7, enter **=C7+(C7D7)** into E7.
Fill the formula down and confirm that Excel has computed the expected sales prices.
In addition to filling the formula down by copying and pasting, you can also use several alternatives:
- Select the formula cell and then double-click the fill handle -- the little square in the lower right corner of the cell.
- Select the formula cell and then click and drag the fill handle.
- Select the formula cell and the remaining cells and then use the Home > Fill > Down command.
- Select the formula cell and the remaining cells and press Ctrl-D.
In addition to computing price with the Cost+(Cost*Markup) formula, you can use the equivalent Cost*(1+Markup) formula.
If you have a retail sales spreadsheet, you can include the cost data and use the markup formula to determine the markup percent.