The Planning Worksheet in Business Central

Planning in Business Central #5

When running a replenishment plan in Business Central, generally, the planning worksheet is used. The requisition worksheet can also be used, but only to plan items that are purchased or transferred, or for special order or drop-shipment sales orders.

I tend to recommend using the planning worksheet for all replenishments other than special order or drop-shipments.

The planning worksheet, like the name suggests, is a worksheet. This means that when a plan is run, and planning lines are suggested, the planner is expected to review the lines and work on the plan, before executing it.

Planning is not a magic feature! The elements that contribute to calculating a plan have to be maintained. This includes:

  • Dates: if dates are not maintained, the suggestions will make no sense
  • Parameters: changes in demand pattern, seasonality, changes in the supply chain etc… generally have an impact on the planning parameters used. So these need to be maintained

Regenerative Plan Vs. Net Change Plan

In the ribbon three planning actions can be performed:

  • Calculate Regenerative Plan…: this function is used to plan all items, and will consider changes in master data, routing or capacity for example
  • Calculate Net Change Plan…: this functions is used to calculate changes since the last plan was run and only considers items where there has been a change in demand or supply profile
  • Get Action Messages…: this is used in the context of dynamic order tracking, or order pegging, which is a different type of planning, not covered in this post
The Planning Worksheet

Business Central uses the Planning Assignment table to determine which items should be planned when using the Calculate Regenerative Plan or Calculate Net Change Plan functions.

This is done by using flags on the planning assignment records. Note that the names of the flags I’m using do not match the ones used in the table, but they help demonstrate what happens.

If an item is given a reordering policy, then this item will be added to the Planning Assignment table as soon as there is a change in demand/supply profile. The record(s) created will be based on the item, location and variant combination(s). Creating a SKU for a given location and/or variant will immediately create a Planning Assignment record for the item/location/variant combination. These records are marked as ‘planned’ and ‘active’.

If an item or SKU is set back to manual planning, i.e. the reordering policy is removed, then the ‘planned’ and ‘active’ flag of the assignment records are turned off.

If specific events occur that change the demand/supply profile of an item/location/variant combination that exists in the Planning Assignment table and which is flagged as ‘planned’, then these items are flagged as ‘active’ in that table if they weren’t already. These events are:

  • A new sales order, forecast, component, purchase order, production order, assembly order, or transfer order with a quantity
  • Change of item, quantity, location, variant, or date on a sales order line, forecast, component line, purchase order line, production order line, assembly order, or transfer order line
  • Cancellation of a sales order, forecast, component, purchase order, production order, assembly order, or transfer order
  • Consumption of items other than planned
  • Output of items other than planned
  • Unplanned changes in inventory
  • Deletion of a planning worksheet line
  • Changes in the following planning parameters:
    • Replenishment System
    • Reordering Policy
    • Lead Time
    • Safety Lead Time
    • Safety Stock Quantity
    • Reorder Point
    • Overflow Level

When running the Calculate Regenerative Plan, all items in the Planning Assignment table that are flagged as ‘planned’ are considered.

When running the Calculate Net Change Plan, all items that are flagged as ‘planned’ AND ‘active’ in the Planning Assignment table are considered.

The ‘Calculate Regenerative Plan’ and ‘Calculate Net Change Plan’ actions will often give you the same results. However, calculating the Net Change Plan will be quicker, as only items that are active will be considered, i.e any item where the demand/supply profile has changed.

But importantly, the ‘Calculate Net Change Plan’ action will NOT consider any item where there have only been changes in important master data – and some of these changes can impact planning. These changes include (the list is probably not exhaustive):

  • Change of status to ‘Certified’ in the production BOM header
  • Deleted BOM line (child item)
  • Change of status to ‘Certified’ in the routing header
  • Changes in capacity on a Work Centre or Machine Centre, including shop floor calendars
  • Changes in the following item card fields:
    • Production BOM No. (and all children of old BOM reference)
    • Routing No.
  • Changes to the Assembly BOM of an item
  • All planning parameters not listed in the previous list

To consider these changes, you have to run the ‘Calculate Regenerative Plan’ function, though some changes such as change of BOM or route or will not update existing supply orders.

So both functions have got their place, and to summarise:

  • Use the Regenerative Plan where there have been changes in master data
  • Use the Net Change Plan where there have only been changes in demand/supply profile

Running the Plan

When running the plan, i.e. clicking the Regenerative or Net Change Plan buttons, you will be presented with a request page where you can input parameters.

What you select as parameters will drive the results.

MPS Vs. MRP

  • MPS = master planning schedule. If you select this option, the planning system checks that the item is on a demand forecast or on a sales order. If not, the item is not included in the plan
  • MRP = material requirements planning. If you select this option, items which are not MPS items are included in the plan

This can sometimes lead to confusing scenarios. For example, if an item is replenished using the fixed reorder quantity policy, and the item is not on a sales order, then you need to run MRP. If the item is currently on a sales order line, you will need to run MPS.

You can run both MPS and MRP together, and this can be enabled on the Manufacturing Setup page. This is generally used in the context of businesses that resell inventory.

Alternatively, it is common to run MPS first, then MRP.

Starting Date and Ending Date

The starting date and ending date are used to set the planning horizon, that is the date boundaries to use where demand and supply events will be considered.

  • Starting Date: this is the starting date of the plan. The starting date is important as all demand and supply events with a due date before this date, will be considered to have taken place on the starting date. Therefore any demand or supply event with a date up to, and including, the day before the starting date will be included in the ‘opening’ inventory calculation. This is why it is crucial that dates are maintained, and orders which are not needed are removed.
  • Ending Date: this is the ending date of the plan. This means that supply and demand events up to this date will be used in the calculation of the projected inventory timeline. Any supply or demand event beyond this date is not considered or included in the calculations.

What dates should you use as starting date and ending date? Well it depends what you are doing.

Firstly, it is worth considering the following points:

  • Is production planning and purchase planning done by different teams?
  • How far ahead is your short-term production plan?
  • How far ahead is your long-term production plan?
  • Does demand for your products change at short notice, or is it predictable and stable?
  • Is it possible and necessary to change your short-term plan at short-notice to react to changes in demand?
  • Are there any purchased items with a significant lead time?
  • How often do you want to place purchase orders?
  • Is it possible to change purchase orders once placed with your suppliers?
  • Is the supply of your components relatively secure and predictable, or are there frequent changes in the condition of the supply chain?

For businesses that manufacture, it is common to have a short term plan for example 2 weeks, and a long term plan, for example 6 months. So the process could be:

  1. Create a long term production plan plan on a rolling basis, every two weeks, that excludes the short term plan (so for example, with a starting date two weeks from now, and an ending date 6 months from now)
  2. Create a purchase plan that supports the production plan, considering the lead time of the components, planning buckets, propensity to carry stock etc… For components with a short lead time, you might run a plan weekly with a starting date of today, and an ending date two weeks from now. For components with a long lead time, your plan will be structured differently. For example, if an item has a lead time of 6 months, and you want to place orders monthly, you might run the plan monthly, with a starting date 6 months from now, and an ending date a month later, with a time bucket of a month.
  3. Tweak the short-term plan manually, or run a net change plan only for the items in your short-term plan
  4. Run the short term purchase plan again, to tweak the supply orders where needed, considering lead time, allowed changes etc…

For businesses that resell inventory, generally, the lead time and market requirements will be the main driver for the length of the purchase plan.

Using a Forecast

It is common to use demand forecasts when using the planning functionality.

The request page allows to select a forecast, and to exclude forecast entries before a specific date. When you run the plan, forecast entries before the Starting Date are excluded. So the ‘Exclude Forecast Before’ field is used if you want to exclude future forecast entries from your plan.

Forecasts are generally used in combination with the lot-for-lot reordering policy. Although you can forecast for items with a reorder point policy, this is generally not recommended, because the anticipated demand is already expressed in the reorder point.

The reorder point is the “anticipated demand during the lead time”, and therefore using a forecast could lead to unwanted results. Generally, instead the reorder point will be set to a number that includes expected demand during the lead time + demand during the time bucket + an additional percentage to make up for unexpected demand.

Forecasts are used to ensure there is demand in the system. Business Central will consume the forecast against actual orders. For example, if there is a forecast sales quantity of 100 for April 2023, and there are sales orders with a total quantity of 80 in April, then only 20 from the forecast will be included in calculations, bringing the demand to 100.

Likewise, blanket sales orders can be used to create collaborative forecasts with your customers. In that case, sales orders connected to a blanket sales order will consume the blanket order forecast.

Other Parameters

The ‘Stop and Show First Error’ toggle works in the following way, as indicated on Microsoft website. Take the website information with a pinch of salt, as some information on the page appears to be inaccurate, according to my testing.

The ‘Respect Parameters for Exception Warnings’ toggle allows to create orders that respect planning parameters, in the case of Exception warnings.

Exception Warnings are created when projected inventory falls below safety stock. When this happens, Business Central will suggest a supply order to cover exactly the quantity required to stop the safety stock breach. If you tick this box, then the quantity suggested will take planning parameters into consideration, for example the reorder quantity, minimum order quantity etc…

This can lead to excess inventory however, or going above the overflow.

Filters

It is common (and recommended!) to use filters when running the plan. The most commonly used filter is the location filter, but item filters can be useful too, such as the reordering policy, replenishment system, lead time calculation and so on.

Use case of the filters could be:

  • Production and purchase planning are done separately, and by different teams. The replenishment system can be used to filter items on that basis
  • Production take place in a dedicated location
  • Different operating sites have their own location in BC, and plan their inventory independently
  • Your items are categorised, and you only want to replenish one category at a time
  • You want to plan items with a long lead time separately

Warning! There is a significant pitfall: the item field filters are based on the item records, not the stockkeeping unit records! If you use stockkeeping units, the filters will NOT work as expected. For example if the item record is setup with a replenishment system of ‘Purchase’, and you have two SKUs, one setup to replenish with an assembly order and one setup to replenish with a transfer order, then filtering the replenishment system to Assembly or Transfer will generate no results. Conversely, filtering to Purchase will generate supply orders for assemblies and transfers!

Running the Plan

When you run the plan, Business Central will:

  • Determine which items should be planned, and for which location/variant combinations
  • Check if there is a requirement to create a supply order within the planning horizon
  • Check if there are existing supply orders
  • Calculate when to order
  • Calculate how much to order

The outcome of the plan could be:

  • Create a new supply order
  • Change the date of an existing supply order (i.e. reschedule)
  • Change the quantity of an existing supply order
  • Change the date and the quantity of an existing supply order
  • Cancel an existing supply order

Each outcome will generate a planning worksheet line with a specific action message.

The planning worksheet lines could have some warnings: it is very likely that some lines will have warnings.

There are three possible warnings:

  • Emergency: generally, this is triggered by backdated events, which lead to a projected inventory which is negative on the planning starting date. For example, today, 16 Apr 2023, I have 0 inventory for an item, and there is an old sales order with a shipment date of 01 Mar 2023 for 15 of that item. In that case, if I run the plan starting on 16 Apr 2023, the projected inventory on the starting date will be -15. This will create an emergency supply order, with an emergency warning attached to it. Emergency warnings often occur where there is poor data management for dates. Again, if you want to use the planning functionality effectively, you must maintain dates!
    • To remedy projected negative inventory, Business Central will suggest a backwards supply order for the quantity needed to bring inventory back to 0, on the day before the starting date. This will generally lead to a planning line with a starting date in the past, which could be before the work date! This will create a secondary warning of the type ‘Attention’.

  • Exception: exception warnings are triggered if inventory is projected to fall below safety stock (safety stock can be 0!).
    • To remedy this, Business Central will suggest a backwards supply order for the quantity needed to bring inventory back to safety stock on the day where the safety stock breach is expected. You can force Business Central to respect planning parameters so that the quantity is calculated based on the planning parameters, rather than the expected fall below safety stock.

  • Attention: attention warnings are triggered if:
    • The planning line starting date is earlier than the work date
    • The planning line suggests to change a released purchase or production order

You can view the details of the warning by clicking the warning link on the planning line.

You can also view the details by opening the fact box:

If a planning line has a warning, the planner is expected to investigate the line. This is why the line is not ticked to ‘Accept Action Message’. After investigation, the line can be ticked, deleted, modified etc…

If a line doesn’t carry a warning, then the action message is ticked to be accepted by default.

On the planning line, a lot of information can be displayed. A lot of the fields must be personalised in however.

FieldNotes
Planning LevelIn the case of multi-level production BOMs, this show the BOM level
Low Level CodeIndicates the hierarchy of the item. The higher the low-level code, the lower in the hierarchy. In my example, the low-level code is 2, because the item is used in an assembly BOM, and the ‘parent’ itself is a component in another assembly BOM. So the top parent will have a low-level code of 0, the secondary parent 1, and so on.
Replenishment SystemThe type of supply order suggested
No.The item no. being replenished
Original Due DateIn the case of a line being rescheduled, the original due date
Order DateThe date on which the order must be placed, or start, based on lead time
Due DateThe date on which the supply is due for the line
Location CodeThe location at which the line is planned
Transfer-from CodeIn the case of replenishment via transfer, the location code from which the line will be replenished

FieldNotes
Original QuantityIn the case of an action message suggesting a change in quantity, the original quantity
MPS OrderSpecifies if the line was triggered by MPS
QuantityThe quantity of the supply order suggestion
Reserved QuantityThe quantity reserved against this planning line. Generally, lines will show reservations for order to order links, i.e. in the case of the Order reordering policy
Planning FlexibilityThe planning flexibility that will be transferred to the supply order. It can be changed before creating the supply order
Ref. Order TypeThe type of supply order that will be created
Ref. Order No.In the case of a change or cancellation of an existing supply order, the supply order No.
Ref. Order StatusIn the case of a change or cancellation of an existing supply order, the supply order status
Vendor No.In the case of a purchase line, the default vendor from which the supply will be purchased. You can select another vendor in the drop-down if a catalogue of vendors has been created against the item record. You can create a catalogue entry on the fly by navigating from this cell.

The planner will generally review the planning lines, and investigate warnings. This can lead to correcting master data, and deleting or altering lines.

You can edit most fields on the line. Remember that this is just a supply proposal. You can override what Business Central suggests before creating the supply order.

When ready, supply orders are created by running the Carry Out Action Message… action.

To be executed, a planning line must be:

  • Ticked to accept the action message
  • Visible, i.e. not hidden. If you hide a planning by using filters, the line will not be executed

When carrying out the action messages, Business Central will allow you control which type of orders should be created. In addition, some other options are available in the drop down. The most notable is the choice of a production order status of either Planned, or Firm Planned.

You can choose to ‘Combine Transfer Orders’. If ticked, then all supplies being transferred between the same locations will be consolidated into a single transfer order. Generally, this is ticked.

Once the action messages have been carried out, then the planning lines will be deleted.

Tools to Help Investigation

When reviewing the planning lines, some tools are available to help investigating why a line was created.

Firstly, so that you are not overwhelmed by the results, use filters! They can be used either before running the plan, or after, to focus on specific planning lines.

The fact box can also give useful information at a glance. If you expand the fact box, you will get access to:

  • Planning parameters for the line: be careful, these come from the item record, NOT the SKU record!
  • Untracked quantity information, and warnings

The Order Tracking function under the Line group of the ribbon will show you the actual demand that was included in the calculation, i.e. sales order lines.

You can then view forecast demand in the untracked quantity, in the fact box, or by clicking through from the order tracking page.

In the ribbon, under the Item Availability by tab, you will find several availability reports.

One that is often used is the Item Availability by Event.

This is very useful, as it gives a breakdown of all the supply and demand events, including the planning lines.

The columns can be a bit tricky to get used to, but here what they show you.

FieldNotes
TypeThis will tell you if the line comes from actual demand, a demand forecast, an actual supply, or a planning line
Remaining Quantity (Base)The quantity for the event on the line. Negative quantities show demands, whilst positive quantities show supplies.
Gross RequirementThe gross actual demand, i.e. coming from orders
ForecastThe quantity coming from the forecast
Remaining ForecastThe quantity remaining on the forecast after actual demand has been deducted
Projected Available BalanceThis is the projected available inventory excluding forecast, and planning lines
Forecast Projected InventoryThis is the projected inventory, considering demand forecast and blanked sales orders
Action Message Qty.The quantity on the planning line
Suggested Projected InventoryThe projected inventory, including demand forecast, blanket sales orders, and planning lines

The report shows a series of events, starting from the earliest one at the top, to the latest one at the bottom.

The events are grouped according to the settings on the report: day, week etc…

The group shows at the top in bold, and the events that make up the group are indented directly beneath the group.

Some of the totals for the events are summed up to show the values against the group. These include gross quantities, and action message quantities.

The projected balance fields are a bit tricker to read. They aren’t a sum, but a running total.

For example, the projected available balance on 17 April, against the purchase plan line, shows a balance of 5, because the opening inventory is 5. The event is a planning line, so it has not effect on the projected available balance, so it is still 5. Then the next line is a sales order for 25. So the projected available balance is 5 – 25 = -20. So overall for the group, the projected available balance is -20.

The other “balance” columns work in a similar fashion. The difference is which lines get included in the calculation.

If all else fails, and you struggle to visualise why Business Central is suggesting a line, do a graphical representation in Excel! Create a table with four columns: Date, Supply Qty, Demand Qty, Projected Balance; then plot these values in a graph. To start with, only indicate starting inventory and demand events, be it actual demand or forecast. Then check when inventory reaches critical points, such as safety stock and reorder point.

Leave a Reply