This post is in response to a subscriber’s request for a profit & loss spreadsheet (aka an income statement).
I’m an economist – not a finance or business expert (bet ya thought that they were synonymous, right?!), so I had to google examples of simple P & L statements.
My favorite came from this post written by Dwight Fujimoto. The post is short and clarifies what to include/not to include in each section of the P & L statement, so please be sure to check out his post if you aren’t already well-versed on the topic.
Okay, let’s dive right into this profit and loss beast!
What’s included in a profit & loss statement?
In order to create a profit & loss (or P&L) statement, it’s helpful to know what one tends to contain.
Ground breaking, I know.
For all of you business newbs, your P&L statement is also known as your income statement.
An income statement contains:
- Your biz’s itemized revenue
- Your biz’s itemized costs/expenses
- Your biz’s net income
Some exceptionally wicked P&L spreadsheets even come with charts.
Btw, you can snag the exact same spreadsheets – those that we’re about to create – for free by clicking here 🙂 By clicking this link, you will be taken to my Google Drive, where you may access Google Sheets and Excel versions of this workbook. TO USE THE GOOGLE SHEETS FILE, YOU MUST SAVE A COPY OF IT TO YOUR OWN DRIVE.
[UPDATE! While you can certainly download the free files above, I have a much more badass FREE P&L Tracker available here. It’s exactly the same as the one I sell in my shop except that it’s limited to 5 income and 5 expense categories, and 50 income and 50 expense transactions.]
*Psst – This tutorial assumes a basic understanding on how to use spreadsheets. If you are a total spreadsheet virgin, please first work through my FREE From Spreadsheet Virgin to Goddess in 30 Minutes.
*Psst again – If you have any questions about the formatting in this tutorial, please take a peek at this brief post.
Here is a gallery of screenshots detailing the process that follows
Here’s how to create a basic P & L statement
I created this tutorial using Excel, but the process is the same for Google Sheets.
Step 1: Open a new workbook
Create the following spreadsheets:
- P & L Summary
Step 2: Set up your Income sheet
- Create the following headers in A1:D1:
- Income Source
- Income Type
- If you want to get really snazzy, add a drop-down list containing your different income types in column C (see this post if you don’t know how to do this – it’s super easy!).
- Select/highlight column D => right-click => ‘Format Cells’ => select ‘Currency’ from the ‘Category’ list in the ‘Number’ tab.
- Select A1:D1 => click the ‘Data’ tab => click the ‘Filter’ icon.
- Format your spreadsheet however you want! I would suggest changing the default font, adding color to your header row, and resizing your columns (*Psst – I cover how to do this here).
- Rename the spreadsheet by double-clicking on the ‘Sheet2’ tab and typing Income.
Note: An example of an ‘Income Source’ would be Amazon affiliate commissions, while an ‘Income Type’ would be affiliate commissions (which would encompass affiliate commissions from all sources).*
Step 3: Set up your Costs sheet (and create your Expenses sheet)
- Save yourself some time by right-clicking on the ‘Income’ tab => ‘Move or Copy’ => select ‘Move to end’ => check the ‘Create a copy’ box => click ‘OK’. Repeat.
- Double-click on the ‘Income (2) tab and rename it ‘Costs’.
- Double-click on the ‘Income (3) tab and rename it ‘Expenses’.
- Go back to your ‘Costs’ sheet and delete column C.
- Replace the word ‘Income’ with ‘Cost’ in B1.
- Resize your columns as needed.
Note: Your costs are only those that are directly associated with the selling of your product, service, etc.*
Step 4: Set up your Expenses sheet
- Go to your ‘Expenses’ sheet and replace ‘Income’ with ‘Expense’ in B1 and C1.
- Resize your columns if needed.
- If you created a drop-down list for your ‘Income Type’ column, you will want to update it to reflect your expense types.
Note: Your expenses are those that are required for operating your biz that exist even if you stopped selling your product, service, etc. (e.g. rent, website hosting, etc.).*
Step 5: Set up Your P & L Summary sheet
- Add your biz’ name in A1 => select A1:C1 => ‘Merge’.
- Type Profit & Loss Statement in A2 => select A2:C2 => ‘Merge’.
- Enter the time period in A3 => select A3:C3 => ‘Merge’.
- In A5, type INCOME.
- In A6, click the indent icon in the ‘Home’ tab (Excel only) and then type your first income type. Repeat for each income type (for simplicity, I’m assuming three types).
- Select/highlight column C => right-click => ‘Format Cells’ => select ‘Currency’ from the ‘Category’ list in the ‘Number’ tab.
- In C8, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
- In A10, type TOTAL INCOME and bold it.
- In C10, type =SUM(C5:C8)
- In A12, type COSTS and bold it.
- In C12, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
- In A14, type GROSS PROFIT and bold it.
- In C14, type =C10-C12
- In A16, type EXPENSES and bold it.
- In A17, click the indent icon in the ‘Home’ tab and then type your first expense type. Repeat for each expense type (for simplicity, I’m assuming five types).
- In C21, click the border icon in the ‘Home’ tab and choose ‘Bottom Border’.
- In A23, type TOTAL EXPENSES and bold it.
- In C23, type =SUM(C17:C21)
- In A25, type NET INCOME and bold it.
- In C25, click the border icon in the ‘Home’ tab and choose ‘Double Bottom Border’. Then, type =C14-C23
Note: Despite my love for beautiful spreadsheets, I would advise you to not add color to the P & L Summary sheet if you intend to print and distribute it. However, feel free to resize it, change its fonts and font sizing, and resize your blank rows, etc.
Step 6: Populate your workbook & save it as a pdf or print it
- Fill out your ‘Income’ spreadsheet. Then, click on the filter arrow in your ‘Income Type’ header in C1 => deselect all but the income type you entered in A6 of your ‘P & L Summary’ sheet => select/highlight the cells that contain entries in column D and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C6 of your ‘P & L Summary’ sheet.
- Repeat for your other income types.
- Now, fill out your ‘Costs’ spreadsheet. Then, select/highlight all cells that contain amounts in column C and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C12 of your ‘P & L Summary’ sheet.
- Now, fill out your ‘Expenses’ spreadsheet. Then, click on the filter arrow in your ‘Expenses Type’ header in C1 => deselect all but the income type you entered in A17 of your ‘P & L Summary’ sheet => select/highlight the cells that contain entries in column D and note the total in your progress bar at the bottom of your spreadsheet. Enter this in C17 of your ‘P & L Summary’ sheet.
- Repeat for your other expense types.
- To print the ‘P & L Summary’ sheet, select A1:C26 => ‘File’ => ‘Print’. Although unlikely, you may need to check the ‘Scaling: Fit to’ box.
- To create a pdf of the ‘P & L Summary’ sheet, select A1:C26 => ‘File’ => ‘Print’ => select ‘Save as PDF’ from the drop-down in the lower left corner => adjust the name, etc. in the new box that appears => ‘Save’.
Feel free to download the exact P&L workbook that we just created in this tutorial by clicking here (no email required!). This link contains both Google Sheets and Excel versions. Please make sure that you are logged into your Google account if you want to use the Google Sheets version. Simply right-click on the files and then select ‘Make a copy’. That’s it!
If you’d like a more robust and exceptionally wicked profit & loss/income & expense series of spreadsheets, be sure to check out my premium Profit & Loss Tracker. I use it exclusively and freakin’ love it’s automations and ease of use. Just sayin’. ?
Now it’s your turn! I’d love to hear from any of you lovelies who are finance professionals. What would you include in a P & L statement? As for the rest of you, what do you struggle with when it comes to tracking your biz’ finances? Please share your thoughts, suggestions, and questions below! Thanks a bunch 🙂
Make your P & L spreadsheet even more wicked…
If you are already exceptionally badass with spreadsheets and are comfortable defining names in Excel or Google Sheets, you can increase the functionality of these sheets by defining names for each income/expense type and entering ‘SUM’ functions referencing those names in column C of your ‘P & L Summary’ sheet.
*HUGE DISCLAIMER: I am not a financial advisor, accountant, or budgeting authority (I’m just a recovering economist who digs spreadsheets). The purpose of this post is simply to teach you how to create a basic spreadsheet to track your income and losses. I strongly encourage you to work with a legit financial advisor to ensure that you are properly recording your income, losses, and other such tidbits that the IRS and/or other governmental entities require for reporting and tax purposes.