Introduction Jack Bosch is a new member of Triumph Riders Ltd. (TRL) executive team in a role to supervise production operations. He has evaluated the firm data that has been collected/reported,...

Introduction

Jack Bosch is a new member of Triumph Riders Ltd. (TRL) executive team in a role to supervise production operations. He has evaluated the firm data that has been collected/reported, deciding that significant statistical analysis is required. Jack has hired your consulting group from York University to be responsible for much of this analysis. All the required data is in the Microsoft Excel file titledGroup Project - Data(56 KB - link will open in new window). This Excel file has many tabs (worksheets) - here is a brief description of each worksheet and the data it contains:

• Bicycle Unit Salescontains sales for bicycles, a main product manufactured by TRL, by region on a monthly basis. Unit sales for each region are aggregated to obtain world sales figures.


• Motorcycle Unit Salescontains sales for bicycles, a main product manufactured by TRL, by region on a monthly basis. Unit sales for each region are aggregated to obtain world sales figures.


• Industry Bicycle Total Saleslists the number of units of bicycles sold by all producers by region.


• Industry Motorcycle Total Saleslists the number of units of motorcycles sold by all producers by region.


• Customer Satisfaction Surveycontains 200 observations of customer ratings for TRL motorcycles. The survey looks at specific attributes of TRL motorcycles: price, service, ease of use, and quality. Each is rated on a scale that goes from 1 to 5, with 1 being the lowest score and 5 being the highest.


• Wheel Weightincludes samples of motorcycle wheel weights to help gauge how consistent the manufacturing process is.


• Procurement Surveyincludes data from a survey of procurement managers of customers of Triumph Riders Ltd.


• Post-Delivery Defectshighlights the number of defects in materials found in all supplier shipments.


• Worker Retention, includes data from a worker retention survey (how long workers stayed with the firm) for TRL. The 40 subjects were identified by reviewing hired workers from ten years prior and identifying those who were involved in managerial positions (either hired into management or promoted into management) at some time in this ten-year period.

It is recommended for the group project that you split the work between different members. Which part each member is responsible for should be highlighted in the introduction to your report. While you will be marked on a group basis, students that did not participate in the group project will receive a zero.

All of your analysis within excel should be in ONE file (multiple worksheets is fine). You must summarize your results in a maximum 5-page report. The five pages does not include graphs/charts/tables which can be either included in the body of the report or in an appendix. Your report should include summaries of your analysis, interpretations of the data or trends, andconclusions. The report is not just your group showing what analysis was done (e.g., we ran a regression in excel, this was the output), you need to interpret the results and come up with conclusions (e.g., the regression output indicates that the relationship between location and house prices is significant, which means that…).

Your final submission should be ONE Microsoft Word File (not a PDF or Apple pages file) and one Microsoft Excel file (not an Apple numbers file) showing your analysis. An excel template for your group Project has been provided with each worksheet clearly labeled, corresponding to the questions in this project.

____________________

Part 1

Jack first wants you to analyze the historical sales performance of the firm.

A. Jack make available to you the prices for TRL products for the past five years:

Calculate gross sales by month and region, as well as worldwide totals, for each product using the data inBicycle Unit SalesandMotorcycle Unit Sales.

B. Elizabeth wants to know the market share for each product and region by month with the use of theIndustry Motorcycle Total SalesandIndustry Bicycle Total Salesworksheets, and the average market share by region over the five years. Chart Part A and Part B (on an annual basis) in your report and interpret the data, commenting on sales trends and market share trends.

____________________

Part 2

Jack continues to get inquiries from other TRL departments regarding quality, customer satisfaction, and operational performance. Jack wants your group to summarize some data from theGroup Project - Data ExcelFile using statistical tools and analysis:

A. Create histograms and frequency distributions utilizing the data including in theCustomer Satisfaction Surveytab.
B. Create a frequency distribution and histogram for the wheel weight samples in the worksheetWheel Weight.
C. Correlations among the variables provided in the worksheetProcurement Survey.

Comment on the charts for A and B, as well as the significant of the correlations from Part C.

____________________

Part 3

Jack has a particular question he would like you to generate some insight on from theGroup Project - Data Excel File, using theWorker RetentionWorksheet. Are there differences in worker retention due to university graduation status, gender, or whether or not the worker is native to the local area? Conduct your analysis and interpret your results in your report.

____________________

Part 4

Mr. Bosch, using data from thePost-Delivery Defectsworksheet, has noticed that the number of defects has been declining.

Jack tells you that in 2014, TRL had some quality issues due to supplier materials experiencing an increasing number of defects. In August 2015 (by the end), the computer started an improvement program to help its suppliers decrease these defects, improve the coordination of purchases, and help improve supplier production policies.

Jack had indicated that the program has appeared to reverse an increasing trend in defects; he would like to predict what might have occurred had this quality program not been executed, what is the trend with the current defects, and what he might expect to occur in the future.

Use trendlines and regression analysis to assist Jack in evaluating this data. Summarize your conclusions in your report.

____________________

Part 5

Jack is concerned with the high worker turnover rate for front-level staff. Other departments have requested Jack to examine the firm’s recruiting policies in order to identify the characteristics of individuals that can lead to greater retention. The HR department could not reach an agreement on these characteristics.

Some members of the HR department claimed that years of education and GPA (grades) were the best predictors. Other members contended that hiring more mature candidates would lead to greater retention. To settle this dispute, the HR department decided to conduct a statistical study to determine the effect university GPA, years of Education, and age when hired have on retention. A sample of 40 front-line workers hired ten years ago was selected to determine the influence of these variables on how long each worker stayed with the company. Data are compiled in the Employee Retention worksheet.

Jack wants you to use trendlines and regression analysis to assess this data included in theWorker Retentionworksheet. Summarize your work (including critical insights) in your group’s formal report with all appropriate analysis and results.