Need a good Min Max formula

shanna1 · 2006-01-31

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.

shanna1 · 2006-01-31

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

shanna1 · 2006-02-02

No Idea guys? I'm still waiting all your suggestions.

Thanks

Savatage · 2006-02-02

What did you do in the past?

Kowa · 2006-02-02

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.

themave · 2006-02-02

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.

Savatage · 2006-02-02

Kowa wrote:

Create a report for inventory turnover.

That is a built in navision report.

Kowa · 2006-02-03

Savatage wrote:

Kowa wrote:

Create a report for inventory turnover.

That is a built in navision report.

Which Report ID in which version ?

Savatage · 2006-02-03

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.
  }
}

Kowa · 2006-02-06

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.

Need a good Min Max formula

Comments

Categories