Rep 1 Rep 2 -969.7223 45.48038 -1423.171 2145.658 1266.354 1498.317 669.6547 609.8002 2382.335 1254.25 1078.899 386.8203 2257.35 1281.534 4165.156 3113.451 2495.654 -0.813944 3340.619 2547.969 647.153 1184.687 -750.4453 2550.765 2077.222 -1138.903 409.764 588.1974 2700.714 691.8435 1885.834 3155.715 2606.721 2227.262 2780.218 -895.7556 627.9544 2889.376 3339.8 -673.0527 2391.287 1143.901 -815.5808 660.5343 2767.98 757.8562 1223.724 1773.391 2419.439 1276.11 1439.727 2508.48 1550.561 -159.4494 607.439 2017.577 649.0328 4052.443 1578.673 90.81133 1008.436 313.1253 1643.297 833.5908 -1286.626 1660.32 1743.698 719.1514 1048.504 1092.156 -870.5131 2142.658 1698.692 3735.079 1133.126 1266.233 2057.525 3141.548 867.3943 4027.282 1980.538 1828.1 1214.541 1643.922 1301.684 1530.346 1032.002 1665.986 3343.01 2279.391 3322.957 2515.272 675.5766 3178.373 2448.298 4613.969 1427.449 1335.299 4309.356 -744.477 3354.147 772.4567 3966.956 2589.11 2502.598 2654.33 1104.896 3177.835 1299.839 1247.603 531.6999 2410.216 1853.578 2776.659 1832.017 1212.157 898.7696 1782.518 -37.10556 3260.285 -332.6641 2559.856 1426.055 2103.478 1176.018 2368.089 -32.13834 711.1438 -2512.715 119.9121 2124.344 1129.012 461.8926 3015.117 1187.13 1283.396 1499.728 -1264.552 1938.865 31.03061 3519.15 2420.732 536.3346 1017.153 2911.059 234.7042 2337.675 2852.695 1808.603 1812.299 3102.308 5954.378 -118.6857 323.6823 408.9285 581.0948 1424.057 2407.332 2315.336 2553.051 2542.962 84.21011 802.2438 3588.749 -428.9544 1976.457 1513.051 4263.184 3499.115 2776.099 -855.9303 1752.125 730.3565 2704.287 94.33552 1169.768 -430.6483 -1681.314 1922.069 -220.6508 2528.768 418.5764 -335.7069 -264.3648 2623.335 2014.433 279.0804 1230.04 1136.15 1766.394 3818.331 2138.553 4312.82 2940.102 853.0751 656.6543 636.4945 1939.189 3259.618 624.6011 Question Background Insurance companies take risks when they sell policies to ensure for example a property or a life. They must price their policies in such a way that their expected profits enable them to survive. While they can base their projections on actuarial tables, in reality the insurance business often requires that they discount policies to a variety of customers or situations. A more difficult aspect is that until a policy expires, the company won't know if they have made a profit, no matter what premium they charge. A manager of an insurance company wants to see how some of the sales representatives are doing. For two sales representatives, the manger has data on 100 recently matured policies that have been sold by each of the sales representatives and has computed the profit (premium charged minus paid claims) of each policy. The file \"Insurance.xlsx\" contains the profits of the policies for the two sales representatives (Rep 1 and Rep 2). Based on data from the previous years it is known that the population standard deviation in profits of policies is 1300 (applies to all/each sales representative). Complete the following tasks a. Use Excel to provide numerical descriptive statistics in terms of measures of central location and measures of dispersion for the profits of the policies of each sales representative. b. Generate a histogram of the data for each sales representative. Briefly describe their shapes. To make your histograms comparable, ensure each histogram uses the same set of intervals. You may wish to use an interval width of $1,000. c. In the past the insurance company has sometimes used the rule of thumb that profits on matured policies should average $1,400 in order for the company to remain profitable. Conduct a hypothesis test at the 5% level to test whether the mean profits of the sales representative with the higher sample mean (use the information from your answer to part a) is above this threshold. For the test, follow the 6 steps of the hypothesis test from the lectures and tutorials and show your workings. Make sure to provide evidence to support any assumptions that you make when constructing your test. d. Use Excel to compute the p-value for the test you conducted in part (c). Interpret your result. How strong is the statistical evidence in support of the alternative hypothesis? Based on the p-value, would your conclusion in part (c) change if the test were conducted at the 1% or 10% significance levels? 1 e. Construct a 95% confidence interval estimate for the difference in mean profits between sales representatives 1 and 2. In your answer you should discuss the relevant CI estimator and its conditions. You should also provide evidence to support any assumptions that you make when constructing your estimate. Makes sure to interpret your result. Define profits of all policies of sales representative 1 as population 1 and profits of all policies of sales representative 2 as population 2. f. Suppose that the two sales representatives for who the manager has data (rep 1 and rep 2) are the most successful ones in the team. The manager would like to award a prize to Rep 1 as he is more senior one of the two, but has been told that the prize should be awarded to the Rep that has obtained higher mean profits on all of their policies. The manager only has access to data for the 100 most recently matured policies of each sales representative as provided in the excel file. Using these data, conduct a hypothesis test at the 5% level to provide the manager with statistical evidence to decide whether sales representative 1 should be awarded the price. g. Use Excel to compute the p-value for the test you conducted in (f). How strong is the statistical evidence in support of the alternative hypothesis? Based on the p-value, would your conclusion in part (f) change if the test were conducted at the 1% or 10% significance levels? Now, suppose we want to conduct a two-sided test that mean profits of Rep 1 and Rep 2 are different. Compute the p-value for such a two-sided test and interpret the result. h. The company has a new CEO that promised the shareholders a sales strategy that focuses on delivering profitable policies. Combining the data on the two sales representatives in \"Insurance.xlsx\