Calculating Your Net Profit From The Sales of Inventory Items Using QuickBooks Online
In order to get your net profit from the sales of your inventory items, you can use the “Sales by Product/Service Detail” report in QuickBooks Online. Begin by going to the Reports section located on your left hand pane in QuickBooks Online. QuickBooks opens the section location from your previous activity. Therefore, click on “All Reports” from the very top pane and then select the “< All Reports” link. This will bring you to the top section of the All Reports Section. This is what the page will look like.
Select the “Review Sales” link in order to get to get to the reports that are related to Sales. This is what this section will look like.
Once you arrive to this section, select the “Sales by Product/Service Detail” link to open this report. It is important to customize this report in order to filter out and get the desired data. For example, what date range of data would you like to have this report to show. At the top of this page, press the white “Customize” button.
Press the Customize button and a form will appear. This form needs to be modified. Modify the Transaction Date to the desired time frame.
You will need to add a column of data in this report in order to filter out unnecessary data when exporting this report to an Excel Spreadsheet. Press the Blue “Change Columns” button.
This will bring up an additional form. Select the “Account” selection from the left hand selection box. Then, press the blue “Add >” button located in the center. Press “OK“. This will take you to the previous screen. Press the blue “Run Report” button located at the bottom of the form.
This report provides the total amount of sales by prodcut for the time period specified. For each inventory sales transaction, it provides three (3) related accounts that are involved in the transaction. The most important one is the Sales Revenue account. The second transaction, which needs to be filtered out, is the Inventory Account. The third, which is the amount we need to minus from Revenue, is the Cost of Goods Sold account.
This report must be exported to an Excel Spreadsheet. Go to the top of the report and export the report. Click on the down arrow. A drop down box will open. Select the “Excel (XLSX)” selection if you have the Excel 2010 software version or later. If you save the file, you will have to know where you saved it in order to open it. If you open it, you can work with the file right away.
Filtering out the Necessary Data
Select the cells that describe data and select “Filter” in the Excel software. This will provide a worksheet that you can use to add Sales and subtract Cost of Goods Sold which will give you the Net Profit for each product in the report.
Using the “Account” cell, which now has a filter button, we are going to make sure that only Sales transactions and Cost of Goods transactions remain in the report. Using the filter drop down selection from the “Account” cell, uncheck any Inventory related account and any Sales Tax Related account. The selections that need to be unmarked are any Inventory account and Sales Tax account.
Once this is complete, the report will show only data from Sales and its related Cost of Goods Sold. I recommend deleting the “Balance” column, since it is no longer accurate due to the filtering out of information. Lastly, the “Total for” amount for each product needs to be manually updated by using the “AutoSum” function in Excel. This has to be completed for all of the totals of the products in this report. Once this is completed, you can review your report. You now have the Total of Net Profit for every product for the given time period of this report.