Problem 3 Abourne Ltd manufactures a microcomputer for the home use market. The management accountant is considering using regression analysis in the annual estimate of total costs. The following information has been produced for the 12 months ended 31 December: 2 Month 1 2 3 4 5 6 7 8 9 10 11 12 Number of Output, X1 employees, X2 Total cost Y (numbers (numbers 38 200 300 28 40 480 320 30 41 400 350 30 51 000 500 32 52 980 530 32 60 380 640 35 70 440 790 41 32 720 250 41 75 800 820 41 71 920 780 39 68 380 750 38 33 500 270 33 = 637 200 1 = 6 300 2= 420 Direct labour hours, X3 (hours 4 480 4 780 4 800 5 120 5 150 5 700 7210 3 200 7300 7 200 6 400 3960 EX3 = 65 220 The management accountant wants to select the best independent variable (X1, X2 or X3) to help in future forecasts of total production costs using an ordinary least-squares regression equation. You are required to: (a) Identify which one of the three independent variables (X1, X2 or X3) given above is likely to be the least good estimator of total costs (Y). Give your reasons, but do not submit any calculations. (b) Compute separately, for the remaining two independent variables, the values of the two parameters a and B for each regression line. Calculate the coefficient of determination (R2) for each relationship (using formulae given in the textbook). (c) Using Microsoft Excel to check the results in (a) and (b) (draw graphs for the three relationships, and run regression function. Please submit the excel file for this requirement). (d) State, with reasons, which one of these independent variables should be used to estimate total costs in the future given the results of (b) above