Essay | Midterm Case: WAREHOUSE ACTIVITY PROFILING Warehousing and Distribution Management

1

Midterm Case: WAREHOUSE ACTIVITY PROFILING Warehousing and Distribution Management Dr. Nima Zaerpour, Operations & Supply Chain Management Department, California State University San Marcos Based on a case by Dr. John Bartholdi, Georgia Tech, Atlanta, USA and Dr. Rene De Koster, RSM Erasmus University, the Netherlands. Assignment Description

• Can be done in groups of 3. • Please read the case and submit your report (in Word or PDF format) at Cougar

Courses (Midterm folder). • Report (Word of PDF): It includes two parts:

o Part A.  In total, 8 questions must be answered while at least one question is answered

from each question group (0, 1, 2, 3). Questions are listed at the end of case at page 4.

 Choose your questions such that they give you a good impression about the operation and performance of the warehouse which helps you answer the questions in part B. The questions answered must be formulated explicitly and answers must be argued clearly, preferably in a graphical manner (e.g. using charts and graphs). You need to indicate how you arrived at the result (steps followed in Access). The clarity of presentation is explicitly weighed.

o Part B. Based on the analysis (refer back to answers in Part A!): what is interesting in this warehouse and what are weak and strong points? Do you see obvious improvements? What categories of improvement actions did you identify? Explain (minimum ¾ page).

• Grading o Part A scores 160 points and part B scores 90 points. In order to work out the

assignment, you need a computer with MS Access, Oracle, or another database tool with the possibility to generate queries.

o Note: For part A, only the first 8 questions will be graded, even if you submit answers to more questions.

2

Purpose and introduction Data mining ability, which is simply jargon for sifting through historical data for opportunities and insights that might confer advantage, is one of the most important skills of Logisticians. Opportunities for improvement (process improvements, assortment/inventory or supplier rationalizations, delivery performance etc.) stem from thorough analysis based on data available in the company. As the name suggests, there is a certain amount of luck involved, along with a knowledge of what to look for and how to search most efficiently. It is also important to have the right tools. Warehouse profiling is just a special case of data-mining. Warehouse profiling is necessary, especially in the design phase, to obtain such data as: what are the SKUs, how large are they, how many are there, where are they stored? It is also important to know data about orders and order lines: where are items picked, how large are the orders, how many orders can be picked in a single area, which items are the real fast movers, etc. It is part of standard design methods such as Muther’s Systematic Layout Planning. The data of most enterprises resides in large relational database management systems, such as Oracle, Informix, Sybase, and others. A well-known PC-based database system is MS Access. The data in a database is stored as a collection of tables, which are similar in some ways to huge spreadsheets: Every row describes some object, such as a SKU; and every column describes some aspect of the object, such as its name. Although it is usually not easy to obtain good data in a company, it usually is possible (with some effort), to obtain a SKU file and an order file. To mine data requires first that you manage large datasets. The main tool you will need is some program that will allow you to query multiple tables and to perform joins, which connect the data in one table with that in another through some common key. For example, if both the file of SKUs and the file of orders contain a field SKUs_id, then these two tables may be joined to form a new table that combines the data of the two. Now each order will contain, in addition to the SKUs_id, all the information about that SKUs from the SKUs file. In most companies, the stored data is not fully reliable. Usually there is some degree of internal corruption. Also, there might be a difference between reality and the information system (think of inventory accuracy). Therefore, you have to be keen on internal consistency.

3

Piece picking on the mezzanine (zone A/B) Order pick truck, picking large items pallet rack (zone G)

Case: A wholesale distributor of office products In this warehouse every item has a single location. Small stuff is in shelving upstairs on a mezzanine (zones A, B); larger cartons are downstairs in shelving (zones C, D); and the largest items are in pallet rack (zone G). Small, expensive items are picked from a security area (zone E). Data files On CC, two data files can be found, spr-lines.txt and spr-skus.txt. Check CC for the instructions of importing a txt file to MS Access. The file of SKUs descriptions (spr-skus) is in tab-delimited format and contains the following information:

• Sku_id: A tag that uniquely identifies each SKUs • Desc: A brief description of the product • Vendor: Abbreviation identifying the supplier of the SKUs • Zone: In which zone of the warehouse is the product stored • Aisle: In which aisle is the product stored. Note: import this field as text! • Bay: In which bay of rack is the product stored. Note: import this field as text! • Sell unit: The smallest level of packaging shipped to the customer (for example: EA: each, or

BX: box, or PK: overpack). • Pack_1: The finest level of packaging that the warehouse might handle • Pack_2: A coarser level of packaging

4

• Pack_3: A still coarser level of packaging • DOFT: Date of first transaction, to help identify new products (format: YYMMDD)

The file of orders (spr-lines) is in tab-delimited format and contains the following information:

• Order_id: A tag that uniquely identifies this order and customer • Date: The date the order was picked and shipped (YYMMDD) • Sku_id: A tag that uniquely identifies the SKUs. Note: This corresponds to a similar entry in

the file of SKUSs. • Order_qty: How many selling-units were requested by the customer in this order • Ship_qty: How many selling units were actually shipped to the customer

Questions Explore the warehouse. Amidst your explorations, discuss the following questions. 0. Start with some counting issues:

a) How many lines per order? (average, distribution) b) How many orders per working day? (average, distribution) c) How many units per line? (average, distribution)

1. Describe the SKUs

a) Where are the SKUs stored (percentage of number by zone, aisle)? b) Which are most popular SKUs, e.g. give top 10 most popular SKUs (how to measure

‘popularity’)? c) How many SKUs are never requested? d) How does the population of SKUs change over time?

2. Describe the work.

a) How much work is there and where is it? For example, how is it distributed among SKUs, among zones, among aisles, among bays, among orders, among vendors, among days, etc. (answer at least two of these)?

b) Which orders have more than 20 lines, e.g. give top 10 largest orders? What percentage of orders are for more than 20 lines?

c) What is the distribution of lines per order? d) What is the distribution of lines per order within each zone? What percentage of the total

number of orders do single-line orders represent? e) What percentage of orders touch the mezzanine (zones A, B). What percentage of orders touch

the bin-shelving on the floor (zones C, D), the security zone (E) and the pallet rack (G)? 3. Discrepancies

a) Measure the shipping discrepancies, in how many orders or in how many order-lines you observe discrepancies (quantity shipped < quantity ordered)?

b) Which SKUs show how many of such discrepancies, e.g. give the top 10 SKUs with largest discrepancies?