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.
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.
How to Calculate Business Income and Expense in Excel Worksheet: 5 Quick Steps
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.
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.
- 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
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.
- 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.
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.
- 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.
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.
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
- 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
