In the business world, calculating sales revenue is a crucial task that helps organizations to monitor their financial performance. Excel, being one of the most popular spreadsheet software, offers various formulas to calculate sales revenue with ease and accuracy. Using Excel, you can quickly calculate sales revenue based on different scenarios, such as fixed pricing, variable pricing, etc. In this article, we will explore six different examples of how to calculate different types of sales revenue using Excel formulas. By the end of this article, you will have a better understanding of the Sales Revenue Formula Excel and how to use it effectively
Download Practice Workbook
You can download the Excel workbook that we used to prepare this article.
How to Use Sales Revenue Formula in Excel (6 Suitable Examples)
1. Calculating Sales Revenue By Multiplying No of Units sold with Price
In this article, we are going to find out sales revenue by multiplying no of units sold by their prices.
Sales revenue= No of Unit Sold x Their Prices
We are going to use conventional formulas for most of the calculations, and the SUM function at the end. The SUM function sums up the range, arguments, or arrays inside of it.
To do so we are going to follow the following steps on the below dataset.
- First, we are going to multiply price and quantity to find individual revenue. Here in cell F5, we calculated sales revenue by multiplying price (cell E5) and quantity(cell D5).
- Now we are going to drag the Fill Handle bar down from F5 to F12. After pressing Enter in the F5 cell.
- Now we are going to find out Gross Sales Revenue by summing up individual sales revenue.
- Finally, we are going to find out individual and gross sales revenue.
2. Calculating Sales Revenue By Pricing Table
In this article, we are going to find out sales by using a pricing table.We have used VLOOKUP function to find out the individual price from pricing table then we calculate the individual sales revenue and summed them up for gross revenue .To do so we are going to follow the steps given below in the dataset shown below.
- First, we are going to use the VLOOKUP function to seek out the price of the fabric.
=VLOOKUP(B5,$H$5:$J$8,3)
🔎Formula Breakdown
👉 The first argument, “B5”, is the value that we want to look up in the table.
👉 The second argument, “$H$5:$J$8”, is the table array that we want to search. This table array is composed of three columns: H, I, and J, and four rows: 5, 6, 7, and 8. Note that the dollar signs in front of the row and column references make them absolute references, which means that they won’t change when the formula is copied to other cells.
👉 The third argument, “3”, is the column index number that we want to return a value from. In this case, we want to return a value from the third column of the table array (column J).
- After pressing Enter we are going to drag the Fill Handle down C5 to C11 to find individual prices.
- Then we are going to find out sales revenue like example 1 by multiplying the Price and Meters of Fabric Sold. We will be using the following formula.=C5*B5.
- Now after pressing Enter and dragging the Fill Handle icon down, we find the individual sales revenue.
- After that, we are going to find out gross sales revenue. To do so, we just have to sum up the individual sales revenue.
- After pressing Enter, we are going to have total sales revenue.
3. Calculating Incremental Sales Revenue
It is very easy to find incremental sales revenue. The formula for incremental sales revenue,
Incremental Sales Revenue=Sales Revenue(Present)-Sales Revenue(Previous Date)
In order to find incremental sales revenue in Excel we are going to follow the steps given below. We have used a dataset given below.
- First, we put the formula in the G5 cell.
- After pressing Enter, we are going to find Incremental Sales Revenue.
4. Calculating Average Sales Revenue
For calculating average sales revenue we have divided gross revenue by the total quantity sold. How to find gross sales revenue is given in both example and example 2. We are just going to divide gross sales revenue by the total quantity sold. To do so, we have to follow the steps given below and use the dataset below.
- First, we are going to write the formula in H8. The formula is Gross Sales Revenue/total sold quantity. We have used the SUM function to find out the total quantity sold.
- The formula in Excel is
=H5/SUM(D5:D12)
SUM(D5:D12)=Sum of Sold Quantity
- Now pressing Enter, we are going to find the average sales revenue.
5. Calculating Quarterly Sales Revenue
In this section, we are going to find quarterly sales revenue using Excel formula by subtracting the old total sales revenue from the new quarterly sales revenue. We have used the SUM function to find out the quarterly sales revenue of both quarter 1 and quarter 2 and subtracted quarter 1 total sales from Quarter 2 sales to find out quarterly sales revenue. To do so, we are going to follow the steps given below and we have used the dataset below.
- First, we are going to find out the total individual sale revenue below.
- Then we are going to use the following formula in cell E5 and fill up to E8 accordingly.
=D5*C5
- Then we are going to sum up the total sales of quarter 1 in F9 cell.
- Likewise, we have to create the data set of quarter 2 like quarter 1 by multiplying Price(D12)*Unit Sold(C12) in the E12 cell and dragging the fill handle icon from E12 to E15 cell. Then sum up E12 to E15 in the E16 cell.
- Then finally we are going to find out the Quarterly Sales Revenue by subtracting Q2 Total(E16)-Q1 Total(E9).
6. Calculating Marginal Sales Revenue in Excel
To calculate marginal sales revenue we have to follow this formula,
(Total Revenue - Old Revenue) / (Total Quantity - Old Quantity)
To do so in Excel, we have to follow the steps and use the dataset below.
- First, we have to write the Excel formula in the B9 cell.
Marginal Sales Revenue=(Total Revenue(F6) – Old Revenue(F5)) / (Total Quantity(D6) – Old Quantity(D5)), using this formula we find the marginal sales revenue.
- You can copy the formula from here.
=(F6-F5)/(D6-D5)
- After pressing Enter, we are going to have the Marginal Sales Revenue.
Frequently Asked Question
- Q: Why is it important to calculate sales revenue in Excel?
A: Calculating sales revenue in Excel is important because it helps businesses track their financial performance, monitor their sales trends, and identify areas where they can improve their revenue streams. By using Excel formulas to calculate sales revenue, businesses can also automate their calculations, save time, and reduce the risk of errors.
- Q: Can I use Excel to create sales reports and projections based on sales revenue data?
A: Yes, you can use Excel to create sales reports and projections based on sales revenue data by using pivot tables, charts, and other analysis tools. These reports and projections can help you gain insights into your sales performance, forecast future trends, and make data-driven decisions to improve your business outcomes.
- Q: How can I use Excel to track my sales revenue on a daily, weekly, or monthly basis?
A: You can use Excel to track your sales revenue on a daily, weekly, or monthly basis by entering your sales data into a worksheet or table, and using date and time functions to group your data by day, week, or month. You can then use formulas and charts to calculate and visualize your sales revenue for each period.
- Q: Can I use Excel to calculate sales revenue for e-commerce transactions, such as online orders and digital products?
A: Yes, you can use Excel to calculate sales revenue for e-commerce transactions by importing your sales data from your e-commerce platform or payment processor into Excel, and using formulas to calculate the revenue based on the quantity and price of the products sold. You can also use Excel to track your e-commerce sales performance, analyze customer behavior, and optimize your online store for better revenue outcomes.
Things to Remember
- Make sure to enter your sales data accurately into Excel, including the quantity, price, discounts, taxes, and shipping costs for each product sold.
- Use absolute cell references ($) when using formulas that refer to specific cells or ranges, to avoid errors when copying and pasting the formulas.
- Check your formulas and calculations carefully to make sure they are correct, and use error-checking tools such as Excel’s “Trace Error” feature to identify and fix any errors.
- Use Excel’s formatting tools to make your sales revenue calculations more visually appealing and easier to read, such as number formats, conditional formatting, and charts.
- Regularly update your sales data and recalibrate your formulas as needed to ensure accurate and up-to-date sales revenue calculations.
Conclusion
In conclusion, learning how to calculate sales revenue using Excel formula can be a powerful tool for businesses of all sizes. By mastering the different formulas for sales revenue, businesses can automate their sales revenue calculations, gain insights into their financial performance, and make data-driven decisions to improve their revenue streams. With the right data inputs, formulas, and formatting techniques, Excel can be a powerful ally in the quest for increased revenue and profitability.
