How to Build a Profit and Loss Statement in Excel

by Laura Chapman; Updated June 27, 2018
Business analyst - hand with pen; calculator, sheet and graph from top view

A business has four major financial statements: the income statement, statement of cash flows, balance sheet and statement of equity. The income statement is sometimes called a profit and loss statement, and may even be abbreviated in conversation to "P&L." While accounting software programs may simplify creating this statement, you can also build a profit and loss statement in Excel. You will need to understand the definition of revenues, expenses, gains and losses. You must also specify the period of time that the statement covers. Lastly, you will need to decide which one of two formats you will use that comply with generally accepted accounting principles, either the single-step or the multi-step income statement.

Information on a Profit and Loss Statement

The income statement or profit and loss statement contain information about the revenue, expenses, gains and losses in a particular period of time. Revenue, or income, can be from operations or from sources not directly related to the business's primary activities. Gains are unusual and relatively unpredictable sources of revenue, such as from receiving a settlement payment from a lawsuit. Expenses can also be operating expenses such as utilities and wages paid, or non-operating expenses such as interest paid on loans. Losses are unusual and unpredictable expenses, such as from disposing of long-term assets at less than their value.

Formats of Income Statements

Generally accepted accounting principles (GAAP) demand one of two formats for profit and loss statements that will be seen by people outside the company. Managers may find different formats more useful for internal purposes but must ensure that these reports are not given to external users. The two GAAP-compliant formats are the single-step income statement and the multi-step income statement. The main difference in these two formats is how they display the cost of goods (or service), and where they place the gains and losses on the statement.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

Creating The Profit and Loss Statement in Excel

You may find it helpful to view examples of profit and loss statements before you begin. There are certain elements that are essential to the statement regardless of your business's specifics. You will have a three-row heading centered at the top. The first line will be the business name. The second will be the title of the report: Profit and Loss Statement. On the third line, you must specify the time period that the statement covers. Some examples might be:

  • For the quarter ended March 31, 2017
  • For the month ended September 30, 2017
  • For the year ended December 31, 2017

Leave at least one blank row after the heading for clarity. The next steps will vary based on whether you are using single-step or multi-step, but some elements are shared. You will begin in the first column, and whenever information needs to be indented to set it apart, you will move to the next column. You will edit the column width of that first column so that the information in the second column lines up after the first several characters of the information above it, like in the image below.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla
Screenshot showing how to indent a line by resizing a column

In either format, you are going to list the revenues first, followed by expenses, with a total called net income at the bottom. After all of the account titles are entered, you will skip over one or two columns to leave some space before entering the dollar values. Set this column to display as currency.

The Single-Step Income Statement

The single-step income statement uses a basic formula: net income equals revenues and gains less expenses and losses. You will type "Revenues & Gains" on the first line, then indent the next lines where you list the various revenue and gains accounts for your business. The following line will be indented once again, and will say "total revenue & gains." After a blank row, type "Expenses & Losses" and then indent the next lines where you list the expense and loss accounts. You will indent again for "total expenses & losses." The final line is income and expense.

The Multi-Step Income Statement

The significant difference from single-step is that we separate the operations from non-operating items. Your statement will begin with sales, cost of goods sold and gross profit. You will follow that with operating expenses, subtracting them to find the subtotal titled "operating income." You will then list non-operating revenue, gain, expense and loss, in that order, and then total the non-operating items. Adding operations and non-operating items together gives you the net income.

About the Author

Laura Chapman holds a Bachelor of Science in accounting and has worked in accounting, bookkeeping and taxation positions since 2012. She has written content for online publication since 2007, with earlier works focusing more in education, craft/hobby, parenting, pets, and cooking. Now she focuses on careers, personal financial matters, small business concerns, accounting and taxation. Laura has worked in a wide variety of industries throughout her working life, including retail sales, logistics, merchandising, food service quick-serve and casual dining, janitorial, and more. This experience has given her a great deal of insight to pull from when writing about business topics.

Cite this Article A tool to create a citation to reference this article Cite this Article