need this accounting question question answered

Office 2016 – myitlab:grader – Instructions Accounting Project

Excel Projects - How to Enter Answers (NEW)

Project Description: Chapter 0 Tutorial - v2

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 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. 0.000 2 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. 0.000 3 (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 0.000 4 (2) All answers will be entered on the ENTER SOLUTION HERE tab; we will start by filling in the table [10 points] a) In cell B4, type = then on the Data tab, click on cell B7 to create a cell reference; next type /4, as Sam will receive the scholarship divided over 4 years, and then click enter b) In cell C4, type = then on the Data tab, click on cell B8; next type /3, as Jo will receive the scholarship divided over 3 years, and then click 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 divide the amount e) In cell B5, type = then click on cell B4 on same tab (ENTER SOLUTION HERE), and then click enter; since the scholarship is paid out in equal amounts, the amount paid out in the second year for Sam is the same as the first year 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 year 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 h) INTRODUCING ERRORS: In cell B7, type 8500; this is an error because we are not using a cell reference AND because we entered the wrong value; we will get no credit i) When we review, you will notice that we received partial credit in cell F7; this is because we got the wrong final value, but used the correct formula 10.000 5 (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 start 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 drag and fill the RANK.EQ column without the cell references changing d) Click on cell G4; click and hold the fill handle and drag down to cell 8.000 6 (4) Last, 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 2.000 7 SAVE & SUBMIT: After entering your answer in the second tab (ENTER SOLUTION HERE), save your spreadsheet by clicking FILE > SAVE AS. Make sure you DO NOT included spaces in your file name, and remember to save the file somewhere you will be able to find it, such as the Desktop. 0.000 8 Follow instructions in spreadsheet to complete ATTEMPT #2, to earn 100%. 0.000 Total Points 20.000

Updated: 09/16/2016 1 Current_Instruction.docx