Running a business means keeping an eye on your money: how much you’re making and how much you’re spending. A profit and loss statement (sometimes just called a P&L) is a great way to do this. Google Sheets is a handy tool to create this document.
What Is a Profit and Loss Statement?
A profit and loss statement, often called a P&L, is like a business’s financial scoreboard. It’s a document that tallies up all the money a company makes (its income) and all the money it spends (its expenses) over a certain period.
P&L helps business owners understand exactly where their money is going, making it a crucial tool for making smart, informed decisions about their business’s future.
Step 1: Set Up Your Google Sheet
Start by opening Google Sheets in your web browser. You can either use an existing spreadsheet or create a new one. Then, rename the spreadsheet to make it more personalized. To do that, click “Untitled Spreadsheet” and enter your preferred name. For example, “Profit and Loss.”
Step 2: Create the Basic Structure
Add the basic structure of the template, which is the headers. You can use the following headers below in your template:
- Date
- Description
- Income
- Expenses
- Profit/Loss
To make the headers stand out, select their cells, click the bold icon (B) in the toolbar, and center-align the text.
Step 3: Input Your Data
In the “Income” column, list all sources of income for the period you’re tracking. For example, if you received $1,000 from a client, you would enter that amount in the corresponding row under “Income.”
In the “Expenses” column, list all your costs. This could include rent, utilities, supplies, etc.
Briefly describe each income and expense entry in the “Description” column. This makes it easier to track where the money is coming from and where it’s going.
Enter the date for each transaction in the ” Date ” column. This will help you keep a timeline of your financial activities.
Step 4: Calculate Profit and Loss
In the first cell under the “Profit/Loss” column, type the following formula:
=C7-E7
This formula subtracts the amount in the “Expenses” column from the amount in the “Income” column, giving you the profit or loss for that entry.
Step 5: Calculate Totals
In the first empty cell under the “Income” column, type the following formula to sum all income:
=SUM(C7:C16)
This will give you the total income for the period.
Similarly, in the first empty cell under the “Expenses” column, type the formula to sum all expenses:
=SUM(E7:E16)
This will give you the total expenses.
Finally, in the first empty cell under the “Profit/Loss” column, type the formula to calculate the total profit or loss:
=SUM(F7:F16)
This will show you whether your business made a profit or incurred a loss over the period.
Step 6: Format Your Template
Select the cells with your income, expenses, and profit/loss values to make your numbers easier to read. Then, click the $ icon to set the numbers in Currency. This will format your numbers as currency, making them more visually clear.
Color coding can help you quickly identify profits and losses. Select the cells in the “Profit/Loss” column, then click on Format > Conditional formatting. Set rules to change the cell color based on the value.
After you’re done with your template, if you want other people to access your sheet, you only need to click Share and enter their email addresses. You can also control whether they can view, comment, or edit your template.
Get the Free Profit and Loss Template
Get a copy of the free profit and loss template for Google Sheets. I’ve populated some cells as examples, but you can customize them as needed.
Final Thoughts
Building a Profit and Loss template in Google Sheets is a simple task that can make a big difference for your business. This guide has equipped you with the tools to track income, expenses, and profitability efficiently. Regular updates to your P&L statement empower you to make informed financial decisions, helping to keep your business on the path to success.
Frequently Asked Questions
How do I determine the net profit in Google Sheets?
Finding the net profit is straightforward once your total income and expenses are calculated. Below your totals, subtract the total expenses from total income using a simple minus formula: =B11-B21 (assuming B11 and B21 are your total income and expenses cells)
Can I apply conditional formatting to make the template easier to read?
Yes, Google Sheets allows you to add color coding through conditional formatting. This feature can help visually distinguish between income (possibly green), expenses (red), and net profit (blue), enhancing readability and making trends easier to spot at a glance.
