Thank you!!

complete this problem, including providing instruction on certain Excel skills.) REQUIRMENT: 1. Enter the information in the Yearly Prize Payout table to detemine the total amount of money the Scholarship Competition will pay out each year. (Make sure to use cell references, formulas and functions where appropriate.) [14 points] - Make sure to use the RANK.EQ function to fill out Ranks column. 2. During which year is the Scholarship Competition paying out the most money? [2 points] - Enter your answer in cell F10. - Make sure to use the LOOKUP function. a) To start, click View in the top tool bar. b) Click New Window (first option in fourth section of tool bar). c) Click VIEW > Arrange All (second option in fourth section of tool bar), then select Vertical and click OK. d) On the right hand screen, click on the ENTER SOLUTION HERE tab, so that you can see the Data tab on the left, and the 5 OLUTION tab on the right (2) All answers will be entered on the ENTER SOLUTION HERE tab; we will start by filling in the table [10 points] b) In cell C4, type = then on the Data tab, click on cell 8 ; next type /3, as Jo will receive the scholarship divided over 3 years, and then dick enter c) In cell D4, type = then on the Data tab, click on cell B9; next type /2, as Andi will receive the scholarship over 2 years, and then click enter d) In cell E4, type = then on the Data tab, click on cell B10, and then click enter, Loren receives the scholarship as a lump sum, so no need to dhide the amount f) Click on cell B5; dick and hold on the small green square in the bottom right comer of the cell (called the fill handle), and then drag across to cell D5 - Your cell reference is copied across cells; since it is a relative cell reference, the reference changes relative to the position of the new cell - You only drag to cell D5 because Loren's scholarship was completely paid out in the first year g) In cell B6, type = then click on cell B5 on the same tab (ENTER SOLUTION HERE), and then click enter h) Click on cell B6; click and hold the fill handle and drag across to cell C6 - You only drag to cell C6 because Loren's scholarship was completely paid out in the first year and Andi's was completely paid out after the second year i) In cell B7, type = then click on cell 86 on the same tab (ENTER SOLUTION HERE), and then click enter j) INTRODUCING ERRORS: In cell D5, type 5000; this is an error because we are not using a cell reference; we will get partial credit i) When we review, you will notice that we recelved partial credit in cell F7; this is because we got the wrong final value, but used the correct formula (3) We will now sum the rows and assign ranks [4 points] a) In cell F4, type =SUM(B4:E4), and then click Enter b) Click on cell F4; click and hold the fill handle and drag down to cell F7 c) In cell G4, type =RANK.EQ(F4,\$F\$4:\$F\$7,0] - The RANK.EQ function will assign the approproiate rank to the totals in column F with 1 as the greatest total - The $ is used to create an absolute cell reference, or one that continues to reference the exact same cell no matter the relative position; we use this so we can d) Click on cell G4; click and hold the fill handle and drag down to cell 4) Now, we will determine during which year the scholarship committee paid out the most money [2 points] a) In cell F10 type =L00KUP(1,G4:G7,A4:A7) - The LOOKUP function finds the cell with a rank of 1 in cells G4:G7, and then grabs the value in the corresponding row in cells A4:A7 During which year is the Scholarship Competition paying out the most money? Data ENTER SOLUTION HERE