Seasonal Inventory Forecasting in Dynamics NAV

Advanced Planning: How does Dynamics NAV handle seasonal forecasting?

Many clients have been asking recently about Microsoft Dynamics NAV’s capabilities for handling seasonal forecasting. Most clients already know that Dynamics NAV can automate your inventory replenishment by maintaining minimum quantity on hand, but many are not yet aware that you can take it a step further and set sales/production forecasts for inventory over a period. This is helpful as seasonal changes will increase/decrease demand for an item over the course of the year.

It is important to note that this blog article does not cover the Sales and Inventory Forecast function introduced in Dynamics NAV 2017; this feature incorporates Microsoft’s Machine Learning algorithm that evaluates past sales and suggests future forecasts. This blog article describes how to set your own Sales Forecast by using the Production Forecast feature, which will drive future replenishment (i.e. Production and/or Purchases).

A quick recap, Dynamics NAV evaluates inventory demand and supply through Master Production Scheduling (MPS) and Material Requirements Planning (MRP). The MPS algorithm considers the demand placed on an item in a Sales Order Line and Production Forecast. Of further note, Dynamics NAV takes precedence over actual Sales quantity above forecasted quantity; meaning that Dynamics NAV will consume forecasted demand in favor of actual demand.

Let’s go through the basic setup requirements and some troubleshooting tips on how to incorporate sales forecasts to maintain adequate inventory over a period.

Step 1: Setup Production Forecast

Create a new (or modify an existing) Production Forecast. As you can see below, I’ve created a new 2018 Forecast.

 

Step 2: Set Forecasted Quantity of Item

To drill into the Production Forecast, click the Edit Production Forecast button, which will open the Production Forecast page.

Tip #1: Set the Location Filter in Production Forecast

I find that the MPS/MRP calculation in general works more reliably, if you set the Location Filter to an actual location. In all my Dynamics NAV deployments, I always recommend that the client sets a Location Code even if they only have 1 physical location.

In this example, I’ve set it to the BLUE Warehouse location code.

Step 2: Set Scheduling Period

Typically, clients will forecast per month. You can set the forecasted period by updating the View By field. This will change the date columns in the matrix by the period you’ve selected (i.e. Day, Week, Month, etc.)

Step 3: Enter Quantity per Item

Using Item No. 1000 (Bicycle), I’ve scheduled my sales forecast for the bicycle item for Dec 2017 and Jan 2018 as shown below. Entering a quantity in the Production Forecast matrix will enter a record in the Production Forecast Entries table which will be taken into consideration when running the MPS algorithm.

Step 4: Setup Item Planning Parameters

To run the MPS algorithm on the item that you’ve forecasted, the planning parameters must be setup per item. By default, I set the Reorder Policy: Lot-for-Lot.

Tip #2: Set Lot Accumulation Period to match the forecasted period.

If you forecast per month as I’ve shown you above, then schedule for 30D or 1M as I’ve entered below. Running a Reorder Policy of Lot-for-Lot for an item, Dynamics NAV will consolidate multiple quantities into 1 line at the start of the forecasted period. Without taking this extra step of setting the Lot Accumulation Period, Dynamics NAV will disregard the quantity that you’ve budgeted at the beginning of the forecast period.

Tip #3: Create Stockkeeping Unit per Item when running MPS

As I’ve indicated before, when running MPS/MRP and setting item planning parameters, I find Dynamics NAV gives you more accurate results when you forecast inventory per Location Code. As an additional step, generate a Stockkeeping Unit based on the planning parameters that we’ve setup on the Item level; you can run the Create Stockkeeping Unit routine.

In the example, I’m only going to generate it for the BLUE warehouse location.

Step 5: Run MPS for the forecast period

Since I’m working with a mix of Production and Purchase items, I’m going to be using the Planning Worksheet to run the MPS algorithm via Calculate Regenerative Plan function.

Below is a list of the fields that are used in the Calculate Plan prompt:

• Select MPS checkbox
• Set Starting/Ending Date fields to cover the period that I’ve forecasted for the item
• Make sure that the Use Forecast field is assigned the correct Production Forecast

 

After running the routine, notice that this resulted with the 1000 units that I’ve forecasted for Item No. 1000 in the month of December.

That’s all good based on what we’ve forecasted for the month of December, but what happens if an actual sales order is entered for that item. How will Dynamics NAV handle that? The good news is that Dynamics NAV will take precedence over the actual demand placed on an item above what’s been forecasted, meaning; Dynamics NAV will consume actual demand that is entered for an item off what was forecasted.

For example, I’m going to place a Sales Order for 500 units of the Bicycle and schedule it to be shipped by Dec 15^th, let’s look at the result when I re-run MPS. Notice that 500 of the items originated from the Sales Order I just placed, and then an additional 500 units remain from what I’ve originally forecasted in December.

What happens if we’ve forecasted below actual sales? Will Dynamics NAV limit us to replenish only for what we’ve forecasted? The answer of course is, no. Dynamics NAV will schedule to replenish actual demand that’s been entered in the system. For example, if I increase the original quantity scheduled to 1,500 units, let’s look at the result.

Notice that the original forecasted quantity of 1000 units has been completed consumed and Dynamics NAV is only taking into only actual sales quantity.

This has been an underutilized tool that is readily available to Dynamics NAV users that can help take into account seasonal changes that may change the quantity demand of an item over the course of the year.