Thompson Industries’ supply chain manager, Xun Xu, wishes to better understand the quality and on-time performance of his supply base. He suggests that you, using the data in Figure G.5, use Excel to create a PivotTable that displays the number of suppliers, grouped by quality level (sorted largest-to-smallest), and subtotaled by percentage of on-time shipments (but only for suppliers with more than 50% on-time shipments). Show values as % of Column Total. What value appears in the cell for 100% on-time shipping percentage and quality level of 3?

Figure G.5

Transcribed Image Text:

A B 1 Thompson Industries Monthly Supplier Report - April 2 3 SupplierName 4 Maverick Supplies 5 Cowboy Pete's Lumber 6 ABC Metals 7 Perkins Plastics 8 Everything Brass 9 Jones Aluminum & Copper 10 Rick's Wrought Iron 11 Penelope's Paper Products 12 Anthony's Adhesives 13 Columbus Manufacturing 14 Smart Electronics, Inc. 15 Watson Woodworking 16 Fred's Foam Molding 17 Inpenetrable Insulation, Inc. 18 Suzy's Shipping Supplies SupNo 6071 6894 5054 5528 4413 5858 9733 4718 9944 1343 4785 2730 2949 1443 8039 SupCat P P S P P P S P P S P S S P P D Purchases $39,117 $25,554 $13,051 $27,886 $29,300 $16,862 $29,078 $25,716 $22,750 $20,103 $30,062 $32,589 $47,886 $24,971 $23,851 E Change $5,000 $400 $13,051 $3,256 ($4,658) $16,862 ($3,121) $8,795 $1,268 $469 $30,062 $471 $47,886 ($6,895) $1,269 Quality 5 5 3 4 5 4 1 3 4 4 2 5 5 4 5 On-Time 100% 90% 0% 100% 100% 50% 60% 100% 90% 100% 33% 25% 100% 15% 50% Figure G.5 Thompson Industries Spreadsheet after Cleaning the Data in Preparation for Pivot Table Analysis H Returns 0% 0% 75% 3% 0% 5% 80% 12% 4% 0% 50% 1% 0.50% 4% 0% RejShip 0 0 2 0 0 1 6 0 0 0 0 0 0 0 0 NewSup N N Y N N Y N N N N Y N Y N N