How to Calculate Business Income and Expense in Excel Worksheet

When you’re dealing with your personal finance, you have to have clear knowledge about it. Not knowing general financial concepts can lead you to financial disasters. Microsoft Excel can easily calculate your business income and expenses. This will save you a lot of time and energy. Today, in this article, we’ll learn five quick and suitable steps to calculate the business income and expense worksheet in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Business Income and Expense

A standardized template that keeps track of and logs a company’s income and expenses is a spreadsheet for business income and expenses. You can get some extra benefits from the income and expense statement. The benefits of the business income and expense are:

  • Taking better financial decisions.
  • Saving time.
  • Saving money.
  • Identifying erratic or fraudulent expenditures.
  • Income and Expense tracking may be seen at a glance.

5 Quick Steps to Calculate Business Income and Expense in Excel Worksheet

Let’s assume we have an Excel large worksheet that contains the information about the Income and Expenses of the Armani Group. From our dataset, we will calculate the Income and Expenses of the Armani Group using a mathematical summation, subtraction formula and the SUM function, and so on. We will do those five easy and quick steps, which are also time-saving. Here’s an overview of the dataset for today’s task.

business income and expense worksheet excel


Let’s follow the instructions below to calculate the business income and expenses!

Step 1: Use Summation Formula to Calculate Business Income

In this portion, we will calculate the business income using the summation formula. From our dataset, we can easily do that. This is time-saving also. From our dataset, we have different categories of income. Now, we will sum up those incomes using the mathematical summation formula. Let’s follow the instructions below to calculate the business income!

  • First of all, select cell F6 to apply the mathematical summation formula. Hence, write down the following formula in the Formula Bar. The formula is,
=D6+E6
  • Where D6 is the income from the category A product, and E6 is the income from the category B product.

  • After that, press ENTER on your keyboard. As a result, you will get income from several categories of products. The income is $99,817.00.

business income and expense worksheet excel

  • Hence, AutoFill the summation formula to the rest of the cells in column F.
  • While performing the above process, you will be able to calculate the income of the several products which has been given in the below screenshot.

Read More: How to Keep Track of Small Business Expenses in Excel (2 Easy Ways)


Step 2: Apply Summation Formula to Calculate Business Expense

Now, we will calculate the business expenses using the summation formula. From our dataset, we can easily do that. This is time-saving also. From our dataset, we have different categories of expenses. Now, we will sum up those expenses using the mathematical summation formula. Let’s follow the instructions below to calculate the business expenses!

  • First of all, select cell I6 to apply the mathematical summation formula. Hence, write down the following formula in the Formula Bar. The formula is,
=G6+H6
  • Where G6 is the expenses from the category A product, and H6 is the expenses from the category B product.

business income and expense worksheet excel

  • After that, press ENTER on your keyboard. As a result, you will get expenses from several categories of products. The income is $89,096.00.

  • Hence, AutoFill the summation function to the rest of the cells in column I.
  • While performing the above process, you will be able to calculate the expenses of the several products which have been given in the below screenshot.

business income and expense worksheet excel


Step 3: Use Subtraction Formula to Calculate Net Balance

In this step, we will calculate the business net balance using the Subtraction formula. From our dataset, we can easily do that. This is time-saving also. From our dataset, we have different categories of income and expenses. From those income and expenses, we will calculate the net balance using the Subtraction formula. Let’s follow the instructions below to calculate the business net balance!

  • First of all, select cell I6 to apply the mathematical subtraction formula. Hence, write down the following formula in the Formula Bar. The formula is,
=F6-I6
  • Where F6 is the total income, and I6 is the total expenses.

  • After that, press ENTER on your keyboard. As a result, you will get a net balance. The net balance is $10,721.00.

business income and expense worksheet excel

  • Hence, AutoFill the summation function to the rest of the cells in column J.
  • While performing the above process, you will be able to calculate the net balance which has been given in the below screenshot.

business income and expense worksheet excel


Step 4: Apply SUM Function to Calculate Business Income and Expense

Undoubtedly, applying the SUM function to calculate the total net balance is an easy and time-saving task. Let’s follow the instructions below to calculate the business net balance!

  • First of all, select cell I6 to apply the SUM function. After that, write down the following formula in the Formula Bar. The formula is,
=SUM(J6:J15)

  • Hence, press ENTER on your keyboard. As a result, you will get the total net balance. The total net balance is $116,307.00.

business income and expense worksheet excel


Step 5: Create Chart to Analyze Business Income and Expense

Last but not the least, we will create a chart to understand the income and expenses of the business. To understand the income and expenses of the business easily follow the instructions below to create a chart!

  • First of all, select the data range. From our dataset, we will select F6 to F15 and I6 to I15 for the convenience of our work. Hence, from your Insert tab, go to,

Insert → Charts → 2-D Column

business income and expense worksheet excel

  • Further, we will be able to make a chart to understand the business income and expense worksheet which has been given in the below screenshot.


Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Conclusion

I hope all of the suitable steps mentioned above to calculate business income and expense worksheet will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo