How to Build a Profit and Loss Statement in Excel

Save

A profit and loss statement, or P&L, shows a company’s revenues, expenses and profit or loss for an accounting period. Your profit or loss equals total revenue minus total expenses. When you run a business, it is important to build and review your P&L each period to identify areas of strength and weakness. Microsoft Excel can help you simplify this process. Input your data and the proper formulas into a blank spreadsheet and Excel totals up your revenues and expenses and calculates your profit or loss.

  • Click Cell A1. Type “Product Revenue,” “Service Revenue” and “Total Revenue” in cells A1 through A3, respectively. Press “Enter” after typing in each cell. Omit either type of revenue that your business doesn’t earn.

  • Click Cell B1. Type the amount of revenue you generated selling merchandise in Cell B1 and the amount you earned providing services in Cell B2. Press “Enter” after typing each amount. For example, assume you had $5,000 in product revenue and $10,000 in service revenue last month. Type “$5,000” in Cell B1 and “$10,000” in Cell B2.

  • Click Cell B3. Type “=B1+B2” and press “Enter.” Excel adds your revenues in Cells B1 and B2 to calculate your total revenue. In this example, you get “$15,000” in total revenue in Cell B3.

  • Click Cell A5. Type the name of one of your expenses in Cell A5 and the names of your other expenses in the cells below Cell A5. Press “Enter” after each expense. In this example, assume you paid $7,000 in cost of goods sold and $2,000 in wages. Type “Cost of Goods Sold” in Cell A5 and “Wages” in Cell A6.

  • Click Cell B5. Type the amount of each of your expenses in the cell in Column B that is adjacent to the corresponding expense in Column A. Press “Enter” after each amount. In this example, type “$7,000” in Cell B5 and “$2,000” in Cell B6.

  • Click the cell below your last expense in column A. Type “Total Expenses” and press “Enter.” In this example, type “Total Expenses” in Cell A7.

  • Click the cell in Column B that is adjacent to the Total Expenses cell. Type the formula “=SUM(first:last)” but replace “first” with the column and row of the cell that contains the amount of your first expense, and replace “last” with the cell that contains your last expense. Excel adds all of your expenses in column B and shows the total in the cell adjacent to Total Expenses. In this example, click Cell B7, type “=SUM(B5:B6)” and press “Enter.” Excel shows “$9,000” in Cell B7.

  • Click the cell two cells below “Total Expenses” in Column A. Type “Net Profit/Loss” and press “Enter.”

  • Click the adjacent cell in Column B. Type “=B3-total expenses” but replace “total expenses” with the cell that contains your total expenses. Excel subtracts total expenses from total revenue to calculate your net profit or loss. A negative result represents a net loss. Concluding the example, type “=B3-B7” in Cell B9. Excel shows a “$6,000” net profit.

References

  • Photo Credit Polka Dot RF/Polka Dot/Getty Images
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Are You Really Getting A Deal From Discount Stores?

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!