IT Asset Management – Part 2 Usage Tracking

This is the second in a series of articles to help you improve your inventory management system. Read the first installment in the series IT Asset Management – Part 1 Catalog of Assets.

Usage Tracking

Usage data is a key part of an inventory management system. The data provide from usage tracking will help you:

• Determine the minimum on-hand inventory you need to maintain
• Help you set minimum inventory levels to trigger ordering
• Spot trends in usage that indicate the need for root cause analysis
• Give you snapshots in time of inventory usage

What Do You Need to Track?

Everything. Whatever you use, internal or external needs to be tracked. If you have it in inventory you should track it. If you order it you should track it. If you must maintain on-hand inventory you should track it. I also like to break down the data between our different internal and external customers. This shows what is going where and provides data that can be used in other reports such as for a particular customer.

Setup a Tracking Sheet

I am a spreadsheet fanatic so I use Excel to track usage. You can use data from your inventory list such as part/item number or just a description. There are several workbooks I use. They are formatted for ease of data entry and as automated as possible.

Monthly Tracking Workbook

Snapshot Sheet – this is a snapshot of data for the month. All of the data displayed is pulled from the other sheets in the workbook. It is intended to give you a snapshot of the usage activity for the month. This sheet list all items tracked broken down between the various internal and external customers with a total column of all customers combined for the month, for the year and the 12 week average.

Customer Daily Log Sheets – These are the daily log sheets for each internal and external customer.  I enter the daily usage for all items tracked according to what customer it was used for.

Shipping Sheet – I like to keep track of the shipping methods used each day. Overnight, Standard Overnight, Express Saver, Ground and internal.

Worksheet – I use this sheet to calculate the data for use in the weekly usage report. It is a simple copy of Monday through Friday’s data and pasted into a formula driven sheet that adds the week up. All I have to do is copy the total over to my weekly report. I also total the shipping methods for the week on this worksheet.

Fiscal Year Tracking Workbook

This workbook is an accumulation of the data from each monthly tracking sheet. I have tab sheets for each month of the fiscal year that list the totals broken down between internal and external customers, as well as the average usage for the month for each piece. The first tab sheet is a snapshot of the entire fiscal year using all of the year-to-date data from each month. Adding data to the sheet is as easy as copying and pasting the data from the monthly snapshot workbook into the monthly sheet.

Weekly Usage Report Workbook

This workbook is intended for consumption by upper management and other departments such as for root cause analysis. I also use this sheet to see what my current 12 week average usage is and plug those numbers into my main inventory projection sheet. That sheet (to be covered later) compares current inventory levels against current usage, adds in time from order to delivery and tells me what I need to order.

Snapshot Sheet – this is a snapshot of data for the week. All of the data displayed is pulled from the Monthly Tracking Workbook. It is intended to give you a snapshot of the usage activity for the week.This sheet list all items tracked broken down between the various internal and external customers with a total column for the week, for the year and the 12 week average.

Fiscal Year Snapshot – this sheet is the same snapshot sheet from the Fiscal Year Tracking Workbook. I copy and past the current data over to this sheet. It provides upper-management an up-to-date look at the fiscal year’s usage without having to distribute the entire workbook.

Weekly Totals – this sheet displays the total usage each week broken down between internal and external customers with a total at the end and a year to date total at the bottom.

12 Week Average – on this sheet I paste in the data from the total usage for all customers for one week. I track 12 weeks worth of data to come up with a 12 week average. You could track more weeks, but I am looking for current data to determine current usage trends for ordering purposes.

Top Ten Lists – on this sheet I display the top ten pieces used for the year based on the data on the already included Fiscal Year Snapshot sheet. It displays the top ten pieces used so far in the fiscal year along with the totals and weekly averages. The second top ten list is for the week. It is based on the data already included in the Snapshot Sheet for the week. It is the total usage from all customers, average daily usage for the week, the 12 week average for that piece and a percentage up or down against the 12 week average. I use conditional formatting to display the increased usage percentage in red and the decreased usage percentage in green.

In summary tracking usage data provides vital information used in other areas of asset management. It also helps spot trends which can trigger a root cause analysis of why a particular piece has had a spike in usage. Once you have your three workbooks setup with formulas in place it is then just a matter of entering the data on a daily basis. I track around 150 pieces a day using this method and it takes me about 10-15 minutes to enter all of the data.