[Stacey Timothy]

I am trying to create a smartlist that will list item sold, retail price, actual selling price and cost but I can’t seem to figure it out since some of this data resides in the Sales module and some in the Inventory/Purchasing Module.

I’m trying to pin point an increase in our COGS to a particular item sale and/or sales rep.

Does anyone have any suggestions on how I can go about getting all of this data on one report? Thanks in advance for any help!

——————————
Stacey Timothy
CFO, Chief Financial Officer
ShuBee
Macon GA
——————————

[Matthew Arp]

Stacey, you should be able to create a margin SmartList from the canned Sales Line items SL. It should already have the unit cost/price, then you’d be able to look for items that have a lower than expected margin. You can also do this at the document level and first find documents that have a low margin and then from there look at the lines and find the culprit.

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Stacey Timothy]

The issue with the canned SL report is that once the unit price has been changed (discounted) from the original retail price, the new price is what pulls in on these reports. I want to see what the actual retail price is, what the actual price on a particular order was and the cost. The retail price gets replaced by the actual selling price so I am not able to see a true margin.

Any way to show both prices on the same report?

——————————
Stacey Timothy
CFO, Chief Financial Officer
ShuBee
Macon GA
——————————
——————————————-

[Andrew Ells]

Stacey,

You can also create an SSRS report that will automatically email you these figures in almost any format that you can dream up.   I have created these and have even took it a step further and created a Margin analysis by Customer report (We use Chargeback processing so I had to link other tables.) 

——————————
Andrew Ells
Senior Finance Manager
Fitness EM LLC
Uxbridge, MA

Gpug.comBoston
——————————
——————————————-

[Stacey Timothy]

Andrew, will the SSRS report show me the retail price and the actual selling price or will the actual selling price over-ride the retail price?

——————————
Stacey Timothy
CFO, Chief Financial Officer
ShuBee
Macon GA
——————————
——————————————-

[Matthew Arp]

To get the retail price of an item you would have to take several things into account (price list, customer, location, UoM, etc.) This puts calculating what the price should have been a bit out of the grasp of a standard SL and would probably require approaching a consultant. I would still suggest that the retail price is not really needed, a margin calculated would only require the selling price and selling cost, both of which are already on the Sales Line. I would recommend looking at your margins first before going down the rabbit hole of adding retail price.

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Andrew Ells]

Stacey,

The report will use the unit price on the invoice, so if you change that to reflect the price to the consumer then no it wont reflect the retail price.  I may not be reading your comment right, but If you use the markdown and trade discount fields on the sales orders to reflect the discounts you are giving to customers then I believe this will maintain the price to the customer.   But if you are a wholesaler the retail price and customer price will always differ anyway and are not reflective in a customer margin analysis because they aren’t paying retail to start.  Again I may not be reading the comment right.

——————————
Andrew Ells
Senior Finance Manager
Fitness EM LLC
Uxbridge, MA

Gpug.comBoston
——————————
——————————————-

[Ken Roy]

Hi Stacey,

Depending on you price structure, this can be done in SSRS or a SQL query.  If you are using Percentage of List Price as price structure for your customers, here’s where the data is located:

SOP30300
      SOPNUMBE, ITEMNMBR, UNITCOST, UNITPRICE
SOP30200
      SOPNUMBE, PRCLEVEL
IV00108
       ITEMNMBR, PRCLEVEL, UOMPRICE
IV00105
       ITEMNMBR, LISTPRCE

The retail price for my item would be the default of the price list.  Retail = LISTPRCE/(UOMPRICE/100)

If LIST PRICE is $10 for an item and the default price list on the order is 25% OFF LIST, you get:
$7.50=$10/(75/100)

Join all these tables and create your selections based on the dates or criteria you decide.  You can then calculate the margin based on what the item sold for and what the item would have sold for using the default price list for the customer.

——————————
Ken Roy
Premier Manufacturing
Tualatin OR
——————————
——————————————-