Inventory Key Performance Indicators (KPI), scorecard and dashboard

There is no comprehensive list of KPIs that can be specific to each type of industry. Caution is needed whenever an organization is identifying its performance measures. Measures drive employee behavior towards achieving the organizational goals. Organizations must understand the basic concepts that differentiate scorecards from dashboards and analytics, and KPIs from other business performance indictors.

What exactly is it? Developed by Kaplan & Norton in 1996, there are many definitions to it. Primarily, it is:

■ a strategic planning and management system used to align business activities to the vision and mission statements of an organization.
■ a strategic concept that involves creating a set of measurements from four perspectives - financial goals and performance, customer requirements and success factors, internal business processes and operational excellency, and learning and growth.

These four measurement perspectives must be linked, consistent and mutually reinforcing. Some of specific areas of measurements are:

■   Both represent a way to track results.
■   Both use traffic lights, dials, sliders and other visual aids.
■   Both have targets, thresholds and alert messages.
■   Both provide linkage or drill down to other metrics and reports.

The difference comes from the context in how they are applied. Scorecards are intended to be strategic. Dashboards are primarily for data visualization; they display what is happening during a time period. Most organizations begin with identifying what they are already measuring and construct a dashboard dial from there. However, dashboards do not communicate why something matters. In contrast, a scorecard does provide the information lacking in dashboards. A scorecard additionally answers questions by providing deeper analysis, drill-down capabilities, traffic light alert messaging, and forecasting for inferences of performance potential to determine motivational targets. Scorecards do not start with the existing data, but rather they begin with identifying what strategic projects to complete and core processes to improve and excel in. The selection and validation of the correct or best KPIs is a constant debate.

■ Scorecards chart progress toward strategic objectives. A scorecard displays periodic snapshots of performance associated with an organization’s strategic objectives and plans. It measures organizational activity at a summary level against pre-defined targets to see if performance is within acceptable ranges. Its selection of KPIs helps executives communicate strategy to employees and focuses users on the highest priority projects, initiatives, actions and tasks required to execute plans.

There are two key distinctions of scorecards: (1) Each KPI must require a predefined target measure; (2) KPIs should be comprised of both project-based KPIs (e.g., milestones, progress percentage of completion) and process-based KPIs (e.g., percent on-time delivery against customer promise dates).

■ Dashboards monitor and measure processes. A dashboard, on the other hand, is operational and reports information typically more frequently than scorecards and usually with measures.2 Each dashboard measure is reported with little regard to its relationship to other dashboard measures. Dashboard measures do not directly reflect the context of strategic objectives.

Goals Setting

Here are some guidelines on how you should go about setting goals. First, make sure you understand exactly what it is your measuring. What drives this measure? What causes failure? Where do you need improvement? Once you can answer these questions, you're in a better position to set your goals. Benchmarking is one other way to set your goals. Also, you should practice SMART goals which are:

Gross Margin Return On Investment	=	(GM% x sales turnover) / (1 - markup %)

Gross Margin Return On Inventory	=	[(Unit Selling Price of an Item - Unit Cost of an Item) X Annual Demand for the item] / Average Inventory Cost of the product

Weeks of Inventory (WOI)	=	Ending Inventory Balance
Weeks of Inventory (WOI)	=	Average weekly sales for a given period of time

Days of Inventory (DOI)	=	Ending Inventory balance
Days of Inventory (DOI)	=	(Average weekly sales for a given period of time) x 7

or alternatively,
Days of Inventory (DOI)	=	COGS/ 365 days (it gives you the number of days of stock on-hand)

Weeks of Inventory (WOI)	=	COGS/ 52 weeks (it gives you the number of weeks of stock on-hand)

Months of Inventory (WOI)	=	WOI/4.33
some call it Inventory Months of Supply
or alternatively,	=	Inventory On Hand / Average Monthly Usage

Sell through %	=	units sold / (units sold + on hand inventory)

Stock Sales Ratio	=	Beginning of month $ Stock / Sales for period

BTP Shrinkage	=	Difference between book and physical inventory

Table 12.1 below shows the several inventory KPI measurements over a period of 12 months. A company I went for an interview told me that his business unit was tied down with too much inventory dollars of more than $64 millions and they were experiencing a poor inventory turnover of less than 6.0 turns compared to 16 turns the supply chain was well-performing a few years ago. I made out a plan to help the supply chain to improve the inventory turnover in a steadily progressive manner from 5.0 turns to 16.0 turns, measuring from May-2008 up to April-2009. Together with the goal of inventory turnovers are also the plan to improve sales forecast accuracy, to lower the cost of goods sold while at the same time ensuring customer service levels are met continually without risk of stockout, to improve target gross profit margin, to reduce DOI (Days of inventory) and overall average inventory dollars cumulative saving.

Assuming time is into year 2009 and actual data are available for measuring inventory management performance. A considerable improvement in inventory turnover from 4.2 turns in May-08 to 16.2 turns in Apr-09 equate to a huge reduction in actual inventory of $50,740,000, or in another word, 81.5% of actual inventory dollars cumulative saving. Over the 12 months period it had also freed up $50.7 millions dollars in cash for the other important businesses. In terms of DOI, it also translates into a reduction in the average days of inventory on-hand from 73 days to 22.8 days. Even with a small 0.3 turns improvement from May-08 to July-08 had resulted in a reduction in average days of inventory on-hand from 73 days to 52.1 days, from the initial capital outlay of $64.24 millions in May-07 beginning inventory level. It was able to free up nearly 10 million dollars ($9.93 M) for other business investment over just a 3-months period. It is not enough to re-emphasize the power of maximizing inventory turns as you can see there were also simultaneous improvement efforts made in other areas of inventory control - beginning inventories, net purchases, ending inventories, cost of goods sold, month-over-month inventory $ cumulative saving.

With tightened control on purchasing which had reduced excess and surplus purchases, reduced safety stock level, or reduced the min. and max-order-quantities buying for the slow-usage materials, getting suppliers improved in supply lead time, and hence the costs of goods sold were able to reduce steadily over the measuring period. The lower COGS that was maintained with the different planning strategies, coupled with thje steadily increasing growth in sales revenue over the period, together had contributed to the higher gross profit margin from 2% to 36% over the 12 months.

For any level and list of inventory items, this inventory scorecard can be applied to different product lines, multi-location warehouses, different material classes and material categories, existing Service Level Agreement programs, and also for manufacturing site that has production plan with built-to-stock model.

The following is an good example of a dashboard that you can use to effectively track, measure, and evaluate the various categories of the inventory against predefined goals on a weekly basis instead of once every month which are normally too late to react to problems and underperforming results. It evaluates the three levels of inventories - accessible inventory, WIP inventory and the restricted inventory. It also includes the planning and purchasing areas of responsibilities which evaluate the many important issues on inventory productivity and operational efficiencies, and the KPIs in the last section including Customers Service Level, Inventory Turns, Gross Profit Margin and so on. When your organization has the monthly goals on inventory turns, gross profit margin and other KPIs, this weekly dashboard will allow you to timely track and evaluate all the underperforming KPIs before it becoming too late to do anything towards the end of the months.

Over-shipment value would have to be excluded from the inventory total value because that is the extra stock shipped out without being captured in the computer system. The units of measurement are in multiple of million $, percentage and number. The frequency of evaluating the performance is weekly and monthly for some KPI. The "traffic lights" color-coding symbols are used to measure the weekly performance status. I have developed an Excel VBA macro (generic enough and no hardcode) to automatically color-code the status in the column "Trend" so that managers don't need to waste unnecessary time on putting in the color symbols but they can use their valuable time to go through the misperformance with the various stakeholders. You can easily easily develop an Workbook_SheetBeforeDoubleClick Change Event code so that when you double-click on any of the performance measurement indicator (ie. any cell in second column under header "Measurement"), it will bring out the detailed worksheets where the different process owners had reviewed and based their weekly results on. To include such Analytics macro will save you much time because you can quickly and effectively go through the causes and effects analysis with the respective stakeholders. Ask all the stakeholders to save their analysis files in a single designated shared directory with specified subfolder names. This way it is a lot easier for you simply to assign a Constant to the designated directory path. If you need my help on writing the code for the analytics, you can write to me. Further down below is the syntax for this dashboard.

You can DOWNLOAD this dashboard worksheet as shown below which also has the code in VB Module.

When declaring your variables for cell values, positions of columns and rows, you want to make sure you are using the correct data types. If you have problem following my code below, you may want to refer my other page on creating If..Then..Else Conditional Statements and Loops Decision.

Below I give you a long list of the various Performance Indicators that are commonly used and some are the standard measurements being used across the various industries. You can study them, and decide how your people are going to effectively collect and munching raw data, setting criteria, automate analysis, and implement control plan before you want to include them in your process dashboard in addition to the KPIs I have shown you in table above. I segment them by the various business unit functions - Inventory, Order Fulfillment, Supply Chain, Procurement, Manufacturing, Production, Outsourcing, Shipping.

Inventory

Inventory is a list for goods and materials, or those goods and materials themselves, held available in stock by a business. Inventory are held in order to manage and hide from the customer the fact that supply delay is longer than delivery delay, and also to ease the effect of imperfections in the manufacturing process that lower production efficiencies if production capacity stands idle for lack of materials.

Inventory Service Level
Service Level KPI is to measure the level of service performed by Inventory or Stock Control in fulfilling their customer (user) requirement. It is reflected by the percentage of quantity or value fulfilled toward total quantity or value requested (demand) during the period of reporting.

Independent Demand Ratio
For manufacturers that also supply replacement parts and consumables this metric helps to define the % mix of demand for an item from independent (outside sources) vs dependent (inside sources). The ratio is calculated by dividing the unit usage for customer orders by the total unit usage of the item from all sources (work orders, sales samples, destructive testing, inventory adjustments, etc.).

Early Receipts to MRP Date (required date)
Early receipts to MRP date - This is a measure on your Planning efficiencies. Some planners or warehouse personnel may request that the material be brought in long before the plant/operators need the parts. Reasons for doing so may be quality, lead time variance, buffer stock etc. Early receipts to MRP produce higher levels of inventory that are not required yet. In a way, this is at the other end of the scale than JIT. Measure: MRP due date vs Receive to Dock (stores) date.

Early receipts to PO date - This is a measure on your suppliers and their diligence to supply per the contract date. Early receipts to PO produce unexpected deliveries turning up, congested goods inwards and of course higher that projected inventory levels. Measure: PO due date vs Receive to Dock (stores) date.

Inventory Lead Time
Lead time is the length of time it takes to obtain inventory from suppliers.

Inventory Accuracy

Most Advanced Planning Systems calculate net inventory requirements. If the book inventory used as the basis for these calculations has a high error, the net inventory requirements generated will not reflect the true inventory needs. The inventory error should be factored into the safety stock calculation to protect service levels from variance in inventory due to inventory count accuracy.

Assertive continuous improvement programs should be in place to support a decrease in inventory count errors.
The formula is: (book inventory - counted inventory)/book inventory

Average Age of inventory

The (average) age of each product in stock. For example, product received in Jan, but remains until Aug.

Unit Cost Per Batch
Unit Cost per batch = (Cost/Quantity) for each batch
Primarily used in FIFO (First In First Out) Method

Assumes an inventory of non-unique goods (that is, every one is similar to every other one) Generally preferred inventory valuation method. Assumes inventory is sold in the order that it is stocked, with the oldest goods sold first and the newest goods sold last. Uses the unit cost per batch of acquired/produced goods, and counts the inventory backwards from the newest batch.

Inventory Value
Inventory Value = Average Unit Cost x Units of Current Inventory

Inventory Carrying Cost
It is measure by Inventory Carrying Rate X Average Inventory Value. [ See this example ]

GMROI (Gross Margin Return on Inventory)
GMROI = [(Unit Selling Price of an Item - Unit Cost of an Item) X Annual Demand for the item] / Average Inventory Cost of the product.

Note that Unit Inventory Value tells you what it costs you to make the product. The Unit Selling Price - Unit Inventory Value tells you the Margin.

Stock Cover
Stock cover is the length of time that inventory will last if current usage continues.

Obsolescence for Raw Material, WIP, and Finished Goods Inventory
Inventory reserves taken due to obsolescence and scrap and includes products exceeding the shelf life, i.e. spoils and is no good for use in its original purpose (do not include reserves taken for Field Service Parts).

Field Service Parts Obsolescence
Reserves taken due to obsolescence and scrap. Field Service Parts are those inventory kept at location usually outside Manufacturing plant i.e., distribution center or warehouse.

Go To Top

Order Fulfillment

Fill Rates (Order Fill Rate, Line Item Fill Rate, Unit Item Fill Rate)
It calculates the service level between 2 parties and is a measurement of shipping performance expressed as a percentage of the total order. It calculates the amount of order lines shipped on the initial shipment versus the amount of lines ordered, and has to take into consideration the requested delivery date.

Order Fill Rate or Service Level
It is measured as Orders filled/Orders Requested. The percentage of your deliveries to customer delivered in full (ie. number of orders, lines, or units delivered in full divided by the total number of orders customer requested). For example: 5 lines (=10 units) not delivered in full from one order of 10 lines (=200 units) = 0% Order Service Level.

Line-Item Fill Rate
Total Line items filled / Total Lines shipped in time per order per period. For example: 5 lines (=10 units) not delivered in full from one order of 10 lines (=200 units) = 50% Line-Fill Service Level.

Unit-Item Fill Rate
Total Units items filled / Total Units shipped in time per order per period. For example: 5 lines (=10 units) not delivered in full from one order of 10 lines (=200 units) = 95% Unit-Fill Service Level.

Lines Late to Promise
Lines shipped after promised ship date

Lines Late to Schedule
Lines shipped after scheduled ship date

Order Fulfillment Cycle Time
Order Fulfillment Cycle Time is a continuous measurement defined as the amount of time from customer authorization of a sales order to the customer receipt of product.

Customer order Promised Cycle Time
The anticipated or agreed upon cycle time of a Purchase Order. It is gap between the Purchase Order Creation Date and the Requested Delivery Date. This tells you the cycle time that you should expect (NOT the actual).

Number of deliveries with past due goods issue date
Number of Deliveries where the Goods Issue date (date it should leave the premises to reach customer on time based on transit time) is equal to or less than today’s date.

Perfect Order Measure / Fulfillment
The error-free rate of each stage of an order. Error rates are captured at each stage (order entry, picking, delivery, shipped without damage, invoiced correctly) and multiplied together.

% of Backorders
The number (or percentage) of unfulfilled orders. A backorder is an unfilled customer order, or demand (immediate or past due) against an item whose current stock level is insufficient to satisfy demand. Can also measure Aged Backorders which are backorders in past-due time buckets based on the Requested Delivery Date/Requested Ship Date.

Net Booked Order Value
Value of booked order lines less the value of booked return lines for the sale of products but not services.

Past Due Schedule Lines
Number of booked order lines with past due scheduled ship date.

Past Due Schedule Value
Value of open order lines with past due scheduled ship date.

Sell Through %
It refers to the percentage of the inventory received from factory or distribution depot that is actually sold to the consumer.
Percentage of units sold during a period = units sold / (units sold + on hand inventory).
This can also be described as Units sold divided by Beginning Inventory Qty.

On-Time Ship Rate

What percent of orders where shipped on or before the requested ship date. On time ship rate can be calculated on a line item, SKU, case or value basis.

Go To Top

Supply chain

Days Inventory Outstanding (DIO)
It measures cash investment tied up in inventory that will impact Working Capital. A decrease is an improvement, an increase a deterioration. DIO is closely tie with Inventory Turns metric. DIO is generally good if Inventory Turns numbers are increasing.
DIO = Inventory / (Net Sales / 365)

Note:
Many companies use Cost of Goods Sold instead of Net Sales when calculating DPO and DIO.

Days Sales Outstanding (DSO)
Sellers want to shorten their cash conversion cycle by measuring DSO. It measures the average number of days that a company takes to collect revenue after a sale has been made. A low DSO number means that it takes a company fewer days to collect its accounts receivable. A high DSO number shows that a company is selling its product to customers on credit and taking longer to collect money. Thus, a decrease in DSO represents an improvement, an increase a deterioration.
      DSO = Account Receivables / (Net Sales / 365)

Days Payable Outstanding (DPO)
It measures the total time it takes a business to pay back its creditors. Buyers want to delay payment for as long possible by extending DPO.         DPO = Account Payables / (Net Sales / 365)

Days Working Capital (DWC)
Days of Working Capital (or the overall cash conversion cycle ) is the average number of days a firm takes to convert working capital into sales revenue. The fewer the number of days, the more efficient the use of working capital. Thus, the lower the number of days, the better.     DWC = ( Account Receivables + Inventory – Days Payable Outstanding) / (Net Sales / 365)

% of time spent picking back orders
Number of hours spent on picking back orders as a percentage of working hours.

Sales order by FTE
This indicator measures the number of customer orders that are processed by full time employees per day. This helps evaluate the workforce cost per order.

Scrap value %
Scrap value as a percentage of production value.

Manufacturing Schedule Adherence
   Manufacturing Schedule Adherence = (|Scheduled Production - Actual Production|)/Scheduled Production.
which is the absolute variance of actual production to scheduled production.

When manufacturing over or under produces against plan, either service level or inventory investment is adversely affected.
Assertive continuous improvement programs should be in place to decrease the variance.

Inventory Accuracy
Most Advanced Planning Systems calculate net inventory requirements. If the book inventory used as the basis for these calculations has a high error, the net inventory requirements generated will not reflect the true inventory needs. The inventory error should be factored into the safety stock calculation to protect service levels from variance in inventory due to inventory count accuracy.

Assertive continuous improvement programs should be in place to support a decrease in inventory count errors. Formula is:
(book inventory - counted inventory)/book inventory

Inventory Carrying Costs
Inventory Carrying Cost = Inventory Carrying Rate X Average Inventory Value [ see above ]

Inventory Carrying Rate
This can best be explained by the example below…. 1. Add up your annual Inventory Costs: Example: $800k = Storage, $400k = Handling, $600k = Obsolescence, $800k = Damage, $600k = Administrative, $200k = Loss (pilferage etc), and $3,400k Total

1. Divide the Inventory Costs by the Average Inventory Value: Example: $3,400k / $34,000k = 10%
2. Add up your: 9% = Opportunity Cost of Capital (the return you could reasonably expect if you used the money elsewhere) 4% = Insurance 6% = Taxes 19%
3. Add your percentages: 10% + 19% = 29% Your Inventory Carrying Rate = 29%

Direct Product Cost
Sum of costs associated with manufacturing a specific product.

Direct Labor Cost
Sum of costs associated with payment of employees, insurances, taxes, etc.

Direct Material Cost
Sum of costs associated with acquisition of raw material and support material.

Cost of goods sold (COGS)
Cost of Goods Sold includes all expenses directly associated with the production of goods or services the company sells (such as material, labor, overhead, and depreciation).

Cost of managing processes

Periodic costs of managing processes, usually based on the number of FTEs involved in management functions for processes.

Total Supply Chain Management Cost
Total Supply Chain Management Cost is a discrete measurement defined as the fixed and operational costs associated with the Plan, Source, Make, and Deliver supply chain processes.

Time needed to recruit/hire/train additional labor
Amount of time required to achieve a certain substantial improvement concerning the number of employees.

Missed Deliveries per Million (MPM)
Measures supplier on time delivery by part reference ordered using the same logic as the quality measure PPM.
Several missed categories are defined such as ; Missing part reference, under-shipped, over-shipped, delivery window missed etc.
(Total number of missed deliveries / Total number of part references ordered) x 1,000,000

Delivery Schedule Adherence (DSA)
Delivery Schedule adherence (DSA) is a business metric used to calculate the timeliness of deliveries from suppliers. Delivery schedule adherence is calculated by dividing the number of on time deliveries in a period by the total number of deliveries made. The result is then multiplied by 100 and expressed as a percentage.

Manufacturing Cycle Time
Measured from the Firm Planned Order until the final production is reported. It usually takes into account the original planned production quantity verses the actual production quantity.

Inventory Replenishment Cycle Time
Measure of the Manufacturing Cycle Time plus the time included to deploy the product to the appropriate distribution center.

Cash To Cash Cycle Time
The number of days between paying for raw materials and getting paid for the product by the customer.

Upside Supply Chain Flexibility
Upside Supply Chain Flexibility is a discrete measurement defined as the amount of time it takes a supply chain to respond to an unplanned 20% increase in demand without service or cost penalty.

Go To Top

Procurement

Procurement refers to the overall process of acquiring a product or service. Depending on the circumstances, it may include some or all of the following: identifying a need, specifying the requirements to fulfill the need, identifying potential suppliers, soliciting bids and proposals, evaluating bids and proposals, awarding contracts or purchase orders, tracking progress and ensuring compliance, taking delivery, inspecting and inventorying the deliverable, and paying the supplier.

Procurement spend per procurement employee
Average procurement spend per procurement employee (in FTE).

% of suppliers that are responsible for 80% of spend
Percentage of suppliers that are responsible for 80% of spend on these suppliers.

Number of non-conformities during vendor inspection
Suppliers defect rate can be calculated by dividing number of defective items by the total number of items purchased (or the number of shipments with defects by the total number of shipments). This KPI measures the quality of purchases made, or in another way, to measure the performance of different vendors.

Requisition to item issuance time
To measure the delay between the time when a procurement request is initiated and the time when the request is fulfilled (expressed in terms of days).

Travel & entertainment costs as % of gross margin
Travel and entertainment costs as a percentage of gross margin.

% of (preferred) suppliers not used in last 12 months
Percentage of (preferred) suppliers not used in last 12 months (or any other given period).

% of On-Time Supplier Deliveries.
Percentage of procurement requests satisfied by the preferred supplier list.

% of Supplier Deliveries in Full (Line-Fill and Unit-Fill)
The percentage of your supplier deliveries is delivered in full (ie. number of orders, lines, or units delivered in full divided by the total number of supplier orders.) An example: 5 lines (=10 units) not delivered in full from one order of 10 lines (=200 units) = 0% Order Service Level, 50 % Line-Fill Service Level and 95% Unit-Fill Service Level.

Proportion of purchases made from contracts/frameworks
Proportion/percentage of purchases made from contracts/frameworks.

% of payable invoices without purchase order
Percentage of payable invoices that have not been matched to a purchase order.

Total negotiated cost reduction savings
Total negotiated cost reduction savings during the procurement process within the measurement period.

Value of Supplier Idea Implementation
Advanced procurement organizations actively solicit ideas from suppliers for cost savings, revenue growth, and the like. They track these ideas and measure the contribution to the organization's bottom line. This KPI measures how well the procurement department leverages the intellect in the supply base.

Procurement Cycle Time
The average time it takes between requisition submission and purchase order placement is one measure of procurement cycle time. Another type of procurement cycle time that can be measured is the time it takes from the beginning of a sourcing process to the time that a contract is signed.

Travel & entertainment costs as % of increased revenue
Travel and entertainment costs as a percentage of increased revenue from new orders and sales. To understand whether a business is gaining true value from its investment in travel.

Average number of responses received to RFPs
Average number of responses received to Request for Proposals (RFP).

Average size of discounts of items
Average size of discounts in procurement of items.

Go To Top

Outsourcing

% Actual vs. Estimated Savings
Percentage of actual or realized savings on spend against a negotiated contract vs the estimated spend determined during the sourcing initiative.

% of time sheets in need of correction/validation
Percentage of time sheets in need of correction and/or validation by submitter.

Vendor/client quality calibration variance
Vendor/client calibration variance identifies the difference between client and vendor quality scoring, is calculated as (VendorQualityResult - ClientQualityResult) / ClientQualityResult.

% of vendor services delivered without agreed service targets
Percentage of vendor services delivered without agreed service targets.

% of (major) suppliers subject to monitoring
Percentage of (major) suppliers subject to monitoring.

% of user complaints due to contracted services
Percentage of user complaints on contracted services as a percentage of all user complaints.

% of invoices disputed
Percentage of invoices disputed.

Number of formal disputes with suppliers
Number of formal disputes with suppliers

% of key stakeholders satisfied with suppliers
Percentage of key stakeholders satisfied with their suppliers.

Go To Top

Freight cost per unit shipped
Calculated by dividing total freight costs by number of units shipped per period. Useful in businesses where units of measure are standard (e.g., pounds). Can also be calculated by mode (barge, rail, ocean, truckload, less-than-truckload, small package, air freight, intermodal, etc.)

Outbound freight costs as percentage of net sales
Calculated by dividing outbound freight costs by net sales. Most accounting systems can separate "freight in" and "freight out." Percentage can vary with sales mix, but is an excellent indicator of the transportation financial performance.

Inbound freight costs as percentage of purchases
Calculated by dividing inbound freight costs by purchase dollars. It is important to understand the underlying detail. The measurement can vary widely, depending on whether raw materials are purchased on a delivered, prepaid, or collect basis.

Transit time
Measured by the number of days (or hours) from the time a shipment leaves your facility to the time it arrives at the customer's location. Often measured against a standard transit time quoted by the carrier for each traffic lane. Unless you are integrated into your customers' systems, you will have to rely on freight carriers to report their own performance. This is often an important component of lead time. Transit times can vary substantially, based on freight mode and carrier systems.

Claims as % of freight costs
Calculated by dividing total loss and damage claims by total freight costs. Generally measured in total and for each carrier. A high number generally indicates packaging problems, or process problems at the carrier.

Freight bill accuracy
Calculated by dividing the number of error-free freight bills by the total number of freight bills in the period. Errors can include incorrect pricing, incorrect weights, incomplete information, etc. Generally measured in total and for each carrier. Formula is: [number of error-free freight bills] / [total number of freight bills]

Accessorials as percent of total freight
Calculated by dividing accessorial and surcharges by total freight expenditures for the period. Many freight carriers will charge extra fees for trailer detention/demurrage, re-delivery, fuel increases, and other expenses or extra services. Often, these are extra costs incurred due to inefficient processes.

Percent of truckload capacity utilized
Generally used for shipments over 10,000 lbs. Calculated by dividing the total pounds shipped by the theoretical maximum. For example, assume your trucks can hold 40,000 lbs. of product. During the prior month, there were 675 shipments totaling 22.95MM lbs. The percentage utilization was 85%. The 15% unused capacity is an opportunity for more efficiency.

Mode selection vs. optimal
This is calculated by dividing the number of shipments sent via the optimal mode by the total number of shipments for the period. To measure this, each traffic lane must have a designated optimal mode, based on freight costs and customer service requirements.

Truck turnaround time
This is calculated by measuring the average time elapsed between a truck's arrival at your facility and its departure. This is an indicator of the efficiency of your lot and dock door space, receiving processes, and shipping processes. This also directly affects freight carrier profits on your business.

Shipment visibility/traceability percent
Calculated by dividing the total number of shipments via carriers with order tracking systems, by the total number of shipments sent during a period. This is an indicator of the relative sophistication of your carrier base, and one measure of the non-price value available from your carrier base.

Number of carriers per mode
Calculated by counting the total number of freight carriers used in a given period, by mode (ocean, barge, rail, intermodal, truckload, LTL, small package, etc.). This is an indication of your volume leverage and control over the transportation function.

On-time pickups
Calculated by dividing the number of pick-ups made on-time (by the freight carrier) by the total number of shipments in a period. This is an indication of freight carrier performance, and carriers' affect on your shipping operations and customer service.

Fill Rate
Calculates the service level between 2 parties, is a measure of shipping performance expressed as a percentage of the total order. It measures the amount of order lines shipped on the initial shipment versus the amount of lines ordered. This measure has to take into consideration the requested delivery date (except if the receiving party does not accept early shipments).

On-time delivery - value %
On-time value rate: value of order lines shipped on-time / total value of the order.

On-time delivery - line count
On-time line count: amount of order lines shipped on-time relative to the amount of lines ordered.

Internal cycle time Receipt of material to GRN/MRR
Efficiency in warehouse operations hinge on quick turn around of activities and ensuring visibility of material tracking by creating Goods receipt note or material receipt report timely in system.

Number of deliveries with past due goods issue date
Number of Deliveries where the Goods Issue date (date it should leave the premises to reach customer on time based on transit time) is equal to or less than today’s date.

% of undamaged goods after shipping/transportation
Percentage of undamaged goods after shipping/transportation.

Average time of import or export transaction
Average time needed to process and complete an import or export transaction.

Empty miles
Percentage of miles (trips) that carried no inventory/freight.

Shrinkage Value
The costs associated with breakage, pilferage, and deterioration of inventories. Usually pertains to the loss of material through handling damage, theft, or neglect.

Go To Top

Production

Scrap value %
Scrap value as a percentage of production value.

Manufacturing cycle time
Measured from the Firm Planned Order until the final production is reported. It usually takes into account the original planned production quantity verses the actual production quantity.

Defects Per Million Opportunities (DPMO)
DPMO is a Six Sigma calculation used to indicate the amount of defects in a process per one million process opportunities.
      DPMO = Total Number of Defects / Total Number of Process Opportunities for a Defect) x 1,000,000

Defects Per Opportunities (DPO)
      DPO = Total Number of Defects / Total Number of Process Opportunities for a Defect

Process Yield (%)
Formula is:   (100 - defects percentage)%

Average production costs of items
Average production costs of items produced within measurement period.

Mean-Time Between Failure (MTBF)
The average time between equipment failures over a given period i.e. the average time a device will function before failing. It is the reliability rating indicating the expected failure rate of equipment. It is the reliability rating indicating the expected failure rate of equipment. More is better.

Mean Time To Repair (MTTR)
Average time (e.g. in hours) between the delivery of an assembly, SKU for Finished Goods out for repair and its resolution.

Finished product cycle time
Average time associated with finalizing activities, such as: package, stock, etc.

Schedule production activities cycle time
Average time associated with scheduling the production activities.

Go To Top

Manufacturing

Utilization
Utilization is the measurement ratio of the Actual Production Output to its Design Capacity, that is required to produce one unit of output. In reality the line cannot be run continuously at its maximum Design Capacity because of the need for line maintenance, changeover, stoppages, loading, etc. What remains, after such loss times are accounted for, are called the Effective Capacity.
      Utilization = Actual Output/Design Capacity

Efficiency
Efficiency is the ratio of the Actual Production Output achieved to its Effective Capacity.
      Efficiency = Actual Output/Effective Capacity

Equipment quality
      Equipment quality = Good Pieces / Total Pieces

Equipment performance
      Equipment performance = Ideal Cycle Time / (Operating Time / Total Pieces)   or,
      Equipment performance = (Total Pieces / Operating Time) / Ideal Run Rate

Equipment availability
       Equipment availability = Operating Time / Planned Production Time

Overall Equipment Effectiveness (OEE)
      Overall Equipment Effectiveness (OEE) = Availability x Performance x Quality
      Availability = Operating Time / Planned Production Time
      Performance = Ideal Cycle Time / (Operating Time / Total Pieces)
or,
      Performance = (Total Pieces / Operating Time) / Ideal Run Rate
      Quality = Good Pieces / Total Pieces

Manufacturing Schedule Adherence (MSA)
Manufacturing Schedule Adherence = (|Scheduled Production - Actual Production|)/Scheduled Production. The absolute variance of actual production to scheduled production.
When manufacturing over or under produces against plan, either service level or inventory investment is adversely affected.
Assertive continuous improvement programs should be in place to decrease the variance.

Manufacturing cycle time
Measured from the Firm Planned Order until the final production is reported. It usually takes into account the original planned production quantity verses the actual production quantity.

Takt Time
In simple words, Takt Time is the pace of production needed to meet customer demand. It is not cycle time. It is the available daily work time, taking into account the shifts worked and making allowances for planned stoppages (for planned maintenance, team briefings, breaks) divided by the anticipated daily sales rate or demand (including spare parts) plus any extras such as test parts and anticipated scrap.
Takt Time = Net Available Time per Day / Customer Demand per Day

% lost manufacturing capacity
Percentage of lost manufacturing capacity due to e.g. technical problems and/or failures.

Water used per amount of product manufactured
Water (in m3) used per amount (in e.g. tonne) of product manufactured.

Rate of rejects
Measures the quality and uniformity of the final product.

Unit costs of the operations
Ratio of actual to projected unit production costs.

Dim i As Long, j As Long, cogs As Long, lastrow As Long, measure As Byte, exec As Byte, freq As Byte, gol As Byte, tren As Byte, lastmth As Byte, lastmth_2 As Byte, lastwk As Byte, Measurement As Long, Execute As String, Frequency As String, Goal As Single, Month As Single, Month_2 As Single, Week As Single, Week_m2 As Single, Week_m3 As Single, Week_m4 As Single, Week_m5 As Single, Trend As String, cell As Range, symbol As String, Week_last3 As Single

With ActiveSheet
Columns(2).Cells.Find(What:="COGS*", After:=[B1]).Select
cogs = ActiveCell.Row
lastrow = Cells(65536, 2).End(xlUp).Row
lastmth = .Cells(5, .Columns.Count).End(xlToLeft).Column
lastmth_2 = Cells(5, lastmth).End(xlToLeft).Column
lastwk = .Cells(8, .Columns.Count).End(xlToLeft).Column
Rows("6:6").Cells.Find(What:="Measure*", After:=[A6]).Select
measure = ActiveCell.Column
Rows("6:6").Cells.Find(What:="Exe*", After:=[A6]).Select
exec = ActiveCell.Column
Rows("6:6").Cells.Find(What:="Freq*", After:=[A6]).Select
freq = ActiveCell.Column
Rows("6:6").Cells.Find(What:="Goal*", After:=[A6]).Select
gol = ActiveCell.Column
Rows("6:6").Cells.Find(What:="Trend", After:=[A6]).Select
tren = ActiveCell.Column
End With

' ' ' measuring to meet Goal or Max threshold (>,< or =)
For i = 8 To lastrow
Measurement = Cells(i, measure).Row
Execute = Cells(i, exec).Value
Frequency = Cells(i, freq).Value
Goal = Cells(i, gol).Value
Trend = Cells(i, tren).Value
Month = Cells(i, lastmth).Value
Month_2 = Cells(i, lastmth_2).Value
Week = Cells(i, lastwk).Value
Week_m2 = Cells(i, lastwk).Offset(, -1).Value
Week_m3 = Cells(i, lastwk).Offset(, -2).Value
Week_m4 = Cells(i, lastwk).Offset(, -3).Value
Week_m5 = Cells(i, lastwk).Offset(, -5).Value
Week_last3 = Application.Sum(Cells(i, lastwk).Offset(, -2).Resize(, 3))

On Error Resume Next
If IsEmpty(Execute) Then
    Trend = ""
ElseIf Execute = "N" And IsEmpty(Week_last3) Then
    Trend = ""
ElseIf Execute <> vbNullString Then
    If Measurement <= cogs Then
        If Execute = "Y" And Frequency = "W" Then   ' if it is a KPI that has been traking and is weekly update
            If Week <= Goal Then
                Trend = ChrW(9786)
            ElseIf Week > Goal Then
                If Week > Week_m2 Then
                    Trend = ChrW(9650)
                ElseIf Week > Week_m2 And Week_m2 > Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week < Week_m2 And Week_m2 > Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week < Week_m2 And Week_m2 < Week_m3 Then
                    Trend = ChrW(9660)
                ElseIf Week = Week_m2 And Week_m2 < Week_m3 And Week_m3 < Week_m4 Then
                    Trend = ChrW(9660)
                ElseIf Week = Week_m2 And Week_m2 = Week_m3 And Week_m3 = Week_m4 Then
                    Trend = ChrW(9650)
                End If
            ElseIf Week = vbNullString And Week_m2 > Goal Then
                If Week_m2 > Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 > Week_m3 And Week_m3 > Week_m4 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 < Week_m3 And Week_m3 > Week_m4 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 < Week_m3 And Week_m3 < Week_m4 Then
                    Trend = ChrW(9660)
                ElseIf Week_m2 = Week_m3 And Week_m3 < Week_m4 And Week_m4 < Week_m5 Then
                    Trend = ChrW(9660)
                End If
            ElseIf Week = vbNullString And Week_m2 = vbNullString Then
                Trend = ""
            End If
        ElseIf Execute = "Y" And Frequency = "M" Then
            If Month = vbNullString Then
                If Month_2 > Goal Then
                    Trend = ChrW(9650)
                ElseIf Month_2 <= Goal Then
                    Trend = ChrW(9786)
                End If
            ElseIf Month <> vbNullString Then
                If Month > Goal Then
                    Trend = ChrW(9650)
                ElseIf Month <= Goal Then
                    Trend = ChrW(9786)
                End If
            End If
        ElseIf Execute = "N" And Frequency = "W" Then 'if it's a weekly KPI and not tracked
            If Not IsEmpty(Week) Then
                Trend = ChrW(9673)
            Else: Trend = ""
            End If
        ElseIf Execute = "N" And Frequency = "M" Then 'if it is a monthly KPI and not tracked
            If Month <> vbNullString Then
                Trend = ChrW(9673)
            Else: Trend = ""
            End If
        End If
    ElseIf Measurement > cogs Then
        If Execute = "Y" And Frequency = "W" Then   ' if it is a KPI that has been traking and is weekly update
            If Week >= Goal Then
                Trend = ChrW(9786)
            ElseIf Week < Goal Then
                If Week < Week_m2 Then
                    Trend = ChrW(9650)
                ElseIf Week < Week_m2 And Week_m2 < Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week > Week_m2 And Week_m2 < Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week > Week_m2 And Week_m2 > Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week = Week_m2 And Week_m2 > Week_m3 And Week_m3 > Week_m4 Then
                    Trend = ChrW(9660)
                ElseIf Week = Week_m2 And Week_m2 = Week_m3 And Week_m3 = Week_m4 Then
                    Trend = ChrW(9650)
                End If
            ElseIf Week = vbNullString And Week_m2 < Goal Then
                If Week_m2 > Week_m3 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 < Week_m3 And Week_m3 < Week_m4 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 > Week_m3 And Week_m3 < Week_m4 Then
                    Trend = ChrW(9650)
                ElseIf Week_m2 > Week_m3 And Week_m3 > Week_m4 Then
                    Trend = ChrW(9660)
                ElseIf Week_m2 = Week_m3 And Week_m3 > Week_m4 And Week_m4 > Week_m5 Then
                    Trend = ChrW(9660)
                End If
            ElseIf Week = vbNullString And Week_m2 = vbNullString Then
                Trend = ""
            End If
        ElseIf Execute = "Y" And Frequency = "M" Then
            If Month = vbNullString Then
                If Month_2 < Goal Then
                    Trend = ChrW(9650)
                ElseIf Month_2 >= Goal Then
                    Trend = ChrW(9786)
                End If
            ElseIf Month <> vbNullString Then
                If Month < Goal Then
                    Trend = ChrW(9650)
                ElseIf Month >= Goal Then
                    Trend = ChrW(9786)
                End If
            End If
        End If
    End If
Else
End If
Cells(i, tren).Value = Trend
Next i

For Each cell In Range("H8:H100")
symbol = cell.Value
Select Case symbol
    Case Cells(1, 1)
        cell.Interior.ColorIndex = 4 'Green
    Case Cells(2, 1)
        cell.Interior.ColorIndex = 3 'Red
        Case Cells(3, 1)
        cell.Interior.ColorIndex = 6 'Yellow
    Case Else
        cell.Interior.ColorIndex = -4142
End Select
Next