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

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.

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.