VMI Data Collection - POS Data

Point of Sale (POS) Data

• represents each register sale at a customers location
• typically received daily via EDI from each trading partner, sometimes downloaded from a trading partner's system
• typical fields are: sender/receiver ID (unique identifier of the trading partner), store/location ID, UPC/item ID, transaction date, quantity.  It is also helpful to collect a unique control/subcontrol number and a received/imported date.

POS Data Considerations

Variation in Data
There is a lot of variation in what each trading partner will send as their store/location ID and UPC/item ID.  Sometimes it will be the UPC, sometimes your internal number, sometime a GTIN.  There is often variation in whether leading zeros are sent, and whether check digits are sent.  To handle this, you need the ability to map external values to internal values for each trading partner.

Missing or Duplicate Data
Because you are dependent on many other IT systems to feed you the POS data, inevitably there will be periodic hiccups where data doesn't come through, or even comes through multiple times.  The sooner you can identify this problem and alert your trading partner the better.  Your back-end EDI system (Gentran, Data Masons, etc) might be useful in this, but watching for data anomalies inside of AX VMI ends up begin more effective.

Data Anomalies
It's important to be flexible in handling POS data that 'doesn't make sense'. Below are three common situations which should be planned for:

• Receiving sales (or returns) for an item that was never shipped to the customer location or that was discontinued years ago.
• Receiving a high quantity of unit sales of the same item from the same store on the same day.  In researching these with the trading partners, we believe these are legitimate large purchases where the cashier scans a single item and keys in a quantity rather than scanning each separate item.  
• If you sell product to a trading partner under different terms - some pay from scan (PFS)/ scan based trade (SBT), some Net 30, we've seen situations where outright sales come through EDI.  Similarly, this issue can arise when you ship an order to a customer based on a special request from one of their customers. 

AX VMI Architecture

Below is the architecture of the VMI module surrounding POS Data collection.

Tables
POSTransactionExternal - SQL table, not an AX table to avoid RecId requirements.  Records are inserted into this table from sources outside of AX. AX batch process moves records from this table table into POSTransactionStaging.
POSTransactionStaging - AX table containing raw POS data.  Users may review import errors, manually adjust data, and import/unimport data as needed. External values are mapped to internal values. An AX batch process automatically maps and imports data unless an error is detected.  This data is purged once there is no longer a need to unimport POS data - usually after a year.
POSTransaction - AX table containing validated POS transactions with all fields in terms of 'internal' values. (i.e. UPC has been translated to ItemId)

Screens
POS Import Journal - this screen allows users to view/edit/delete the data in the staging table, review/fix errors (typically where mapping has not been set up), and post/unpost (a.k.a import/unimport) data.

The status filter is used to highlight records that require attention.

Below is a successfully mapped/posted record.  The error description field contains details about why a record could not be mapped or posted.  Until a record has been posted, users receive reminder emails to address this issue.  In some cases, we use the 'Ignore and Never Post' option and add a note to the error description describing why this record is an exception.

The user can use the following buttons to:

1) manually import POS data from an Excel/text file.

2) manually Post/Unpost selected POS records

POS Data collection is just a small portion of the overall VMI structure, but it can be a problem area if it's not flexible to handle typical POS data anomalies.

Vendor Managed Inventory (VMI) Concepts

I have been deeply involved in VMI (Vendor Managed Inventory) system architecture and development in Microsoft Dynamics AX for the past year.  As this project is winding down I'd like to post a few articles to share the best practices we followed and lessons-learned along the way.

 

Background

This VMI system is responsible for maintaining inventory at 15,000 customer locations with annual sales of 50 million units.  In addition to the high unit volume, one unique requirement for this system is that the business is completely seasonal with product displays being put up and torn down at various times throughout the year.

The VMI system is a Dynamics AX module, integrated with the AR, Inventory Management, Production, Master Planning, and General Ledger modules.

 

Key Concepts

Below is a basic outline of key VMI concepts and some examples related to my current implementation.

Raw Data Collection
A flexible, fault-tolerant, utility is needed to collect data for use in the VMI module.

• Historical Sales/Shipment data import and storage as a baseline for forecasting and year-over-year comparisons.
• Point of Sale (POS) Import (EDI)
• Shipment Import (Sales Order integration)
• Manual POS or Shipment Import
• Map external (customer-system) values to internal (AX) values

Forecasting
Generate a number of expected unit sales for each item at each customer location during the forecast period.

• Determine forecast period (i.e. forecast upcoming year based on prior years)
• Assign representative forecasting curve to each product at each customer location
• determined by factors such as product type, geographical region, volume of available historical sales, etc.
• Determine forecast by comparing current sales trend with historical forecasting curves.
• Dampen unrepresentative sales trends early in the forecasting period.
• Update Master Planning Sales Forecast

Statistics
Statistics provide real-time reporting for various levels of decision makers and allow for review/audit of system-generated forecasts and replenishment shipments.

• customer-location inventory
• weeks of supply
• sell through percentage
• percent stocked
• highlight notable situations such as "zero sales", out of stock risk, etc.

Recommended Replenishment Shipment
Determine which items, in what quantities, should be shipped to each customer location if a shipment was made today.

• A stocking formula takes into account the forecast sales as well as various criteria.  For example:
• Don't ship more than the customer's capacity to store the item. Maintain minimum stock levels.
• Consider planned replenishment frequency, if specified.
• Consider available inventory
• Create larger orders for smaller customer by shipping a higher percentage of the forecast.

Recommended Replenishment Date
Balance the risk of customer-location out of stock (OOS) with costs associated with the replenishment shipment.

• Consider picking/shipping costs
• Consider who is responsible for merchandising at the customer location
• Don't recommend sending a shipment for a high percentage of the forecast until the forecast is sufficiently mature.

Automatic Replenishment Order Creation

• Replenishment orders are automatically created on a daily basis in response to POS data.
• Allow easy override of system recommended replenishment shipments in response to customer requests or unusual situations.

In future posts, I hope to deal with each conceptual area separately in more detail.  Please feel free to contact me if you are implementing a VMI system and would like to compare notes.