How to Do a Profit and Loss Statement in Excel | Smartsheet (2024)

1. Download and open the free small business profit and loss statement template for Excel. The template should automatically open in Excel.

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

1. Type the business name in the Company Name cell.
   

   See Also

   Profit and Loss Statement Template | Free Download | FreshBooksProfit and Loss Templates to Optimize Financial Decision-Making | CreatelyFREE 10+ Sample Profit and Loss Forms in PDF | ExcelProfit and Loss Statement Template - Free Download
2. Type in the reporting period start and end dates in the Statement Reporting Period cell. Save the file.
   

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.

1. 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.
   
2. 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.
3. 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.
   
4. 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.

1. 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.
   
2. 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.
   

   See Also

   Monthly Profit and Loss Statement Template | Excel Templates
   Once copied, a dotted-green line will appear around the highlighted cells.
   
3. Hover the cursor over the copied cells and right-click. A new menu will appear. Select Insert Copied Cells from the dropdown menu.
   
4. A new pop-up menu will appear. Select the Shift cells down option and press Enter or click OK.
   
   A pop-up message will appear, warning you that the operation will cause some merged cells to unmerge. Click OK.
   
5. A new row will appear below with the copied Other Revenue cells.
   
   Click on the New Title column and enter a title. When you enter the amount in the adjacent cell, the template will continue to calculate gross revenue, gross profit, and net income. Repeat the process if the revenue section needs more custom rows.
   

1. 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.
   
2. Press Return. The COGS and Total COGS cells will populate.
   
   The template will also calculate the gross profit by subtracting the total COGS from the gross revenue. Review the amounts to make sure the calculations are accurate.
   
   Pro Tip: The gross profit will be listed either in black, indicating a profit, or in red with parenthesis, indicating a loss.

This template uses the following formula to calculate net income:

Net income = Gross profit - total 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.

1. Start by clicking on the Wages and Benefits cell and inputting the total wages and benefits for the period.
   
2. Press Return. The template will automatically add the number to the Total Expenses column and update the amount in the Net Income cell.
   
3. Repeat steps one and two in this section for each of your expenses.
   

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

1. Highlight a selected expense category title and input cell. Do not use the first row (Wages and Benefits) to avoid inconsistent cell formatting.
   
2. Right-click on the highlighted row cells. Select Insert….
   
3. On the dropdown menu, select the Shift cells down and then click OK.
   
4. A new alert menu will pop up. Click OK.
   
5. 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.
   
   Pro Tip: You can add or delete multiple rows at once. To add, highlight as many rows as you would like to insert. Follow steps two through five with multiple rows selected.

• Deleting Expense Row Cells

1. Select the expense row or rows you wish to delete by clicking on a single cell and dragging the mouse over the remaining cells.
   
2. Right-click the highlighted cells. Select Delete… from the dropdown menu.
   
3. A new Delete menu will pop up. Select Shift cells up and then click OK.
   
4. An alert menu will pop up. Click OK.
   
5. The selected cells will be deleted and the existing expense rows will move up.
   
   Pro Tip: Clean up your newly formatted spreadsheet by deleting any extraneous template banners that moved while reformatting document cells. Highlight any non-formatted cells you wish to delete, and use the delete process above to create a tidy, customized spreadsheet.