GSC-5600 BlueStar Office Supply Company Read the pdf file and use the data in the Excel file to answer the questions below. Ignore the questions at the end of the case study pdf file. Write a concise report. Using bullet points in the answer is acceptable but your answers need to be complete and coherent, not requiring further explanation. The write-up should be prepared in MS Word or equivalent. You may use MS Excel or Access to do the analysis. In Excel, you should use pivot tables since it will make your job very easy. Questions 1. Who are the top 5 TL carriers (in terms of total spent)? (5 points) 2. What are the top 5 origin-destination pairs for TL shipments? Use both total spent and frequency of shipments as two different metrics. Are the two lists the same? Why or why not? (15 points) 3. How many TL carriers currently serve each of the top 5 TL origin-destination pairs? (You may use any of the metrics, e.g. total spent or frequency of shipments) Do you have any suggestions for risk mitigation based on the number of carriers currently serving the top TL origin-destination pairs? (15 points) 4. What is the average shipment size and length of haul for each carrier type? Based on these averages, do you have any general suggestions? (20 points) 5- For each carrier type, calculate the averages for on-time delivery, damage-free delivery, billing accuracy and cost and comment on these numbers. (30 points) a. Based on your calculations, do you have any recommendations to improve transportation planning for Blue Star? Explain the way how these suggestions would be actualized? b. How would you deal with billing accuracy, on time delivery and damaged goods issues? 6- Do you see any other opportunities for cost savings? Provide examples. (You may focus on top origin- destination pairs) (15 points) Notes about data file: I. The following columns do not exist in the file, although mentioned in the pdf file: SCAC, Carrier Name, Preaudit II. There are some extra columns in the file. Below are the definitions: Column Heading Carrier Code On-time Origin Destination Pair Delivered-Complete Damage-Free Billed Accurately Carrier Type Definition. A unique identifying code for each transportation company 1 means delivered on time, 0 means delivered late Origin city and destination city as a pair 1 means delivered complete, 0 means delivered incomplete 1 means delivered without damage, 0 means delivered with damage 1 means no error in billing, 0 means error in billing Type of the carrier. SP stands for Small package, TL: Truck load, LTL: Less than truck load Council of Supply Chain CSCMP Management Professionals The World's Leading Source for the Supply Chain Profession." 2012 ACADEMIC CASE STUDY SERIES BlueStar Office Supply Company By: Rodney Thomas, Ph.D. Assistant Professor Georgia Southern University Stephen Rutner, Ph.D. Professor Georgia Southern University Christopher Boone, Ph.D. Assistant Professor Georgia Southern University *Acknowledgment: The authors would like to thank Dr. Karl Manrodt for the data set utilized in this case study. Council of Supply Chain Management Professionals 333 East Butterfield Road, Suite 140 Lombard, Illinois 60148 USA +1 630.574.0985 education@cscmp.org cscmp.org Introduction BlueStar Office Supply Company BlueStar is a well established office supply company with annual revenue of $950M. The company sources, manufactures, and markets a variety of products ranging from paper clips to file folders. BlueStar serves thousands of customer locations throughout the United States and Canada. The company supplies numerous items to major retailers, distributors, wholesalers, small business owners, and consumers. Key customer accounts include major retailers like OfficeMax, Office Depot, and Walmart. In addition to these large retailers, BlueStar also sells directly to commercial accounts for companies like IBM, Toyota, and Boeing. If an individual works in any type of office environment, he likely uses BlueStar products on a daily basis. Supply Chain Status Although BlueStar has popular products and a large market share, there have been a growing number of problems with BlueStar's supply chain. Customer service levels have been dropping and costs have been increasing. As a result, corporate sales have stagnated and product margins have steadily declined. This poor financial performance has the executive leadership team very concerned about the BlueStar supply chain. In order to address these problems, the company has hired Katherine Powell as the new Executive Vice President of Supply Chain Management. She has been challenged to dramatically improve the BlueStar supply chain and return the company to a leadership position in the industry. The CEO and board members want to see both immediate and long term improvement. Therefore, Katherine is feeling the pressure to quickly deliver tangible results. Katherine was formally trained as an undergraduate in Industrial Engineering at the Georgia Institute of Technology and received her MBA from Stanford University with a concentration in Operations Management. She has held leadership positions in several leading consulting firms and helped numerous corporate clients improve their operations capabilities. With this type of background, Katherine has successfully worked in a number of production and distribution facilities throughout the world and established a reputation as a thought leader in manufacturing strategy. Given her personal expertise, Katherine would like to focus her efforts on improving BlueStar's production facilities and regional distribution centers. However, she also thinks there are tremendous opportunities for improvement in her transportation group. Transportation Opportunities In your initial meeting, Katherine explained why transportation is such an important component of the BlueStar supply chain. From a product cost perspective, transportation represents a relatively large percentage of the total landed cost due to the low value and high density nature of many office products (i.e., file folders). Another issue that exacerbates the relatively high transportation costs of BlueStar products is fuel prices. In the current environment where fuel surcharges from carriers must be accepted, the percentage of product costs associated with transportation expenditures continues to rise. In fact, transportation costs now account for over 30% of product costs at BlueStar. If transportation costs are not significantly reduced, then BlueStar products will not remain price competitive in the marketplace and sales will decline. Therefore, transportation cost reduction is a key priority for the company. Given the increased corporate scrutiny on transportation costs, the BlueStar transportation group attempted to reduce costs over the past year. The transportation group decided to move away from established core carrier relationships and long term contracts. Instead, BlueStar placed every single load on every single lane up for bid. This change was designed to ensure carriers were offering the best price possible on every shipment. Unfortunately, this open bidding process did not have the desired effects and it caused several additional problems. 2 3 By moving away from utilizing their traditional core carriers and placing all loads up for bid, BlueStar awarded some business to new carriers that did not understand the service requirements for key customers. This lack of understanding and customer focus resulted in late shipments, extended delivery times, and dissatisfied customers. The problem was made worse because customers sometimes received loads from different carriers on each shipment. Therefore, the opportunity to collaborate and develop a good working relationship was limited. As these service issues continued, BlueStar sales began to stagnate and key customers began to evaluate other office products suppliers. In addition to the customer service problems, last year's change to an open bidding process also resulted in price increases from most of the old core carriers. Apparently, the carriers were unable to plan for demand on established lanes and unable to efficiently stage equipment as needed. In order to pick up BlueStar loads, some carriers had to dramatically increase their empty miles and incur additional costs. Plus, by spreading loads around to numerous carriers, economies of scale were lost. Without sufficient volume, the traditional core carriers were simply unable to offer their normal prices. Transportation Strategy Development Once she assumed her new role at BlueStar, Katherine Powell set up meetings with each key area in the supply chain. She wanted to understand the challenges each functional area faced and learn more about their approach to problem solving. Although all areas of the supply chain appeared to have opportunities to improve, it was clear that the BlueStar transportation group needed outside expertise and direction. Katherine was not impressed in her initial meetings with the managers in the BlueStar transportation group. She was frustrated and complained that: "No one can even give me a basic summary of the business or typical metrics that a good transportation manager should know. They cannot tell me high level things like how much we spend each year, where we spend our money, who are our top carriers, where we ship most of our freight, what modes we use, etc. At a more tactical level, they don't know basic things like what is the average length of haul or average shipment weight for each mode of transportation. If they cannot tell me these kinds of simple 4 things, I hate to think about how much money we are wasting and how much risk we have in critical areas. " Based on her lack of confidence in the internal transportation group, Katherine contacted your school for help and is looking for your team to analyze and synthesize some of the BlueStar freight payment data in order to develop a revised transportation strategy. At a minimum, she wants summary information about overall transportation spending, basic metrics for each mode of transportation, cost savings opportunities, and risk assessments. In her closing remarks to your group she said: "Let me make this simple. Tell me what I need to know, tell me what I need to do, and tell me what we can improve. I need to reduce costs and improve service. Give me tangible, data driven recommendations that are actionable." Directions Your task is to review the attached freight payment data, transform the data into useful information, and address Katherine Powell's concerns about the BlueStar transportation group. As you work on this project, please make certain to demonstrate a thorough understanding of transportation spending, identify cost saving opportunities, address service levels issues, and assess transportation related risks. As you structure your approach to this project, please consider some of the following types of sample questions: 1. How much does BlueStar annually spend on transportation? 2. What are some of the key metrics for LTL shipments? 3. What are the top origin-destination pairs for LTL shipments? 4. How many carriers currently serve the top TL origin-destination pairs? 5. What is the average shipment size and length of haul for each mode? 6. Who are the top TL carriers? 7. What are your criteria for LTL consolidation? 5 In the freight payment data file, there will be column headings with abbreviations or acronyms. In order to help you interpret the data, BlueStar has provided the following definitions for each column heading: Ship Mo Ship Date Column Heading SCAC Carrier Name BlueStar Definition A unique identifying code for each transportation company Name of each transportation company The month of a shipment in numerical format (i.e. January = 1) Date of the actual shipment Origin City City where a shipment initially started Origin State State where a shipment initially started Origin Zip Dest City Dest State Dest Zip Weight Miles Preaudit $ Frt Paid $ The zip code where a shipment initially started The city where a shipment was finally delivered The state where a shipment was finally delivered The zip code where a shipment was finally delivered The weight of a shipment in pounds The number of miles travelled between origin and destination cities The amount charged to BlueStar for a shipment by a carrier The amount BlueStar actually paid a carrier for a shipment 9 S37 fx A B C D E F G H J K L M N P Q R S 1 Carrier Code Ship Mo# Ship Date Origin City Origin State Origin Zip Dest City Dest State Dest Zip Origin Destination Pair Weight Miles Freight Paid On-Time Delivered Complete Damage-Free Billed Accurately Carrier Type 2 FTPC 3 3/1/12 ATLANTA GA 30269 CARROLLTON TX 75006 ATLANTA-CARROLLTON 42360 779 $ 2,235.73 0 1 1 1 LTL 3 FTPC 3 3/1/12 ATLANTA GA 30269 CARROLLTON TX 75006 ATLANTA-CARROLLTON 43370 779 $ 2,235.73 0 1 1 1 LTL 4 FTPC 3 3/1/12 ATLANTA GA 30269 CRANBURY NJ 08512 ATLANTA-CRANBURY 42113 801 $ 2,899.62 1 1 0 1 LTL 5 FTPC 3 3/1/12 ATLANTA GA 30269 CRANBURY NJ 08512 ATLANTA-CRANBURY 43371 801 $ 2,899.62 0 1 1 1 LTL 6 FTPC 3 3/1/12 ATLANTA GA 30269 NEENAH WI 54956 ATLANTA-NEENAH 43359 903 $ 3,268.86 0 1 1 1 LTL 7 HMES 3 3/1/12 PAINESVILLE OH 44077 WINNIPEG, MANITOBA R3J 3X4 PAINESVILLE-WINNIPEG, MANITOBA 480 1800 $ 8 WWAT 3 3/1/12 PAINESVILLE OH 44077 QUEBEC H4R 2J5 PAINESVILLE-QUEBEC 200 650 $ 9 PITD 3 3/1/12 LOWELL IN 46356 MILTON, ONTARIO L9T 3J2 LOWELL-MILTON, ONTARIO 940 450 $ 3,596.16 3,681.00 3,705.96 1 1 1 0 LTL 1 1 1 0 LTL 0 1 1 0 LTL 10 HMES 3 3/1/12 RANCHO CUCAMONGA CA 91730 KELOWNA, BC V1X-2K1 RANCHO CUCAMONGA-KELOWNA, BC 583 1200 $ 4,161.00 1 1 0 1 LTL 11 CRSE 3 3/1/12 RANCHO CUCAMONGA CA 91730 MONROVIA CA 91016 RANCHO CUCAMONGA-MONROVIA 11213 25 $ 652.00 1 1 1 1 TL 12 PYLE 3 3/1/12 ATLANTA GA 30269 FLOWERY BRANCH GA 30542 ATLANTA-FLOWERY BRANCH 10973 108 $ 652.00 0 1 1 1 TL 13 CRSE 3 3/1/12 FT WAYNE IN 46808 SKOKIE IL 60076 FT WAYNE-SKOKIE 11083 211 $ 652.00 1 1 1 1 TL 14 CRSE 3 3/1/12 FT WAYNE IN 46808 BLOOMINGDALE IL 60108 FT WAYNE-BLOOMINGDALE 11573 195 $ 652.00 1 1 1 1 TL 15 CRSE 3 3/1/12 FT WAYNE IN 46808 VERNON HILLS IL 60061 FT WAYNE-VERNON HILLS 11307 211 $ 652.00 1 0 1 1 TL 16 CRSE 3 3/1/12 CARROLLTON TX 75006 BOSSIER CITY LA 71111 CARROLLTON-BOSSIER CITY 13785 186 $ 652.00 1 1 1 1 TL 17 TCSR 3 3/1/12 CHICOPEE MA 01020 FRAMINGHAM MA 01702 CHICOPEE-FRAMINGHAM 16279 80 $ 652.00 1 1 1 0 TL 18 ROEV 3 3/1/12 FT WAYNE IN 46808 HOWELL MI 48843 FT WAYNE-HOWELL 13605 150 $ 652.00 1 1 1 1 TL 19 WSKT 3 3/1/12 CHICOPEE MA 01020 SOMERSWORTH NH 03878 CHICOPEE-SOMERSWORTH 10301 160 $ 652.00 0 1 1 1 TL 20 ODFL 3 3/1/12 ATLANTA GA 30269 CHATTANOOGA TN 37422 21 WSKT 3 3/1/12 PAINESVILLE OH 44077 FRANKFORT OH 45628 22 TNT1 3 3/1/12 NEENAH WI 54956 MATTESON IL 60443 ATLANTA-CHATTANOOGA PAINESVILLE-FRANKFORT NEENAH-MATTESON 12252 143 $ 652.00 1 1 1 1 TL 10298 212 $ 767.44 1 1 1 1 TL 12473 217 $ 785.54 1 1 1 1 TL 23 PYLE 3 3/1/12 PAINESVILLE OH 44077 MILTON PA 17847 PAINESVILLE-MILTON 10605 280 $ 1,013.60 1 1 1 0 TL 24 HJBT 3 3/1/12 FT WAYNE IN 46808 ST. PETERS MO 63376 FT WAYNE-ST. PETERS 11975 411 $ 1,179.57 1 1 1 1 TL 25 WSKT 3 3/1/12 RANCHO CUCAMONGA CA 91730 GILROY CA 95020 RANCHO CUCAMONGA-GILROY 10460 327 $ 1,183.74 1 1 1 1 TL 26 CRSE 3 3/1/12 CONLEY GA 30027 LONGWOOD FL 32750 CONLEY-LONGWOOD 12050 418 $ 1,199.66 1 1 1 1 TL 27 SAIA 3 3/1/12 GREENSBORO NC 27409 HUBER HEIGHTS OH 45424 GREENSBORO-HUBER HEIGHTS 12238 442 $ 1,268.54 1 1 1 1 TL 28 SAIA 3 3/1/12 RANCHO CUCAMONGA CA 91730 PHOENIX AZ 85040 RANCHO CUCAMONGA-PHOENIX 14372 353 $ 1,277.86 1 1 1 1 TL 29 CRSE 3 3/1/12 PAINESVILLE OH 44077 LOUISVILLE KY 40228 PAINESVILLE-LOUISVILLE 10493 353 $ 1,277.86 1 1 0 1 TL 30 CRSE 3 3/1/12 RANCHO CUCAMONGA CA 91730 STOCKTON CA 95215 RANCHO CUCAMONGA-STOCKTON 11208 354 $ 1,281.48 1 1 1 1 TL 31 ODFL 3 3/1/12 RANCHO CUCAMONGA CA 91730 LODI CA 95240 RANCHO CUCAMONGA-LODI 12134 354 $ 1,281.48 1 1 1 1 TL 32 PYLE 3 3/1/12 ATLANTA GA 30269 JACKSON MS 39212 ATLANTA-JACKSON 11112 378 $ 1,368.36 1 1 1 1 TL 33 PYLE 3 3/1/12 RANCHO CUCAMONGA CA 91730 HAYWARD CA 94544 RANCHO CUCAMONGA-HAYWARD 10599 382 $ 1,382.84 1 1 1 1 TL 34 CRSE 3 3/1/12 PAINESVILLE OH 44077 ROME NY 13440 PAINESVILLE-ROME 14161 390 $ 1,411.80 1 1 1 1 TL 35 TCSR 3 3/1/12 HAMILTON OH 45011 PEACHTREE CITY GA 30269 HAMILTON-PEACHTREE CITY 14990 492 $ 1,412.04 1 1 1 1 TL 36 CRSE 3 3/1/12 MENTOR OH 44060 QUAKERTOWN PA 18951 MENTOR-QUAKERTOWN 11693 398 $ 1,440.76 1 1 0 1 TL 37 FAKF 3 3/1/12 ATLANTA GA 30269 FORT WAYNE IN 46808 ATLANTA-FORT WAYNE 43695 625 $ 1,793.75 1 1 1 1 TL 38 CRSE 3 3/1/12 CARROLLTON TX 75006 DES MOINES IA 50321 CARROLLTON-DES MOINES 11562 648 $ 1,859.76 1 1 1 1 TL 39 CRCR 3 3/1/12 RANCHO CUCAMONGA CA 91730 SALT LAKE CITY UT 84125 RANCHO CUCAMONGA-SALT LAKE CITY 14312 670 $ 1,922.90 1 1 1 1 TL 40 CRSE 3 3/1/12 CARROLLTON TX 75006 PEACHTREE CITY GA 30269 CARROLLTON-PEACHTREE CITY 11553 779 $ 2,235.73 1 1 1 1 TL 41 ARFW 3 3/1/12 GREENSBORO NC 27409 MIAMI FL 33122 GREENSBORO-MIAMI 13467 783 $ 2,247.21 1 1 1 1 TL 42 CRSE 3 3/1/12 PAINESVILLE OH 44077 RIVERSIDE MO 64150 PAINESVILLE-RIVERSIDE 33755 808 $ 2,399.76 1 1 1 1 TL 43 CRSE 3 3/1/12 RANCHO CUCAMONGA CA 91730 LYNNWOOD, WA 98036 RANCHO CUCAMONGA-LYNNWOOD, 11953 1163 $ 2,407.41 1 1 1 1 TL 44 HJBT 3 3/1/12 ATLANTA GA 30269 CRANBURY NJ 08512 ATLANTA-CRANBURY 42009 801 $ 2,899.62 1 1 1 1 TL 45 PYLE 3 3/1/12 RANCHO CUCAMONGA CA 91730 LUBBOCK TX 79404 RANCHO CUCAMONGA-LUBBOCK 11053 1063 $ 3,848.06 1 1 0 0 TL 46 WSKT 3 3/1/12 PAINESVILLE OH 44077 COSTA MESA CA 92626 47 TCSR 3 3/1/12 RANCHO CUCAMONGA CA 91730 HAMILTON OH 45011 48 CRSE 3 3/1/12 KENT WA 98031 PEACHTREE CITY GA 30269 PAINESVILLE-COSTA MESA RANCHO CUCAMONGA-HAMILTON KENT-PEACHTREE CITY 8483 2344 $ 4,148.88 1 1 1 1 TL 18520 2137 $ 4,423.59 1 1 1 0 TL 11567 2639 $ 49 WSKT 3 3/1/12 HAMILTON OH 45011 SAINT JOHN, NB E2R 1A3 HAMILTON-SAINT JOHN, NB 8873 1600 $ 50 ODFL 3 3/1/12 RANCHO CUCAMONGA CA 91730 AJAX, ONTARIO L1S-2 G3 RANCHO CUCAMONGA-AJAX, ONTARIO 37508 2400 $ 5,462.73 6,312.00 7,968.00 1 1 1 1 TL 1 1 1 0 TL 1 1 1 1 TL 51 EUSA 3 3/2/12 KENT WA 98031 VERONA MS 38879 KENT-VERONA 69 2379 $ 52 EUSA 3 3/2/12 LOWELL IN 46356 SAN ANTONIO TX 78212 LOWELL-SAN ANTONIO 80 1195 $ 897.08 1,158.00 0 1 0 0 AIR 0 1 1 0 AIR 53 RKS1 3 3/2/12 KNOXVILLE TN 37921 KING NC 27021 KNOXVILLE-KING 8121 217 $ 22,332.75 1 1 1 0 AIR AN AUTO ou Lacon RICCICCALICA ON HAMILTON MICCISCALICA ON 3505 - 3 Shipment Data +