P/O Forecast Calculations (PO07)

Additional Information
Automated Purchasing Process

The Maintain Forecast Calculation Code (PO07) program allows the set up and maintenance of the information required for determining anticipated or future inventory requirements. PO07 is used by the Forecast, Purchasing, Lead-time Recalculation (PO25) program to calculate what the forecasted (future) demand of a Product (or group of Products) will be for a reorder period, based on past demand of that Product (or group of Products).

  • The Product-forecast figure is required to calculate the reorder quantity (ROQ). It is imperative to forecast anticipated requirements with as much accuracy as possible in order to stock sufficient inventory items. Overstocking items inflates carrying costs, while understocking items lowers Customer Service and increases stock-out costs. Forecasted requirements must be done on a per item basis; however, it is often possible to apply the same variables to similar Products to determine a forecast. As a result, forecast calculation tables are built independently of Products; the appropriate code is then referenced in the 'Forecast Calc.' field on each Product’s inventory master record in Maintain Inventory Master (IM13/Stock).

  • The Weighting Table(s) in PO07 provides the parameters or 'model' for the calculation performed by PO25. The Weighting Table defined in PO07 is applied to the demand statistics in Display Product Availability (IC45/C2) for each Product and a new forecast is calculated.

  • A demand forecast is enhanced by using historical usage and 'normalizing' it so that it more closely resembles the reorder period to be forecast. For example, if the historical data being used does not include promotions (flyers), but the period being forecast does have a promotion, the historical data should be massaged to better represent the quantity required. In this example, the units are increased by a 'flyer adjustment factor'. If the reverse situation were occurring, the data is decreased by a 'flyer adjustment factor'.

  • In addition, a normal growth rate may be incorporated into a calculation because the forecast figure is going to be used to apply to future periods. It is reasonable to adjust the forecast to take into account the normal rates of growth that are expected in a business. If a negative growth rate is anticipated, future demand will likely decline and calculations should be adjusted accordingly. In addition, when using historical data as a basis for forecasting demand, certain past periods may be more significant than others in order to determine a forecast. As a result, it is useful to have the ability to 'weight' the importance of those periods.

  • The program requires parameters or limits to be entered when building the tables. When the forecast is calculated, the results are compared to the preset upper and lower limits to determine if the results are reasonable. If the calculated forecast does not fall within the preset limit range, the program includes logic to recalculate the forecast using a second set of parameters. If the second calculation also falls outside the range specified, the system reverts to calculating a forecast that is the straight simple average of the past demand.

The following list defines every field on PO07:

  • Forecast Calculation - enter or select from the query the Forecast Calculation code. The code can be a maximum of 3 alpha numeric characters in length. This code is applied to individual Products in the 'Forecast Calculation' field in IM13/Stock.

  • Description - enter a description of the Forecast Calculation in the field directly below the Forecast Calculation field. The description can be a maximum of 30 alpha numeric characters in length. If you are recalling an existing code, the description automatically displays once the code is entered.

  • # of Months Comparison for Growth - a growth factor is calculated by the system in order to extrapolate year over year changes in demand. The growth factor calculation is triggered when a non-zero value is entered in this field. The number of months entered specifies the number of months to be used in comparing data year to year.

    • Enter the number of previous months of this year to compare against the same period of last year in order to calculate the actual growth. You can enter a maximum of 12 in this field. If you attempt to enter a value greater than 12, the following message displays: "## is not a valid input value. Valid values are: 1-12. OK".

    • The most recent history is compared. If you enter '6', then the previous 6 months of demand are compared to the same six months of the prior year.

    • Note: The system retains 24 months of history. Therefore, the most recent history is always compared to the previous history for the same months in order to calculate a growth factor.

    • For example, if you are currently in June, and you enter '6' in this field, a growth factor is calculated based on a comparison of the bookings for the following periods:

      Recent History   Previous History
      (This Year)   (Last Year)
      May   May
      April   April
      March   March
      February   February
      January   January
      December   December

    • Note: If a Product does not have sufficient demand history accumulated, a growth factor will not be calculated by the system when PO25 runs.

  • Lower Growth Factor - when a value other than zero is entered in the # of Months Comparison for Growth field, the growth factor is calculated by the system. A check is performed to ensure that the result falls within reason. While you can set the system to simply use the calculated growth factor, in many cases you will want to filter out unreasonable situations. Entering a value in the Lower Growth Factor field allows you to filter out growth that is deemed to be too low (i.e. a steep drop in demand).

    • Enter a value in the Lower Growth Factor field that represents the lower limit used by PO25 to test whether the forecasted growth falls within reason, i.e. above the factor entered in this field. The default is 0.5 (50% of the calculated value).

    • If the growth factor that is calculated from the comparison of historical bookings quantities is less than the value that you input in this field, then the value in this field is used instead in the forecast calculation.

    • If you enter 0 (zero) in this field, there will be no lower limit test for the growth factor, i.e. as long as the calculated growth factor is less than the Upper Growth Factor limit, the calculated factor is accepted by the forecast recalculation routines.

    • If both the Upper and Lower Growth Factor limits are set to 0 (zero), then the calculated growth factor is used.

  • Upper Growth Factor - when a value other than zero is entered in the # of Months Comparison for Growth field, the growth factor is calculated by the system. A check is performed to ensure that the result falls within reason. The Upper Growth Factor is used to test the calculated growth factor, to see if the calculated growth factor is within a reasonable amount, allowing you to filter out growth that is deemed to be too high (i.e. a steep increase in demand).

    • Enter a value in the Upper Growth Factor field that represents the upper limit used by PO25 to test whether the forecasted growth falls within reason, i.e. below the factor entered in this field. The default is 2.0 (double the calculated amount).

    • If the growth factor that is calculated from the comparison of historical demand is greater than the value that you input in this field, then the value in this field is used instead in the forecast calculation.

    • If you enter 0 (zero) in this field, there will be no upper limit test for the growth factor, i.e. as long as the calculated growth factor is greater than the Lower Growth Factor limit, the calculated factor is accepted by the forecast recalculation routines.

    • If both the Upper and Lower Growth Factor limits are set to 0 (zero), then the calculated growth factor is used.

  • Lower Weighted Demand Limit - the Weighting Table (Primary or Secondary) provides the parameters or the model used in the forecast calculation performed by PO25. The Weighting Table is applied to the demand statistics in IC45/C2 for each Product and a new forecast is determined. The result of the calculation is compared to the Lower Weighted Demand Limit to determine if the forecast falls within reason. This reasonability check is first applied to the primary weighting, and if it fails, then the check is applied to the secondary weighting.

    • Enter the lower limiting condition that must be met when comparing weighted demand calculated from the primary (or secondary) weighting table to the simple average usage calculated for the Product (to a maximum of 24 months). The Lower Weighted Demand Limit represents the lower limit of the reasonableness test. The default lower limit is 0.5, or 50%.

    • The weighted demand (calculated using the Primary Weighting Table) is compared to the simple demand (simple or straight demand = a weighting of 1.00). If this ratio falls below the Lower Weighted Demand Limit, the recalculated forecast fails the reasonability test and the system examines the Secondary Weighting Table and recalculates the weighted demand. If the secondary weighting comparison also falls below the Lower Weighted Demand Limit, then the system uses the 'simple weighting' to determine the new forecast value.

    • If you enter 0 (zero) in this field, the Lower Weight Demand Limit test will not be done. As long as the primary weighted demand, expressed as a fraction of average or simple demand, is less than the Upper Weighted Demand Limit, then it is deemed reasonable.

  • Upper Weighted Demand Limit - the Upper Weighted Demand Limit is paired with the Lower Weighted Demand Limit. To recap: the Weighting Table (Primary or Secondary) provides the parameters or the model used in the forecast calculation performed by PO25. The Weighting Table is applied to the demand statistics in IC45/C2 for each Product and a new forecast is determined. The result of the calculation is compared to the Upper Weighted Demand Limit to determine if the forecast falls within reason. This reasonability check is first applied to the primary weighting, and if it fails, then the check is applied to the secondary weighting.

    • Enter the upper limiting condition that must be met when comparing weight demand calculated from the primary (or secondary) weighting table to the simple average usage calculated for the Product (to a maximum of 24 months). The Upper Weighted Demand Limit represents the upper limit of the reasonableness test. The default upper limit is 2.0, or two times greater.

    • The weighted demand (calculated using the Primary Weighting Table) is compared to the simple demand (simple demand = a weighting of 1.00). If this ratio is greater than the Upper Weighted Demand Limit, the recalculated forecast fails the reasonability test and the system uses the Secondary Weighting Table to recalculate the weighted demand. If the secondary weighting comparison is also greater than the Upper Weighted Demand Limit, then the system uses the 'simple weighting' to determine the new forecast value.

    • If you enter 0 (zero) in this field, the Upper Weight Demand Limit test will not be done. As long as the primary weighted demand, expressed as a fraction of average or simple demand, is greater than the Lower Weighted Demand Limit, then this is deemed reasonable.

  • Flyer Adjustment Factor - enter the factor to be applied to promotional booking data to normalize it for comparison purposes. The default is 0.25. Bookings for the previous 24 months are stored in two separate 'buckets'; one holds total units booked for the period, while the second holds flyer units booked for the period. Note: The Flyer Adjustment Factor is only relevant for companies that use flyer type Price Lists (defined in Maintain Price Lists (IM18)).

    • The usage recalculation routines check the Flyer Effective Days field and then check Price Lists to determine whether this Product (or group of Products) is on an active flyer.

    • If the Product is on an active flyer, then each period in the 24 month booking history is examined for the presence or absence of flyer units booked.

    • Each period that has no flyer units booked must now be 'normalized' to simulate a flyer condition. Multiplying the total units booked for the period by the Flyer Adjustment Factor does this. For example, the previous 24 months' demand is:

      Months Total Unit Flyer Unit Flyer Adj. Normalized
      Previous Demand Demand Factor Demand
      01 63 10 N/A 63
      02 75 0 0.25 94
      03 70 0 0.25 88
      04 85 15 N/A 85
      ... ... ... ... ...
      24 69 0 0.25 86

    • If the Product is not on an active flyer, then any period in the booking history that has flyer units must be reduced by the flyer adjustment factor in order to normalize it and simulate a non-flyer condition.

    • Therefore, the 24 month booking history may be increased or decreased by the value of the flyer adjustment factor.

    • Note: When a value is entered in the Flyer Adjustment Factor field, PO25 uses the 'normalized demand' in the calculation of the new forecast. The system continues with its reasonability checks and reverts to the Secondary Weighting Table as required.

    • If you enter a 0 (zero) in this field, the flyer files are not read to determine flyer activity and no adjustment is made to the booking history.

  • Flyer Effectivity Days - enter the number of days into the future to check the flyer type Price Lists in order to determine if a flyer is in effect. The default for this field is 30. The program reads the system date and adds the value set in this field to obtain the limiting future date. If the Product is on an active flyer, then the history is adjusted to simulate a flyer condition, otherwise the history is adjusted to simulate a non-flyer condition.

    • If you enter 0 (zero) or 00 in this field, the flyer files are not checked at all and booking history is not adjusted.

  • Additional Growth Factor - is used to massage the forecasted usage when known influences, not reflected in history, affect the bookings. The value entered in this field represents the future growth rate which is applied to the forecast calculation. The weighted demand is multiplied by the value stored in this field. For example, if the Additional Growth Factor is 1.25, then the weighted usage value is increased by an additional 25%.

    Note: Entering a value of zero causes the additional growth calculation to be skipped. This is equivalent to entering a value of 1.00.

    You can use the Additional Growth Factor in the following situations:

    1. We have a Product which has consistent bookings and possible growth figures, but we know that strong competitive Product is being released which will cause our Product to suffer as a result. We can force down the forecasted usage by entering a factor less than 1.

    2. If we know our Supplier is putting on a major marketing effort which will likely increase demand, we can increase the usage by using a factor greater than one. This forces the forecasted usage upwards.

    Caution: Additional Growth Factors are typically utilized on a temporary basis. You should review the results regularly.

  • Primary Weighting - this is the first folder on PO07. The Primary Weighting table or 'model' is a series of 24 fields representing the previous 24 months. Enter the 'weight' to be applied to each period when the recalculation routine in PO25 is calculating the primary weighted demand. The default value is '1.00'.

    For example, a new store location has opened within the last 7 months. Product usage is calculated using the following model or weighting structure for the previous 7 periods:

      Month Weight  
      1 5  *
      2 4  *
      3 3  *
      4 2  *
      5 1  **
      6 1  **
      7 1  **
      8 0  
      9 0  
      10 0  
      11 0  
      12 0  

      * The most recent month past has the most significance because sales most likely have increased over time from the store's opening.

      ** Conversely, the first several months that the store was open most likely had a lower level of sales or a sales level not indicative of the norm due to one-time opening specials, etc.

  • Secondary Weighting - the Secondary Weighting table is a back up for the Primary Weighting table. The Secondary Weighting table is a series of 24 fields representing the previous 24 months. Enter the weights to be applied to history for each period being compared. The default is 1.00.

    • The Secondary Weighting table is used in the event that the results calculated for weighted usage from the Primary Weighting Table either do not meet the Lower Weighted Demand Limit or exceed the Upper Weighted Demand Limit on adjustment of usage when compared to the average usage for the past 24 months. As a result, a different set of weights should be used in the Secondary Weighting table.

    • The second set of weightings listed on the Secondary Weighting folder are used in cases where the demand data for a Product is inconsistent on a month-to-month basis, most often for a seasonal Product. Demand may spike for a short period of time, for example for 2 to 3 months. The primary weightings in this case may focus on one or two months. When this narrow band hits lower demand months, the forecast might not be deemed 'reasonable'. The Secondary Weighting folder can be set up to examine a larger window of time, for example 3 or 4 months. This 'smooths out' the forecast, especially in the months just before and just after the demand spike.

The following table outlines the functionality of each button on PO07:

Button Label Icon Functionality
Submit Submit Saves the information entered for the current Forecast Calculation code.
Delete Delete Deletes the current Forecast Calculation code.
Clear Clear All fields on PO07 clear or revert back to their default values allowing you to add a new Forecast Calculation code.

PO07
Maintain Forecast Calc. Code (PO07)