Team Case Assignment: Product Classification for Spend Analysis

Question

Team Case Assignment: Product Classification for Spend Analysis

Introduction

A volunteer Humanitarian Assistance operation, Iraq Displaced Relief (IDR), has been operating in Balad, Iraq since the beginning of the year supporting displaced people in Iraq. IDR is a relatively new non-profit organization focused on providing basic medical supplies and healthcare to refugees in war-torn countries. The organization's venture into Iraq is a new experience in the complexities of running a complicated supply chain, even on a very small scale. You have been asked to help analyze Iraq Displaced Relief's expenditures to see if there are ways that their supply chain can be simplified so that IDR may better leverage their total spend. Your analysis will form the basis of a report to be shared not only with the leadership of IDR but also with many of their key charitable contributors.

The group's health and welfare expenses -- the day-to-day needs required to live in an austere environment such as Balad -- are all funded through charitable donations of cash or commodities from church groups located across the United States. In Balad, Iraq Displaced Relief distributes medical supplies from the donated warehouse and office space. IDR purchases necessary medical supplies and distributes configured packages to a number of local Iraqi agencies. The local agencies deliver the items to villages throughout central Iraq. The majority of funding for the medical supplies comes from a number of charitable trusts from the Midwest United States. Although automation support is severely limited, the group does keep track of its expenditures using a rudimentary excel spreadsheet. IDR's Director of Purchasing, a volunteer herself, has provided you with an Excel spreadsheet listing the top expenditures for the last month.

Details on the spreadsheet:

The spreadsheet lists the top expenditures for medical supplies for the month of August 2009. The table includes information on the Source of the Material, the Part number that is used to track the item, a short Product description, the number of purchases and the total dollar value spent on that item for the month.

The Stock-keeping unit or Part Number used by IDR is an internally derived number, which represents the Vendor's Identification number. Although an alpha character was originally designed to signify a specific vendor, the alpha character now is only used to signify the type of material. SKUs starting with A through E represent items generally considered to be medical or surgical in nature -- covering everything from bandages to surgical gloves to sutures. SKUs starting with F are generally regarded as pharmaceutical items. SKUs starting with M characterize either durable items (items that can be reused many times) or equipment items.

The alpha characters are not shown in the spreadsheet for your team exercise. For your team assignment, categorize the items listed according to the scheme suggested in the previous bullet point. Add an alpha character to identify a product category as described above. You may have approximately five groups (A through E) representing items generally considered to be medical or surgical in nature. Please note that the "correct" solution has five categories.

Identify pharmaceutical items with an F and durable items or equipment with M. For example, Allegra-D, identified by 00088-1090-47 would be categorized F00088-1090-47, and so on for all of the other items.

The Vendor, Product Nomenclature (although often undecipherable except by IDR volunteers), the numbers of purchases, and the total value of purchases are generally considered to be accurate data.

 Spreadsheet Download  Solution Template File. This file should be used for this assignment.

Assignment Deliverable

Deliverable: The deliverable for this assignment is a spreadsheet file with your item classifications, labeled by an alpha character. Please turn in the spreadsheet sorted by SKU number.

Submit Assignment: Please have one team member submit your team's file into the M3L1 Dropbox: Team Project: Product Classification Exercise by clicking on the "Next" button at the bottom of this content page. Include your team number in the file name.