Master Data Management – Data Structure part 2

Master Data Management – Data Structure part 2

by | Feb 9, 2023 | Master Data

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

    Master Data Management – Data Structure part 1

    by | Feb 9, 2023 | Master Data

    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.

    Customer Service Excellence Often Touted Rarely Achieved

    Customer Service Excellence Often Touted Rarely Achieved

    by | Dec 8, 2022 | Service

    Customer Service Excellence Often Touted Rarely Achieved

    by Nigel Brownjohn

    Excellent Customer Service

    Many companies claim superior service or customer service excellence. But how many companies actually deliver? It’s not easy for companies to deliver superior service year after year. If leadership can stick to a few core principles, superior service can be obtained and sustained.

    Core Principles

    Know Your Customer
    This seems obvious, but do you and your team know who your customer is? Some departments serve only external customers, customers of your business. Some departments serve both external and internal. Nobody serves only internal customers. While some departments think of themselves as not serving external customers, in reality everyone serves the customers of your business. Because without those customers, you have no business. If you think your department, or role has only internal customers – think about how the service you provide enables others to serve customers directly. From the janitor, to the canteen, to finance and sales if you are not providing a value which at a minimum enables delivery of a product or service to a customer – you are just overhead.

    Leadership Must Model Customer Service Excellence
    Superior service is one of the core values that must be embraced by all leaders in the company. Leadership must do more than talk about superior service. They should demonstrate and reward it. As a leader, do you respond to your internal customers in a professional and timely manner? How does the service you provide to your team reflect on the service they are able to provide to the customers of your business? Do you model the excellent customer service traits that you claim to expect of your team? It starts at the top and continues to the team members on the frontline.

    Invest in Service
    People buy from people, and people are the reason that customers stay customers! In almost all businesses it is far cheaper to keep acquired customers than to acquire new customers. Churn not only introduces customer acquisition costs; it also places former customers in an increasingly vocal marketplace. Hire the best customer facing team members who like building relationships with customers and aspire to exceed customer expectations. Hire people that you would want to deal with, and you are likely hiring the people your customers will want to deal with. Smart, articulate, knowledgeable staff who understand your customers, and their needs will drive customer satisfaction and loyalty. Also, as a customer it is rare to speak with a knowledgeable service contact who actually knows you and your account. How do your customers feel when they contact your company? When service is viewed as a cost to be managed, live support turns into FAQ’s, a chatbot and frustrating calls with people who have little to no real knowledge of customer needs.

    Meaningful Service Metrics
    Most companies have a customer satisfaction survey or track net promoter scores, but how often those metrics are reviewed and used varies widely. For example, when was the last time you were called back from a company representative about a survey response? Surveys done well provide valuable insight. To gain the insights, the data has to be reviewed in a timely manner and actions must be taken. Customer service staff enjoy hearing positive feedback and knowing their efforts are appreciated by customers. They also respect hearing constructive feedback that helps them improve their skills. Do you use survey data to inform, motivate and shape the service you are providing? Or is it just another number?

    Additionally, customers like to know that their feedback is heard – not with an automated response, but real responses, from real people. Not all feedback warrants a personal response, but quality input from customers who care whether negative or positive deserves a response. Positive response is great, it validates everything you and your team are doing – so share it, internally, externally (if appropriate) and celebrate it. Negative feedback is, of course, even more important as it is an opportunity to grow and improve. Don’t look to blame, look to learn – does it suggest a change in process, policy, training or even external service provider? Don’t look to justify, or defend – apologize, make it right and thank the customer for highlighting the issue.

    Principles in Practice

    At Cyberscience, we try to live these principles. As a leading Business Intelligence solution provider, we measure the service we provide. Our daily survey data goes to all executives. Not just the satisfaction scores, but the customer comments too – no censorship, no dealing with the issues “before it gets to management”. We know we can trust our team, because we have the right people. We differentiate our service by using real experts who have built solutions and trained customers to staff our customer service team. No bots, no “first level” support reading from a FAQ – just people who know our products, know our customers, know the challenges they face, and have a deep passion for helping people succeed. We are committed to having the right team for our customers.

    If these principles resonate with you and you’d like to connect and talk further, please contact me at njb@cyberscience.com.

    Customer Success Story – Pharmacal Health and Wellness Solutions

    Customer Success Story – Pharmacal Health and Wellness Solutions

    by | Nov 1, 2022 | Customer Stories

    Customer Success Story – Pharmacal Health and Wellness Solutions

    by Mike Main

    Mitch Miller – Purchasing Manager and Chris Gaspardo – IT and Customer Service Manager, at Pharmacal Health and Wellness Solutions (Pharmacal), spoke with me about their experience with Cyberquery. Pharmacal is a privately held consumer goods manufacturer headquartered in Jackson, Wisconsin. With over 100 years of experience, the company has grown into a leader in the outdoor health and safety solutions sector. The business is complex as raw material is used in multiple products branded and private label with unique formulas along with fluctuating seasonal demand and servicing 1000’s of retailer customers. They needed a BI solution that could accommodate this complexity and provide early warning to supply chain issues.

    “We depend on Cyberquery reports for consolidating customer requirements and managing orders with potential stock outages. Our teams can quickly get the data and initiate internal and external communication.” Mitch Miller

    Pharmacal needed an efficient way to access, process and present the information to purchasing, production and customer service that would alert the team to potential stock outs allowing them to proactively work the supply chain. The Cyberquery data dictionary can create file relationship between disparate data including QAD 2014SE ERP, EDI and multiple excel tables allowing for a full end-to-end view of the supply chain – quote to cash.

    Before implementing Cyberquery, Pharmacal staff spent many hours daily and monthly downloading ERP data and maintaining complex offline Excel reports while trying to understand inventory shortages which resulted in misses to customer due dates. Now with offline data “online” and relationships stored in the Cyberquery data dictionary, daily and monthly reports are refreshed automatically without manual manipulation. Also, with Cyberquery, they have implemented automated supplier emails, further streamlining the process.

    “The data dictionary and file relationships capabilities within Cyberquery is powerful. It allows us to add offline data to Cyberquery and create file relationships between disparate data sources quickly and easily for a comprehensive view of the business.” Chris Gaspardo

    The main benefits from implementing Cyberquery are that Pharmacal has saved money and time. They have been able to better manage their ERP user licenses, reduce time spent on ad hoc reporting, minimizing manually manipulating excel data, and maintaining off-line data. All of this while improving service levels.

    Pharmacal’s next initiative with Cyberquery is automating supplier score cards that measure performance of delivery, price, quality, and cost savings. These metrics, OTD, PPV, receipt reject rate and cost savings initiatives are measured to goal, trended monthly and rolled up to a calculated supplier score shown on one score card.

    About Pharmacal 

    Pharmacal Health & Wellness Solutions is home to many well-known brands such as Potable Aqua, Sting-Eze, Baitmate, MG217, and vH Essentials as well as the official licensee of Coleman repellent and first aid products. Additionally, Pharmacal continues to expand its contract manufacturing portfolio.

    If you have a need for a new business intelligence solution that provides benefits as Mitch and Chris describes, contact Cyberscience for a demo or test drive.  – info@cyberscience.com or +1 303-745-3900.

    Author: Mike Main, Regional Manager at Cyberscience. 

    Customer Success Story – United Equipment Accessories

    Customer Success Story – United Equipment Accessories

    by | Oct 3, 2022 | Customer Stories

    Customer Success Story – United Equipment Accessories

    by Jim Maughan

    Howard Samarin at United Equipment Accessories (UEA), spoke with me about their experience with Cyberquery and Cyberscience. UEA has been a Cyberquery user since 2015 when they implemented the SyteLine ERP from Infor.

    “Cyberquery has given us insights into our ERP data much faster than relying on standard ERP reporting for making both short-term and long-term business decisions.”

    One of the main features of Cyberquery that UEA has come to rely on is that it is both fast and easy to use. UEA values Cyberquery’s speed for developing and deploying reports as well as querying information. The team saves a lot of time creating reports in Cyberquery vs creating data views or form reports in their ERP system. They noted how Cyberquery easily handles all of their data including user defined tables and fields, and even tables and spreadsheets which are external to SyteLine. This has allowed the UEA team to generate both internal reports and external supplier and customer reports efficiently combining data from many sources.

    “We can’t even imagine conducting business without Cyberscience and Cyberquery”

    Their ERP Support Analyst designed an online pricing calculator for their customer service team using Cyberquery. The customer service agents develop quotes for custom engineered to order (ETO) parts based on labor and material costs. By having the automated form with up-to-date costing, customer service can provide a quote over the phone, immediately with confidence.

    Another reason Howard and the rest of UEA can’t imagine conducting business without Cyberscience and Cyberquery is the Cyberscience service team. The Cyberscience consultants have a deep knowledge of Cyberquery, ERP data structures and business applications. UEA also noted the Cyberscience customer service team’s response time is “excellent.”

    About UEA

    United Equipment Accessories, based in Waverly, Iowa, engineers and manufactures custom slip rings, rotary unions, cable reels and shift controls. UEA’s products are used by equipment manufacturers in a wide variety of industries across the US and the world. Their ERP system is the Infor SyteLine/CSI ERP supporting their global engineered to order business.

    If you have a need for a new business intelligence solution that provides benefits as Howard describes, contact Cyberscience for a demo or test drive.  – info@cyberscience.com or +1 303-745-3900.

    Author: Jim Maughan, BI Practice and Business Development Manager at Cyberscience. Jim has a passion for helping customers optimize their business intelligence and reporting capabilities.