Reduction key fixed month in Dynamics AX

The Reduction key is the period table for forecasting and master planning.

Most companies forecast in monthly buckets, a few get down to weeks, and I worked with a company once where the forecast was loaded in for every 2 weeks. Once the forecast is loaded, the typical process is that sales orders are netted off against forecast. If we’re going to do that we need to know which forecast relates to which sales order.

Dynamics AX master planning uses the Reduction key table. That was the only option in AX 2009. There’s a setup option on the Master plan:


Both the ‘Percent’ and ‘Purchase/sales order’ Reduction principle are driven by Reduction keys.

But AX 2012 offers a fourth option ‘Transactions – dynamic period’:


‘Transactions – dynamics period’ doesn’t use a Reduction key. The forecast records themselves define the period – a forecast period starts on a forecast record, and ends on the next period.

But whenever a company tells me that they forecast in calendar months I still tend to use Reduction keys, because it’s a simple and robust setup for monthly forecasting.

The Reduction key is defined on the Coverage group. It’s an optional field, but don’t leave it blank or you will find that sales orders aren’t netted off against forecast (the demands are just added together).


Where did that Reduction key come from? Master planning > Setup > Coverage > Reduction keys:


As you can see there’s a Wizard. Click the button and step through the setup. Not much to do on the first screen except click Next:


The next form lets you define the length of your buckets (e.g. months); the number of buckets (periods or reduction key lines) you wish to create; and whether the periods have a fixed start date or start on the current date.

This week I learnt to leave the Opening date blank, and I normally set the number of periods so that I’ve covered the forward time horizon for master planning, with plenty to spare, (although really when you think about it, you really only have to cover the forward time horizon of your open sales orders, not forecasts):


On the next form you define the Reduction key ID and a Description:


Then we’re given a preview:


… and a summary:


And Finish creates the Key and the Lines:


Now for the clever bit. As I said, until this week I never used to leave the Opening date blank. I suppose I was conditioned by the AX 2009 demo data which looked like this:


And then I reminded everyone that the Reduction key setup Opening date needs to be re-set once a year.

But as you can see from my AX 2012 screenshot above – you don’t have to enter an opening date. Leave it blank and the system just uses 1st January of the current year. Simple! But don’t setup the Reduction key with only 12 monthly periods, because that’ll give you issues as you get into December (or earlier if you have a long forward open order book).

Dynamic safety stock

“Deck the halls with boughs of holly”.

The Holly Export Co-operative of New Zealand (motto “What the HEC”) have a common supply chain issue: a predictable seasonal up-surge in demand. The supply chain planners are given a monthly forecast, and sales normally fluctuate around a base forecast of 1,000 units per month, except for December when they expect to sell 10,000 units. They’re prepared to hold a safety stock buffer in their UK warehouse (to cater for the sales slightly above forecast), so the forecast, and safety stock profile for this item is:

Month

September

October

November

December

January

February

Forecast

1,000

1,000

1,000

10,000

1,000

1,000

Safety stock

100

100

1,000

1,000

100

100

The transport lead time from New Zealand is 7 weeks (air-freight is not an option) and the shipping contract is a shipment every two weeks.

So let’s set this up in master planning. Entering the sales forecast is simple enough. Released product > Plan > Demand forecast:


We can also setup the safety stock as a Minimum on the item coverage record, and we can setup a minimum key on the item coverage, so our safety stock flexes up and down, Released product > Plan > Item coverage:


Let’s assume it’s the middle of September. We have some stock and some open purchase order lines. Net requirements shows:


So we have a nice fat planned purchase order for delivery at the beginning of December, but unfortunately it’s not fat enough. The system’s still planning based on the current safety stock and simply isn’t taking our planned increase in safety stock into account. Also there’s no attempt to build stock in November – again because the system’s planning for the current safety stock level and not the stock level that we’re going to need in the future.

So let’s try something completely different. I’m adding an additional sales forecast to represent my safety stock buffer stock:


Additionally I’m taking advantage of the ‘Include customer forecast in the demand forecast’ check box setting on the Coverage group. Master planning > Setup > Coverage groups:


With this setting de-selected, my customer specific ‘dummy’ forecast is added to the normal (all customers) forecast. (If this parameter is selected the system effectively ignores the customer specific forecast, and only plans to the normal ‘all customers’ forecast). Naturally now I don’t have a minimum stock in my item coverage. Net requirements shows me:


That’s better – I’m being prompted to build stock a little in November and my December planned order is covering my worst case scenario, but haven’t I gone a little bit too far? I’m now assuming that I’m going to consume all of my safety stock buffer every month – obviously that’s un-realistic, so what I need to do is insert into my demand forecast the incremental increase in my safety stock. So my demand forecast is:


I can’t enter the negative forecast that represents the planned reduction in the safety stock, but just for a minute I’m going to ignore that problem. Now, with my minimum stock re-instated my planning looks like this:


That’s pretty close.

But there’s an alternative method of representing demand (and supply) in master planning. We can take advantage of an un-posted inventory movement journal. We can even create a movement journal without an off-set account so that it can’t be posted (or use security user group settings to prevent normal users accessing the journal). What we need to do now is get a clever developer to write us a journal line representing demand when the safety stock’s going to increase, and a journal line representing supply when the safety stock’s decreasing. It’s not the world’s simplest customisation – but it’s easy to test.

So my conclusion is that Item coverage safety stock and Minimum keys are not sufficient to plan in a relative long lead time planning environment. A more detailed analysis of the sales forecast and inventory plan is required. The sales forecast is represented in Dynamics AX as a demand forecast, and an inventory journal is used to represent the additional demand (and supply) created by dynamic safety stock buffers.

You also probably noticed that I haven’t attempted to reconcile the difference between the 2-week supply cycle and a monthly forecast. That’s the subject for another day (or you could simple lookup ‘Period keys’).

Monthly forecast to weekly production plan

I’ve lost count of the times that I’ve been shown a forecast spreadsheet which shows the sales forecast in monthly buckets – and of course we’re going to want to import that into Dynamics AX.

So we face two problems – the first of course is that the Demand forecast table holds the forecast in separate records – so we’ll need to convert our matrix of monthly forecasts into a columnar list – or we’ll need to use a customised import function. In the past I’ve used both options, but that’s not the subject of this post. This post’s about the second problem, converting a monthly forecast to a weekly demand pattern.

The standard Dynamics AX solution is the ‘Period key’. So let’s create a Demand forecast for an item. Inventory management > Inquires > Forecast > Demand forecast:


I’ve entered a Date (1st December 2015), and an Item number and a sales quantity. The system’s defaulted the sales unit, the sales price and a warehouse. I suppose that we should note in passing the warehouse might not be needed – we could have setup our Storage dimension group so that ‘Coverage plan by dimension’ isn’t active for the warehouse – but I don’t see that setup being used very often.

So, we already know that a monthly forecast like that isn’t very helpful to production planners who are working on weekly (or 2-weekly) production plan cycles – or buyers who want to create purchase orders every week or two. To solve this problem we’re going to use an Allocation method of ‘Key’ – but first we’ll need to setup a ‘Period key’. (Incidentally the other Allocation option of ‘Period’ is useful for creating recurring test data – where it’ll quickly duplicate your entered forecast on a weekly or monthly basis up to an entered ‘End date’). But back to the Period key. Inventory management > Setup > Forecast > Period allocation categories:


I’ve created a new key; clicked on the Lines button; and added four lines (25% per line), with an offset of 7 days per line. Your allocation doesn’t have to add up to 100% but mostly it will. Back on your demand forecast, select the Allocation method Key and enter your Period key (and click Create lines or Save) and the system will split your entered forecast as specified:


So typically, what I’ve seen done is that the forecast import allows the user to specify a Period key – and often that’s only used for the first few months of the forecast. Once you get out past your short-term planning horizon, having a forecast in monthly buckets is probably acceptable – and not using a Period key on all forecasts will speed up the import of the forecast, and master planning’s run time. But a major problem with that type of import is that it ignores the 4,4,5 weeks to months conversion pattern that gives you a more ‘real-world’ conversion. So really we should build a bit of intelligence into our import (by which I mean another parameter on the calendar we’re using) so that we can use two (or more) period keys to convert the monthly forecast to planning weeks.

Another problem we need to address when we’re importing forecasts is what happens when the forecast date (typically the first of the month) falls on a non-working day. This year (2015) 1st November is a Sunday. Enter a forecast for 1st November 2015 and run master planning and you’ll get:


That first line shows a Requirement date of 30th October, not the 1st November. The system’s reacting to demand on a non-working day by prompting you to supply against the demand on the first working day before the forecast. Unfortunately most planners won’t want that behaviour and your forecast import will need to use a calendar to determine the first working day after the forecast date. Actually, the other forecast I entered on 1st December shows another problem. The 1st December 2015 is a Tuesday. Do your planners want the forecast to be entered on a Monday (or perhaps a Friday?) – more customisation of your import is the only way forward here.

Finally – I’ve shown here a Period key with an even distribution (25%; 25%; 25%; 25%), but you don’t have to use period keys like that. It may be that in your company most orders are received at the end of the month, and period key like (15%; 15%; 20%; 50%) might give you a better approximation of an item’s actual demand pattern. I once saw monthly forecast’s being split into 60% at the beginning of the month and 40% two weeks later. That worked well for that company because they had a 2-week production cycle (most products were made once every 2 weeks) and they could use the 60% forecast to drive a ‘spike orders’ report, which highlighted those items that had sold more than 60% of the monthly forecast in the first 2 weeks.

Of course, you might be able to avoid all of these issues by converting your marketing department into giving the planners a weekly forecast. Good luck with that one.