Our tutorial breaks down the process of creating a profit and loss statement in Excel for small businesses into three easy steps, so you can get started using our customizable templates.
We’ve included instructions for customizing your template using basic Excel formulas. For additional resources and tutorials, see our guide to writing a general profit and loss statement.
Pam Prior, creator of Profit Concierge™, says that when creating a profit and loss statement (P&L) for a small business, “use categories that work for your business and make sense to you.” Our template helps you understand the basic income statement format, so you can tailor your profit and loss statements to meet the specific needs of your small business.
“The whole point of doing a profit and loss statement is seeing it in context,” says Prior. “You want to have a column for each month because then you can see the story of what happens over time, and that’s when the numbers become useful to you. The value of Excel is that you can input and see 12 months clearly in the columns. The only Excel you need to know is plus and minus.”
Once you learn how to create a report for a 1-month period using our template instructions below, you can easily compile reports and create a 12-month statement. By doing so, you will be able to see trends and create forecasts for your business. Plus, we’ve added instructions for customizing statements for your business in Excel.
View and download our complete range of free profit and loss templates to find the most suitable, fully customizable templates for your business. For more template options with a small business focus, see our free small business profit and loss statement templates.
Before getting started, make sure you have your credit card and bank account statements on hand. This tutorial uses a monthly reporting period, but you can apply these instructions to quarterly or annual reporting structures.
1. Download, Open, and Save the Excel Template
- Download and open the free small business profit and loss statement template for Excel. The template should automatically open in Excel.
- Select File from the menu bar and click Save As. Retitle the document in the overlay screen, select the preferred folder, and click Save.
Pro Tip: Determine a consistent file naming system so that you can easily locate and retrieve any statement. Include the reporting end date in the title of your document. For example: Business Name_P&LStatement_Feb22
Input Your Company and Statement Dates
- Type the business name in the Company Name cell.
- Type in the reporting period start and end dates in the Statement Reporting Period cell. Save the file.
2. Calculate Gross Profit
In order to calculate gross profit, you first need to calculate your gross revenue, or total income, and the total cost of goods sold (COGS). Subtract total goods sold from your gross revenue to determine gross profit:
Gross profit = Gross revenue - COGS
This template has built-in formulas that compute these calculations automatically as you enter information into the relevant sections.
Input Sales Revenue to Calculate Gross Revenue
- Find the Sales Revenue cell and click once on the cell next to it to highlight the cell. Calculate your sales revenue from the reporting period and enter the amount into the cell. You do not need to add commas when typing larger amounts.
- Press Enter, or click another cell to deselect. The Gross Revenue, Gross Profit, and Net Income cells will automatically populate. At this point, these will be the same as your sales revenue.
Reminder: The sales revenue for a basic small business profit and loss statement needs to include deductions for returns and discounts. It does not include taxes, interest, depreciation, or amortization. Those are added to the statement in a later step when you report expenses. - If you have additional revenue to record, click the cell next to the Other Revenue cell and input the amount. If you have no other revenue to report for this period, leave it blank or enter zero.
- Press Enter. Again, your Gross Revenue, Gross Profit, and Net Income cells will automatically populate with the updated amounts.
Reminder: Save your Excel document after every step to avoid data loss.Pro Tip: Customize this revenue section by creating categories that make sense for your business. For example, you might edit and update the text in existing sections to specify revenue sources or add line items to account for additional revenue streams. If you need to add line items, complete the following tutorial. If not, skip to the next section.
- Highlight the last input title and input cell in the Revenue section. To do this, click on the title cell and drag your cursor to the right, releasing the cursor only once all desired cells are highlighted. Another method is to click once on the title cell, then press Shift and click on the cell to the right to highlight both cells.
- Copy the highlighted cells using Command + C (Mac) or Ctrl + C (PC), or by right-clicking on the cells and selecting Copy from the dropdown menu.Once copied, a dotted-green line will appear around the highlighted cells.
- Hover the cursor over the copied cells and right-click. A new menu will appear. Select Insert Copied Cells from the dropdown menu.
- A new pop-up menu will appear. Select the Shift cells down option and press Enter or click OK.
- A new row will appear below with the copied Other Revenue cells.
Input the Cost of Goods Sold (COGS)
- Locate the Cost of Goods Sold section below the Revenue section. Click once on the input cell to the right of the COGS in Column D and input the total cost of goods sold for the reporting period.
- Press Return. The COGS and Total COGS cells will populate.
Calculate the Net Income
This template uses the following formula to calculate net income:
Net income = Gross profit - total expenses
Input Your Business Expenses
By following the steps in this section, you will learn how to input the expenses incurred for the reporting period and how to customize the template for your business by adding and deleting expense rows. Before you start, you may want to calculate your expenses in one location using our free small business expense templates.
- Start by clicking on the Wages and Benefits cell and inputting the total wages and benefits for the period.
- Press Return. The template will automatically add the number to the Total Expenses column and update the amount in the Net Income cell.
- Repeat steps one and two in this section for each of your expenses.
Customize Your Expense Categories
After you complete the profit and loss template and record your net income for this period, create expense categories that make the most sense to you with this easy-to-customize template. The following tips will help you with the most common template adjustments:
- Renaming an Expense Without Adjusting Cells: Click on the title cell you wish to rename. Type in your desired category title, and either press Return or click on the next cell. Delete any expense category title without adjusting cells by clicking on the title and pressing Delete to leave the row blank.
- Adjusting Expense Cells: To customize expenses and match the exact number ofcategories, you can add or delete a line item as needed.
- Adding New Expense Row Cells
- Highlight a selected expense category title and input cell. Do not use the first row (Wages and Benefits) to avoid inconsistent cell formatting.
- Right-click on the highlighted row cells. Select Insert….
- On the dropdown menu, select the Shift cells down and then click OK.
- A new alert menu will pop up. Click OK.
- An empty expense row with a title cell and an input cell will appear in the same row and shift the existing cells down. Add your custom category title to the cell and enter the expense amount in the input cell to the right. The template will continue to update your total expenses as you add customized rows.
- Deleting Expense Row Cells
- Select the expense row or rows you wish to delete by clicking on a single cell and dragging the mouse over the remaining cells.
- Right-click the highlighted cells. Select Delete… from the dropdown menu.
- A new Delete menu will pop up. Select Shift cells up and then click OK.
- An alert menu will pop up. Click OK.
- The selected cells will be deleted and the existing expense rows will move up.