# 1 Assignment for Excel – assignmentcollections.com

Business Finance Assignment Collections – assignmentcollections.com

HI, I already finish the step on I,and you can start to do it from J.

and please follow the requirements carefully make sure you finish all the details.

## Directions:

• Follow the instructions listed on the next pages.
• Enter your name on cell A1 of the worksheet.
• Submit your RecitationProject.xlsx file to Sakai using the Sakai->Test & Quizzes link by following these 3 steps after scrolling towards the bottom of the instructions page:

## Instructions:

1. Go to Sakai → CS170 Gradebook page and do the following:
1. Select the Gradebook Items, Grades, Due Dates and Comments columns. Do not select the column headings, only the items and their information.
2. Copy the selection by right-clicking on it and choosing the option Copy.
3. Start Excel, create a Blank worksheet.
4. Select the cell C3 on the worksheet and paste the information copied. Widen the C column width.
• Rename the worksheet as Grades.
5. Delete column E: click on the E letter that identifies that column and then right -click and select Delete (this is needed since that column came with Data format).
6. Add the following entries to the list of activities on the C column (below the last activity listed): Final Exam Part 1, Final Exam Part 2and Final Exam Part 3.
7. Starting on the cell E3, type the maximum number of points possible for each Gradebook Item:
• 2 points for Assignment 1
• 20 points for the other Assignments and the Recitation Project
• 80 points each for Exams 1 and 2
• 120 for the Final Exam (simply ignore it if this entry is not listed yet)
• Note: If there is an item for which you do not have a grade yet, do not enter the maximum points for it.
8. On row 28 do the following:
• Enter the label “Totals” on C28
• The total points obtained will be calculated on D28. Since there might be some blank cells, an appropriate function for this cell is SUMIF.
• Arguments for SUMIF:
• range: the list of scores on the D column (including blank cells for the activities with no scores yet)
• criteria: greater than or equal to 0 (express this using Excel notation)
• sum_range: the list of scores on the D column (including blank cells for the activities with no scores yet)
• Copy the function from D28 to E28 using the fill handle.
9. On row 29:
• Enter the label “Performance” on C29.
• On D29 enter a formula to calculate your Performance % by simply dividing your total points by the total maximum points (those numbers are on row 28).
• Format your performance with Percentage style with 1 decimal.
10. On G3, start a table of equivalences between % points (on the G column) and letter grades (on the H column). Use the following Grading table which is derived from the Grading section of the Syllabus (once completed the Grading table should start at G3 and end at H9.)
 0% F 60% D 70% C 76% C+ 80% B 87% B+ 90% A

On row 30:

• Enter the label Letter Grade on C30.
• On D30 insert the VLOOKUP function to calculate the current letter grade based on your Performance % and the Grading table created on G3:H9.
• The arguments for the VLOOKUP function are:
• lookup_value: your numeric performance % (from D29).
• table_array: the table that converts percentages into letter grades which you created starting on G3.
• col_index_num: 2 (since the second column contains the letter grades).
1. What-If Analysis:
• Now that you have calculated a letter grade for your current scores, you will run some simulation Scenarios to evaluate the possible effect of the Final Exam.
• The formula entered on cell D28 which currently contains SUMIF needs to be modified to process the hypothetical Final Exam scores for each of its parts.
• To process the Second Chance option, the formula on D28 needs to be expanded. Instead of just =SUMIF(…) the format will be: =SUMIF(…) + IF(…) + IF(…)
• The first IF that needs to be added – use the Formula bar to add it – will have the following arguments (use Excel notation; value_if_false does not need an entry):
• Logical_test: 2 x Score of Final Exam Part 1 > Score of Exam 1
• Value_if_true: (2 x Score of Final Exam Part 1) – Score of Exam 1
• Value_if false:

At this point D28 should have the following format:

=SUMIF(…) + IF(…)

• The second IF that needs to be added will have the following arguments (use Excel notation; value_if_false does not need an entry):
• Logical_test: 2 x Score of Final Exam Part 2 > Score of Exam 2
• Value_if_true: (2 x Score of Final Exam Part 2) – Score of Exam 2
• Value_if false:

At this point D28 should have the following format:

=SUMIF(…) + IF(…) + IF(…)

• Some numbers have to be added to the rows where Final Exam Part 1, Final Exam Part 2 and Final Exam Part 3 are located.
• On the D column of those rows place some hypothetical numbers (Example: 32 on each of those three cells).
• The E column for those rows should have 40 on those cells since the maximum score for each part of the Final Exam is 40.
• At this point, the What-If Analysis may start. Click on D30, then on the Data tab → What-If Analysis → Scenario Manager.
• Proceed to create at least three Scenarios with different data for the Changing Cells (the 3 cells on the D column with the hypothetical scores for the parts of the Final Exam).
• The Result Cell when closing the Scenario Manager – by clicking the Summary button – is D30.
• After generating the Scenario Summary, save your work and submit it to Sakai → Test and Quizzes section.
• Follow these 3 steps after scrolling towards the bottom of the instructions page: