Gross Sales Formula in Excel (4 Practical Usages)

This article aims to provide a comprehensive guide on how to Use gross sales formula in Excel. In this article, we have discussed four different cases that can help readers to calculate gross sales effectively. By following the step-by-step instructions provided in this article, readers will be able to calculate gross sales, even with the inclusion of discounts, returns, allowances, and prepayment discounts. This article is beneficial for small business owners, accountants, and anyone who needs to calculate gross sales accurately and efficiently

In the business world, calculating gross sales is an essential task to keep track of a company’s overall financial health. However, calculating gross sales can be a daunting task, especially for those who are new to the field of accounting.

Download Practice Workbook

What Is Gross Sales?

Gross sales is the total amount of money a business earns by selling goods or services before taking into account any discounts, returns, or other expenses. It is the starting point for calculating a company’s revenue, and is important for evaluating the overall performance of a business. Essentially, it represents the total sales generated by a business without considering any deductions or costs associated with the sales.

Understanding Gross Sales Formula

The most common gross sales formula is:

Now let us look into the individual terms of the formula:

Net Sales: Net sales refer to the total amount of revenue generated from the sale of goods or services, after subtracting any discounts, returns, or allowances. It is the actual amount of money received by a business from its customers. Below is the net sales formula:

Sales Returns: Sales Returns refer to products that customers have returned to the business for various reasons, such as defects, damages, or dissatisfaction. When a customer returns a product, the business must reduce its sales revenue by the amount of the return. 

Discounts: Discounts are reductions in the price of a product or service offered to customers as an incentive to purchase. These discounts can be in the form of a percentage or a fixed amount.

Allowances: Allowances are reductions in the price of a product or service granted by the business to customers as compensation for defects, damages, or other issues with the product or service.

How to Calculate Gross Sales Using Formula in Excel: 4 Practical Examples

Jenny’s Electronics is an electronics store. We have taken the required sales data of that store to demonstrate the following four cases to you.

1. Calculate Total Gross Sales by Summing Daily Gross Sales

In this case, the electronic store made numerous sales transactions and needs to calculate its gross sales for the month of May. Let us get into that.

As,

We just need to type the following formula inside cell C15:

and press ENTER.

The Total Gross Sales will be visible inside the cell C15, which $53,000.

2. Calculate Gross Sales with Discounts, Sales Returns, or Allowances on Sales

In this Case, we are required to calculate the total gross sales from the following details of Jenny’s Electronics store:

Invoice 789 – The net sales were $2,000. However, a $200 discount was given on the said invoice.

Invoice 790 – The net sales after the return of goods were $150. $20 of goods were returned.

Invoice 791 – A mobile phone had a small defect. After the allowance was given, the total amount paid by the customer was $350. An allowance of $50 was given to the customer for the defect.

We know that,

And,

So, first we need to calculate the Gross Sales for each of the invoices. For that, inside cell E5, type the following:

and press ENTER.

Also, inside cell E6, type the following and Press ENTER.

Similarly, Inside cell E7, type the following and press ENTER.

Lastly we need to Sum all the individual gross sales in order to get the total gross sales. For that, Merge the cells F5, F6 & F7, inside the merged area, type the following formula and press ENTER.

We will get our desired output.

3. Calculate Gross Sales with Percentage (%) Discounts on Sales

In this case, We are required to calculate the total gross sales from the following details using Excel formula:

• Invoice 501 – The net sales were $1,500. It has given a 15% discount.
• Invoice 502 – After a discount of 25%, the net sales were $200.
• Invoice 503 – After a discount of 20%, the net sales were $450.

Here,

And,

In order to calculate the gross sales for each invoice, inside cell E5 type the following formula and Press ENTER.

After that, click at the bottom right corner of cell E5 and drag the cursor down to cell E7.

All the gross sales values will be calculated.

Finally, merge the cells F5, F6 & F7, inside the merged area, type the following formula and press ENTER.

You will get your desired output.

4. Calculate Gross Sales by Including Prepayment Discount

In this case, the store has the policy of giving a discount of 5% on the sales if payment is made within seven days of the date of the sale. The net sales for February are $50,000. Payment before seven days is made on 40% of the gross sales. We are required to calculate the gross sales amount.

As we are dealing with Percentage, Let’s assume that the total gross sale for February is $100.

If payment before seven days is made on 40% of the gross sales, then the gross sales, whose payments was made early is $40.

Now,

So,

The net sales amount on which discount is not given because of not being qualified for prepayment privilege, would be the same as the gross sales amount, which is $60.

So,

Finally, for $50000 worth of net sales, the gross sales would be

To do the entire calculation in Excel, first we need to type the following formula inside C9 and press ENTER.

The discount amount is now visible.

Then, inside cell C11, type the following formula and press ENTER.

The total net sales is visible now.

Finally inside cell C13, type the following formula and press ENTER.

We will get our desired output.

Why Do We Need Gross Sales Value?

Gross sales are an important financial metric for businesses of all sizes and types. It refers to the total amount of revenue generated by a company before any deductions are made for expenses such as cost of goods sold, taxes, and discounts.

Calculating gross sales is essential because it gives an accurate picture of a company’s financial performance and the overall demand for its products or services. By tracking gross sales over time, businesses can identify trends and patterns in their revenue, which can help them make informed decisions about future investments and growth strategies.

Moreover, gross sales serve as the starting point for calculating other financial metrics, such as net sales, gross profit, and operating profit. By understanding how to calculate gross sales, businesses can get a better understanding of their financial position and make more informed decisions to achieve their long-term goals.

How to Calculate Net Profit with Formula in Excel

Attaining the net profit value of a business could be the ultimate goal for creating an Income Statement for that business. As, most of the business decisions rely upon the net profit value.

To calculate the net profit in Excel, you’ll need to have the following information:

Revenue: Total income earned from sales of products or services.

Cost of Goods Sold (COGS): The direct costs of producing the goods or services sold.

Operating Expenses: All other expenses incurred in running the business, such as salaries, rent, and utilities.

Total Interest and Tax Expenses: The combined cost of paying interest on loans and taxes owed by a company in a given period.

Once you have this information, you can calculate the net profit using the following formula:

Let us look into the Income Statement of Jenny’s Electronics for the year ended December 31, 2022.

Type the following formula inside cell C11 and press ENTER.

You will get our desired net profit value inside cell C11, which is $180,000.

Things to Remember

• When inputting data in Excel, make sure you separate returns, discounts, and allowances in separate columns to avoid confusion and errors.
• Keep your data organized and sorted by date, customer, and product to easily track sales, returns, discounts, and allowances.

Conclusion

Whether you’re a business owner, an accountant, or just someone who wants to improve their Excel skills, this article can help you understand the concept of gross sales formula in Excel and how to calculate it using various scenarios. So, start practicing and mastering the techniques shared in this article to take your Excel skills to the next level.

If you have any queries or suggestions regarding the content, feel free to mention them in the comment section below. And don’t forget to visit ExcelDemy for more informative articles on Excel.