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.

Howdy, Stranger!

Quick Links

Categories

Need a good Min Max formula

Comments