If you need to do a profit and loss statement for your business, Excel is a great tool to use. Profit and loss statements show your revenue, expenses, and net income for a period of time. They can help you track your business performance over time and make informed decisions about where to invest or cut costs.
How do you do a P&L in Excel?
- Start by inserting the heading and the period you will prepare the report.
- Create three sections, namely Revenue, Expenses, and Summary.
- Add all the revenues such as service, interest, and sales revenue under the revenue section.
- Sum up all the revenues using the SUM function.
- Similarly, add all expenses like advertisements and employee expenses and compute their total using the SUM formula.
- Calculate the profit or loss under the summary section using the formula: Net Profit/Loss = Net Revenue – Total Expenditures
- Print your P&L report after completion.
Complete Guide:
Creating a profit and loss statement in Excel is simple. First, open a new workbook and create two worksheets: one for your income and one for your expenses. On the income worksheet, list all of your revenue sources in column A and enter the corresponding amounts in column B. On the expense worksheet, list all of your expenses in column A and enter the corresponding amounts in column B.
Then, on both worksheets, create a third column called “Total.” In the Total column on the income worksheet, sum up all of the amounts in column B. In the Total column on the expense worksheet, sum up all of the amounts in column B. Finally, on a third worksheet called “Profit & Loss,” subtract the total expenses from the total income to get your net profit or loss for the period.
How to Do a Profit And Loss Statement in Excel:
- Start by opening a new Excel spreadsheet
- Enter your company’s income and expenses for the period you want to measure into the spreadsheet
- Be sure to include all sources of income and all expenses, including both one-time and recurring items
- Once you have all of your data entered, you can begin creating your profit and loss statement
- Begin by creating separate columns for each type of income and expense
- Next, calculate your total income by adding up all of the values in the income column
- Then, calculate your total expenses by adding up all of the values in the expense column
- Finally, subtract your total expenses from your total income to determine your net profit or loss for the period you measured
How To Make an Income Statement in Excel in 8 Steps
1. Prepare your Excel file
2. Determine the categories
- Gains and losses from continuing operations: This category includes expenses unrelated to operations, such as cash movements, interest and taxes.
- Nonrecurring events: This section includes significant one-time losses and gains, such as selling or purchasing important assets.
- Operating expenses: This category includes the day-to-day expenses to support business operations.
- Revenue and profits: This section includes any money the organization earned from its primary business purposes.
3. Choose the subcategories
- Income before taxes
- Income loss from continuing operations
- Interest expenses
- Other expenses
- Other gains
- Tax expenses
- Total gains from continuing operations
- Total losses from continuing operations
Here are some examples of possible nonrecurring event subcategories:
- Gains from accounting changes
- Gains from extraordinary items
- Income from discontinued operations
- Losses from accounting changes
- Losses from discontinued operations
- Losses from extraordinary items
- Total gains from nonrecurring events
- Total losses from nonrecurring events
Examples of subcategories for the operating expenses category include:
- Advertising
- Marketing
- Other
- Rent
- Salaries
- Total operating expenses
- Transportation
- Utilities
Some examples of subcategories for a revenue section may include:
- Bad merchandise
- Beginning inventory
- Cost of goods sold
- Ending inventory
- Gross profit
- Gross sales
- Manufacturing labor
- Net sales
- Purchased goods
- Raw materials
- Sales
- Total cost of goods sold
- Total goods available
4. Input the categories and subcategories
-
Separate column: Consider pressing “Tab” to the next column and cell over, such as cell B6. Type the name of the subcategory, then press “Enter” and continue to add subcategories in column B until your list is complete.
-
Same column: Think about typing all categories and subcategories in column A by pressing “Enter” until you complete the list. Use the alignment tool to add a hanging indent to separate the subcategories from the categories.
5. Set up the formulas
Highlight the specific cells on your income statement and apply the relevant formulas. For example, select the cells to the right of your subcategories for total revenue, then apply the SUM formula to them.
6. Input the data
nput the data in the corresponding cells on the income statement.
7. Consider additional formatting
Consider highlighting important rows or filling the rows of each category. Another option may be highlighting any negative numbers in red font.
8. Finalize the document
Review your income statement and look for any potential errors or missing information.
Profit And Loss Statement Format Excel Free Download
A profit and loss statement, also called a P&L or income statement, is a financial report that shows your revenue, expenses, and profit for a specific period of time. The profit and loss statement format is typically used by businesses to track their progress and performance over time. There are many different ways to format a profit and loss statement.
However, the most common format is the single-step P&L, which groups all revenues and expenses into two broad categories: operating and non-operating. Operating revenues and expenses are those that are directly related to your business’s core operations. For example, if you’re a manufacturing company, your operating revenues would include sales of your products.
Operating expenses would include the cost of materials used to make those products, as well as labor costs. Non-operating revenues and expenses are not directly related to your business’s core operations. For example, if you’re a manufacturer, non-operating revenue might come from interest earned on investments or from the sale of property or equipment that’s no longer needed.
Non-operating expenses might include losses from natural disasters or write-offs of bad debts. The single-step P&L format is the easiest to use because it provides an overall picture of your business’s profitability. However, some businesses prefer the multi-step P&L format because it provides more detailed information about where your profits are coming from (or going).
