EXECEL

Performance Lawn Equipment (PLE) - Read the file description in pages 31-32 and answer the following questions.Your manager, Elizabeth Burke, has received several questions from other managers regarding On-time delivery, Process costs, and employee retention. Using statistical tools and analysis, she would like you to provide her with some answers using the following files below.

1. In the worksheet, On-Time Delivery, has the proportion of on-time deliveries in 2018 significantly improved since 2014?
2. Although engineering has collected data on alternative process costs for building transmissions in the worksheet Transmission Costs, why didn't they reach a conclusion as to whether one of the proposed processes is better than the current process?

1199 1201 1212 1 On-Time Delivery 2 3 Month Number of deliveries Number On Time Percent 4 Jan-14 1085 1069 98.4% 5 Feb-14 1101 1080 98.1% 5 Mar-14 1116 1089 97.6% 7 Apr-14 1216 98.6% 8 May-14 1183 1168 98.7% 9 Jun-14 1176 1160 98.6% 10 Jul 14 1198 1181 98.6% 11 Aug-14 1205 1189 98.7% 12 Sep-14 1223 1210 98.9% 13 Oct-14 1209 1194 98.8% 14 Nov. 14 1198 1180 98.5% 15 Dec-14 1243 1223 98.4% 16 Jan-15 1220 98.4% 17 Feb-15 1241 1224 98.6% 18 Mar-15 1237 1217 98.4% 19 Apr-15 1258 1242 98.7% 20 May-15 1262 1246 98.7% 21 Jun-15 1227 98.8% 22 Jul-15 1243 1227 98.7% 23 Aug-15 1281 1264 98.7% 24 Sep-15 1272 1254 98.6% 25 Oct-15 1295 1278 98.7% 26 Nov-15 1298 1281 98.79 27 Dec-15 1318 1296 9823% 28 Jan-16 1281 1264 98.7% Feb-16 1320 1304 98.8% 30 Mar-16 1352 1334 98.7% 31 Apr-16 1338 1320 98.8% 32 May-16 1291 1276 98.8% 33 Jun-16 1342 1326 98.8% 34 Jul-16 1352 1337 98.9% 35 Aug-16 1377 1360 98.8% 36 Sep-16 1385 1368 98.8% 37 Oct-16 1356 1338 98.7% 32 Nov-16 1362 1346 98.8% 39 Dec-16 1349 1333 98.8% 40 Jan-17 1371 98 9% Feb-17 1358 1342 98.8% 42 Mar-17 1371 1356 98.9% 43 Apr 17 1362 1348 99.0% 44 May-17 1350 1338 99.1% 45 Jun-17 1381 1366 98.9% Jul-17 1392 1378 99.0% 47 Aug-17 1371 1350 99.1% 48 Sep-17 1402 1387 98.9% 49 Oct-17 1384 1370 99.00 50 Nov. 17 1999 1377 98.4 51 Dec-17 1309 1357 99.1% 52 Jan-18 1401 1390 992 53 Feb-18 1388 1376 99 1 54 Mar. 18 99 3% 55 Apr-18 1412 1401 99.2% 56 May 18 1403 1392 99.2% Jun-18 1415 1402 99.1% SE Jul 18 1426 1415 92% 59 Aug.18 1431 1420 99 2% 60 Sop-18 1445 1426 98.7% 61 Oct 18 1425 1414 99.2% 62 Nov-18 1413 1400 99.3% Dec-18 1456 1427 SON RR XXX 1388 1395 CASE: PERFORMANCE LAWN EQUIPMENT ce pudines analytic tools le le al of at 5. . 25 en In each chapter of this book, we use a a fictitious com- pany, Performance Lawn Equipment (PLE), within a case exercise for applying the tools and techniques introduced in the chapter. To put the case in perspective, we first provide some background about the company, so that the applications of business analytic tools will be more meaningful PLE, headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. In the past ten years, PLE has added another key product, a medium-size diesel power lawn trac- tor with front and rear power takeoffs. Class I three-point hitches, four-wheel drive, power steering, and full hydrau- lics. This equipment is built primarily for a niche market consisting of large estates, including golf and country clubs. resorts, private estates, city parks, large commercial com- plexes, lawn care service providers, private homeowners with five or more acres, and government (federal, state, and local) parks, building complexes, and military bases. PLE provides most of the products to dealerships, which in turn, sell directly to end users. PLE employs 1,660 people world- wide. About half the workforce is based in St. Louis: the remainder is split among their manufacturing plants. In the United States, the focus of sales is on the east- ern seaboard, California, the Southeast, and the south central states, which have the greatest concentration of customers. Outside the United States, PLE's sales include a European market, a growing South American market, and developing markets in the Pacific Rim and China. Both end users and dealers have been established as important customers for PLE, Collection and analysis of end-user data showed that satisfaction with the products depends on high quality, easy attachment/dismount of implements, low maintenance, price value, and service, For dealers, key requirements are high quality, parts and feature availability, rapid restock, discounts, and timeli- ness of support PLE has several key suppliers: Mitsitsiu, Inc., the sole source of all diesel engines: LANTO Axles, Inc., which provides tractor axles: Schorst Fabrication, which provides subassemblies; Cuberillo, Inc, supplier of trans- missions: and Specialty Machining, Inc., a supplier of precision machine parts, To help manage the company, PLE managers have developed a "balanced scorecard" of measures. These data, which are summarized shortly, are stored in the form of a Microsoft Excel workbook (Performance Lawn Equipment) accompanying this book. The database con- tains various measures captured on a monthly or quar- terly basis and is used by various managers to evaluate business performance. Data for each of the key measures are stored in a separate worksheet. A summary of these worksheets is given next: Dealer Satisfaction, measured on a scale of 1-5 (1 = poor, 2 = less than average, 3 = average, 4 = above average, and 5 = excellent). Each year, dealers in each region are surveyed about their overall satisfaction with PLE. The worksheet contains sum- mary data from surveys for the past five years. End-User Satisfaction, measured on the same scale as dealers. Each year, 100 users from each region are surveyed. The worksheet contains summary data for the past five years. Customer Survey, results from a survey for customer rat- ings of specific attributes of PLE tractors: quality, case of use, price, and service on the same 1-5 scale. This sheet contains 200 observations of customer ratings FO be d he olulita 32 Chapter 1 Introduction to Business Anayece . Employee Resention, data from u study of employee duration dength of hire) with PLE The 40 subjects were identified by reviewing hires from ten years prior and identifying those who were involved in managerial positions (either hired into management promoted into management) at some time in this ten year period - Shipping cost, which gives the limit sipping cou for mowers and tractors from existing and proposed plants for a supply chain design study Fixed Cost, which lists the fixed cost to expand exist ing plants or build new facilities, also as part of the supply chain design study, Purchasing Survey, which provides data obtained from a third-party survey of purchasing managers of customers of Performance Lawn Care - Comants, which shows the number of complaints registered by all customers each month in each of PLE's five regions (North America, South America Europe, the Pacific, and China) Mower Unit Sales and Tractor Unit Sales, which pro vide sales by product by region on a monthly basis Unit sales for each region are aggregated to obtain world sales figures Industry Mower Total Soler and Industry Tractor Total Sales, which list the number of units sold by all producers by region Unit Production Costs, which provides monthly account ing estimates of the variable cost per unit for mangfactor ing tractors and mowers over the past five years Operating and rulerest Expenses, which provides monthly administrative depreciation, und interest expenses at the corporate level On Time Delivery, which provides the number of deliveries made each month from each of PLE's major supplies, the number on time, and the percent on time Defects After Delivery, which shows the number of defects in supplier provided material found in all ship- ments received from suppliers - Time to Pay Suppliers, which provides measurements in days from the time the invoice is received until pay- ment is sent Response Time, which gives samples of the times taken by PLE customer service personnel to respond to service calls by quarter over the past two years. Employee Sarbfaction, which provides data for the past four years of internal surveys of employees to determine their overall satisfaction with their jobs, using the same scale used for customers. Employees are surveyed quarterly, and results are stratified by employee category: design and production, manage rial, and sales administrative support Elizabeth Burke has recently joined the PLE man agement team to oversee productiod operations. She has reviewed the types of data that the company.collects and has assigned you the responsibility to be her chief analyst in the coming weeks. She has asked you to do some pre- liminary analysis of the data for the company 1. First, she would like you to edit the worksheets Dealer Satisfaction and End-User Satisfaction to display the total number of responses to each level of the survey scale across all regions for each year, 2. Second, she wants a count of the number of failures in the worksheet Mower Test 3. Next, Elizabeth has provided you with prices for PLE products for the past five years Year 2014 2015 2016 2017 2018 Mower Price Tractor Price $150 $3,250 $175 $3,400 $180 $3,600 $185 $3,700 $190 $3,800 In addition to these business measures, the PLE data- base contains worksheets with data from special studies Engines, which lists 50 samples of the time required to produce a lawn mower blade using a new technology Transmission Costs, which provides the results of 30 samples each for the current process used to pro- duce tractor transmissions and two proposed new processes Blade Weight, which provides samples of mower blade weights to evaluate the consistency of the pro duction process Mower Test, which lists te results of mower fune tional performance after assembly for 30 samples of 100 units each Create a new worksheet to compute gross revenues by month and region, as well as worldwide totals, for cach product using the data in Mower Unit Sales and Tractor Unit Sales 4. Finally, sbo wants to know the market share for each product and region by month based on the PLE and industry sales data, and the average market share by region over the five years. Summarize all your findings in a report to Ms. Burke 1199 1201 1212 1 On-Time Delivery 2 3 Month Number of deliveries Number On Time Percent 4 Jan-14 1085 1069 98.4% 5 Feb-14 1101 1080 98.1% 5 Mar-14 1116 1089 97.6% 7 Apr-14 1216 98.6% 8 May-14 1183 1168 98.7% 9 Jun-14 1176 1160 98.6% 10 Jul 14 1198 1181 98.6% 11 Aug-14 1205 1189 98.7% 12 Sep-14 1223 1210 98.9% 13 Oct-14 1209 1194 98.8% 14 Nov. 14 1198 1180 98.5% 15 Dec-14 1243 1223 98.4% 16 Jan-15 1220 98.4% 17 Feb-15 1241 1224 98.6% 18 Mar-15 1237 1217 98.4% 19 Apr-15 1258 1242 98.7% 20 May-15 1262 1246 98.7% 21 Jun-15 1227 98.8% 22 Jul-15 1243 1227 98.7% 23 Aug-15 1281 1264 98.7% 24 Sep-15 1272 1254 98.6% 25 Oct-15 1295 1278 98.7% 26 Nov-15 1298 1281 98.79 27 Dec-15 1318 1296 9823% 28 Jan-16 1281 1264 98.7% Feb-16 1320 1304 98.8% 30 Mar-16 1352 1334 98.7% 31 Apr-16 1338 1320 98.8% 32 May-16 1291 1276 98.8% 33 Jun-16 1342 1326 98.8% 34 Jul-16 1352 1337 98.9% 35 Aug-16 1377 1360 98.8% 36 Sep-16 1385 1368 98.8% 37 Oct-16 1356 1338 98.7% 32 Nov-16 1362 1346 98.8% 39 Dec-16 1349 1333 98.8% 40 Jan-17 1371 98 9% Feb-17 1358 1342 98.8% 42 Mar-17 1371 1356 98.9% 43 Apr 17 1362 1348 99.0% 44 May-17 1350 1338 99.1% 45 Jun-17 1381 1366 98.9% Jul-17 1392 1378 99.0% 47 Aug-17 1371 1350 99.1% 48 Sep-17 1402 1387 98.9% 49 Oct-17 1384 1370 99.00 50 Nov. 17 1999 1377 98.4 51 Dec-17 1309 1357 99.1% 52 Jan-18 1401 1390 992 53 Feb-18 1388 1376 99 1 54 Mar. 18 99 3% 55 Apr-18 1412 1401 99.2% 56 May 18 1403 1392 99.2% Jun-18 1415 1402 99.1% SE Jul 18 1426 1415 92% 59 Aug.18 1431 1420 99 2% 60 Sop-18 1445 1426 98.7% 61 Oct 18 1425 1414 99.2% 62 Nov-18 1413 1400 99.3% Dec-18 1456 1427 SON RR XXX 1388 1395 CASE: PERFORMANCE LAWN EQUIPMENT ce pudines analytic tools le le al of at 5. . 25 en In each chapter of this book, we use a a fictitious com- pany, Performance Lawn Equipment (PLE), within a case exercise for applying the tools and techniques introduced in the chapter. To put the case in perspective, we first provide some background about the company, so that the applications of business analytic tools will be more meaningful PLE, headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. In the past ten years, PLE has added another key product, a medium-size diesel power lawn trac- tor with front and rear power takeoffs. Class I three-point hitches, four-wheel drive, power steering, and full hydrau- lics. This equipment is built primarily for a niche market consisting of large estates, including golf and country clubs. resorts, private estates, city parks, large commercial com- plexes, lawn care service providers, private homeowners with five or more acres, and government (federal, state, and local) parks, building complexes, and military bases. PLE provides most of the products to dealerships, which in turn, sell directly to end users. PLE employs 1,660 people world- wide. About half the workforce is based in St. Louis: the remainder is split among their manufacturing plants. In the United States, the focus of sales is on the east- ern seaboard, California, the Southeast, and the south central states, which have the greatest concentration of customers. Outside the United States, PLE's sales include a European market, a growing South American market, and developing markets in the Pacific Rim and China. Both end users and dealers have been established as important customers for PLE, Collection and analysis of end-user data showed that satisfaction with the products depends on high quality, easy attachment/dismount of implements, low maintenance, price value, and service, For dealers, key requirements are high quality, parts and feature availability, rapid restock, discounts, and timeli- ness of support PLE has several key suppliers: Mitsitsiu, Inc., the sole source of all diesel engines: LANTO Axles, Inc., which provides tractor axles: Schorst Fabrication, which provides subassemblies; Cuberillo, Inc, supplier of trans- missions: and Specialty Machining, Inc., a supplier of precision machine parts, To help manage the company, PLE managers have developed a "balanced scorecard" of measures. These data, which are summarized shortly, are stored in the form of a Microsoft Excel workbook (Performance Lawn Equipment) accompanying this book. The database con- tains various measures captured on a monthly or quar- terly basis and is used by various managers to evaluate business performance. Data for each of the key measures are stored in a separate worksheet. A summary of these worksheets is given next: Dealer Satisfaction, measured on a scale of 1-5 (1 = poor, 2 = less than average, 3 = average, 4 = above average, and 5 = excellent). Each year, dealers in each region are surveyed about their overall satisfaction with PLE. The worksheet contains sum- mary data from surveys for the past five years. End-User Satisfaction, measured on the same scale as dealers. Each year, 100 users from each region are surveyed. The worksheet contains summary data for the past five years. Customer Survey, results from a survey for customer rat- ings of specific attributes of PLE tractors: quality, case of use, price, and service on the same 1-5 scale. This sheet contains 200 observations of customer ratings FO be d he olulita 32 Chapter 1 Introduction to Business Anayece . Employee Resention, data from u study of employee duration dength of hire) with PLE The 40 subjects were identified by reviewing hires from ten years prior and identifying those who were involved in managerial positions (either hired into management promoted into management) at some time in this ten year period - Shipping cost, which gives the limit sipping cou for mowers and tractors from existing and proposed plants for a supply chain design study Fixed Cost, which lists the fixed cost to expand exist ing plants or build new facilities, also as part of the supply chain design study, Purchasing Survey, which provides data obtained from a third-party survey of purchasing managers of customers of Performance Lawn Care - Comants, which shows the number of complaints registered by all customers each month in each of PLE's five regions (North America, South America Europe, the Pacific, and China) Mower Unit Sales and Tractor Unit Sales, which pro vide sales by product by region on a monthly basis Unit sales for each region are aggregated to obtain world sales figures Industry Mower Total Soler and Industry Tractor Total Sales, which list the number of units sold by all producers by region Unit Production Costs, which provides monthly account ing estimates of the variable cost per unit for mangfactor ing tractors and mowers over the past five years Operating and rulerest Expenses, which provides monthly administrative depreciation, und interest expenses at the corporate level On Time Delivery, which provides the number of deliveries made each month from each of PLE's major supplies, the number on time, and the percent on time Defects After Delivery, which shows the number of defects in supplier provided material found in all ship- ments received from suppliers - Time to Pay Suppliers, which provides measurements in days from the time the invoice is received until pay- ment is sent Response Time, which gives samples of the times taken by PLE customer service personnel to respond to service calls by quarter over the past two years. Employee Sarbfaction, which provides data for the past four years of internal surveys of employees to determine their overall satisfaction with their jobs, using the same scale used for customers. Employees are surveyed quarterly, and results are stratified by employee category: design and production, manage rial, and sales administrative support Elizabeth Burke has recently joined the PLE man agement team to oversee productiod operations. She has reviewed the types of data that the company.collects and has assigned you the responsibility to be her chief analyst in the coming weeks. She has asked you to do some pre- liminary analysis of the data for the company 1. First, she would like you to edit the worksheets Dealer Satisfaction and End-User Satisfaction to display the total number of responses to each level of the survey scale across all regions for each year, 2. Second, she wants a count of the number of failures in the worksheet Mower Test 3. Next, Elizabeth has provided you with prices for PLE products for the past five years Year 2014 2015 2016 2017 2018 Mower Price Tractor Price $150 $3,250 $175 $3,400 $180 $3,600 $185 $3,700 $190 $3,800 In addition to these business measures, the PLE data- base contains worksheets with data from special studies Engines, which lists 50 samples of the time required to produce a lawn mower blade using a new technology Transmission Costs, which provides the results of 30 samples each for the current process used to pro- duce tractor transmissions and two proposed new processes Blade Weight, which provides samples of mower blade weights to evaluate the consistency of the pro duction process Mower Test, which lists te results of mower fune tional performance after assembly for 30 samples of 100 units each Create a new worksheet to compute gross revenues by month and region, as well as worldwide totals, for cach product using the data in Mower Unit Sales and Tractor Unit Sales 4. Finally, sbo wants to know the market share for each product and region by month based on the PLE and industry sales data, and the average market share by region over the five years. Summarize all your findings in a report to Ms. Burke