Part b(i) Use the information provided to create a warranty cost prediction model - that is, to estimate the probability of a claim. See the additional information tab and use the

information in part 2 to estimate the probability of a claim (that is, the probability of the HSS-10 breaking down), by customer. The key information from part 2 has been reproduced below: Column Ref. B I G H i) Customer Survey Ratings (customer satisfaction) are a lead indicator of the probability of a breakdown (scores of 9 & 10 have a 1% chance of breakdown in year 2, scores of 7 & 8 = 2% chance, 5 & 6 = 3%, and under 5 = 5%). ii) The number of repairs in year 1 is a starting point for likely breakdowns in year 2. There is some reversion to the mean. Over all, 0 breakdowns implies a 1% chance of a breakdown, 1 = 2%, 2 = 3%, 3 or more = 5%. iii) Crime statistics provide a rough estimate of failures of the HSS-12 product. If crimes reported are greater than 3 per thousand, probability of a breakdown (per ii above) increases by 50% (that is, 1% increases to 1.5%, 2% increases to 3%, etc) iv) The climate impact score ranges from 50 to 100, with a higher number implying humidity and weather conditions. To convert the climate impact score to a breakdown probability, take column H/100 x 3%. f you assume that the information from the Raw Data tab provides a random sample, you can determine the probability of each individual customer having a breakdown. While no individual estimate is perfect, calculating the average probability of a breakdown based on items i), iii) and iv) above is considered a reasonable start for a model. This can be done by following the detailed instructions below: 1) 2) 3) 4) Add a column "J" to the raw data tab and insert the probability of a claim based on the customer survey rates (per i) above). Add a column "K" to the raw data tab and taking into account the number of repairs in year 1, adjusted by the crime statistics, estimate the probability of a claim in year 2 (per ii and iii above). Add a column "L" to the raw data tab and calculate the climate impact score to 2 decimal places (e.g., 2.64%) (per iv above). Add a column "M" to the raw data tab by taking the average of Columns J, K and L and insert it as the "Average probability of a claim in year 2" (to two decimal places)/nCompton Canada Corporation (CCC) - Data Analytics to estimate warranty costs Part b(ii) Use the information provided to create a warranty cost prediction model - that is, based on the probability of a claim, estimate the average cost per claim. See the additional information tab and, using the information in part 3 of that tab, estimate the average cost of a claim, by customer. The key information from additional information part 3 has been reproduced below: Column Ref. C D E v) The average repair cost varies over time, but is quite consistent within each region. vi) Because the HSS-12 is more complex than most products in the industry, the repair costs are expected to increase at the industry 3 year average plus 2%. vii) Technicians provide useful estimates of the expected increase in cost of repairs, but they are typically 1.5% too low in their estimates (on average). If you assume that the information from the Raw Data tab provides a random sample, you can determine the average cost of a claim. While no individual estimate is perfect, calculating the average cost of a repair based on the information in v), vi) and vii) above is considered a reasonable start. This can be done by following the detailed instructions below: 5) 6) 7) 8) 9) Add a column "N" to the raw data tab by taking the 3 year trend of cost increase (per column D) + 2%. Add a column "O" to the raw data tab by taking the average repair cost per column C and increasing it by the Adjusted industry trend (column N). Add a column "P" to the raw data tab by taking the Technicians expected price increase per column E + 1.5%. Add a column "Q" to the raw data tab by taking the average repair cost per column C and increasing it by the Adjusted technician price increase estimate (column P). Add a column "R" to the raw data tab by taking the average of Columns O and Q (to two decimal places)./nCompton Canada Corporation (CCC) - Data Analytics to estimate warranty costs Using the model you created in Part b, calculate an estimate of the required provision for the second year of the warranty for HSS-12. Using the probability of a customer making a claim and the estimated average cost of a claim from Part b(i) and b(ii), you can now calculate the probability of a claim and average cost of a claim for each region (i.e. BC-VCR, Alberta, etc.) This can be done by taking the number of units sold by region (see below) and multiplying this by both the average probability of a claim in that region and the average cost per claim in that region (using data from part b(ii)). A Pivot Table is the most efficient way to use the data in part b(ii) to determine the probability of a claim and average cost of claim by region. The key information required is in columns M and R in part b(ii). Create this Pivot Table in the space provided below. To create a Pivot Table, first ensure you are on the data on the part b(ii) tab. When you are anywhere on this data (no need to highlight it all), from the top menu bar select Insert, Pivot Table. The entire data will be selected for you. Choose where you want the Pivot Table to be placed by choosing Existing Worksheet (see a screen shot here) and clicking on the cell in the student work area below (again refer to the screen shot here for assistance). Part c (Hint: If you are not familiar with creating Pivot Tables, you may wish to use Excel's help feature to assist you. If you do end up creating it on a new tab, simply copy it from that tab and paste it into the student work area below). When the Pivot Table is created below, place appropriate fields in the 4 quadrants to complete it. A useful allocation for the Pivot Table fields is as follows: (not required) 1) Report filter: 2) > Values: AVERAGE of both columns M and R (average probability of a claim and overall average adjusted repair cost). Customer Location-Region 3) Row Labels: 4) Column Labels (not required) Be sure to set both value fields to show as averages (instead of sums). Format the probability values to display as a percentage, 1 decimal, and the average cost values to display as accounting, 2 decimals. Change the column title in column 1 from Row Labels to "Region". Re-size any columns as necessary. Create Pivot Table Choose the data that you want to analyze O Select a table or range Table/Range: Part b(ii)'!$A$53:SR$154 O Use an external data source Choose Connection... Connection name: Use this workbook's Data Model Choose where you want the PivotTable report to be placed O New Worksheet O Existing Worksheet Location: Part c'!SAS54 Choose whether you want to analyze multiple tables Add this data to the Data Model OK ? X Cancel ↑/nPart d Briefly summarize the limitations of your warranty cost prediction model