PLEASE POST EXCEL FORMULAS

B C E 3 4 Employee ID Trained Years Experience Sales Revenue 5 L001143 1 5 $ 1,533,252.35 6 0002841 0 18 $ 860,492.04 7 1003826 1 14 $ 1,208,479.33 8 F004627 0 17 $ 1,924,462.45 9 HO05520 0 23 $ 2,076,426.01 10 L006500 0 11 $ 1,701,243.44 11 V007578 1 10 $ 1,306,217,76 12 D008237 1 34 $ 2,544,495.90 13 J009279 0 32 $ 2,158,713.15 14 0010605 0 33 $ 1,487,033.14 15 0011115 0 20 $ 1,369,137.75 16 R012566 0 12 s 899,621.89 17 Y013250 1 17 $ 1,450,348.53 18 A014875 1 36 $ 2,071,138.88 19 PO15139 0 22 S 1,292,951.45 20 H016217 1 4 $ 1,397,057.35 21 B017577 0 24 $ 1,102,806.60 22 B018443 1 34 S 2,621,997.61 23 TO19874 1 30 $ 1,673,995.99 24 PO20241 1 20 $ 2,046,343.17 25 Z021332 0 27 $ 1,183,118.60 26 B022923 1 6 $ 1,128,325.72 27 G023351 1 3 S 859,467.24 28 WO24433 1 9 5 2,038,283.61 29 KO25320 1 34 $ 2,729,790.02 30 VO26936 0 50 $2,094,368.45 31 1027975 1 32 $ 2,192,374.91 2 Instructions Your Info Q1 Q2 Q3 Ready Accessibility. Investigate 20 1 3004 The table to the left includes data for 125 salespeople at Dunder Mifflin Inc. -"Employee ID" 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 ando and otherwis "Years Experience is the number of years that the employee has been a salesperson at Dunder Mittlin - 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, with Sales Revenue" as your "Y"variable and "Trained" and "Years Experience" as your two "X"variables. Output the results in the highlighted area below (H23 should be selected as the "Output Range" for the regression), and then use the results to answer the following question (Hint: 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) 2. Based on your results, if an employee was trained and has 21 years of experience, what is his or her predicted sales revenue? 3. Using employee 104586, what is the regression model prediction error for this particular employee? B C E 3 4 Employee ID Trained Years Experience Sales Revenue 5 L001143 1 5 $ 1,533,252.35 6 0002841 0 18 $ 860,492.04 7 1003826 1 14 $ 1,208,479.33 8 F004627 0 17 $ 1,924,462.45 9 HO05520 0 23 $ 2,076,426.01 10 L006500 0 11 $ 1,701,243.44 11 V007578 1 10 $ 1,306,217,76 12 D008237 1 34 $ 2,544,495.90 13 J009279 0 32 $ 2,158,713.15 14 0010605 0 33 $ 1,487,033.14 15 0011115 0 20 $ 1,369,137.75 16 R012566 0 12 s 899,621.89 17 Y013250 1 17 $ 1,450,348.53 18 A014875 1 36 $ 2,071,138.88 19 PO15139 0 22 S 1,292,951.45 20 H016217 1 4 $ 1,397,057.35 21 B017577 0 24 $ 1,102,806.60 22 B018443 1 34 S 2,621,997.61 23 TO19874 1 30 $ 1,673,995.99 24 PO20241 1 20 $ 2,046,343.17 25 Z021332 0 27 $ 1,183,118.60 26 B022923 1 6 $ 1,128,325.72 27 G023351 1 3 S 859,467.24 28 WO24433 1 9 5 2,038,283.61 29 KO25320 1 34 $ 2,729,790.02 30 VO26936 0 50 $2,094,368.45 31 1027975 1 32 $ 2,192,374.91 2 Instructions Your Info Q1 Q2 Q3 Ready Accessibility. Investigate 20 1 3004 The table to the left includes data for 125 salespeople at Dunder Mifflin Inc. -"Employee ID" 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 ando and otherwis "Years Experience is the number of years that the employee has been a salesperson at Dunder Mittlin - 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, with Sales Revenue" as your "Y"variable and "Trained" and "Years Experience" as your two "X"variables. Output the results in the highlighted area below (H23 should be selected as the "Output Range" for the regression), and then use the results to answer the following question (Hint: 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) 2. Based on your results, if an employee was trained and has 21 years of experience, what is his or her predicted sales revenue? 3. Using employee 104586, what is the regression model prediction error for this particular employee