Data verification and validation are essential to the success of the implementation of software that performs statistical analysis of data, like Smart IP&O.  This article describes the issue and serves as a practical guide to doing the job right, especially for the user of the new application.

The less experience your organization has in validating historical transactions or item master attributes, the more likely it is there were problems or mistakes with data entry into the ERP that have so far gone unnoticed.  The garbage in, garbage out rule means you need to prioritize this step of the software onboarding process or risk delay and possible failure to generate ROI.

Ultimately the best person to confirm data in your ERP is entered correctly is the person who knows the business and can assert, for example, “this part doesn’t belong to that product group.”  That’s usually the same person who will open and use Smart. Though a database administrator or IT support can also play a key role by being able to say, “This part was assigned to that product group last December by Jane Smith.” Ensuring data is correct may not be a regular part of your day job but can be broken down into manageable small tasks that a good project manager will allocate the time and resources to complete.

The demand planning software vendor receiving the data also has a role.  They will confirm that the raw data was ingested without issue. The vendor can also identify abnormalities in the raw data files that point to the need for validation.  But relying on the software vendor to reassure you the data looks fine is not enough.  You don’t want to discover, after go-live, that you can’t trust the output because some of the data “doesn’t make sense.”

Each step in the data flow needs verification and validation.  Verification means the data at one step is still the same after flowing to the next step.  Validation means the data is correct and usable for analysis. 

The most common data flow looks like this:

Less commonly, the first step between ERP master data and the interfacing files can sometimes be bypassed, where files are not used as an interface.  Instead, an API built by IT or the inventory optimization software vendor is responsible for data to be written directly from the ERP to the mirrored database in the cloud.  The vendor would work with IT to confirm the API is working as expected.  But the first validation step, even in that case, can still be performed.  After ingesting the data, the vendor can make the mirrored data available in files for the DBA/IT verification and business validation.

The confirmation that the mirrored data in the cloud completes the flow into the application is the responsibility of the vendor of software as a service.  SaaS vendors continually test that the software works correctly between the front-end application their subscribers see and the back-end data in the cloud database. If the subscribers still think the data doesn’t make sense in the application even after validating the interfacing files before going live, that is an issue to raise with the vendor’s customer support.

However the interfacing files are obtained, the largest part of verification and validation falls to the project manager and their team.  They must resource a test of the interfacing files to confirm:

1. They match the data in the ERP. And that all and only the ERP data that was necessary to extract for use in the application was extracted.
2. Nothing “jumps out” to the business as incorrect for each of the types of information in the data
3. They are formatted as expected.

DBA/IT Verification Tasks

1. Test the extract:

IT’s verification step can be done with various tools, comparing files, or importing files back to the database as temporary tables and joining them with the original data to confirm a match.  IT can depend on a query to pull the requested data into a file but that file can fail to match. The existence of delimiters or line returns within the data values can cause a file to be different than its original database table.  It is because the file relies heavily on delimiters and line returns to identify fields and records, while the table doesn’t rely on those characters to define its structure.

2. No bad characters:

Free form data entry fields in the ERP, such as product descriptions, can sometimes themselves contain line returns, tabs, commas, and/or double quotes that can affect the structure of the output file.  Line returns should not be allowed in values that will be extracted to a file.  Characters equal to the delimiter should be stripped during extract or else a different delimiter used.

Tip: if commas are the file delimiter, numbers greater than 999 can’t be extracted with a comma. Use “1000” rather than “1,000”.

3. Confirm the filters:

The other way that query extracts can return unexpected results is if conditions on the query are entered incorrectly.  The simplest way to avoid mistaken “where clauses” is to not use them.  Extract all data and allow the vendor to filter out some records according to rules supplied by the business.  If this will produce extract files so large that too much computing time is spent on the data exchange, the DBA/IT team should meet with the business to confirm exactly what filters on the data can be applied to avoid exchanging records that are meaningless to the application.

Tip: Bear in mind that Active/Inactive or item lifecycle information should not be used to filter out records.  This information should be sent to the application so it knows when an item becomes inactive.

4. Be consistent:

The extract process must produce files of consistent format every time it is executed.  File names, field names, and position, delimiter, and Excel sheet name if Excel is used, numeric formats and date formats, and the use of quotes around values should never differ from one execution of the extract one day to the next. A hands-off report or stored procedure should be prepared and used for every execution of the extract.

Business Validation Background

Below is a break down each of validation step into considerations, specifically in the case where the vendor has provided a template format for the interfacing files where each type of information is provided in its own file.  Files sent from your ERP to Smart are formatted for easy export from the ERP.  That sort of format makes the comparison back to the ERP a relatively simple job for IT, but it can be harder for the business to interpret.  Best practice is to manipulate the ERP data, either by using pivot tables or similar in a spreadsheet.  IT may assist by providing re-formatted data files for review by the business.

To delve into the interfacing files, you’ll need to understand them.  The vendor will supply a precise template, but generally interfacing files consist into three types: catalog data, item attributes, and transactional data.

• Catalog data contains identifiers and their attributes. Identifiers are typically for products, locations (which could be plants or warehouses), your customers, and your suppliers.
• Item attributes contain information about products at locations that are needed for analysis on the product and location combination. Such as:
  • Current replenishment policy in the form of a Min and Max, Reorder Point, or Review Period and Order Up To value, or Safety Stock
  • Primary supplier assignment and nominal lead time and cost per unit from that supplier
  • Order quantity requirements such as minimum order quantity, manufacturing lot size, or order multiples
  • Active/Inactive status of the product/location combination or flags that identify its state in its lifecycle, such as pre-obsolete
  • Attributes for grouping or filtering, such as assigned buyer/planner or product category
  • Current inventory information like on hand, on order, and in transit quantities.
• Transactional data contains references to identifiers along with dates and quantities. Such as quantity sold in a sales order of a product, at a location, for a customer, on a date.  Or quantity placed on purchase order of a product, into a location, from a supplier, on a date. Or quantity used in a work order of a component product at a location on a date.

Validating Catalog Data

Considering catalog data first, you may have catalog files similar to these examples:

1: Check for a reasonable count of catalog records

For each file of catalog data, open it in a spreadsheet tool like Google Sheets or MS Excel. Answer these questions:

1. Is the record count in the ballpark? If you have about 50K products, there should not be only 10K rows in its file.
2. If it’s a short file, maybe the Location file, you can confirm exactly that all expected Iidentifiers are in it.
3. Filter by each attribute value and confirm again the count of records with that attribute value makes sense.

2: Check the correctness of values in each attribute field

Someone who knows what the products are and what the groups mean needs to take the time to confirm it is actually right, for all the attributes of all the catalog data.

So, if your Product file contains the attributes as in the example above, you would filter for Status of Active, and check that all resulting products are actually active.  Then filter for Status of Inactive and check that all resulting products are actually inactive.  Then filter for the first Group value and confirm all resulting products are in that group.  Repeat for Group2 and Group3, etc.  Then repeat for every attribute in every file.

It can help to do this validation with a comparison to an already existing and trusted report.  If you have another spreadsheet that shows products by Group for any reason, you can compare the interfacing files to it.  You may need to familiarize yourself with the VLOOKUP function that helps with spreadsheet comparison.

Validating Item Attribute Data

1: Check for a reasonable count of item records

The item attribute data confirmation is similar to the catalog data.  Confirm the product/location combination count makes sense in total and for each of the unique item attributes, one by one. This is an example item data file:

2: Find and explain weird numbers in item file

There tends to be many numerical values in the item attributes, so “weird” numbers merit review.  To validate data for a numerical attribute in any file, search for where the number is:

• Missing entirely
• Equal to zero
• Less than zero
• More than most others, or less than most others (sort by that column)
• Not a number at all, when it should be

A special consideration of files that are not catalog files is they may not show the descriptions of the products and locations, just their identifiers, which can be meaningless to you.  You can insert columns to hold the product and location descriptors that you are used to seeing and fill them into the spreadsheet to assist in your work.  The VLOOKUP function works for this as well.  Whether or not you have another report to compare the Items file to, you have the catalog files for Products and Location with show both the identifier and the description for each row.

3: Spot check

If you are frustrated to find that there are too many attribute values to manually check in a reasonable amount of time, spot checking is a solution. It can be done in a manner likely to pick up on any problems.  For each attribute, get a list of the unique values in each column.  You can copy a column into a new sheet, then use the Remove Duplicates function to see the list of possible values.   With it:

1. Confirm that no attribute values are present that shouldn’t be.
2. It can be harder to remember which attribute values are missing that should be there, so it can help to look at another source to remind you. For example, if Group1 through Group12 are present, you might check another source to remember if these are all the Groups possible.  Even if it is not required for the interfacing files for the application, it may be easy for IT to extract a list of all the possible Groups that are in your ERP which you can use for the validation exercise.  If you find extra or missing values that you don’t expect, bring an example of each to IT to investigate.
3. Sort alphabetically and scan down to see if any two values are similar but slightly different, maybe only in punctuation, which could mean one record had the attribute data entered incorrectly.

For each type of item, maybe one from each product group and/or location, check that all its attributes in every file are correct or at least pass a sanity check.  The more you can spot check from a broad range of items, the less likely you will have issues post go live.

Validating Transactional Data

Transactional files may all have a format similar to this:

1: Find and explain weird numbers in each transactional file

These should be checked for “weird” numbers in the Quantity field.  Then you can proceed to:

1. Filter for dates outside the range you expect or missing expected dates entirely.
2. Find where Transaction identifiers and line numbers are missing. They shouldn’t be.
3. If there is more than one record for a given Transaction ID and Transaction Line Number combination, is that a mistake? Put another way, should duplicate records have their quantities summed together or is that double counting?

2: Sanity check summed quantities

Do a sanity check by filtering to a particular product you’re familiar with, and filter to a relatable date range such as last month or last year, and sum the quantities.  Is that total amount what you expected for that product in that time frame?  If you have information on total usage out of a location, you can slice the data that way to sum the quantities and compare to what you expect.  Pivot tables come in handy for verification of transactional data.  With them, you can view the data like:

The products’ yearly total may be simple to sanity check if you know the products well.  Or you can VLOOKUP to add attributes, such as product group, and pivot on that to see a higher level that is more familiar:

3: Sanity check count of records

It may help to display a count of transactions rather than a sum of the quantities, especially for purchase order data.  Such as:

And/or the same summarization at a higher level, like:

4: Spot checking

Spot checking the correctness of a single transaction, for each type of item and each type of transaction, completes due diligence.  Pay special attention to what date is tied to the transaction, and whether it is right for the analysis.  Dates may be a creation date, like the date a customer placed an order with you, or a promise date, like the date you expected to deliver on the customer’s order at the time of creating it, or a fulfilment date, when you actually delivered on the order.  Sometimes a promise date gets modified days after creating the order if it can’t be met.  Make sure the date in use reflects actual demand by the customer for the product most closely.

What to do about bad data 

If the mis-entries are few or one-off, you can edit the ERP records by hand as they are found, cleaning up your catalog attributes, even after go-live with the application.  But if large swathes of attributes or transaction quantities are off, this can spur an internal project to re-enter data correctly and possibly to change or start to document the process that needs to be followed when new records are entered into your ERP.

Care must be taken to avoid too long a delay in implementation of the SaaS application while waiting on clean attributes.  Break the work into chunks and use the application to analyze the clean data first so the data cleansing project occurs in parallel with getting value out of the new application.

Utilities in the electrical, natural gas, urban water and wastewater, and telecommunications fields are all asset intensive. Generation, production, processing, transmission, and distribution of electricity, natural gas, oil, and water, are all reliant on physical infrastructure that must be properly maintained, updated, and upgraded over time. Maximizing asset uptime and the reliability of physical infrastructure demands effective inventory management, spare parts forecasting, and supplier management.

A utility that executes these processes effectively will outperform its peers, provide better returns for its investors and higher service levels for its customers, while reducing its environmental impact. Impeding these efforts are out-of-date IT systems, evolving security threats, frequent supply chain disruptions, and extreme demand variability.  However, the convergence of these challenges with mature cloud technology and recent advancements in data analytics, probabilistic forecasting, and technologies for data management, present utilities a generational opportunity to digitally transform their enterprise.

Here are seven digital transformations that require relatively small upfront investments but will generate seven-figure returns.

1. Inventory Management is the first step in MRO inventory optimization. It involves analyzing current inventory levels and usage patterns to identify opportunities for improvement. This should include looking for overstocked, understocked, or obsolete items.  New probabilistic forecasting technology will help by simulating future parts usage and predicting how current stocking policies will perform.  Pats planners can use the simulation results to proactively identify where policies should be modified.

2. Accurate forecasting and demand planning are very important in optimizing MRO service parts inventories. An accurate demand forecast is a critical supply chain driver. By understanding demand patterns that result from capital projects and planned and unplanned maintenance, parts planners can more accurately anticipate future inventory needs, budget properly, and better communicate anticipated demand to suppliers. Parts forecasting software can be used to automatically house an accurate set of historical usage that details planned vs. unplanned parts demand.

3. Managing suppliers and lead times are important components of MRO inventory optimization. It involves selecting the best vendors for the job, having backup suppliers that can deliver quickly if the preferred supplier fails, and negotiating favorable terms.  Identifying the right lead time to base stocking policies on is another important component. Probabilistic simulations available in parts planning software can be used to forecast the probability for each possible lead time that will be faced. This will result in a more accurate recommendation of what to stock compared to using a supplier quoted or average lead time.

4. SKU rationalization and master data management removes ineffective or out-of-date SKUs from the product catalog and ERP database. It also identifies different part numbers that have been used for the same SKU. The operating cost and profitability of each product are assessed during this procedure, resulting in a common list of active SKUs.  Master data management software can assess product catalogs and information stored in disparate data bases to identify SKU rationalizations ensuring that inventory policies are based on the common part number.

5.  Inventory control systems are key to synchronizing inventory optimization.    They provide a cost-efficient way for utilities to track, monitor, and manage their inventory. They helps ensure that the utility has the right supplies and materials when and where needed while minimizing inventory costs.

6. Continuous improvement is essential for optimizing MRO inventories. It involves regularly monitoring and adjusting inventory levels and stocking policies to ensure the most efficient use of resources. When operating conditions change, the utility must detect the change and adjust its operations accordingly. This means planning cycles must operate at a tempo high enough to stay up with changing conditions. Leveraging probabilistic forecasting to recalibrate service parts stocking policies each planning cycle ensures that stocking policies (such as min/max levels) are always up-to-date and reflect the latest parts usage and supplier lead times.

7. Planning for intermittent demand with modern Spare Parts Planning Software.  The result is a highly accurate estimate of safety stocks, reorder points, and order quantities, leading to higher service levels and lower inventory costs.   Smart Software’s patented probabilistic spare parts forecasting software simulates the probability for each possible demand, accurately determining how much to stock to achieve a utility’s targeted service levels.  Leveraging software to accurately simulate the inflow and outflow of repairable spare parts will better predict downtime, service levels, and inventory costs associated with any chosen pool size for repairable spares.

Smart IP&O offers automated statistical forecasting that selects the right forecast method that best forecasts the data.  It does this for each time-series in the data set.  This blog will help a laymen understand how the forecast methods are chosen automatically.

Smart makes many methods available including single and double exponential smoothing, linear and simple moving average, and Winters models.  Each model is designed to capture a different sort of pattern.  The criteria to automatically choose one statistical method out of a set of choices is based on which method came closest to correctly predicting held-out history.

Earlier demand history is passed to each method and the result is compared to actuals to find the one that came closest overall.  That “winning” automatically chosen method is then fed all the history for that item to produce the forecast.

The overall nature of the demand pattern for the item is captured by holding out different portions of the history so that an occasional outlier does not unduly influence the choice of method.  You can visualize it using the below diagram where each row represents a 3-period forecast in held out history, based on different amounts of the red earlier history.  The variances of each pass are averaged together to determine the method’s overall ranking against all other methods.

For most time series, this process can accurately capture trends, seasonality, and average volume accurately. But sometimes a chosen method comes mathematically closest to predicting the held-out history but doesn’t project it forward in a way that makes sense.

Users can correct this by using the system’s exception reports and filtering features to identify items that merit review.  They can then configure the automatic forecast methods that they wish to be considered for that item.

Managing spare parts inventories can feel impossible. You don’t know what will break and when. Feedback from mechanical departments and maintenance teams is often inaccurate. Planned maintenance schedules are often shifted around, making them anything but “planned.”   Usage (i.e., demand) patterns are most often extremely intermittent, i.e., demand jumps randomly between zero and something else, often a surprisingly big number. Intermittency, combined with the lack of significant trend or seasonal patterns, render traditional time-series forecasting methods inaccurate. The large number of part-by-locations combinations makes it impossible to manually create or even review forecasts for individual parts.   Given all these challenges, we thought it would be helpful to outline a number of do’s (and their associated don’ts).

1. Do use probabilistic methods to compute a reorder points and Min/Max levels
   Basing stocking decisions on average daily usage isn’t the right answer. Nor is reliance on traditional forecasting methods like exponential smoothing models. Neither approach works when demand is intermittent because they don’t take proper account of demand volatility. Probabilistic methods that simulate thousands of possible demand scenarios work best. They provide a realistic estimate of the demand distribution and can handle all the zeros and random non-zeros. This will ensure the inventory level is right-sized to hit whatever service level target you choose.
    
2. Do use service levels instead of rule-of-thumb methods to determine stocking levels
   Many parts planning organizations rely on multiples of daily demand and other rules of thumb to determine stocking policies. For example, reorder points are often based on doubling average demand over the lead time or applying some other multiple depending on the importance of the item. However, averages don’t account for how volatile (or noisy) a part is and will lead to overstocking less noisy parts and understocking more noisy parts.
    
3. Do frequently recompute stocking policies
   Just because demand is intermittent doesn’t mean nothing changes over time. Yet after interviewing hundreds of companies managing spare parts inventory, we find that fewer than 10% recompute stocking policies monthly. Many never recompute stocking policies until there is a “problem.” Across thousands of parts, usage is guaranteed to drift up or down on at least some of the parts. Supplier lead times can also change. Using an outdated reorder point will cause orders to trigger too soon or too late, creating lots of problems. Recomputing policies every planning cycle ensures inventory will be right-sized. Don’t be reactive and wait for a problem to occur before considering whether the Min or Max should be modified. By then it’s too late – it’s like waiting for your brakes to fail before making a repair. Don’t worry about the effort of recomputing Min/Max values for large numbers of SKU’s: modern software does it automatically. Remember: Recalibration of your stocking policies is preventive maintenance against stockout!
    
4. Do get buy-in on targeted service levels
   Inventory is expensive and should be right-sized based on striking a balance between the organization’s willingness to stock out and its willingness to budget for spares. Too often, planners make decisions in isolation based on pain avoidance or maintenance technicians’ requests without consideration of how spending on one part impacts the organization’s ability to spend on another part. Excess inventory on one part hurts service levels on other parts by disproportionally consuming the inventory budget. Make sure that service level goals and associated inventory costs of achieving the service levels are understood and agreed to.
    
5. Do run a separate planning process for repairable parts
   Some parts are very expensive to replace, so it is preferable to send them to repair facilities or back to the OEM for repair. Accounting for the supply side randomness of when repairable parts will be returned, and knowing whether to wait for a repair or to purchase an additional spare, are critical to ensuring item availability without inventory bloat. This requires specialized reporting and the use of probabilistic models.  Don’t treat repairable parts like consumable parts when planning.
    
6. Do count what is purchased against the budget – not just what is consumed
   Many organizations will allocate total part purchases to a separate corporate budget and ding the mechanical or maintenance team’s budget for parts that are used. In most MRO organizations, especially in public transit and utilities, the repair teams dictate what is purchased. If what is purchased doesn’t count against their budget, they will over-buy to ensure there is never any chance of stockout. They have literally zero incentive to get it right, so tens of millions in excess inventory will be purchased. If what is purchased is reflected in the budget, far more attention will be paid to purchasing only what is truly needed. Recognizing that excess inventory hurts service by robbing the organization of cash that could otherwise be used on understocked parts is an important step to ensuring responsible inventory purchasing.

​

5 Steps to Consensus Demand Planning

The whole point of demand forecasting is to establish the best possible view of future demand.  This requires that we draw upon the best data and inputs we can get, leverage statistics to capture underlying patterns, put our heads together to apply overrides based on business knowledge, and agree on a consensus demand plan that serves as cornerstone to the company’s overall demand plan.

Step 1: Develop an accurate demand signal.   What constitutes demand?  Consider how  your organization defines demand – say, confirmed sales orders net of cancellations or shipment data adjusted to remove the impact of historical stockouts  – and use this consistently.  This is your measure of what the market is requesting you to deliver.  Don’t confuse this with your ability to deliver – that should be reflected in the revenue plan.

Step 2: Generate a statistical forecast.  Plan for thousands of items, using a proven forecasting application that automatically pulls in your data and reliably produces accurate forecasts for all of your items.  Review the first pass of your forecast, then make adjustments.  A strike or train wreck may have interrupted shipping last month – don’t let that wag your forecast.  Adjust for these and reforecast.  Do the best you can, then invite others to weigh in.

Step 3: Bring on the experts.  Product line managers, sales leaders, key distribution partners know their markets.  Share your forecast with them.  Smart uses the concept of a “Snapshot” to share a facsimile of your forecast – at any level, for any product line – with people who may know better.  There could be an enormous order that hasn’t hit the pipeline, or a channel partner is about to run their annual promotion.  Give them an easy way to take their portion of the forecast and change it.  Drag this month up, that one down …

Step 4:  Measure Accuracy and Forecast Value Add.  Some of your contributors may be right on the money, other tend to be biased high or low.  Use forecast vs. actuals reporting and measure forecast value add analysis to measure forecast errors and whether changes to the forecast are hurting or helping.  By informing the process with this information, your company will improve it’s ability to forecast more accurately.

Step 5: Agree on the Consensus Forecast.  You can do this one product line or geography at a time, or business by  business.  Convene the team, graphically stack up their inputs, review past accuracy performance, discuss their reasons for increasing or reducing the forecast, and agree on whose inputs to use.  This becomes your consensus plan.  Finalize the plan and send it off – upload forecasts to MRP, send to finance and manufacturing.  You have just kicked off your Sales, Inventory and Operational Planning process.

You can do this.  And we can help.  If you have any questions about collaborative demand planning please reply to this blog, we will follow up.