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.

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

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF