Assignments

instruction

BISM 1200 Excel Grade Analysis Project (100 Points)

Project Requirements:

  1. Graded Items Data: Create a main data entry worksheet for your grades fromMoodle.
    1. Name the tab “Grades Entry” and shade the tab gray (This may be any gray in the white or black spectrum given)
    2. For each item(class participation/assignment/quizzes/projects/exams/final exam), list the name ofthe item, the total points earned, and the maximum required points.
      1. Use data started with the Excel 1 Assignment.
      2. Update the Excel 1 Assignment
        1. Rename ExcelProject_FirstLast
        2. Update with the latest grades
  • Insert an 80 for the Excel Project and the Final Exam. The Excel Project is due at the beginning of the Final exam.  If this Excel project is done correctly, you should only have to change the grades of both the project and the exam in the “Grades Entry” to find out what your grade will be for the class.
  1. Use AutoSumto find the totals for each column.
  2. Find the percentage using only strait grades. (This is not the percentage in the class.)

 

  1. Data Input Validation: each input cell (i.e., cells for entering the student scores) shallhave data input validation as follows:
    1. Input Message Boxes: assist the user by providing a message related to the typeof input required for a given cell as the cell is clicked.
    2. Data Validation Rules: add validation rules for each input cell based upon thepossible values for that cell. For example, the Final Exam is worth 100 points sothe valid range for input data is 0 to 100.
      1. Prevent the user from entering invalid data into the cells for quizassessment scores.
    3. Error Messages: Provide a descriptive error message to alert the user as toexactly what the problem is and how to fix it.

 

  1. Graded Category Worksheets: Create separate worksheets to represent each gradecategory for Class Participation/Assignments, Quizzes, Projects, andExams. Note: Final Exam should be shown with the Exams.
    1. Use cell referencing to list the grades as entered on the Grades Entryworksheet (item, total points earned, total points possible) as well as the percentage ofpoints earned for each graded item.
    2. Name and color the tabs as follows: (You may use any shade within the given spectrum of colors)
      1. “Class Participation/Assignments” – yellow
      2. “Quizzes” – orange
  • “Projects” – blue
  1. “Exams” – green

 

  1. Grade Category: For each graded category worksheet,
    1. In the graded category Quizzes, Use excel to drop the lowest grade before calculating percentage
    2. Calculate the overallpercentage of points earned
    3. Maximum and minimum score for all graded items in that category
    4. Use Excel to display thecorresponding letter grade using a nested IF structure for the overall percentage of pointsearned as listed on each grade category worksheet
    5. For the Exams worksheet, in addition to the overall exam analysis, break down the examgrades on the Exam worksheet to calculate the percentage of points earned with respect tocontent questions (i.e., percentage of points earned for all Moodle-based questionsacross all three exams) as well as the skills-based exercises (i.e., percentage of pointsearned for the Word, PowerPoint and Excel assignments across all three exams).

 

  1. Grade Category Summary Assessment: Provide a summary worksheet to display theoverall total points scored for each grade category (Class Participation/Assignments, Assessments, Projects, and Exams), the overall percentage of pointsearned in each grade category, and the overall weighted score to be used in calculatingthe class grade.
    1. Name the worksheet “Summary” and use a red shading from the spectrum
    2. Since the Final Exam score does not have a separate worksheet, you mayreference this score from the main Grades Entry page.
    3. Grade Weighting Scale: The grade weighting scale shall be used as listed in thesyllabus in calculating your overall semester average.
      1. Class Participation/Assignments – 15%
      2. Quizzes – 15%
  • Projects – 30%
  1. Exams – 40%

 

  1. Conditional Formatting:
    1. Add a column with the heading “Status” to the summaryworksheet and use conditional logic to display “Pass” or “Fail” for each grade category depending upon the current grade.
    2. Add a column, called Letter Grade, to the summaryworksheet that uses conditional logic to evaluate each grade category as well as theoverall weighted grade and list the equivalent letter grade for each.
    3. Use conditionalformatting to highlight all A’s with Blue, B’s with Green, C’s with Yellow, D’s with Orange,and F’s with Red. (Do not manually highlight cells)

 

  1. Decision Analysis: On the summary worksheet, label a cell as “Number of gradecategory averages below 70”.
    1. Use the CountIF to determine and display the number ofgrade category averages that are below 70%.
    2. Using Excel conditional functions, create a cell that evaluates the overall weightedaverage in the class and displays a message that says either “Passing the Class” or “NotPassing the Class”.

 

  1. Sorting Data: Create a copy of the Grades Entry worksheet named “Sorted Grades”.
    1. Sort the data by category from highest to lowest (i.e., all homework assignments shouldbe grouped together and then sorted from highest homework assignment to lowest, allquizzes should be grouped and sorted, etc.)
    2. Name this tab “Sorted Grades”
    3. Shad the tab the same shade as the Grades Entry tab

 

  1. Charting Data: Display the assignment and quiz grades using charts to visuallydisplay the data trends over the semester. Create two separate charts (one forassignments and one for quizzes) using two different chart types of your choice. Besure to format the charts and include the title, axis labels, and legend as appropriate.

 

  1. Formatting and Professional Appearance: The grading criteria for this assignment willinclude an assessment of the overall professional appearance of the workbook,including column spacing, font formatting to distinguish headings from grade data, etc.
    1. There should be a consistent professional appearance to the project across worksheets.
    2. Be sure to include the project name, student name, and descriptive worksheet title at thetop of each worksheet with an appropriate title for the worksheet.
    3. For example, an appropriate heading for the summary sheet would be:
      1. BISM 1200 Final Excel Grade Analysis Project
      2. Student Name
  • Summary Worksheet

Percentages:  Show all percentages with a % and two decimal places.

Note: Any information entered/changed on the Grades Entry form should automatically update as appropriate across all other worksheets in the project by using the applicable Excel functions and features.

Extra Credit:  (See Charting Data.) Provide one chart as a 3D Pie Chart with beveled edges and the other chart a 3D Column Chart with beveled edges.  Yes there is a way to do this!

52903_1525698928

         $10 per 275 words - Purchase Now