ExcelInsider Team
Profit and loss statement, also known as the income statement, is one of the core financial statements that a company has to make every year. An income statement includes all of the revenues and expenses of a year so that we can calculate the company’s profit/loss for that year. Most of the financial statements are made using Excel, and it is easy to make this one in Excel as well. In this article, we will learn how to create a profit and loss statement in Excel.
➤ Put the revenue first, then subtract the cost of goods sold from the revenue using the following formula to calculate the gross profit:
=B3-B4
➤ Replace B3 with revenue and B4 with COGS.
➤ Subtract the operating expenses from the gross profit using the following formula:
=B5-SUM(B7:B10)
➤ Replace B5 with the gross profit, and B7:B10 with the operating expense range.
➤ Finally, calculate the net profit with the following formula:
=B12-SUM(B13:B16)
➤ Here, B12 is the operating profit you got from subtracting the operating expenses from the gross profit, and B13:B16 is the range of indirect expenses.
Most companies require a monthly distribution of profits and losses; however, they also need to be presented in a yearly statement. To ease things, we can also use a pivot table, which will massively cut down the time for creating the profit and loss statement. In this article, we will learn all of the methods we can use to create a profit and loss statement in Excel.
Creating a Monthly Profit and Loss Statement in Excel
We have a table with 17 entries to create the profit and loss statement with. There are dates, account names, and the amounts associated with those accounts. We will create a profit and loss statement with these entries that will include all months and provide a comprehensive look at the company’s income.
➤ Create a table with 7 columns. The first one should be Description, and the next 6 are the names of the months.
➤ First, let’s insert the Sales Revenue. Add the description first in the table, and then we will need to add the values.
➤ There are multiple sales revenues in the source table, and we can’t really waste our time looking for each one of them. Here is what we can do instead.
➤ Right-click on a Sales Revenue cell.
➤ Select Filter > Filter by Selected Cell’s Value.
➤ Now, we can see that for month 4 (April), there is no sales revenue. If there were sales revenues for all months, we could have copied all of the amounts from this table. As there are not, we need to copy the first three months first, then the next two months.
➤ Select the amount cells for the first three months and press Ctrl + C to copy.
➤ In the profit and loss statement, go to the B2 cell.
➤ From the Clipboard group of the Home tab, select Paste > Transpose.
➤ Now those three values are pasted properly.
➤ Do the same for the next two months as well.
➤ There are no revenues anymore. Sum up the revenues in the next cell of the profit and loss statement with the description: Total Revenue. As there is only one entry, we can just copy and paste the values.
➤ To import COGS, click on the filter icon of the Account Name, select COGS, and deselect Sales Revenue. Press OK to confirm.
➤ Now, only the COGS values will show up, and you can copy and paste like the previous one.
➤ In the next cell, calculate the gross profit using the following formula, and autofill from B5 to G5.
➤ Now, we need to insert the operating expenses. In the source table, the Rent Expense, Salaries Expense, Utilities Expense, and Advertising Expense accounts are operating expenses.
➤ To calculate the Total Operating Expense, insert the following formula in the B11 cell, and autofill till G11.
=SUM(B7:B10)
➤ Calculate the operating profit in B12 with the following formula, and autofill to G12.
=B5-B11
➤ There are four indirect, non-operating expenses here. Those are Bank Fees, Depreciation Expense, Insurance Expense, and Interest Expense.
➤ Calculate the total indirect expense using the following formula, then autofill the rest of the months:
=SUM(B13:B16)
➤ Finally, calculate the net profit using the formula below. Don’t forget to autofill the cells:
=B12-B17
Create a Semiannual Profit and Loss Statement in Excel
The monthly profit and loss statement we just created can be used to create a semiannual profit and loss statement. Here is how we can do that:
➤ Add another column to the monthly profit and loss statement called Semiannual.
➤ Enter the following formula in cell H2 and autofill till H18.
=SUM(B2:G2)
➤ We already have a yearly profit and loss statement sheet with the values of the second half of 2024. All we have to do is copy this column, from H2 to H18, to the old sheet with the column header H1 2025.
Using Pivot Table to Create the Profit and Loss Statement in Excel
This method is the easiest among all of them, although it needs some preparation and tricks. Follow the steps below to create a pivot table that will show the profit and loss of the company.
➤ First, add a new column to the source table with the categories of the accounts. Categories can include revenue, direct expense, operating expense, and indirect expense.
➤ Go to the Insert tab of the ribbon, and select PivotTable. In the new window, select OK to create a new pivot table.
➤ Select all of the fields from the PivotTable Fields section at first.
➤ Deselect Date and Days (Date).
➤ Move Months (Date) to Columns, and move Category over Account Name.
➤ Go to the Design tab, and head to the Layout group.
➤ In the Subtotals button, select Show all Subtotals at Bottom of Group.
➤ For Grand Totals, select On for Rows Only.
➤ The row labels are not organized the way we want. Let’s organize it now.
➤ Right-click on Revenue, and select Move > Move “Revenue” to Beginning.
➤ Right-click on Operating Expense, and select Move > Move “Operating Expense” Up.
➤ Now, calculate the gross profit, operating profit, and net profit using the following formulas in the B20, B21, and B22 cells.
=B7-B10
=B20-B16
=B21-B19
➤ Select B20:B22 and autofill till H20:H22.
Frequently Asked Questions
What is the basic formula for a profit and loss statement?
The basic formula for a profit and loss statement is: Net revenue – COGS – Operating Expenses – Indirect Expenses = Net Profit. However, in some cases, net revenue will be divided into direct and indirect revenue, and indirect expenses can include taxes not mentioned in the trial balance.
What is the general formula for profit and loss?
The general formula for profit is: Selling Price – Cost Price. The loss formula is the opposite: Cost Price – Selling Price.
What are the 4 types of financial statements?
The four types of financial statements are balance sheet, income statement (also known as the profit and loss statement), cash flow statement, and owner’s equity statement. However, the last one is more of a helper sheet and not a needed statement. Hence, the first three are the main financial statements.
What are the red flags on a P&L statement?
Unusual account names in the statement, weird fluctuations, inconsistent trends, etc., are the common red flags on a profit and loss statement.
What are the three types of profit on an income statement?
The three types of profit on an income statement (or a profit and loss statement) are the gross profit, operating profit, and the net profit.
Wrapping Up
In this article, we have learned three ways to create a profit and loss statement. We have gone through the monthly profit and loss statement, the yearly profit and loss statement, and the pivot table-based profit and loss statement. If you have a better method to do these, leave them in the comment section; it will be helpful for others. We will see you in the next article.
