Sales Margin report

  • Sales Margin report

    Posted by stacey.timothy@shubee.com on April 17, 2017 at 1:07 pm
    • Stacey Timothy

      Member

      April 17, 2017 at 1:07 PM

      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

      Member

      April 17, 2017 at 2:05 PM

      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

      Member

      April 18, 2017 at 9:26 AM

      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

      Member

      April 18, 2017 at 8:55 AM

      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

      Member

      April 18, 2017 at 9:27 AM

      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

      Member

      April 18, 2017 at 9:35 AM

      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

      Member

      April 19, 2017 at 10:23 AM

      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

      Member

      April 19, 2017 at 11:21 AM

      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
      ——————————
      ——————————————-

    stacey.timothy@shubee.com replied 7 years, 2 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Sales Margin report’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!