Chip eServices Blog

Copy this hyperlink to a browser to see a Web video on data import to the Chip eServices Suite using Excel 2003 and Web services.

Data import to the Chip eServices Suite using Excel 2003 and Web services

A missing piece of functionality in the Chip eServices Suite has been the ability to import data. We have many customers that have integrated solutions that synchronise data but we had no solution to simply import new data from a file or spreadsheet. We knew we had to tackle this problem. To date we have done data import as a free service for our customers but as our customer base and our partner list grows this situation cannot continue. A simple effective tool was needed. In January we decided to start developing a capability so that our customers could do this for themselves. The solution we have decided to adopt has turned out to be incredibly powerful.

First Insight

When deciding what kind of an import tool we needed we first looked at what format the data was available in. Every request for data import comes in either a delimeted or fixed column file or as an Excel spreadsheet. Since the first two file types can be imported into spreadsheets we soon realised that what we needed was the ability to import from spreadsheets.

Second Insight

All of our customers are Excel experts. Our customers like manipulating data in spreadsheets. The reason that customers like our export functionality is because the get the data in a spreadsheet and once its in a spreadsheet they can do what they like with it. Even though we offer an XML export function, I dont think a single customer has ever found a use for it. Don’t let anyone fool you, when it comes to people managed data migration the spreadsheet is King.

The Solution

So we needed a spreadsheet import solution that our customers found easy to use. At a brainstorming session one of our developers, Christian, who has a little Visual Basic for Applications (VBA) experience told us that Excel 2003 can call a Web service. We have a fully developed suite of Web services for our applications complete with validation rules and built in business logic. The solution was so obvious to everone at the meeting that a silence fell on proceedings. (How come the most useful meetings are always in the canteen and the ones in the board room are a waste of time?).

You guessed it, we are building a spreadsheet for each data type (Model Numbers, Spare Parts, Customers etc.). You copy and paste the data from your spreadsheet into the spreadsheet we supply. Press a button that calls the validation and upload Web services and your data is imported in a jiffy. We build the VBA code into the spreadsheet that does the magic. If the data doesn’t validate correctly we tell you why and on which line the validation failed. Type in a correction and press the button again.

The only cheat is that when a user selects Import from our application menus they are actually downloading a spreadsheet. The upload comes later.

There is one more good idea to tell you about. We do data export into spreadsheets and we do data import from spreadsheets. So, we have decided to make the two spreadsheet types very similar. If, for example, you want to update your Model Number list, export the list to a spreadsheet. Make the modifications you require. Copy the updated records to the import version spreadsheet, validate and upload. Eezeepeezee.

As is always the case in these situations, we will be rolling out this technology one data type at a time. Let us know which spreadsheets you need developed first.

There will be one surprising candidate in the first round of spreadsheets developed. Many customers get large RMA requests from logistics centres that accumulate from multiple retail outlets. So a request for 200 or more Items in an RMA is not unusual. This request usually arrives by spreadsheet attachment to an email. From now on that request can be copy and pasted into the upload spreadsheet and a days work done in a few minutes.

The only limitation on this technology is that we will only support Office/Excel 2003 or later and also remember that Excel only supports about 65,500 lines in a spreadsheet.

If we extend the supply chain metaphor from raw material source to landfill and include the consumption of fossil fuels leading to the creation of greenhouse gases the resulting schematic clearly shows that SCM concepts are at the heart of the ecological challenges facing space ship Earth.

This drawing does not consider waste material recycling or energy generation from renewable sources such as wind, solar or biomass. Neither does it consider energy generated by nuclear sources.

In this context Reverse Logistics can be seen to have ecologically sound benefits.

According to Dyckhoff et al. SCM and RL 2004
‘In SCM, the “way back� is not very important. If any recycling processes are considered, for example, in newer releases of the SCOR-Model, they only affect the return processes for reasons of warranty or maintenance. However, these processes merely describe a small proportion of the recycling processes in real life. A widespread management of circular systems will, therefore, only be possible if SCM is enhanced to Close Loop Supply Chain Management (CLSCM) by looking at different types of collection, reduction and induction processes, and especially by integrating corresponding processes of reverse logistics.’

Most supply chains are managed by commercial organisations that have as a prime objective the maximisation of value for their shareholders. The implementation of initiatives, no matter how ecologically sound, that are not profitable are in contravention of that prime objective. Good corporate citizens do however follow the law. Initiatives that are backed by legislation (e.g. The WEEE Directive and the RoHS Directive) are therefore required where the objectives are ecologically required but not economically justified within the context of a single commercial organisation. This does not mean that companies resent such legislation. Since legislation is applicable to all competitors, in the marketplace where the legislation applies, then the implementation of a legally binding ecologically sound initiative will not result in any one company being at a competitive disadvantage.

Reverse Logistics researchers and practitioners are moving toward an analysis of Closed Loop Supply Chains (CLSC). These CLSCs are populated by three types of organisation not normally considered in SCM.
* Collectors collect, accumulate, and may do some sortation of waste streams.
* Reducers complete the sortation process and process the waste stream into raw materials which are available for consumption.
* Inducers then introduce the recycled raw materials into new manufacturing processes. It is quite common, for example, for a percentage of recycled plastic to be used when moulding new products.

It is important to realise that CLSCM does not necessarily mean that material on the ‘way back’ does not necessarily follow the same path, in reverse, as material on the ‘way there’.

Dyckhoff et al. SCM and RL 2004 proposes the following diagram to represent the CLSC. The upper layer lists the actors and transactions while the lower layer represents material flow and transformation.

The reduced depletion of natural resources when materials are recycled is the intrinsic advantage of viewing Supply Chains as closed loop rather than linear.

Unfortunately there is a need for government intervention in order to implement ecologically sound but commercially expensive initiatives. For example, The Montreal Protocol (1987 and subsequent amendments) limiting the use of Chlorofluorocarbons (CFCs) has been successful at reversing the ozone depletion layer at the polar caps. Failure by the USA to adopt the Kyoto Protocol (1997) on the creation of greenhouse gases has seriously damaged its ability to achieve its objectives.

Within the EU, the implementation of the WEEE Directive, and the RoHS Directive, has had dramatic beneficial effects on the environment. These initiatives put the responsibility for the cost of recycling on the producers and state the specific percentage of these products that must be recycled. These initiatives (especially the WEEE directive) are creating opportunities for companies in collection, reduction and induction within the closed loop supply chain.

As a practitioner in Reverse Logistics it is nice to feel that the sector has the capacity to contribute to the ecological wellbeing of the planet.

A few years ago, Microsoft went on a $2 billion acquisition binge of companies that supply business management software to small and mid-size organizations; Axapta, Great Plains, Navision, and Solomon. These applications were then managed, within Microsoft, by the Business Solutions Group. In 2005, it became increasingly public that Microsoft were going to integrate these products into one product offering. ‘Project Green’ was the working title for this massive technical and marketing integration project.

The ambitious objective of Project Green is to architect a tightly integrated software stack from OS, through database, application and Office.

In September 2005, Microsoft rebranded the Business Solutions Group as Microsoft Dynamics (http://www.microsoft.com/dynamics/default.mspx). The four assimilated business management packages are now called Microsoft Dynamics AX, Microsoft Dynamics GP, Microsoft Dynamics NAV, and Microsoft Dynamics SL. The Dynamics lineup also includes Microsoft’s CRM offering, now rebranded as Microsoft Dynamics CRM.

Coupled with Microsoft’s strength in database (SQL2005) and business integration (BizTalk) software, the Dynamics rebranding looks like Microsoft are positioning themselves as a serious player in the Financial/ERP/SCM software supplier market. It will however be 2008 before the real fruits of Project Green start to appear. Microsoft have committed to supporting existing products until 2013.

Microsoft’s initial focus will be on small to mid-size companies. In this space they will compete with companies like Intuit and Sage/Best. However existing players in the ERP space such as SAP and Oracle are also starting to focus on smaller size customers, so competition in this sector will really heat up.

The term ‘reverse logistics’ has been voted one of the most unnecessary phrases for 2005, by the American Dialect Association. (http://www.americandialect.org/Words_of_the_Year_2005.pdf)

Turn Around Time (TAT) is probably the most important KPI in any Reverse Logistics operation involving a customer service aspect. In an earlier blog, I defined TAT as ‘The time from customer service request to problem resolution‘.

However there are lots of interpretations possible within this definition depending on your point of view. I know of one company in Consumer Electronics that tracks five different flavors of TAT. Here are some possible interpretations.

1) TAT from the perspective of a consumer returning goods at a store.
The consumer may get almost instantaneous problem resolution by the issuing of a credit note or exchange item, if the return is made to a store. Online and mailorder returns may require a longer TAT because of the transportation times involved. If the return is deemed repairable then the client may be waiting a significant time while the product is processed at a service facility and reunited with its owner.

2) TAT from the perspective of a retailer/dealer.
Once a return is at a retailer/dealer’s premises the problem moves back the supply chain and a fresh TAT relating to the next leg of the chain needs to be calculated.

3) TAT from a 3PSP’s perspective.
A Third Party Service Provider’s (3PSP) definition of TAT will be limited to the time the returned product is under their control. For example, a repair company is likely to measure TAT from the point of arrival of the defective item to its shipment if it deems that other aspects of the process are outside its’ control. A freight company may similarly measure the pickup to delivery time delta.

4) TAT may sometimes also be measured from point of customer complaint to disposition (scrap, recycle or resale) of of the returned item. Here the objective is to shorted the overall supply chain pipeline.

A point about the measurement units of TAT:
TAT is obviously measured in time units. Depending on the specifics of the industry sector the measurement unit could be in seconds, hours, days, weeks or months. According to Jose Garcia of Microsoft, who has responsibility for XBOX repair in North America, the appropriate measurement unit for XBOX service is ‘weekends’. Microsoft, in analyzing complaints and commendations from customers after receiving service, noticed that customers whose XBOX was missing for no more than one weekend considered Microsoft service good regardless of the number of days it was away for repair.
Microsoft adjusted their logistics processes accordingly. Units requiring a two day shipment are usually dispatched on a Wednesday while units requiring a single days shipment are dispatched on a Thursday. Microsoft will expedite units that are in danger of exceeding the magic one weekend TAT and will pay extra for freight if necessary to achieve the magic goal. A one weekend TAT can of course be measured as varying from seven to twelve days so measuring TAT for Microsoft in days might not yield the insight they need.

What are the Key Performance Indicators (KPIs) in Reverse Logistics? All KPIs in Reverse and Service Logistics fall into two categories.

1) Those that reflect customer service levels

2) Those that reflect cost

KPIs that reflect customer service levels:
Turn Around Time (TAT)
The time from customer service request to problem resolution.
Call-centre Resolution Rate (CRR)
The percentage of trouble-tickets dealt with by the call centre that do not result in RMA issue.
Boomerang Return Return Rate (BRR)
The percentage of product already returned that is returned a second time. This KPI is important where returned products are repaired as it measures the quality of the repair process.

KPIs that reflect cost:
Net Asset Recovery (NAR)
The percentage of the value of returned products that is recovered through the reverse logistics process.
Return Rate (RR)
The percentage of product sold that is returned.
Scrap Rate (SR)
The percentage of product returned that is scrapped or recycled as distinct from resold.

I will update this particular blog with further KPIs and their definitions in the future. Please leave a comment if you feel a particular KPI should be added to the list.

An ideal question to address, on the first day of a new year, in a Reverse Logistics blog is: What is Reverse Logistics? The following post contains all the aspects of RL that I could think of. Please let me know if I forgot something or if you agree or disagree with what I say.
______________________________________

Reverse Logistics is defined by the Reverse Logistics Executive Council (http://www.rlec.org/) as:
‘The process of planning, implementing, and controlling the efficient, cost effective flow of raw materials, in-process inventory, finished goods and related information from the point of consumption to the point of origin for the purpose of recapturing value or proper disposal’.

Reasons for Return (RfR) are manifold and include shipping damage, faulty goods, product recall, buyers’ remorse and excess stock adjustments.

Frequently, reverse logistics involves the processing of goods using disposition rules designed to maximise the Net Asset Recovery (NAR) from the disposal of goods being processed. Possible dispositions include; resale as new, resale as ‘B’ stock, use as exchange, recycling and disposal. The management and handling of hazardous materials may be a factor in recycling and disposal. The term ‘reverse logistics’ is also used to cover the process or returning reusable containers for reuse and of transporting packaging to recycling facilities.

In some cases a customer may receive credit or an exchange item without the need to return the original item where the return freight costs exceed the recoverable value of the item after return.

Repair, remanufacturing, and refurbishment are sometimes stages in the reverse supply chain. These stages add value to the returned product to maximise NAR or to satisfy a customer need for an exchange item or to enable the re-return of the actual item to its owner after service. Customer chargeable services for both in and out of warranty items can also be considered as part of reverse logistics.

There are some aspects of forward logistics that are closely related to reverse logistics.
a) Spare Parts distribution is required to support repair, remanufacturing, and refurbishment.
b) The design of products and packaging to facilitate reliability, recycling and disposal can reduce after sale costs.
c) Reverse Logistics can be a source of valuable failure analysis and market intelligence data to assist product design and manufacture.
d) Warranty registration data collected in the forward supply chain may be used to control warranty costs in the reverse supply chain.

Reverse Logistics involves the return by a customer (or in some cases the customer of a customer) of product to a supplier. The customer making the return can be a business or an end consumer. As well as the operational issues outlined above, two factors important to the relationship with the customer making the return must be dealt with as part of the reverse supply chain.
1) In many cases the customer is returning goods that are defective, or are perceived by the customer as defective. Therefore, the return can often be viewed as a customer complaint. The processes and procedures in place at the customer boundary are therefore important in determining customer satisfaction.
2) The customer has to get credit, their product repaired, or an exchange product to close the issue. Turn Around Time (TAT) is defined as the time it takes to close a returns issue.

NAR is probably the principle financial KPI, and TAT is probably the principal customer service KPI, in a Reverse Logistics Supply Chain.

The Reverse Logistics Association offers a financial perspective on the topic.
“In other words, anytime money is taken from a company’s Warranty Reserve or Service Logistics budget, that is a Reverse Logistics operation” - Gailen Vick, President RLA

If you are having difficulty implementing Reverse Logistics systems then why not consider engaging the services of a Reverse Psychologist.

 

My thanks to Frank at Bifsniff (www.bifsniff.com) for their permission to use this cartoon.

I have been studying part time for an M.Sc. in Supply Chain Management for the past three years. To complete my studies I am currently writing a Dissertation focused on IT in Reverse Logistics. While doing my research for this dissertation I found several good Web sites relating to Reverse Logistics and to after sales service in general. Here are seven I found particularly interesting with a short note on each.

http://www.warrantyweek.com
This is a fascinating site that collects warranty cost information from published annual reports of listed corporations.

http://www.reverselogisticstrends.com
This organization, headed by Gailen Vick, seems to be the most active reverse logistics organization on the planet with three conference/exhibition held annually (USA, Europe, Far East) and multiple workshops. They offer free profile registration to third party service providers 3PSPs. These profiles are then available to members.

http://www.afsmi.org
The Association for Service Management International is a global not-for-profit professional member based association for customer service and support executives and has been in existance since 1975.

http://www.eicta.org
After you reach the site, select the Issues tab on the menu and go the the ‘After Sales Issue Group’. Eicta publish the IRIS coding system which is a multilingual coding system to facilitate data collection and feedback of service information in the Brown (TV &Hi-Fi) and White (Electrical Kitchen Appliance) goods sectors and the ICT sector. Be sure and download the ‘IRIS Course’.

http://asc.comptia.org
CompTIA offer lots of resources for the Electronics and IT sectors. In particular they list members in different geographic areas. This is useful if you’re looking to appoint a Service Centre.

http://www.rlec.org
The Reverse Logistics Executive Council is a small but very focussed group. Be sure and download the free book by Dale Rogers and Ronald Tibben-Lembke. The book is extremely good and is the most quoted reference on Reverse Logistics ever written.

http://www.rosettanet.org
Rosettanet is an organization that develops Partner Interface Processes (PIPs). These are standards to define eCommerce communication messaging and orchestration. PIP 6 relates to Reverse Logistics.

Please let me know if you have found other Reverse Logistics web sites of interest. For the record, I am not connected in any way with these Websites or with the organisations that publish them other than as a subscriber to their services.