The profit and loss statement is a financial document that summarizes the revenues, expenses, and profits or losses of a company for a given period. It is also known as P&L or earnings report.
This template can help you to create your profit and loss statement. You can use it in any way you like: fill in the missing information, add your logo, information about the company, etc.
Some of the statements in the template are conditional statements, and when you enter a value in any cell, you can later see or hide that particular statement. If you don’t want to use this template for self-practice and study it, then you will find this feature very helpful.
However, if you want to use this template for your own work, I recommend you change the conditional formatting rules so that when you enter a value in any cell, all the statements are visible. You can do this by clicking the Conditional Formatting option in the Home tab of Ribbon and choosing the New Rule command.
Main Formulas
Revenue can be calculated by multiplying the unit price by the number of units you sell.
Expenses are always negative numbers since they represent money spent on producing goods and rendering services, not on acquiring new assets.
Profit= total revenue – total expenses. Profits are positive numbers if they are higher than zero. If there are no profits, the result is a loss.
The sum of revenues and expenses can be calculated as: Total Revenues – Total Expenses = Net Profit or Loss.
By default, the template shows all three types of statements without any totals. You should use two formulas to find either Revenue, expenses, or net profit/loss.
Revenue =SUMPRODUCT(A7:F7,C1:E1)
Expenses = SUMPRODUCT(B7:G7,C2:E2)
Net Profit or Loss =SUMIF(F9:F12,”<0″,g9:g12)
As you can see, it is easier to calculate net profit/loss using two SUMIF functions instead ofone formula with an IF function. If you find the conditional formatting confusing, just change it so that all statements on the P&L statement or your own PROFIT AND LOSS STATEMENT TEMPLATE are visible.
If you want to calculate Net Revenue or net expenses, use the following formulas:
Net Revenue =SUMPRODUCT(A7:F7, C3:E3)
Net Expenses= SUMPRODUCT(B7:G7,C4:E4)
You can also fill in your company name, address, logo, and other information on the left side of the template. You can use any font you want, change size or color to make it look good for you. Also, keep in mind that each cell of this template works as an independent mini-statement. For example, if you change some values on the right side of the sheet, then only some cells on the left side will be affected.
You can also use formulas to calculate revenues based on selling prices and unit quantities, for example, Unit Price * Quantity Sold = Revenue.
To go to the next row on this template, you can use Ctrl + Enter for cell values between -10000000 and 10000000, Shift + Tab for smaller numbers, Home > Cells > Fill Handle icon at the lower right corner of selected area > Down.
If you want to save it on your computer, use File > Save as > choose a directory and type “profit and loss statement” or anything else you like in the File name box.
This template is only one of many other Excel templates available at http://exceltemplates101.com.
However, you should be careful when using any Excel template found on the internet, especially free.
Let’s review some problems that you might face when using any free Excel template:
1. You can’t be sure if the file is safe to download. It might contain macros, viruses, and other malicious code. Even if there are no viruses in it, you can’t be sure what other data has been added by the author of that particular template.
2. Most Excel templates are not 100% compatible with your version of Microsoft Office. They may have some strange features that do not work or stop working when you open them with a newer/older version of MS Office.
3. The author’s sample data might be wrong, misleading, or downright dangerous! Once you copy everything in your worksheet, you will be in big trouble.
4. If the starting row is not 1, it may complicate things for you! For example, all formulas may start counting from 0 instead of 1.
5. Sometimes, the author gives you a link to download, but when you go there, they ask for your email and password, or they may even ask for money!
6. the essential thing: the author of that template probably will not help you if there is a problem, as they may no longer use that particular Excel file. What’s more, they might not even remember writing it
