70.2K

Verified Solution

Link Copied!

show all excel formula used

M N O The table to the left includes data for 140 salespeople Mt Dunder Mjellin Inc. Employee 10" is a unique identifier for each employee "Trained" is a dummy variable that is equal to 1 if an employee has been through the company's new training program and otherwise. "Years Experience is the number of years that the employee has been a salesperson at Dunder Mifflin. "Sales Revenue is the sales revenue that the employee generated last year. 1. You want to determine if the new training program is effective. To do so, you will run a regression using the Data Analysis toolpack, with Sales Revenue" as your "Y"variable and Trained" and "Years Experience" as your two variables. Output the results in the highlighted area below (1116 should be selected as the "Output Range for the regression, and then use the results to answer the following questions. Notes For the follow up questions that require a number from the regression output, reference the cell that contains the output in your formula rather than typing the number 12 IB 2 The ta - "EL - "T "Ye - "Sal 11 1. You w with the hig Note: Employee ID Trained Years Experience Sales Revenue W001345 0 13 $1,541,141.00 0002697 0 18 $1,331,937.68 F003744 1 17 $1,279,377.40 B B004174 0 12 $ 952,637.37 9 F005512 1 18 $1,819,822.12 10 U006985 0 19 $2,094,898.15 11 K007331 1 20 $1,608,225.20 12 B008129 0 35 $ 2,644,796.12 13 TO09256 1 4 $1,004,699.17 14 0010242 1 33 $2,429,136.24 15 PO11222 1 25 $1,669,683.42 16 F012305 0 27 $ 1,805,623.09 17 2013738 0 32 $ 1,155,847.01 18 J014291 0 35 $ 946,900.54 19 R015422 10 $1,072,472.12 20 0016358 19 $2,727,946.18 21 F017479 7 $ 983,833.62 22 Q018900 1 14 $2,074,894.84 23 KO19514 1 2 $ 977,185.30 24 MO20355 34 $2,480,854.31 25 S021464 0 34 $2,140,888.47 26 MO22924 27 26 $1,728,099.16 FO23283 28 34 $2,053,620.72 DO24860 1 29 J025314 25 $1,656,165.19 0 30 2026704 12 $1,213,607.24 KO27587 32 FO29177 RO30995 93 m 0 1 1 O 1 1 1 31 0 BO28559 33 34 $2,325,478.59 4 $ 583,486.27 13 $1,333,852.12 25 $2,753,901.76 14 S 2.386.184,02 1 1 1 01 34 Directions Q2 2. On average, after controlling for experience, how much does going through the training program change sales? $ 709,274,15 5. 124,350.17 $1,620,399.16 51,183,724.91 $1,496,129.60 4 $1,855,191.34 6 $1708, 121.46 4 5. 338,513.58 3. What would you predict sales revenue to be for someone who went through the training program and has 3 years of experience? 4. Is the effect of the training program statistically significant at the 90% level? M N O The table to the left includes data for 140 salespeople Mt Dunder Mjellin Inc. Employee 10" is a unique identifier for each employee "Trained" is a dummy variable that is equal to 1 if an employee has been through the company's new training program and otherwise. "Years Experience is the number of years that the employee has been a salesperson at Dunder Mifflin. "Sales Revenue is the sales revenue that the employee generated last year. 1. You want to determine if the new training program is effective. To do so, you will run a regression using the Data Analysis toolpack, with Sales Revenue" as your "Y"variable and Trained" and "Years Experience" as your two variables. Output the results in the highlighted area below (1116 should be selected as the "Output Range for the regression, and then use the results to answer the following questions. Notes For the follow up questions that require a number from the regression output, reference the cell that contains the output in your formula rather than typing the number 12 IB 2 The ta - "EL - "T "Ye - "Sal 11 1. You w with the hig Note: Employee ID Trained Years Experience Sales Revenue W001345 0 13 $1,541,141.00 0002697 0 18 $1,331,937.68 F003744 1 17 $1,279,377.40 B B004174 0 12 $ 952,637.37 9 F005512 1 18 $1,819,822.12 10 U006985 0 19 $2,094,898.15 11 K007331 1 20 $1,608,225.20 12 B008129 0 35 $ 2,644,796.12 13 TO09256 1 4 $1,004,699.17 14 0010242 1 33 $2,429,136.24 15 PO11222 1 25 $1,669,683.42 16 F012305 0 27 $ 1,805,623.09 17 2013738 0 32 $ 1,155,847.01 18 J014291 0 35 $ 946,900.54 19 R015422 10 $1,072,472.12 20 0016358 19 $2,727,946.18 21 F017479 7 $ 983,833.62 22 Q018900 1 14 $2,074,894.84 23 KO19514 1 2 $ 977,185.30 24 MO20355 34 $2,480,854.31 25 S021464 0 34 $2,140,888.47 26 MO22924 27 26 $1,728,099.16 FO23283 28 34 $2,053,620.72 DO24860 1 29 J025314 25 $1,656,165.19 0 30 2026704 12 $1,213,607.24 KO27587 32 FO29177 RO30995 93 m 0 1 1 O 1 1 1 31 0 BO28559 33 34 $2,325,478.59 4 $ 583,486.27 13 $1,333,852.12 25 $2,753,901.76 14 S 2.386.184,02 1 1 1 01 34 Directions Q2 2. On average, after controlling for experience, how much does going through the training program change sales? $ 709,274,15 5. 124,350.17 $1,620,399.16 51,183,724.91 $1,496,129.60 4 $1,855,191.34 6 $1708, 121.46 4 5. 338,513.58 3. What would you predict sales revenue to be for someone who went through the training program and has 3 years of experience? 4. Is the effect of the training program statistically significant at the 90% level

Answer & Explanation Solved by verified expert

See Answer