You have an investment account that started with $4,00010 years ago and which now has grown to $7,000. a. What annual rate of return have you earned (you have made no additional contributions to the account)? b. If the savings bond earns 13% per year from now on, what will the account's value be 10 years from now? (INSTRUCTIONS: The instructions set up the scenario, and often give information you will need to figure out the solution.) INSTRUCTIONS: This year, Sam, Jo, Andi and Loren won 1st, 2nd, 3rd and 4th place, respectively, in the annual scholarship competition. The first place winner will receive the scholarship over 4 years, the second place winner will receive the prize over 3 years, the third place winner will receive the scholarship over 2 years, and the fourth place winner will receive the scholarship as a single lump sum. \begin{tabular}{|lc|} \hline \multicolumn{2}{|l}{ (DATA: You will often see data included ir } \\ \hline & Total Prize \\ \hline 1st place & $30,000 \\ \hline 2nd place & $20,000 \\ 3rd place & $10,000 \\ \hline 4th place & $2,500 \\ \hline \end{tabular} (REQUIREMENTS: This is where you will see what you need to do to complete the problem. For the purposes of this demo, the original requirement is included in purple. The other rem through how to complete this problem, including providing instruction on certain Excel skills.) REQUIRMENT: 1. Enter the information in the Yearly Prize Payout table to determine 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. (1) We are going to split the screen so you can see both the Data tab and the Start Template tab at the same time. [0 POINTS - this is an excel tip; completing this will never be graded] 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 SOLUTION tab on the right f) Click on cell B5; click and hold on the small green square in the bottom right corner 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 i) In cell B7, type = then click on cell B6 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 (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 the 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 =LOOKUP(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 \begin{tabular}{l|l|c|c|c|} \hline \multicolumn{4}{c|}{ Scholarship } \\ \hline \multicolumn{1}{l|}{} & \multicolumn{2}{c|}{ Yearly Prize Payout } \\ \hline First Year & Jo & Andi & Loren & Total \\ \hline Second Year & & & \\ \hline Third Year & & & \\ \hline Fourth Year & & & \\ \hline \end{tabular} During which year is the Scholarship Competition paying out the most money? The amount that you can afford is 9 (Round to the nearest dollar.)