How to add markup to a base price list in Excel
You can solve elementary marketing tasks efficiently using Microsoft Excel. Let's learn how to apply a markup to a base price list so that the customer won't notice the difference.
Example working with values
We will demonstrate this using a basic table:
| Products | Price |
| Product 1 | $6,800.00 |
| Product 2 | $4,700.00 |
| Product 3 | $3,200.00 |
We have five products that we want to sell quickly without losing value. To achieve this, we'll employ a clever marketing trick: we'll announce a 20% discount but sell at the same price as before. Therefore, we need to calculate how much Product 1 should originally cost to have the original price of $6,800 with a 20% discount.
We create a simple mathematical proportion:
| Formula for Calculation |
| 6800 = (100 - Discount)% |
| X = 100% |
| X = (6800 * 100)/(100 - Discount) |
X represents the new product price. By reducing it by the discount percentage, we will obtain the initial amount we intended to sell the product for originally.
Now, add new columns to our price list. In cell D3, enter the formula for X derived earlier. Drag it down to the other products. You will get the following result:
Now, you can change the prices in your store to the new ones and put up a big banner saying "20% OFF." The likelihood of attracting more customers will significantly increase.
You can adjust the data in the "Customer Discount" column to apply different discounts to various items. The new price will change automatically. Let's give it a try.
We now have new price values. Round them to the nearest dollar since cents are rarely used for such large sums. To do this, slightly modify the formula in cell D3. Place the ROUND function before it. The first argument will be our completed formula, and the second argument is the precision. Since we're rounding to the nearest dollar (meaning we don't need any decimal places), set it to 0.
Drag the formula, and you will see the result:
The prices are now rounded to the nearest dollar. You can add new products and their prices to this template to apply markups at any percentage. It's better to move the price list that customers will see to another sheet or a different workbook. Copy-pasting normally transfers the formulas from cells with new prices, which can lead to issues:
We merely copied the data from the previous table using CTRL+C and pasted it using CTRL+V. The formula was also copied, and it's looking for a reference that doesn't exist on this sheet. Therefore, we will transfer data using "Paste Special."
Copy the new prices from the previous table with CTRL+C and go to the new sheet. Right-click cell B2 and select "Paste Special." The following window will appear:
DownloadChoose "Values" and click OK. You'll see that only values were pasted. Your updated price list with markups is ready.
