How to Make Profit and Loss Account and Balance Sheet in Excel

The main purpose of every business is to make profit. But everything does not go according to our plan. Sometimes we have to accept the loss. In order to calculate profit/loss, we prefer to use the profit and loss account and balance sheet. I am going to discuss here how to make profit and loss account and balance sheet in Excel for two different types of business.


Download Practice Workbook


2 Different Ways to Make Profit and Loss Account and Balance Sheet in Excel

1. Making a Profit and Loss Account and Balance Sheet for Industry Level

There are so many things to consider in terms of running an industry. So, there are more factors to emphasize to make a profit than a local shop. Here, I am going to discuss making a profit and loss account and balance sheet in Excel for an industry.

Steps:

  • First of all, gather all the data of the industry connected with money.
  • Categorized all the incomes and outcomes regarding Earnings, Expense, Assets and Liabilities.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • According to accounting, gather the knowledge of Credit and Debit. From the dataset, Earnings and Liabilities are considered as Credit. The rests are

  • Then, use the following formula to find out debit/credit:
=VLOOKUP(C5,$J$5:$K$8,2,FALSE)

Here, the VLOOKUP function is used to look up vertically in cells J5:K8 that matches with the value in cell C5.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Press ENTER to find out whether the value is debit or credit.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Followingly, categorized the amounts in the Credit and Debit

  • Next, calculate the total credit value by inputting the following formula.
=SUM(F5:F17)

The credit value in cells F5:F17 is added.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Hit ENTER.

  • Similarly, calculate the Debit value using the following formula:
=SUM(G6:G17)

Here, the debit value in cells G6:G17 is added.

How to Make Profit and Loss Account and Balance Sheet in Excel

Now, calculate the balance.

  • Input the following formula:
=F18-G18

Where, F18 is the total credit value and G18 is the total Debit value.

  • Hit ENTER to have the result.

How to Make Profit and Loss Account and Balance Sheet in Excel

Profit/Loss Calculation

  • Use the following formula to find the total income:
=SUMIF(C5:C17,"Earnings",F5:F17)-SUMIF(C5:C17,"Earnings",G5:G17)

Here, SUMIF Function is applied to sum up the value with a condition. The value connected with earnings in the Credit section is added and subtracted from the value connected with earnings in the Debit section.

  • Press ENTER to have the total income.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Similarly, calculate the total expense using the following formula:
=SUMIF(C5:C17,"Expense",G5:G17)-SUMIF(C5:C17,"Expense",F5:F17)

Here, SUMIF Function is applied to sum up the value with a condition. The value connected with expenses in the debit section is added and subtracted from the value connected with expenses in the credit section.

  • Now, press ENTER to have the total expense.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Finally, input the following formula to have the net profit/loss.
=E24-E25

Where,
E24 = Total Income
E25 = Total Expense

  • Hit ENTER to have a full profit and loss account and balance sheet.

How to Make Profit and Loss Account and Balance Sheet in Excel

Read More: Balance Sheet Format in Excel for Proprietorship Business


2. Making a Profit and Loss Account and Balance Sheet Comparing Total Amount

We can also make a profit and loss account and balance sheet in Excel with only purchase and selling prices. We can ignore other factors regarding the whole procedure.

Steps:

  • First of all, gather all the data of a shop connected with purchasing and selling.

Here, I have the data of each product’s total purchasing money and total selling amount.

  • Now, input the following formula to find out the profit or loss.
=IF(I6>F6, "PROFIT",IF(I6<F6, "LOSS", " NO PROFIT/LOSS"))

Here, I applied the IF Function between the total selling price of a product and the total purchasing price of that product. If the total selling price of a product is greater than the total purchasing price of that product, it will be considered as PROFIT.
Again, If the total selling price of a product is smaller than the total purchasing price of that product, it will be considered as LOSS. If the total selling price of a product is equal to the total purchasing price of that product, it will be considered as NO PROFIT/LOSS.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Hit ENTER to have the output.

  • Then, AutoFill the rest cells.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Next, use the following formula to have the amount of profit or loss.
=ABS(I6-F6)

Here,
I6 = Total Selling Price
F6 = Total Purchasing Price

I used the ABS Function here to ignore the negative value.

  • Now, press ENTER to have the amount of profit or loss.

How to Make Profit and Loss Account and Balance Sheet in Excel

  • Finally, use Fill Handle to AutoFill the remaining cells.

Thus, we can make a profit and loss account and balance sheet.

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)


Conclusion

In this article, I have tried to articulate how to make profit and loss account and balance sheet in Excel for two different types of business. I hope it will be helpful for all. For any further questions, comment below. For more information regarding Excel, you can visit our Exceldemy site.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo