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

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.

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

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

  1. Download and open the free small business profit and loss statement template for Excel. The template should automatically open in Excel.
How to Do a Profit and Loss Statement in Excel | Smartsheet (2)

  1. Select File from the menu bar and click Save As. Retitle the document in the overlay screen, select the preferred folder, and click Save.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (3)

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

  1. Type the business name in the Company Name cell.
  2. Type in the reporting period start and end dates in the Statement Reporting Period cell. Save the file.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (5)

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

  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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (6)
  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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (7)
    How to Do a Profit and Loss Statement in Excel | Smartsheet (8)

    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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (9)
  4. Press Enter. Again, your Gross Revenue, Gross Profit, and Net Income cells will automatically populate with the updated amounts.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (10)
    How to Do a Profit and Loss Statement in Excel | Smartsheet (11)

    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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (12)
  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.Once copied, a dotted-green line will appear around the highlighted cells.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (14)
  3. Hover the cursor over the copied cells and right-click. A new menu will appear. Select Insert Copied Cells from the dropdown menu.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (15)
  4. A new pop-up menu will appear. Select the Shift cells down option and press Enter or click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (16)
    A pop-up message will appear, warning you that the operation will cause some merged cells to unmerge. Click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (17)
  5. A new row will appear below with the copied Other Revenue cells.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (18)
    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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (19)

Input the Cost of Goods Sold (COGS)

  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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (20)
  2. Press Return. The COGS and Total COGS cells will populate.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (21)
    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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (22)
    Pro Tip: The gross profit will be listed either in black, indicating a profit, or in red with parenthesis, indicating a loss.

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.

  1. Start by clicking on the Wages and Benefits cell and inputting the total wages and benefits for the period.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (23)
  2. Press Return. The template will automatically add the number to the Total Expenses column and update the amount in the Net Income cell.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (24)
  3. Repeat steps one and two in this section for each of your expenses.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (25)

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
  1. Highlight a selected expense category title and input cell. Do not use the first row (Wages and Benefits) to avoid inconsistent cell formatting.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (26)
  2. Right-click on the highlighted row cells. Select Insert….
    How to Do a Profit and Loss Statement in Excel | Smartsheet (27)
  3. On the dropdown menu, select the Shift cells down and then click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (28)
  4. A new alert menu will pop up. Click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (29)
  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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (30)
    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.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (31)
  2. Right-click the highlighted cells. Select Delete… from the dropdown menu.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (32)
  3. A new Delete menu will pop up. Select Shift cells up and then click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (33)
  4. An alert menu will pop up. Click OK.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (34)
  5. The selected cells will be deleted and the existing expense rows will move up.
    How to Do a Profit and Loss Statement in Excel | Smartsheet (35)
    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.
How to Do a Profit and Loss Statement in Excel | Smartsheet (2024)

References

Top Articles
Latest Posts
Article information

Author: Aron Pacocha

Last Updated:

Views: 5848

Rating: 4.8 / 5 (48 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Aron Pacocha

Birthday: 1999-08-12

Address: 3808 Moen Corner, Gorczanyport, FL 67364-2074

Phone: +393457723392

Job: Retail Consultant

Hobby: Jewelry making, Cooking, Gaming, Reading, Juggling, Cabaret, Origami

Introduction: My name is Aron Pacocha, I am a happy, tasty, innocent, proud, talented, courageous, magnificent person who loves writing and wants to share my knowledge and understanding with you.