Wednesday, February 20, 2019

Creating a Report / Search to show Average Cost versus Average Selling Price

There is no field on the Item Record that stores the Average Selling Price of an Item. Similarly, there is no standard Report / Search that you can use to compare this with the Average Cost.

As an alternate solution, you can create a Saved Search:

1.    Go to Transactions>Management>Saved Searches>New

2.    Select Item

3.    On the Criteria tab add Transaction Fields : Type is Sales Order, Inventory Location is 'specific location', Transaction Fields : Location is 'specific Location'

4.    On the Results tab add Name (Summary Type = Group), Location Average Cost (Summary Type = Group) and Transaction Fields : Item Rate (Summary Type = Average)

5.    Hit Preview or Save and Run

The Saved Search will display three columns: Name, Location Average Cost and Location Average Selling Price. Please note that if you are using Multi-Location Inventory, then you need to run this report for each Location. You can further customize this and consider the following recommendations:

·         You can add a Date field on the Criteria tab if you want to count sales for a given period, e.g. this year

·         You can change Transaction Fields : Type to Cash Sale / Invoice instead of Sales Order if you want to use actual sales

No comments:

Post a Comment