Master planning for retail mass merchandisers – Part 2

By - April 18, 2018

In part 1 of this article, we discussed configuration, setup and some basic performance considerations. In this portion, we will discuss how a buyer can manage the data when there may be millions of item coverage records (one for every item/warehouse combination) to maintain. For clients with hundreds of warehouses and 100,000’s of items, the setup and management item coverages needs to be made scalable. As a result, RSM has developed some tools to make this a more user friendly process.

Vendor firming minimums

Most buyers are responsible for thousands of items at many different locations. There is also a fixed cost associated with each order. As a result, most buyers only want a purchase order to be created if the purchase order is above a certain threshold.

Vendor firming minimums allow purchase orders to be automatically firmed only when the order reaches a certain size. The firming minimum is defined on the vendor. The vendor can have the following options for firming minimums:

  • Dollar
  • Weight
  • Qty
  • Cases
  • Pallets

The vendor can also have a maximum. When the maximum is exceeded, quantity will automatically be reduced until the purchase order is within tolerance. This is especially useful for vendors where each truck load needs to be it’s own purchase order, and weight is the limiting factor. If the quantity omitted from the PO is sufficient to meet the vendor’s minimum for a PO, another PO will be created. Items are reduced in order of the item’s last receipt date.

To enable this for a vendor, all that needs to be done is set “Firm based on vendor minimums” to “Yes” and define the firming rule type and minimum. Afterwards when master planning completes, planned purchases will be firmed based on the vendor minimum.

Order type override

Items normally ordered through direct shipments will sometimes be ordered in bulk into a distribution center in order to receive more favorable terms or prices. When this happens, the item coverage will need to be updated for each item/warehouse combination impacted so that it is set to “Transfer” instead of “Purchase”, and switched back to “Purchase” when the distribution center runs out of inventory. This creates a lot of extra work for buyers.

To ease this burden, an on-hand warehouse was added to the item coverage record. The system will look to see if the inventory is available in the on-hand warehouse. If available, the inventory will be transferred to the store instead of purchased. When the item is no longer in stock at the distribution center inventory will be purchased directly from the vendor.

Only planned purchases with an on-hand warehouse and check for on-hand inventory set to “Yes” will be converted to planned transfers. A process outside of master planning toggles the Check for on-hand inventory to “Yes” or “No” based on inventory availability in the On-hand warehouse. This improves the performance of master planning operations by preventing it from evaluating inventory levels for thousands of items. If there is no On-hand warehouse populated, then an item will always be direct ship. This is useful for items that may be direct ship for some stores, but are always transferred from a DC for other stores.

Item coverage template

Item coverage has many fields, and some of them vary from by store or warehouse, making setting up a new item coverage very difficult for an end user. There are different coverage groups for different locations and scenarios. Item coverage records will have about 6 fields that will vary for each item/store or DC combination. Four of the fields can be assumed based on how the item is ordered. Since we know how the item is going to be replenished, we can use Item coverage templates to define these values for automatically by selecting a template. This significantly reduces the amount of work a buyer has to do to setup an item for automatic replenishment.

The following fields are available on the template:

  • Warehouse (store)
  • Planned order type (Purchase order or transfer from a DC)
  • Replenishment warehouse (if the item is transferred from a DC)
  • Coverage group (used to determine when a transfer or purchase is placed)
  • On hand warehouse (where to check if the item is available to transfer from if the item is normally purchased)

The template can be applied as an items auto replenishment settings are defined with the wizard, or a new template can be applied any time retroactively.

Simply select the Item coverage template button, and select the item coverage template you would like to apply. This will update all of the coverages to use the settings defined in the template.

The template can also be applied when selecting items in the item coverage wizard.

Item coverage worksheet

Sometimes an item’s replenishment settings need to be changed in bulk for a specific sub set of site/warehouses, such as when an item is changing distributors, seasonality, or some locations are transitioning from indirect procurement to direct procurement.

The item coverage worksheet allows the user to update any of the following fields in bulk for any item/warehouses specified.

The user will define the properties to update and the warehouse/stores to update.

The user can then specify the products to update and perform update in bulk on the item/warehouse selected. Below is what it might be like if a user is changing the distributor for a product range for three stores.

Minimum Upload

Many buyers use reports based on sales history to determine minimums for an item at given store. Since this is usually a report that outputs to excel and is calculated based on the results of the report, a tool was built to upload an excel file to set minimums in bulk. The template is buyer friendly and easy to format based on a sales report.

Items and their variants are in columns 1 – 3. Stores/DCs and their minimums are in columns D and beyond. The import is dynamic, so when a new store is opened no code change is required to use the store in the template.

The amount of data that buying staff must maintain for an organization to purchase efficiently poses a challenge for an efficient automated supply chain. Fortunately, there are numerous tools built by RSM to help optimize the process, such as firming minimums, item coverage templates, item coverage worksheet, and minimum upload templates.

Want to learn more about retail planning for mass merchandisers? Review the series in part 1.

<- Part 1

To learn more about how you can take advantage of this and other Dynamics GP features, visit RSM’s Microsoft Dynamics GP resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter. For more information on Microsoft Dynamics 365, contact us.

By: Curtis McDonald

Receive Posts by Email

Subscribe and receive notifications of new posts by email.