[9.23.17 UPDATE: I’ve just added a premium spreadsheet – Profit & Loss Tracker – to my shop. If you need something more robust and automated than the free download included with this post, definitely check it out!]
One benefit of subscribing to my list is that I offer you plenty of opportunities to engage with me (which is a benefit because I translate that into posts you want, spreadsheets you need, etc.).
Oh, and if you are exceptionally badass, you may have even completed a (now inactive) survey sent via my weekly newsletter, which let me what you’d like to see more of at DLS.
I get it, though – you may not dig surveys.
Anyway, subscribers often touch base with me after receiving my quasi-weekly broadcast (you can sign up to receive it here), where I offer a new tip for both Google Sheets & Excel users, a brief intro to the current blog post, promotional content & discounts, and other rad information (such as *shhh!* a soon-to-be-launched private Facebook group for both spreadsheet virgins & goddesses).
Well, one seriously awesome gal replied to said broadcast with a request for a P & L spreadsheet.
For those of you who have no freakin’ clue what P & L stands for, it’s an abbreviation for profit and loss.
I’m an economist – not a finance or business expert (bet ya thought that they were synonymous, right?!), but nonetheless, I wanted to fulfill my awesome reader’s request.
So, I googled examples of P & L statements that would be simple for y’all to set up on your own.
My favorite example 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!
Here’s what’s included in a profit & loss statement
In order to create a profit & loss 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 report.
An income report contains:
- Your biz’ itemized revenue
- Your biz’ itemized costs/expenses
- Your biz’s net income
Some exceptionally wicked P & L spreadsheets even come with charts and cool dashboarding capabilities.
Can you see how awesome it would be to have this information readily available, even if you didn’t have a freakin’ clue as to what a P & L statement was prior to reading this post?
You can thank Selena (I’d link to her blog but I do not believe that she has one) for inspiring today’s post on how to create a basic P & L statement.
And, you can also thank Dwight Fujimoto for inspiring this P & L statement’s layout.
Finally, 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. If you run into any issues, please let me know in the comments below![clickToTweet tweet=”Take the mystery out of P&L statements with this tutorial and my free spreadsheets.” quote=”Take the mystery out of P&L statements with this tutorial and my free spreadsheets.” theme=”style1″]
*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 slideshow with screenshots of the process
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’ve used it exclusively for several months now and freakin’ love it’s automations and ease of use. Just sayin’. ?[clickToTweet tweet=”Take the mystery out of P&L statements with this tutorial and my free spreadsheets.” quote=”Take the mystery out of P&L statements with this tutorial and my free spreadsheets.” theme=”style1″]
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.
The spreadsheet tip of the week that all DLS subscribers received today (10.12.16) covers how to define names. If you would like to receive this email broadcast, subscribe now by clicking here or using the form below and reply to your confirmation email (or another DLS email) requesting the 10.12.16 broadcast so that I know to forward it to you.
*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 consultant 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.