Notes on Excel Class and Exam Analysis

Cengage Scores Overview

• Discussion about Cengage score analysis will help in developing the grading policy for the project.
• The session integrates Chapter 9 and Chapter 10 concepts related to Excel and spreadsheet modeling.

Analysis of Exam Scores

• Main focus will be on analyzing Exam 1 and Exam 2 scores.
• A grading scale will be applied as the average for Exam 2 was below 75% (requires scaling).
• First part of the class will involve understanding individual performance through examination of the scores.
• Goal Seeker will be briefly reviewed, particularly relevant for future classes.

Net Present Value (NPV) Exercise

• The NPV exercise will be demonstrated to summarize spreadsheet modelling concepts learned during the course.
• Objectives include comfort with spreadsheet modeling techniques.

Final Exam Matrix

• The final exam schedule was shared:
  • Scheduled for May 5 at 7:30 PM.
  • Alternative exam time on May 7 at 7:30 AM available upon request (limited spots).
• Encouragement to plan for final exams using the shared matrix.

Exam Score Analysis Process

• Instructions for students to log in to Excel and download the necessary scores data file.
• The scores involve anonymized student performance across various components of both exams.
• Discussion on different versions of exam questions per archetype.

Calculating Percentage Scores

• Process to calculate percentage scores for each exam.
• Emphasis on making one of the references absolute to ensure accuracy when filling down formulas.
• Conditional formatting considerations to help visualize score distribution with green for high scores and red for low scores.

Average Scores Calculation

• The averages need to be calculated for each exam component individually.
• A basic understanding of scaling based on averages being less than 75% was emphasized.

Standard Deviation Analysis

• Explanation of variance and how it's related to standard deviation.
• Importance of standard deviation in providing a measure of distribution from the mean and variability in scores.
• Use of histograms and box plots will be employed to display data distributions better.

Creating Histograms

• Instructions to create histograms for Exam 1 and Exam 2 to visualize data distributions effectively.
• Bin width settings in Excel should be adjusted for meaningful analysis of score distributions.

Box Plot Analysis

• Demonstrations of constructing box plots to analyze the median performance and outliers between the two exams.
• Box plots provide insights into quartiles and overall score distributions, allowing for better understanding than averages alone.

Grade Distribution and Calculation Strategy

• Covered the final course grade distribution:
  • Exam 1: 20%
  • Exam 2: 20%
  • Exam 3: 25%
  • In-class quizzes and Cengage tasks: 30%
  • Class participation: 5%
• Approach to calculating weighted average grade and understanding its importance in assessing overall performance.

Goal Seek Functionality

• Review of the Goal Seek feature in Excel to find the minimum score needed for a desired final grade.
• Example given: determining what score is needed on Exam 3 to achieve a B grade in the course.

Scenario Manager Overview

• Introduction to the Scenario Manager tool within Excel:
  • Useful for analyzing various outcomes based on different input scenarios (e.g., project estimates).
• Method for setting up scenarios, changing variable values, and interpreting the results for various hypothetical situations.
• Demonstrated scenarios related to borrowing amounts or project completion costs to visualize dependencies.

Conclusion and Class Logistics

• Students encouraged to utilize the shared resources and tools discussed during class to enhance understanding of Excel functionalities.
• Reminder of upcoming sessions in preparation for final exams and follow-up evaluations based on analysis covered.