Unit Title: Descriptive Analytics Unit Code: BSS0014-2
Assessment 1: Part (a) Group presentation (20%) in week 7 – you are required to work effectively as a small team and attempt all the tasks. You will then present your results and I will ask from each group member a number of questions and provide feedback.
Part (b) Individual report (30%) in week 9 - You will then need to use the feedback provided accordingly to improve your work and present it individually. It is expected that each student work independently on all the tasks set out in this assignment, applying problem solving strategies and selecting appropriate techniques in data analysis in order to produce an individual report according to the guidelines provided. This individual work must be your own work only with no consultation with others. The individual report must not exceed 2,000 words. Those reports that exceed the similarity index of 25% produced by Turnitin, can only achieve a maximum grade of E.
Assignment Title: “Performance Lawn Equipment”
Issue date: Week One: Monday 19/10/2015
Submission due date: Week 7: 07/12/2015
Week 9: Friday 08/01/2016: A single report per student should be submitted via the TURNITIN link in the Assessment folder. Assignment must not be e-mailed under any circumstances. Unit co-ordinator’s name: C F Shooshtarian Core learning outcomes | | On completion of this unit you should be able to: | Assessment number | 1 | Investigate and analyse a variety of problems and data sets in the context of different analytics to develop insights in order to support management decision making. | 1 | | | 2 | | | | 2 | Apply statistical and mathematical technique to find solutions to analysis conducted using suitable software. | 1 | | | 2 | | | |

Threshold standards | Assessment number | In order to pass the assessment you will need to: | 1 | Create frequency distribution and contingency tables | | Construct charts and statistical summary measures and evaluate statistical evidence | | Report your findings to provide answer to the problem and support the decision making process | 2 | Compare visually presented data for decision making | | Assess problems involving uncertainty to determine the likelihood of an event | | Analyse distribution models for prediction |

Case: Performance Lawn Equipment
Performance Lawn Equipment (PLE), headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. In the past 10 years PLE has added another key product, a medium-size diesel power lawn tractor with front and rear power takeoffs, Class I three-point hitches, four wheel drive, power steering, and full hydraulics. This equipment is primarily built for a niche market consisting of large estates, including golf and clubs, resorts, private estates, city parks, large commercial complexes, 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.
In the United States, the focus of sales is on the eastern 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. The market is cyclical, but the different products and regions balance this, with just less than 30% of sales in spring and summer (in the United States), about 25% in the fall, and about 20% in the winter. Annual sales are approximately $180 million.
PLE employs 1,660 people worldwide. About half the work force is based in St. Louis and the remainder is split among their manufacturing plants.
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, discount, and timeliness of support.
PLE has several key suppliers, Mitsitsiu, Inc., the sole source of all diesel engines; LANTO Axels, Inc., which provides tractor axels; Schorst Fabrication, which provides subassemblies; Cuberillo, Inc, supplier of transmissions; and Speciality Machining, Inc., a supplier of precision machine parts.
To help manage the company, PLE managers have developed a “balanced score-card” of measures. These data, which are summarized shortly, are stored in the form of a Microsoft Excel workbook (Performance Lawn Equipment) accompanying this case study. This database contains various measures captured on a monthly or quarterly basis and 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 summary data from surveys for the past 5 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 5 years. * 2012 Customer Survey, results from a survey for customer ratings of specific attributes of PLE tractors: quality, ease of use, price, and service on the same 1-5 scale. This sheet contains 200 observations of customer ratings. * Complaints, 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 provide sales by product by region on a monthly basis. Unit sales for each region are aggregated to obtain world sales figures. * Industry Mower Total Sales and Industry Tractor Total Sales, which list the number of units sold by all producers by region. * Unit Production Costs, which provides monthly accounting, estimates of the variable cost per unit for manufacturing tractors and mowers over the past 5 years. * Operating and Interest Expenses, which provides monthly administrative, depreciation, and interest expenses at the cooperate level. * On-Time Delivery, which provides the number of deliveries made each month from each of PLE’s major suppliers, number on time, and the percent on time. * Defects After Delivery, which shows the number of defects in supplier-provided material found in all shipments received from suppliers. * Time To Pay Suppliers, which provides measurements in days from the time the invoice is received until payment 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 2 years. * Employee Satisfaction, which provides data for the past 4 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, managerial, and sales/administrative support.
In addition to these business measures, the PLE database 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 produce Tracway tractor transmissions and two proposed new processes. * Blade Weight, which provides samples of mower-blade weights to evaluate the consistency of the production process. * Mower Test, which lists test results of mower functional performance after assembly for 30 samples of 100 units each. * Employee Retention, data from a study of employee duration (length of hire) with PLE. The 40 subjects were identified by reviewing hires from 10 years prior and identifying those who were involved in managerial positions (either hired into management or promoted into management) at some time in this 10-year period. * Shipping Cost, which gives the unit shipping cost for mowers and tractors from existing and proposed plants for a supply-chain-design study. * Fixed Cost, which lists the fixed cost to expand existing 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.
What you need to do in order to prepare for your group presentation and individual report
Part 1.0
Elizabeth Burke has recently joined the PLE management team to oversee production 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. To prepare for this task, you have decided to review each worksheet and determine whether the data were gathered from internal sources or external sources or generated. Also, you need to know whether the measures are categorical, ordinal, interval, or ratio.
Part 1.1
Elizabeth Burke has asked you to do some preliminary analysis of the data in the Performance Lawn Equipment database. 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. Second, she wants a count of the number of failures in the worksheet “Mower Test.” Next, Elizabeth has provided you with prices for PLE products for the past 5 years: Year | Mower Price | Tractor Price | 2008 | $120 | $3,000 | 2009 | $135 | $3,100 | 2010 | $150 | $3,250 | 2011 | $175 | $3,400 | 2012 | $180 | $3,600 |

Create a new worksheet in the database to compute gross revenues by month and region, as well as worldwide totals, for each product using the data in Mower Unit Sales and Tractor Unit Sales. Finally, she wants to know the market share for each product and region based on the PLE and industry sales data in the database. Create and save these calculations in a new worksheet.
Prepare a report summarizing the characteristics of the metrics used in each worksheet and a summary of all your findings in a report to Mrs. Burke.
Part 2: The Performance Law Equipment database contains data needed to develop a pro forma income statement. Dealers selling PLE products all receive 20% of sales revenue for their part of doing business, and this is accounted for as the selling expense. The tax rate is 40%. Develop an Excel worksheet to extract and summaries the data needed to develop the income statement for 2012 and implement an Excel model in the form of a pro forma income statement for the company.
Part 2.1: PLE originally produced lawn mowers, but a significant portion of sales volume over recent years has come from the growing small-tractor market. As we noted in the case in Chapter 1, PLE sells their products worldwide, with sales regions including North America, South America, Europe and Pacific Rim. Three years ago a new region was opened to serve China, where a booming market for small tractors has been established. PLE has always emphasized quality and considers the quality it builds into its products as its primary selling point. In the past 2 years, PLE has also emphasized the ease of use of their products.
Before digging into the details of operations, Elizabeth Burke wants to gain an overview of PLE’s overall business performance and market position by examining the information provided in the database. Specifically, she is asking you to construct appropriate charts for the data in the following worksheets and summarize your conclusions from the analysis of these charts. a. Dealer Satisfaction b. End-User Satisfaction c. Complaints d. Mower Unit Sales e. Tractor Unit Sales f. On-Time Delivery g. Defects After Delivery h. Response Time
Part 2.2: As noted in the case in Chapter 1, the supply chain worksheets provided cost data associated with logistics between existing plants and customers as well as proposed new plants. Ms. Burke wants you to extract the records associated with the unit shipping costs of proposed plant locations and compare the costs of existing locations against those of the proposed locations using quartiles.
Part 2.3: Ms. Burke would also like a quantitative summary of the average responses for each of the customer attributes in the worksheet 2012 Customer Survey for each market region as a cross-tabulation, along with frequency distributions, histogram and quartiles of these data.
Write a formal report summarizing your results for all three parts of this case.
Elizabeth Burke wants some detailed statistical information about much of the data in PLE database. In particular, she wants to know the following: a. the mean satisfaction ratings and standard deviations by year and regions in the worksheets Dealer Satisfaction and End-User Satisfaction. b. a descriptive statistical summary for the 2012 customer survey data c. how the response times differ in each quarter of the worksheet Response Time d. how defects after delivery (worksheet Defects After Delivery) have changed over these 5 years e. how do sales of mowers and tractors compare with industry totals? How strongly are monthly product sales correlated with industry sales?
Perform these analyses and summarize your results in a written report to Ms. Burke.