Need a good Min Max formula

shanna1shanna1 Member Posts: 76
Hi Guys!

I really need some help here:

I 'm implementing Nav4 for a retail company ( several stores, using WMS) and we want to create a formula that can help users to get a realistic Min qty and Max qty for warehouses and stores replenishment.

To built those min, max , Past sales from stores need also to be take into account.

So my question what could be a realistic Min and Max formula to take into account above facts. I'm searching to have a formula but I'm still searching.

Thanks.

Comments

  • shanna1shanna1 Member Posts: 76
    Please can you explain.

    I need a formula to be able to have realistics Min quantity and max quantity when using requisition worksheet in Navision. It will be used based on Item/SKU ; Per Store/Location ; per Item Group.... YES.

    When you say "Use SQL Analysis Services for this. Just export all required information into SQL. Will work MANY times faster than navision reporting. "

    What does it mean and How can I do that?

    Thanks
  • shanna1shanna1 Member Posts: 76
    No Idea guys? I'm still waiting all your suggestions.

    Thanks
  • SavatageSavatage Member Posts: 7,142
    What did you do in the past?
  • KowaKowa Member Posts: 918
    Create a report for inventory turnover, using the item and the date table with monthly intervals, going one year back. Set the datefilters for each month, calculate the average sales per month ( flowfield in item table ) and the average inventory per month ( beginning of month+Endof Month/ 2 ). The field "Net change" can be used for this. Inventory turnover cycle, Inventory coverage ( Days stock on hand) can then be calculated from these figures and are a sound basis for Min-Max settings.
    Kai Kowalewski
  • themavethemave Member Posts: 1,058
    Here is what I have done, I added two fields to the sku table labled

    suggested min
    suggested max

    then I created a non-printing report with the item table and the stockkeeping unit table

    with the following code attached to the sku table section


    Window.UPDATE(1,"Item No.");
    SugMin := Item."Sales (Qty.)" / SalesPeriods * MinFactor;
    SugMax := Item."Sales (Qty.)" / SalesPeriods * MaxFactor;
    "12 Month Sales" := Item."Sales (Qty.)";
    Larger := '=';
    Precision := 1;
    "Suggested Min" := ROUND(SugMin, Precision, Larger);
    "Suggested Max" := ROUND(SugMax, Precision, Larger);
    MODIFY;

    The variables on the request form for the report are MinFactor, MaxFactor and SalesPeriods

    we run the report usally with a twelve month time period as the sales factor, so the report Date Filter equals the prior twelve month period, for example the 2/1/05..1/31/06

    The MinFactor for our company is .75 = approximately 3 weeks
    The MaxFactor is 1.3 = appoximately 6 weeks
    and the salesPeriod would be 12

    so, the report goes through the item table filtered on location and fills the suggested min/max numbers in the appropriate sku card.

    now your min and max factors are going to vary depending on your industry. Once we have suggested min/max numbers it is up to each location to use them are not. They can edit the suggested numbers or accept and I run another non-printing report to update the saftey stock and max qty with the suggested numbers when they are done.

    I don't have access to cal code in forms or codeunits and such so the only way I could come up this is to use non-printing reports. it is crude but it works.
  • SavatageSavatage Member Posts: 7,142
    Kowa wrote:
    Create a report for inventory turnover.

    That is a built in navision report.
  • KowaKowa Member Posts: 918
    Savatage wrote:
    Kowa wrote:
    Create a report for inventory turnover.
    That is a built in navision report.
    Which Report ID in which version ?
    Kai Kowalewski
  • SavatageSavatage Member Posts: 7,142
    report 10146 US seems to stem back to 3.01 version
    OBJECT Report 10146 Item Turnover
    {
      OBJECT-PROPERTIES
      {
        Date=06/25/01;
        Time=10:37:05 PM;
        Version List=NAVUS3.01;
      }
      PROPERTIES
      {
        CaptionML=ENU=Item Turnover;
        OnPreReport=BEGIN
                      CompanyInformation.GET;
                      ItemFilter := Item.GETFILTERS;
                    END;
    
      }
      DATAITEMS
      {
        { PROPERTIES
          {
            DataItemTable=Table27;
            OnPreDataItem=BEGIN
                            BeginDate := GETRANGEMIN("Date Filter");
                            EndDate := GETRANGEMAX("Date Filter");
                          END;
    
            OnAfterGetRecord=BEGIN
                               SETRANGE("Date Filter",BeginDate,EndDate);
                               CALCFIELDS("Quantity on Hand","Sales (Qty.)","Negative Adjmt. (Qty.)");
                               {Find Average Inventory amount}
                               DataPointDate := BeginDate - 1;
                               SETRANGE("Date Filter",0D,DataPointDate);
                               CALCFIELDS("Net Change");
                               TotalInventory := "Net Change";
                               NumDataPoints := 1;
                               REPEAT
                                 DataPointDate := CALCDATE(TimeBetweenDataPoints,DataPointDate);
                                 IF DataPointDate > EndDate THEN
                                   DataPointDate := EndDate;
                                 SETRANGE("Date Filter",0D,DataPointDate);
                                 CALCFIELDS("Net Change");
                                 TotalInventory := TotalInventory + "Net Change";
                                 NumDataPoints := NumDataPoints + 1;
                               UNTIL DataPointDate = EndDate;
                               {Record Ending Inventory amount}
                               EndingInventory := "Net Change";
    
                               AverageInventory := (TotalInventory) / NumDataPoints;
                               IF (AverageInventory <> 0) THEN
                                 NoOfTurns := ("Sales (Qty.)" + "Negative Adjmt. (Qty.)") / AverageInventory
                               ELSE
                                 NoOfTurns := 0;
    
                               EstAnnualTurns := NoOfTurns * (365.0 / (EndDate - BeginDate + 1));
                             END;
    
            ReqFilterFields=No.,Search Description,Location Filter,Date Filter;
          }
          SECTIONS
          {
            { PROPERTIES
              {
                SectionType=Header;
                PrintOnEveryPage=Yes;
                SectionWidth=18150;
                SectionHeight=1692;
              }
              CONTROLS
              {
                { 1001;Label        ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                             FontBold=Yes;
                                                             CaptionML=ENU=Item Turnover }
                { 1002;TextBox      ;14700;0    ;2250 ;423  ;HorzAlign=Right;
                                                             SourceExpr=FORMAT(TODAY,0,4) }
                { 1003;TextBox      ;17100;0    ;1050 ;423  ;HorzAlign=Right;
                                                             SourceExpr=TIME;
                                                             Format=<Hours12>:<Minutes,2> <AM/PM> }
                { 1004;TextBox      ;0    ;423  ;7500 ;423  ;SourceExpr=CompanyInformation.Name }
                { 1005;Label        ;16950;423  ;750  ;423  ;ParentControl=1006;
                                                             HorzAlign=Right }
                { 1006;TextBox      ;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                             SourceExpr=CurrReport.PAGENO }
                { 1007;TextBox      ;16650;846  ;1500 ;423  ;HorzAlign=Right;
                                                             SourceExpr=USERID }
              }
               }
            { PROPERTIES
              {
                SectionType=Header;
                PrintOnEveryPage=Yes;
                SectionWidth=18150;
                SectionHeight=846;
                OnPreSection=BEGIN
                               CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (ItemFilter <> ''));
                             END;
    
              }
              CONTROLS
              {
                { 1   ;TextBox      ;0    ;0    ;18150;423  ;SourceExpr=Item.TABLECAPTION + ': ' + ItemFilter }
              }
               }
            { PROPERTIES
              {
                SectionType=Header;
                PrintOnEveryPage=Yes;
                SectionWidth=18150;
                SectionHeight=1269;
              }
              CONTROLS
              {
                { 9   ;Label        ;5850 ;0    ;1800 ;846  ;ParentControl=21;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 10  ;Label        ;13950;0    ;1800 ;846  ;ParentControl=25;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 11  ;Label        ;15900;0    ;1800 ;846  ;ParentControl=26;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 12  ;Label        ;0    ;0    ;1800 ;846  ;ParentControl=19;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 13  ;Label        ;1950 ;0    ;3750 ;846  ;ParentControl=20;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 15  ;Label        ;7800 ;0    ;2100 ;846  ;ParentControl=22;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 16  ;Label        ;10050;0    ;1800 ;846  ;ParentControl=23;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 17  ;Label        ;12000;0    ;1800 ;846  ;ParentControl=24;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
              }
               }
            { PROPERTIES
              {
                SectionType=Body;
                SectionWidth=18150;
                SectionHeight=423;
              }
              CONTROLS
              {
                { 19  ;TextBox      ;0    ;0    ;1800 ;423  ;HorzAlign=Left;
                                                             SourceExpr="No." }
                { 20  ;TextBox      ;1950 ;0    ;3750 ;423  ;SourceExpr=Description }
                { 21  ;TextBox      ;5850 ;0    ;1800 ;423  ;CaptionML=ENU=Quantity on Hand;
                                                             DecimalPlaces=2:5;
                                                             BlankZero=Yes;
                                                             SourceExpr=EndingInventory }
                { 22  ;TextBox      ;7800 ;0    ;2100 ;423  ;CaptionML=ENU=Average Inventory;
                                                             DecimalPlaces=2:5;
                                                             BlankZero=Yes;
                                                             SourceExpr=AverageInventory }
                { 23  ;TextBox      ;10050;0    ;1800 ;423  ;DecimalPlaces=2:5;
                                                             BlankZero=Yes;
                                                             SourceExpr="Sales (Qty.)" }
                { 24  ;TextBox      ;12000;0    ;1800 ;423  ;DecimalPlaces=2:5;
                                                             BlankZero=Yes;
                                                             SourceExpr="Negative Adjmt. (Qty.)" }
                { 25  ;TextBox      ;13950;0    ;1800 ;423  ;CaptionML=ENU=Number of Turns;
                                                             DecimalPlaces=2:2;
                                                             BlankZero=Yes;
                                                             SourceExpr=NoOfTurns }
                { 26  ;TextBox      ;15900;0    ;1800 ;423  ;CaptionML=ENU=Estimated Annual Turns;
                                                             DecimalPlaces=2:2;
                                                             BlankZero=Yes;
                                                             SourceExpr=EstAnnualTurns }
              }
               }
          }
           }
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=0;
          Height=0;
          SaveValues=Yes;
        }
        CONTROLS
        {
        }
      }
      CODE
      {
        VAR
          CompanyInformation@1000 : Record 79;
          BeginDate@1001 : Date;
          EndDate@1002 : Date;
          ItemFilter@1003 : Text[250];
          EndingInventory@1011 : Decimal;
          AverageInventory@1006 : Decimal;
          NoOfTurns@1007 : Decimal;
          EstAnnualTurns@1008 : Decimal;
          DataPointDate@1005 : Date;
          NumDataPoints@1009 : Integer;
          TotalInventory@1010 : Decimal;
          TimeBetweenDataPoints@1004 : TextConst 'ENU=1W';
    
        BEGIN
        END.
      }
    }
    
  • KowaKowa Member Posts: 918
    edited 2016-08-17
    Savatage wrote:
    report 10146 US seems to stem back to 3.01 version
    Thanks for the info, no such thing available in the German versions, so we had to create one ourselves. :)
    Kai Kowalewski
Sign In or Register to comment.