How to Build a Profit and Loss Statement in Excel

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 definitions 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 to use that comply with generally accepted accounting principles, either the single-step or the multi-step income statement.

The income statement or profit and loss statement contains 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.

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 services) sold, and where they place the gains and losses on the statement.

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, 2018
  
• For the month ended September 30, 2018
  
• For the year ended December 31, 2018
  

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.

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 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 significant difference in the multi-step statement compared to 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.