How to Calculate Total Revenue in Excel [Free Template]
For businesses, tracking and calculating revenue is a fundamental aspect of financial analysis. Excel, with its powerful features, makes revenue calculation a breeze. In this article, I’ll walk you through the steps to calculate total revenue in Excel, providing a clear and concise guide for financial professionals and business owners.
What is Total Revenue?
Total revenue refers to the total amount of money a business or organization receives from its sales of goods or services during a specific period. It is the income before deducting any expenses, such as product-making costs, salaries, rent, and taxes.
Here’s the formula to calculate Total Revenue:
Total Revenue = Unit Price * Quantity
Here, Unit Price is the price of a single item, and Quantity is the total number of units sold.
However, it is a fact that calculating the total revenue can be a more intricate process. Because the total revenue always includes factors such as discounts, taxes, shipping costs, etc. These factors must be included in the calculation. In such cases, the formula for total revenue would be:
Total Revenue = (Quantity x Unit Price x (1 – Discount)) + Shipping Cost + (Quantity x Unit Price x Tax)
Remember, the factors may or may not be the same depending on your activities.
Steps to Calculate Total Revenue in Excel
Follow these steps to calculate Total Revenue in Excel:
Step 1: Organize Your Data
Begin by organizing your data in Excel. Create a spreadsheet with relevant columns: “Product,” “Quantity,” “Unit Price,” “Discount,” “Shipping Cost,” and “Tax Rate.” In this example, I have a product list containing products A to G in column A, starting from cell A2.
Step 2: Calculate Individual Revenues for Each Product
To calculate the revenue for each product, use the following formula in cell G2:
=B2*C2*(1-D2)+E2+(B2*C2*F2)Here,
- B2 is the Quantity of Product A.
- C2 indicates the Unit Price of Product A.
- D2 is the Discount amount.
- E2 denotes the Shipping Cost of the product.
- F2 is the Tax Rate on Product A.
This formula takes into account the quantity, unit price, discount, shipping cost, and tax rate to calculate the revenue for a single product.
Step 3: Copy the Formula
Once you’ve entered the formula in cell G2, simply copy it down to apply the same calculation to all products. You can do this by selecting the cell with the formula, grabbing the small square at the bottom-right corner of the selection, and dragging it down to fill the cells in column G for each product.
This way, I determined the revenue generated from each product.
Step 4: Calculate Total Revenue
To find the total revenue across all products, use the SUM function. In a cell below your calculated revenues (e.g., G9), enter the formula:
=SUM(G2:G8This formula sums up the individual revenues from products A to G, giving you the total revenue for your dataset.
Step 5: Format for Clarity
To make your total revenue stand out, consider formatting it. You can use Excel’s number formatting options to display it in your preferred currency format. Right-click on the cell, choose “Format Cells,” navigate to the “Number” tab, and select the currency format that best suits your needs.
Total Revenue Calculator
Total Revenue:
Download the Total Sales Revenue Excel Template
Here’s a free Excel template for you to calculate the total revenue. This spreadsheet lets you calculate total revenue and net income from your sales data. You can set up the quantity, unit price, markup, & shipping cost to analyze your monthly sales. It also generates some charts and graphs to help you understand how your sales perform over the month.
What are Different Types of Revenue?
There are various kinds of revenue in the business world. The following 5 types of revenue are the most important to understand among the several types:
1. Incremental Revenue
It is the additional revenue that is generated from selling more units of a product or service. It is the difference between the revenue from the previous sales and the revenue from the current sale.
To calculate the incremental revenue you must subtract the previous revenue or the original revenue earned from the product or service. It is the extra revenue coming from a product.
Increment Revenue Formula:
Increment Revenue = Additional Revenue – Original Revenue
Here,
- Additional Revenue = Additional Number of Units Sold x New/Original Selling Price
- Original Revenue = Original Number of Units Sold x Original Selling Price
2. Average Revenue
It is the revenue earned from products divided by the number of products sold. It represents the average revenue earned per unit of product or service.
Average Revenue Formula:
Average Revenue = Total Revenue / Quantity
3. Gross Revenue
It is the total revenue earned from all sales before deducting any expenses or costs. It is also known as gross income. Remember, any type of loss or cost will not be part of the gross revenue.
Gross Revenue Formula:
Gross Revenue = The Number of Units Sold x Average Price
4. Quarterly Revenue
It is the total amount of revenue earned in a three-month period of a fiscal year by a company. Many companies tend to report their financial results on a quarterly basis. It helps them compare the financial growth with previous years. The general formula of quarterly revenue is summing up the total revenue of 3 months.
5. Marginal Revenue
Marginal revenue is the increase in revenue earned from the sale of each extra unit of a product or service.
Marginal Revenue Formula:
Marginal Revenue = Change in Total Revenue/Change in Quantity
Or,
Marginal Revenue = (Total Revenue – Old Revenue) / (Total Quantity – Old Quantity)
Note: You may find the marginal revenue and incremental revenue both similar at first. But there is a slight difference between these terms. Marginal revenue is the additional revenue generated by selling one extra unit of a product. On the other hand, incremental revenue refers to the increase in revenue resulting from a specific change or action, such as launching a new product line or increasing advertising spend. The incremental revenue takes into account all types of change, not just the increased price of units.
Total Revenue Vs. Net Revenue
Total revenue is the total amount of money a company makes from all sources, like sales, interest, and investments. It is estimating only the income before considering any expenses, losses, or deductions in the business.
On the other hand, net revenue, also known as net sales, is the total revenue minus any deductions or returns. It is the remaining portion of the core income of a business.
Usually, total revenue is important for assessing the overall performance. On the other hand, net revenue is important for indicating the profitability of a business. However, having a smaller difference between total revenue and net revenue is a good sign for a company because it means the revenue deduction is less.
Conclusion
Calculating total revenue in Excel is a straightforward process. By entering the appropriate formulas and functions, you can efficiently analyze the financial performance of your products and generate valuable insights. Excel’s flexibility and functionality make it a valuable tool for businesses and individuals alike.
Frequently Asked Questions
Can I calculate total revenue in Excel with different currencies?
Yes, Excel allows you to format your total revenue with various currency symbols and decimal places, making it adaptable to different currencies.
How do I calculate total revenue for a specific time period in Excel?
To calculate total revenue for a specific time period, you can use Excel’s filtering and sorting functions to isolate the data for that period and then apply the same calculation method described in this article.
Are total revenue and total income the same?
No. Total revenue and total income are not the same. Total revenue is a component of total income. Total income is a broader concept that includes all sources of income. Total revenue refers to the income generated by a business through sales or other means. Interest, dividends, or rental income – these are part of total revenue. Incomes like wages, salaries, capital gains, etc. are part of total income.
Is total revenue equal to total sales?
Yes, total revenue is equal to total sales. The terms total revenue and total sales can be used interchangeably to refer to the total amount of money earned by a business or organization through its sales activities.
What is the formula for the revenue multiple?
The formula for the revenue multiple is:
Revenue Multiple = Enterprise Value (EV) / Revenue
Here,
- Enterprise Value (EV) = Market capitalization + Total debt – Cash and cash equivalents
- Revenue = Total revenue of the company over a given period (e.g. fiscal year)
While a higher revenue multiple indicates that a company may be generating more revenue per year, a lower revenue multiple may indicate that a company is generating less revenue per year.
How do you calculate total revenue from the cost function?
Unfortunately, it is not possible to calculate total revenue directly from a cost function. The cost function provides information about the relationship between quantity and the cost of producing those goods. However, it does not provide any information about the quantity and price of the products that are sold.
Where can I find a company’s total revenue?
You can usually find a company’s total revenue in its financial statements or annual reports. Publicly traded companies are required to disclose their financial information to the public, including their total revenue, in their annual reports filed with regulatory bodies such as the Securities and Exchange Commission (SEC) in the United States.
How do you calculate total revenue on a balance sheet?
A balance sheet does not show total revenue, as revenue is a component of the income statement. The balance sheet shows a company’s financial position at a particular point in time, including its assets, liabilities, and equity.
What is the function for revenue in Excel?
The function for revenue in Excel is the PRODUCT function, which multiplies the number of items sold by their price per unit to calculate the total revenue.
How do you calculate total revenue from the cost function?
It’s not possible to directly calculate total revenue from a cost function because they are two different concepts. However, you can use the cost function along with other information to estimate the total revenue generated by a business.
Related Articles
- How to Calculate Profitability Index in Excel [Free Template]
- How to Find Interquartile Range (IQR) in Excel [Free Calculator]
- 2 Ways to Calculate Cumulative Frequency in Excel
- Calculate Percentage of a Number in Excel [4 Cases Explained]
- Calculate 3 Types of Profit Margin in Excel
- 2 Ways to Calculate Percentage Increase in Excel
