Homework answers / question archive / INSTRUCTIONS: This year, Sam, Jo, Andi and Loren won 1st, 2nd, 3rd and 4th place, respectively, in the annual scholarship competition

INSTRUCTIONS: This year, Sam, Jo, Andi and Loren won 1st, 2nd, 3rd and 4th place, respectively, in the annual scholarship competition

Accounting

Share With

---

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.                                                                                                                                                     

                                                                                                                                                               

(DATA: You will often see data included in this first tab; you will need to use this data to complete the problem. As part of this overview, you will learn how to use "cell references".)                                                                                                                                                          

                Total Prize                                                                                                                                          

1st place              $30,000                                                                                                                                                

2nd place             $20,000                                                                                                                                                

3rd place              $10,000                                                                                                                                                

4th place              $2,500                                                                                                                                  

                                                                                                                                                               

(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 requirements will walk you 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                                                                                                                               

(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                                                                                                                                              

(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 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                                                                                                                                      

(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                                                                                                                                             

                                                                                                                                                               

(SAVE & SUBMIT: After completing a project, you need to save your solution spreadsheet, so that you can upload it to be auto-graded. Make sure you do not included spaces in the file name, as this will keep the file from uploading successfully.)                                                                                                                                                    

SAVE & SUBMIT:                                                                                                                                                             

(1) 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.                                                                                                                                                          

(2) Return to the Project Window - where you initially downloaded your spreadsheet - to submit your solution for grading.                                                                                                                                                               

                d) You are now on Step 3; click Choose File                                                                                                                                         

                e) Select your updated spreadsheet, and click Ok                                                                                                                                            

                f) Click Upload                                                                                                                                  

                g) Click Submit for Grading                                                                                                                                          

                                                                                                                                                               

REVIEW RESULTS:                                                                                                                                                           

To see how you did, click on Results in MyAccountingLab; then click on the assignment, and the question you just submitted.                                                                                                                                                         

                                                                                                                                                               

"FIRST ATTEMPT! AT THIS POINT YOU WILL SAVE YOUR FILE, RETURN TO THE PROJECT WINDOW, AND UPLOAD YOUR SUBMISSION FOR GRADING.

REMEMBER TO RETURN TO THIS SPREADSHEET FOR YOUR SECOND ATTEMPT TO CORRECT ALL MISTAKES AND EARN 100%!!"                                                                                                                                                               

                                                                                                                                                               

SECOND ATTEMPT! CORRECT YOUR MISTAKES BY FOLLOWING THE STEPS BELOW, THEN RE-SUBMIT YOUR SOULTION TO RECEIVE 100%!!                                                                                                                                                         

                                                                                                                                                               

                                                                                                                                                               

                                                                                                                                                               

(1) Open the solution spreadsheet you saved for your first attempt                                                                                                                                                       

(2) Correct cell D5 in ENTER SOLUTION HERE tab; click on cell D5 and type =D4                                                                                                                                                    

(3) Correct cell B7 in ENTER SOLUTION HERE tab; click on cell B7 and type =B6                                                                                                                                                     

                                                                                                                                                               

SAVE & SUBMIT:                                                                                                                                                             

(1) 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.                                                                                                                                                          

(2) Return to the Project Window - where you initially downloaded your spreadsheet - to submit your solution for grading.                                                                                                                                                               

                a) In MyAccountingLab, click on Assignments > Do Homework                                                                                                                                   

                b) Click on the assignment                                                                                                                                          

                c) Click on the question you just submitted to open a new attempt                                                                                                                                         

                d) Skip to Step 3, and click Choose File                                                                                                                                   

                e) Select your updated spreadsheet, and click Ok                                                                                                                                            

                f) Click Upload                                                                                                                                  

                g) Click Submit for Grading                                                                                                                                          

                                                                                                                                                               

REVIEW RESULTS:                                                                                                                                                           

To see how you did, click on Results in MyAccountingLab; then click on the assignment, and the question you just submitted.

Scholarship                                                                                        

Yearly Prize Payout                                                                                        

                Sam       Jo            Andi       Loren    Total      Rank

First Year             $7,500   $6,666.67             $10,000                 $2,500   $26,667                 1

Second Year       $7,500   $6,667   $10,000                                 $24,167                 2

Third Year            $7,500   $6,667                                   $14,167                 3

Fourth Year        $7,500                                                   $7,500   4

                                                                                               

                                                                                               

During which year is the Scholarship Competition paying out the most money?                                                                  1