Excel Analytics 02-03 (Static) Job-Order Costing and Regression Analysis (LO2-1, LO2-2] [The following information applies to the questions displayed below.) Landis Company uses a job-order costing system with a predetermined plantwide overhead rate based on direct labor- hours. In an effort to improve its ability to measure job profitability and make decisions, the company is considering allocating some of its overhead costs, such as electrical costs, based on machine-hours instead of direct labor-hours. To further explore the potential benefits of this approach, the company has gathered the following eight weeks of actual (not estimated) data with respect to its electrical costs: Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Total Machine-Hours Direct Labor-Hours 7,700 8,910 8,900 8,920 8,600 8,870 8,100 8,840 7,600 8,990 7,100 8,940 5,500 8,870 6,800 8,910 60,300 71,250 Electrical Costs $ 76,200 83,800 81,000 80,800 79,400 70,700 61,500 73,900 $607,300 Using the data above, the company translated these actual amounts into annual estimates as follows: Total for eight weeks (a) 52 weeks + 8 weeks (b) Annual estimate (a) x (b) Machine-Hours 60,300 6.5 391,950 Direct Labor-Hours 71,250 6.5 463,125 Electrical Costs $ 607,300 6.5 $3,947,450 Click here to download the Excel template, which you will use to answer Requirements 4.5 and 6. Click here for a brief tutorial on Regression Analysis in Excel. Excel Analytics 02-03 (Static) Part 1 and 2 Required: 1. Using the annualized data, what is the estimated electrical cost per direct labor-hour? 2. Usina the annualized data. what is the estimated electrical cost per machine-hour? 3. Assume that the company plans to bid on Job Q400 that would require 270 direct labor-hours and 350 machine-hours. a. How much electrical cost would be allocated to Job Q400 using the estimated electrical cost per direct labor-hour from requirement 1? b. How much electrical cost would be allocated to Job Q400 using the estimated electrical cost per machine-hour from requirement 2? 3-a. The electrical cost allocated to Job Q400 is The electrical cost allocated to Job Q400 is 3-b. 4. Refer to the eight weeks of actual data included in the Excel template. a. Use Excel to prepare a scattergraph plot with direct labor-hours on the horizontal axis and electrical costs on the vertical axis. b. Use Excel to prepare a scattergraph plot with machine-hours on the horizontal axis and electrical costs on the vertical axis. c. Which of the two allocation bases (direct labor-hours or machine-hours) appears to be the better choice for assigning electrical costs to jobs? 4-c. The better choice for allocating electrical cost is Reg 5A Req 5B Reg 5C What is the R2 for the regression line in requirement 5a? (Round your answer to four decimal places.) The R2 is Reg 5A Reg 5B Reg 5C What does the R2 tell us? Olt tells us the percentage of variation in the dependent variable (electrical cost) explained by variation in the independent variable (machine-hours). Olt tells us the percentage of variation in the dependent variable (machine-hours) explained by variation in the independent variable (electrical cost). Olt tells us the percentage of variation in the independent variable (electrical cost) explained by variation in the dependent variable (machine-hours). Olt tells us the percentage of variation in the independent variable (machine-hours) explained by variation in the dependent variable (electrical cost)