4 Easy Excel Discount Formulas For Your E-Commerce Store
Updated May 5, 2023.
Discounts can sometimes make or break a sale. 83% of shoppers say that discount coupons inform their purchasing behavior directly. However, discounts need to be implemented strategically. With monitoring, businesses can calculate optimal discounts, as well as forecast ROI and profits. And Excel can be a powerful and flexible tool to help with this. Excel can streamline and optimize e-commerce operations, allowing businesses to make better-informed decisions and achieve greater success.
1. Discount Price Using Manual Formula
Calculating discount prices using manual formulas in Excel is a simple and efficient way to manage your pricing strategy to help ensure profits.
Let's say you want to offer a 20% discount on a product that originally costs $100.
Here's how you can calculate the discounted price using a manual formula:
1. Enter the original product price ($100) in cell A2.
2. Enter the discount rate as a decimal (0.2) in cell B2.
3. In cell C2, enter the following formula:
Discounted Price = Product Price - (Product Price x Discount Price)
C2=A2-(A2*B2)
4. Excel will calculate the discounted price, which is $80 in this case.
You can easily adjust this formula for different products and discount rates.
Monitor Your Sales Performance
With your discounts in place, you can track profit and revenue metrics to find whether your pricing leads to conversions.
- View data from mobile or desktop devices.
- Connect multiple shops for an integrated view of your business's revenue metrics.
- Create and export customized data reports.
With BeProfit, you can monitor your conversion rates and profits, which can help you spot when product discounts are working for your business.
2. Manual Formula to Calculate Discount Rate
To calculate a discount rate in Excel, you would typically need the optimal price you're hoping to sell the product for first. For example:
1. Enter the original product price ($100) in cell A2.
2. Put in the target discounted price ($50) in cell B2.
3. In cell C2, enter the following formula:
Discount Rate = (Product Price - Target Discounted Price) / Product Price
C2=(A2-B2)/A2
4. Excel will calculate the discount percentage required to achieve the target discounted price. In this example, this would be 50%.
Make sure you format cell C2 as a percentage. You can change this from the "Home" tab in the "Number" section, and select "Percentage" from the drop-down box.
If you'd like to decrease the decimal points so your percentage is rounded to a whole number, you can click on "Decrease Decimal" from the same section.
» Find out how to calculate revenue in Excel using simple formula templates
3. Using Array Formula to Calculate Discount Price
Array formulas in Excel can help you quickly calculate discounted prices for large sets of data.
For example, you have a dataset with 10 products and you want to calculate the discounted price for each product based on its original price and discount rate.
Here's how you can use an array formula:
1. Enter the original product prices in column A.
2. Enter the discount rates as decimals in column B.
4. In column C, enter the following formula:
Discounted Price = Product Price - (Product Price x Discount Rate)
C2=A2-(A2*B2)
5. To apply the formula to the other rows, press Ctrl+Shift+Enter. Excel will automatically calculate the discounted price for each product based on its original price and discount rate.
» Discover the 3 best Excel formulas for calculating markup vs margin
4. Find the Discount Rate With the What-If Analysis
Excel's What-If Analysis function is a bit more involved. But it can help you determine the required discount rate to achieve your desired discounted price.
For instance, let's say you have a product with an original price of $100 and a current discount price of $50, and you want to offer it at a final discounted price of $75.
1. Begin by entering the original price of the product ($100) in cell A2, the current discount rate (50%) in B2, and use the manual formula from the first method to get the current discounted price in cell C2.
2. Next, navigate to the Data tab on the Excel ribbon and click on "What-If Analysis."
Tip: Make sure the cell selected before you click on "What-If Analysis" is C2 (the "Discounted Price" cell)
3. From the drop-down menu, choose "Goal Seek."
5. In the "Goal Seek" dialog box, you will see that "Set cell" is auto-filled with C2. Now:
- Fill in "75" in the "To value" field since this is your desired discounted price.
- Click into the "By changing cell" field, then click on cell B2 to select that value.
6. After clicking OK, Excel will automatically compute the required discount rate to achieve the target discounted price of $75. In this case, a 25% discount rate should be used.
Employing the What-If Analysis function can be a great tool to test different pricing strategies and discounts before implementing them, so you can help boost sales and ensure your final amounts will still convert into revenue and profits.
» Need help? See how to increase e-commerce store revenue with customer discounts
Deduce Your E-Commerce Store's Discounts With Ease
By using Excel to deduce your e-commerce store's discounts, you can take the guesswork out of pricing. This can help you increase profit margins, boost customer satisfaction with competitive pricing, and potentially improve AOV.
But monitoring your pricing effectiveness is also vital. You can use platforms like BeProfit for seamless profit and revenue tracking, so you can have a clear overview of the impact and performance of your discounts and pricing.
