Generate a Report to Display both Cost of Goods Sold and Revenue amounts from Kit/Package Items

Sourcing the cost information through the Kit/Package item record for the sales of the latter item is currently not possible within the system.  The main reason behind this is because the revenue portion pertaining to sales transactions on Kit items is maintained in the kit but the cost as recorded within Item Fulfillment and standalone sales transactions (like Cash Sales and Invoices) are pulled up from the Member Items of the kit.

Notice that if you go through the GL Impact of an Item Fulfillment the Cost of Goods Sold accounts will have distinct lines representing the cost of each Member Item of the kit.

A related enhancement request has already been filed regarding this report.  This is Enhancement 62645: Provide a Report that Calculates Kit Profitability

The closest alternate solution to consolidate both revenue and cost information on kit items is to create (2) separate searches via the following steps:

A: Create a search that would pull up all sales transactions pertaining to Kit/Package items:

1. Navigate to Reports>Saved Searches>All Saved Searches>New

2. Select Search Type = Transaction

3. Under Criteria tab>Standard subtab>Filter column and add the following fields:

a. Item Fields…>Type = Kit/Package

b. Posting = is true

c. Account Type = is Income

d. Main Line = is false

e. Tax Line = is false

f. Shipping Line = is false

4. Under Results tab>Columns subtab>Field column and add the following fields:

-Date

-Created From

-Type

-Number

-Name

-Item

-Amount (Gross)

-Hit Save button

B: The second step is to create a search that will pull up all the transactions where the cost of sales pertaining to kit items is recorded:

1. Navigate to Reports>Saved Searches>All Saved Searches>New

2. Select Search Type = Transaction

3. Under Criteria tab>Standard subtab>Filter column and add the following fields:

a. Item Fields…>Component of = (note: select all kit items where you need to include in your search)

b. Posting = is true

c. Account Type = is Cost of Goods Sold

d. Main Line = is false

e. Tax Line = is false

f. Shipping Line = is false

4. Under Results tab>Columns subtab>Field column and add the following fields:

-Date

-Created From

-Type

-Number

-Name

-Item

-Amount (Gross)

-Hit Save button

Once both reports have already been created, export results in both searches through Excel and consolidate the revenue and cost of sales information by making reference of the Created From field. This may be done through an Excel command like VLOOKUP.

Note: The steps above are most reliable in the scenario where in both sales (Cash Sales and Invoice) and Item Fulfillment transactions are created from a Sales Order.

For kit items where a standalone cash sale or invoice has been created, you may need to create a separate search on this to include both kit and member items so both cost and revenue amounts will be captured in the search results.

Another scenario to consider is if the Member Items may have related sales transactions that are not associated to the kit item.  Meaning the sale is pertaining to the Member Item alone and not to the kit where the latter is a component of.  This will show up in the search but will not be relevant to the information that you need.