The main purpose of every business is to make a 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 accounts and balance sheets in Excel for two different types of business.

**Table of Contents**Expand

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

### 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, Expenses, Assets, and Liabilities.

- 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 the value in cell **C5**.

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

- Use
**Fill Handle**to**AutoFill**the rests.

- 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.

- HitÂ
**ENTER**.

- Similarly, calculate the Debit value using the following formula:

`=SUM(G6:G17)`

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

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.

**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, **the 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.

- Similarly, calculate the total expense using the following formula:

`=SUMIF(C5:C17,"Expense",G5:G17)-SUMIF(C5:C17,"Expense",F5:F17)`

Here, the **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 see the total expense.

- 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.

**Read More: **Calculate Debit Credit Running Balance Using Excel Formula

### 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.

- Hit
**ENTER**to have the output.

- Then,
**AutoFill**the rest cells.

- Next, use the following formula to determine 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.

- Finally, use
**Fill Handle**to**AutoFill**the remaining cells.

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

**Read More: **Debit Credit Balance Sheet with Excel Formula

**Download Practice Workbook**

## 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. Have a nice day!

**Related Articles**

- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Make Projected Balance Sheet in Excel
- How to Calculate Running Balance Using Excel Formula
- How to Keep a Running Balance in Excel
- How to Tally a Balance Sheet in Excel
- How to Make Trial Balance in Excel
- How to Make Stock Balance Sheet in Excel

**<< Go Back To How to Make Balance Sheet in Excel** **|****Excel For Finance** **| Learn Excel**