Best Practices for Using Microsoft Outlook from a Sales Perspective

Arrow-Tip #56 Creating Metrics to Illustrate KPIs Part I

In Arrow-Tip #53 Establishing Key Performance Indicators, we discussed choosing KPIs to track performance at my client company Gulf Coast Welding (GCW). In the next two Arrow-Tips we will discuss the specific metrics we are using to generate those KPIs and how we collect the data to calculate them.

The following are the first two types of KPIs we decided we want to track along with the actual metric we chose to represent the KPI and why we chose it:

KPI: Safety

Metric: Safety Inspection Score – This score is a combination of results from daily shop inspections and weekly safety inspections.

Why we chose it:  The number one issue that any manufacturing company should concern themselves with is the safety of their employees.  

Data collection:  The Plant Superintendent conducts daily inspections and records the results in an Excel spreadsheet.   The GCW QC & Safety Manager conducts a weekly safety audit along with a rotating member of the office staff and enters the results in an Excel spreadsheet.

KPI: Quality

Metric 1: On time shipment – This is a simple yes/no metric – either we meet the deadline specified by the customer or not. 

Why we chose it:  – This was the first metric that customers always asked about and stated as being important to them.  In recent years, many manufacturing companies that GCW serves have followed the trend of just-in-time inventory or keeping their inventory to a minimum.  That means that when equipment is needed, it must be manufactured quickly and GCW does not want to be the bottleneck in that process.

Data Collection:  Due dates are entered on the GCW Production Schedule which is created in a Microsoft Excel spreadsheet.  Ship dates are also recorded in the same spreadsheet and updated when a signed and dated Bill of Lading is uploaded into the electronic job file.  These dates are automatically compared in a separate Microsoft Excel spreadsheet linked to the Production Schedule spreadsheet.

Metric 2: NDT (Non Destructive Testing) pass rate– This metric comes from pass/fail rates for a variety of non destructive tests such as x-rays.  Requirements for these test are usually specified by the customer.

Why we chose it: NDT testing is a recognized standard for determining the integrity of manufactured equipment in GCW’s primary customer industries.  It is a huge testament to the quality of the work that GCW produces that they have a 100% pass rate for all NDT testing conducted in the past year (sorry – just have to brag on them about that fact!)

Data Collection:  NDT pass rates are recorded by the GCW QC & Safety Manager in a data book for each job.  The QC & Safety Manager sends a report at the end of each month to me summarizing pass fail rates that I input into the Dashboard spreadsheet manually.

Metric 3: NCRs (Non Conformance Reports) pass rate– Another simple pass/fail metric – if the customer accepts the work then a pass is recorded, but if the customer rejects the product based on work prerformed by GCW or one of its subcontractors a fail is recorded.  GCW tracks NCR pass rate on a 12 month rolling average.

Why we chose it:  The ultimate failure in customer satisfaction is when a piece of equipment is sent back to the shop because of issues with work performed by the vendor.  GCW often receives purchase orders for clean up jobs where they are fixing mistakes made by other lower quality shops.  The idea here besides helping maintain GCW’s own high standards is showing potential customers that this scenario won’t occur if they pick a quality shop to begin with.  It is so much more expensive to repair a piece of equipment than it is to manufacture it correctly to begin with.

Data Collection: When ship dates are recorded for on-time shipment this field is automatically set to “pass” in the dashboard spreadsheet because the customer has accepted shipment.  If for some reason the product is sent back to GCW at a later date, a new receiver is uploaded into the system which flags me to determine the reason for the return and then manually reset the value to “fail” if applicable.

In Arrow-Tip #57 we’ll cover the remaining two types of KPIs GCW is tracking – Profitability and Revenue.

This post was written by MistyKhan and published on May 21, 2010 in the following categories: Arrow Tips, Excel, Front Page, Management, SharePoint. You can leave trackbacks on this post at this address. To follow the comments on this post subscribe to the RSS feed.


No comments yet.

Leave a comment

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>