Master Data Strategy – Implement a Governance Model

by Tom Oldham

Master Data Governance

The final step for implementing best practices for Master Data Management (MDM) is establishing an ongoing process and set of controls to ensure the maintenance and adherence to the data structures and mapping is successful. Master Data is not static because businesses, product lines, products, suppliers, components and processes can be added, modified or removed throughout the fiscal year. This causes ongoing maintenance to the master data and without a data governance model the data quickly becomes disorganized and reports will show incorrect results. A robust Data Governance Model for Master Data Management has 3 key elements – people, processes, and technology.

People

For a Master Data Governance Model to work a dedicated resource is needed. This resource can be one person in small company or a “team” in a larger company. This resource develops and maintains the forms which control the addition of Customer, Supplier, Product, etc. data. They make sure the workflows are documented and followed. This resource will need support from IT to have automation tools to expedite the upload and download data process. Some of the characteristics that make someone successful in this role are acute attention to detail, strong business system (ERP) knowledge and credibility with management. This is a gate keeper role and they will be saying “no” and asking people to “resubmit forms”; if for example the engineering change notice for a new part is submitted with missing data. This resource can be in any department but oversight for the MDM role should be a joint committee made up of usually accounting, engineering and I.T. management.

Processes

A Master Data Governance Model should have governing processes and forms documented in the company’s quality system usually defined within local work instructions or global work instructions. Tribal knowledge and “go ask Susan or Bob” are not examples of a robust or disciplined system. The forms (an example would be an Engineering Change Request or Price Change Form) ensure that all the data is collected and available for a change to occur in the Master Data. The governing processes ensure that changes have been approved and communicated correctly within the company.

Technology

Lastly, a robust Master Data Governance Model utilizes technology and automation. Best practice is to maintain as much Master Data in your ERP system as possible thus “one version of the truth”. “Extending” your ERP with additional tables and fields to accommodate this is perfectly OK. IT can assist to develop automated tools to upload and download required data which is extremely helpful and almost a requirement. Another area to automate is developing reconciliation procedures that are run at least monthly highlighting the changes from the previous month. The reconciliation will flag adds and deletions from the database as well as flag potential errors such as parts with zero-dollar pricing for example. The automation reduces the overall threat of a manual entry error.

Summary

Master Data is not static, requires maintenance and constant review and attention. It is important the company leadership properly funds the resource or resources with the right people and tools. Management also needs to support the adherence to the processes for updating the Master Data and discourage one off work arounds. With the pace of change companies face, it’s imperative to have a well-supported Master Data Governance Model that ensures the information that leadership is using to make decisions is complete and accurate.

Seek out Experts

At Cyberscience we understand the complexity of ERP data structures and have been helping customers define their master data strategies for years. With 25+ years of experience delving deeply in the world of ERP systems to retrieve data critical for managers to run their business, we know how to support your organization in setting up the best practices of Master Data Management for a successful Business Intelligence Implementation. Give us a call and start your journey.

Author: Tom Oldham, CMA, CFM, is a Product Manager at Cyberscience focused on Manufacturing Solutions.

Master Data Management – Data Structure (part 2)

by Tom Oldham

Master Data Structure

In my last blog, I introduced the topic of Data Structures, the way data is organized and defined within the ERP. I highlighted best practices for finance, corporate and supply chain. I am continuing the discussion of Data Structures, and diving into Marketing, Customer and Sales best practices. There is a lot of content to cover and I promise if teams follow these best practices your business intelligence process will run much smoother.

Data Structure Example – Marketing / Product

Setting up Data Structures for marketing is focused on how the company’s products are defined in the ERP. SKU or Item Number codes are rather proprietary and hard to change but as the company grows and product lines are added existing product structures may no longer meet the needs of the business. Hopefully there is some “smartness” in the coding structure. For example, the length, width, size, diameter, composition, etc. are often part of the product code in question. If not, this “smart coding” should be done in the fields or attributes available in the ERP system directly related to this product. Often there is data offline that team’s reference that describes the Product and is used for various analysis. You must get this “online”! It is important or else you will become lost in the spreadsheet/VLOOKUP world.

Some questions to ask when setting up Product or SKU’s and related fields:

• • What are your products?
  • How do you price them?
  • How do you market them?
  • What differentiates one part from another?
  • What is the sales strategy around these products?
  • Where are they manufactured or purchased?
  • How does Accounting view these products from a financial perspective?
  • How do you measure your Inventory?

Another important note to strengthen your BI is to build a hierarchical or parent/child structure into your products. This will allow drill-down and analysis from summary level to the details with ease and very little work. This is represented by the Product Line, Group and Item Type in the example below.

See a simple SKU/Product Master example below:

Hierarchical Layout:

Product Master Data Structure Best Practices:

Alphanumeric characters: One other best practice is to use only alphanumeric characters in your part numbers if possible. Adding other characters often “confuses” spreadsheet or other analysis tools. And no leading “0’s” if possible! Spreadsheets do not like leading a leading “0”.

Smart Model Codes:  Item Numbers or SKU’s are quite proprietary and often hard to change but many companies have built smartness into them – each segment of the code means something. This is typical in a Configured to Order (CTO) manufacturing model.

Hierarchical Structure: It is also important to build a hierarchy or structure here – in this case using Product Line\ Group\Type\Item Number. Your BI tools will love this structure and queries will be easier to build.

Extended Product Master:  Using the additional fields within the ERP is very useful. These items can be customized to your specific industry needs. Examples of some data that I have seen inputted in the extended product master are – Item Number/SKU – one to one with ERP Product Master, Tariff Code, Size, Volume, Alternate Code, Sales Code, UPC.

All these details must be taken into consideration when setting up your product data and reviewing the fields or ERP relationships available to you. Consultants or your IT/Finance team should be part of the review.

Data Structure Example – Customer

Customers and related analysis can get very complicated, but we will try to stick to the basics here as we did with Product.

There can be many ways to “group” customers and which is best depends on how your management team defines customer segments. Most ERP systems do a good job of giving you the flexibility you need outside of the common Customer and Address Master. Some standard segments are by sales channel, industry, and region. Other modules (whether it be Pricing or Sales related) will allow you to “group” customers however you need – as long as you have the tools to maintain these structures “en masse” via automated loads included in the ERP or built by consultants/developers. Having the right tools is a key issue not to be overlooked.

Like the Marketing recommendations, the Customer data structure it is important to build in a hierarchical or parent/child structure to give the “best” data you have to your BI tools. That is represented by the Group/Parent/Bill-To/Sold-To fields below. BI tools love a good “data model” or structure with drill-down structure built in. That is hugely important to help maximize ERP to drive BI.

Here are questions to think through for Customer setup and maintenance:

• • Who are your customers?
  • What are the relationships between customers?
  • What kind of customer structure do you want (hint: Bill-To, Sold-To and Ship-To)?
  • What markets are they in?
  • What is your sales force structure?
  • Consider all sales reporting – are there many ways that you report this?
  • Do not overlook pricing – form a team to take advantage of what your ERP system offers.
  • Do not overlook incentives and discounts in the same manner.
  • Are they also a Supplier/Vendor?
  • How do you want to treat customers from a financial perspective?
  • What are all the “groupings” of customers that you look at today and want to in the future?

See a simple Customer example below:

Spreadsheet Layout:

Hierarchical Layout:

Customer Master Data Tips

Tip 1: You can also build some “smartness” into your primary Sold-To or Customer number but be careful! If your customer base is rather dynamic with lots of acquisitions or constant changes be careful. ERP systems can build a lot of data and history – changing these primary customer codes can be difficult if you have a large amount of data. There are workarounds for this – the other fields can prevent this problem – especially Group, Parent and Bill-To. Build the smartness into the fields around the “Sold-To/Customer” and you will be good. For example, “point” customer records to another code and build in a parent-child structure. In this case we are using Group →Parent →Bill-To→Sold-To→Ship-To. “Ship-To” is not shown above since this is a customer record – but it is still part of the normal ERP and customer structure. Smaller companies may not be this complicated, so you only have a Group→Bill-To→ Ship-To structure. Whatever works for you. Also, with the customer structure we have a sales force structure built in using Territory→Salespsn1→ Salespsn2.

Tip 2: Investigate loading your customer’s structure into your ERP if it is not too difficult. For example, “Region” above can represent the customer’s structure and you can report back to them the way they see themselves – this is very helpful when managing rebate programs or preparing for a partnership review for example.

Tip 3: Create an “extended” table if you need extra fields. I have seen this used for things such as:

Customer or Sold-To Code – one to one with ERP Customer Master, Parent, Group, Mail-To, UPS Account, FedEx Account, Invoice Type – Values of Email, EDI or Paper.

Data Structure Example – Sales

And finally, as a last example, your sales organization should not be ignored and should be fully implemented into your ERP system for sales reporting, commissions, etc. It is not unusual to add fields to accommodate this structure. This is another area where “extended tables” is commonly used.

See a simple Sales example below:

Spreadsheet Layout:

Hierarchical Layout:

All levels of reporting should find a home – note the VPTerritorySalespersonCSR Cr Mgr structure above. “CSR” is Customer Service Representative and “Cr Mgr” is Credit Manager. This is a great idea to align your sales, inside sales and credit department for best servicing your customer if it is a fit. I recommend loading it all into the ERP system to maximize your analytics and performance in these areas.

Final Thoughts on Master Data Structure

I’ve covered a lot of material and it may seem overwhelming – especially if you have large data sets to begin with. To get started on making improvements, begin with the biggest pain points identified during the Master Data Management Strategy. If your team becomes stuck, instead of accepting the status quo and living with endless Excel manipulation, reach out to experts. At Cyberscience, we can support your Master Data improvement journey.

My next blog will provide insights about the infrastructure and resources need to maintain Master Data.

Author: Tom Oldham, CMA, CFM, is a Product Manager at Cyberscience focused on Manufacturing Solutions.

Master Data Management – Data Structure (part 1)

by Tom Oldham

Master Data Structure

In my two previous blogs about Master Data Management, I gave an overview describing what is Master Data Management and explained how to develop a Master Data Strategy. In this blog I am diving into the details about setting up Data Structures and highlighting best practices. Data Structures are the way data is organized and defined within the ERP.

After the Master Data Strategy is defined, then it is time to refine and implement the Data Structures. Master Data Structures are the secret sauce for effective and efficient business intelligence. I recommend starting with the most critical “pain points” identified in the process map. This may be as simple as adding a parent/child relationship to the existing customer record or as complicated as implementing a new smart model code structure. Also, addressing the need to put some standardized “intelligent groupings” around the business to ease the work required to understand trends and expose areas that need attention.

When setting up or updating the Data Structure, I find that it is easiest to look at things by functional areas – Finance, Corporate, Supply Chain, Marketing, Customer and Sales. Over the years, I have developed a list of best practices for each area. I am breaking this topic into two blog posts and will first go into detail about Finance, Corporate and Supply Chain. (I will cover Marketing, Customer and Sales in my next blog post.)

Data Structure Example – Finance

Much like standardizing a Chart of Accounts for Financial Reporting across your entire organization, Master Data Management can put standardized “intelligent groupings” around the rest of your business to ease the work required to understand trends and expose areas that need attention. Most companies have a good Chart of Accounts simply because that is required to produce financial statements. The Chart of Accounts is translated into the ERP.

Finance teams are under a lot of pressure when closing the books at the end of month, end of quarter and end of year. To help streamline the close and reconciliation of the books, the setup of the financial structure in the ERP is an imperative.

See a simple Finance Structure example below:

Hierarchical Layout:

As shown from the “Top Level” to Domain, Entity, Account, Sub-Account, Cost Center and Project – the discipline and process necessary for well-structured financial data should be taken to other parts of the business. When a new customer, supplier or product is created – that same attention to detail should be taken as when adding a new “Account” for your General Ledger. When setting up data structures teams should ask themselves the following questions and code as desired but in a smart and structured process.

• What are all the ways that particular customer, supplier or product can be identified and analyzed?
• What parts of the business will be aided by doing this?
• What other parts of the ERP system will be enabled by populating this data?

Data Structure Example – Corporate

Corporations measure their business leaders and teams on their results. To easily roll up the entities to a “company” level or drill down to the “site” level, the corporate structure needs to be defined and loaded into the ERP.

The setup is fundamental for any ERP system but there should be some “method to the madness”. There are many names used like Company, Entity, Site, Warehouse, etc. You need a simple structure built for your way of analyzing the business. As with the Chart of Accounts, this area is usually handled well by most since it is such a key component of an ERP system. Treating the different locations with smart coding is a great idea – offices grouped together, plants grouped together, and warehouses grouped together. Also, keep in mind that the business has “reorganizations” and the structure that you put in place should be flexible for adapting to future changes. An example would be adding a “world area” grouping even if the company is solely North American currently but the company vision is to be global in the future.

See a simple Corporate Structure example below:

Spreadsheet Layout:

Hierarchical Layout:

Note: Domain, Entity/Company and Site/Warehouse have a natural hierarchy and smart coding built in. This coding takes advantage of the Domain/Entity/Site structure. Also, using naming conventions like starting an Office site with the letter “O” or all Canadian companies are numbered from 4000 – 4999 makes data analysis and cleansing simpler. .

Data Structure Example – Suppliers / Vendors

An area of the business that is getting a lot of attention is Supplier Performance. With disruptions and inflation happening throughout the Supply Chain, it is no longer practical to analyze vendor data using off-line methods. Whether your company has corporate procurement teams or commodity teams working contracts for price agreement, these teams require ongoing vendor data to negotiate the best deals. To have timely, accurate and repeatable supplier data when analyzing supplier spend, pricing and performance it is absolutely necessary to have the required Master Data built into your data structure. Things to consider when setting up Supplier master data are:

• What kind of supplier are they?
• What market or industry they are in?
• Are they related to other Suppliers?
• Are they related to Customers?
• Are they both a Supplier and a Customer?
• Do they need to be grouped together for pricing or incentives?

A sign that Supplier Master Data Structure is lacking is if your employees must consistently massage the data to get it ready for analysis. They often have some “maps” or “codes” offline to help them do this. That data needs to be loaded into your ERP so all reports and analysis can get the same answer. A common attribute that would benefit from being in the ERP Supplier Master Data is “type.” Once “loaded and coded” the commodity managers can quickly pull all purchases for a specific “type” and see if its tracking higher or lower than the producer price index for that commodity. By saving purchasing teams hours of manually developing reports, they have more time for working vendor programs to improve costs, lead-times and quality. And all of this helps with analyzing the all-important Supplier “spend” analysis.

See a simple Supplier/Vendor example below:

Spreadsheet Layout:

Hierarchical Layout:

Note: Details to consider in this example are that Suppliers begins with an “S”, Sort Name has some intelligence built in and the remaining codes have meaning for supplier analysis. When there is no value for a particular supplier for a particular field, a best practice is to input an actual value such as “blank” or “none”.

Additional Thoughts on Master Data Structure

Often the optimal data structure is not set up in the system because it is too time consuming to load or maintain. This is when the IT department needs to develop automation tools to support configuration management. Also, when you hear a team member say it can’t be done, check with the ERP vendor or your BI vendor. ERP systems are much more sophisticated now and can handle complex reporting structures, supply chain strategies and pricing policies. There should be very little “offline” data and manual manipulation needed for daily, weekly, monthly, quarterly or annual reporting. Reach out to a consultant if your team is having difficulties.

In my next blog, I will expound upon best practices for Data Structures within Marketing, Customer, and Sales.

Author: Tom Oldham, CMA, CFM, is a Product Manager at Cyberscience focused on Manufacturing Solutions.