How to Create a Profit and Loss Statement in Excel
A Profit and Loss statement (or P&L) is one of the three major financial statements and an important part of reporting and forecasting. Let’s walk through how to create a profit and loss statement in Excel.
What is a Profit and Loss Statement (P&L)?
A P&L compiles the revenue and expenses of a company for a specific period of time to show profit (revenue greater than expenses) or loss (revenue less than expenses). The P&L is constructed around the formula Revenue-Expense=Profit(Loss).
What Information Will I Need?
First, you will need data to feed the P&L. This can come from a general ledger, transaction detail, manual entry, or whatever you may have on hand. If you have access to an accounting system such as QuickBooks or SAP, you can export data from those systems to evaluate.
Next, the transaction detail needs to be categorized. The minimum categories you will need are the type of revenue or expense and the time period that the entry occurred. This will feed the row and column headers of the P&L.
Lastly, you will need to know the goal of the P&L in order to lay it out. Sometimes, you will build a P&L to evaluate an operating unit, a project, or a specific part of your business. In this case, the P&L will only need to show through Operating Income (also called EBITDA). If you want to show the results for a full business, you will need to include all below the line expenses like Tax and Depreciation.
Example: Building A Simple P&L
Now, let’s walk through an example of Building a Profit and Loss Statement (P&L) in Excel.
Step 1: Compile Data
First, we need to compile the data that will be used in the P&L. This can often be exported from an accounting system like QuickBooks, Oracle, or SAP into Excel. Make sure to include the following information at a minimum:
- Type of revenue/expense
- Period
- $ amount
You should place this information a separate data tab to keep the P&L clean.
Step 2: Layout the P&L
Next, you will layout a P&L to view the time period and categories of revenue and expense that are important to your reporting.
Step 3: Write Formulas to Fill Out the P&L
Lastly, you will write formulas to lift data from the Data tab to the P&L tab. In this case, I would recommend SUMIFS() as you will have more than one variable (revenue/expense and period) and you may have multiple entries per intersection.
Have any questions on how to create a Profit and Loss statement in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
