Report for Missed Sales opportunities by Qty. Ship. = 0

emulsified
emulsified Member Posts: 139
edited 2010-07-12 in NAV/Navision Classic Client
Does anyone know of a report that I can run that will give me the following:

On a daily, POSTING DATE, or DATE RANGE basis a report that will show me collectively how many items we DID NOT ship/invoice (obviously a 0 quantity for Ship/Invoice).

I need the report to show $$$ in sales lost because we did not have the items in stock.

Once I have this I can stick it to some of our distributors. All help is appreciated.

Thanks.
Half-empy or half-full how do you view your database?

Thanks.

Comments

  • Savatage
    Savatage Member Posts: 7,142
    Do you have a field on the sales line & sales invoice line that holds the Original Qty ordered?

    I know it was a mod for us to add a field that holds that value. Then with that value it's easy.

    If "Qty. Ordered" <> Quantity then
    "Lost Sales" := ("Qty. Ordered" - Quantity) * "Unit Price"
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • rajatkalia
    rajatkalia Member Posts: 65
    HI

    should'nt we be considering the report based on "Promised Delivery Date". This is a field in the sales line. The report should show if system date > Promised delivery Date and Quantity Shipped Base< Quantity Base, then Lost Sales = (Quantity Base - Quantity Shipped Base) * (Unit Price / Quantity per Unit of Measure). this will give in the Base UOM.

    Also we can have a column that shows how many days delay(System date - Promised delivery Date). it will help in future Purchase planning.


    Regards,
    Rajat.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    emulsified wrote:
    Does anyone know of a report that I can run that will give me the following:

    On a daily, POSTING DATE, or DATE RANGE basis a report that will show me collectively how many items we DID NOT ship/invoice (obviously a 0 quantity for Ship/Invoice).

    I need the report to show $$$ in sales lost because we did not have the items in stock.

    Once I have this I can stick it to some of our distributors. All help is appreciated.

    Thanks.

    The way I do it is to craete a new table with primary key like sales line plus another integer. Add a SalesType field :Order,Ship,Sale,Deleted

    Populate this from the sales line according to the SalesType Field and run statistics off this new table.

    Note 1/only put the absolute minimum of needed fields in this tables.
    Note 2/ NEVER use the Sale Line for creating historical reports. Always use a different table.
    David Singleton
  • emulsified
    emulsified Member Posts: 139
    I just checked and realized that the SALES INVOICE form does not have a Qty to Ship, Qty to Invoice like the SALES ORDER form has, at least it is not available under Show/Hide menu.

    I am going under the hood to see if the SALES INVOICE LINE table even has those fields available.

    I suppose I can figure this out easily but thought something existed that I may have overlooked.

    Thanks.
    Half-empy or half-full how do you view your database?

    Thanks.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    emulsified wrote:
    I just checked and realized that the SALES INVOICE form does not have a Qty to Ship, Qty to Invoice like the SALES ORDER form has, at least it is not available under Show/Hide menu.

    I am going under the hood to see if the SALES INVOICE LINE table even has those fields available.

    I suppose I can figure this out easily but thought something existed that I may have overlooked.

    Thanks.


    I am guessing my post above is invisible to you then? ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)
    David Singleton
  • Savatage
    Savatage Member Posts: 7,142
    The Quantity field might show 2 but what if they wanted 10. Without the orig qty stored somewhere..
    either in another table or adding a "Qty. Ordered" field to the sales line, Sales invoice Line & sales shipment line - you're not going to be able to calc how much was lost.

    We're a distributor and this info was definatly important to us also.
    And we don't backorder, so we have no qty remaining.. On posting the order goes to posted invoice regardless.

    We don't delete info, so using sales invoice line table works for us (not optimal) but for daily, week or month totals - it does the job.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org

Categories