In this article, we will explain how to make a profit and loss account and balance sheet for a business, and also how to make a profit and loss account for products.

### Case 1 – Making a Profit and Loss Account and Balance Sheet for a Business

**Steps:**

- Create a dataset with the same columns as in the image below.
- Gather all the relevant financial information for the business required to work out the profit and loss. This includes the amounts for each category of expenses and income, and the value of the assets and liabilities.
- Enter this information, with each line item categorized as either Earnings, Expenses, Assets, or Liabilities.

To derive the Credit/Debit column, Earnings and Liabilities are considered Credits, while Expense and Assets are considered Debits.

- In cell
**J5**, create the following table:

- Enter the following formula in cell
**E5**:

`=VLOOKUP(C5,$J$5:$K$8,2,FALSE)`

Here, **The VLOOKUP ****function** looks for the value in cell **C5** in the range **J5:K8 **and returns *Credit* or *Debit* accordingly.

- Press
**ENTER**.

- Use the
**Fill Handle**to**AutoFill**the rest of the cells in the column.

Now we’ll use the filled **Credit/Debit** column to fill the amounts in the **Credit** and **Debit** columns.

- In cell
**F5**, enter the following formula:

`=IF(E5=$E$5;D5)`

- Press
**ENTER**. - Use the
**Fill Handle**to**Autofill**the rest of the cells in the column. - In cell
**G5**, enter the following formula:

`=IF(E5=$E$6;D5)`

- Press
**ENTER**. - Use the
**Fill Handle**to**Autofill**the rest of the cells in the column.

Let’s get values for **Total Credit** and **Total Debit**.

- In cell
**F18**, enter the following formula:

`=SUM(F5:F17)`

- Press
**ENTER**.

- Similarly, in cell
**G18**enter the following formula:

`=SUM(G6:G17)`

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

Now we can calculate the balance.

- In cell
**C20**, enter the following formula:

`=F18-G18`

Where **F18 **is the **Total Credit** value and **G18 **is the **Total Debit** value.

- Press
**ENTER**.

**Profit/Loss Calculation **

- In cell
**C24**, 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 **adds values that meet a criterion.

- Press
**ENTER**.

- Similarly, in cell
**C25**, calculate the**Total Expense**using the following formula:

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

- Press
**ENTER**.

- Finally, enter the following formula in cell
**C26**to return the**Net Profit/Loss**.

`=E24-E25`

Where,

**E24 **= Total Income

**E25 **= Total Expense

- Press
**ENTER**.

Our full profit and loss account and balance sheet is complete.

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

### Case 2 – Making a Profit and Loss Account for Products

We can also make a profit and loss account to work out whether a product or range of products are making a profit or loss. There is no need to make a balance sheet in this case.

**Steps:**

- Create a dataset that looks like the image below,
- Enter all the purchase and sales information for the products, including their Names, Quantities, Unit Prices and Totals.

Now let’s calculate the profit and loss.

- In cell
**J6**, enter the following formula:

`=IF(I6>F6, "PROFIT",IF(I6<F6, "LOSS", " NO PROFIT/LOSS"))`

Here, we use **the** **IF Function** to check if **Total Sales** is greater than **Total Purchases**. If it is, “**PROFIT**” is returned, else another IF function checks if **Total Sales** is less than **Total Purchases**. If it is, “**LOSS**” is returned, else “**NO PROFIT/NO LOSS**” is returned.

- Press
**ENTER**.

**AutoFill**the rest of the cells in the column.

- In cell
**K6**, use the following formula to determine the amount of profit or loss:

`=ABS(I6-F6)`

Here,

**I6 **= Total Sales

**F6 **= Total Purchases

**The ABS Function **ignores negative values.

- Press
**ENTER**.

- Use the
**Fill Handle**to**AutoFill**the remaining cells.

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

**Download Practice Workbook**

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