Objective/Overview:
This assignment provides students the opportunity to utilize advanced features of a spreadsheet application and perform analysis to establish a business decision. This project uses different features such as functions, absolute addressing, and charts. The student is expected to create a single file that consists of the elements listed below to calculate and illustrate the grades for a fictitious Info 309 class. The information will then be analyzed and presented in a report.

Required – Spreadsheet

General
1. Create a single spreadsheet file and title it myname – tool 1 (example: Mosher – Tool 1)
2. Generate the worksheets (tabs) as described below
3. Some check figures are provided in the attachments to assist in getting the calculations correct

Grade Calculation Worksheet
1. Title worksheet (tab): “Grade Calculation”
2. Create and enter the information supplied in Attachment 1 – List of Students and Grades
3. Utilize the frame found in Attachment 2 – Grade Calculation Frame
4. Calculate the missing data with the following rules:
a. Decision Tools column
i. Total = sum of individual grades (use SUM function)
b. Mid Term column
i. Adjusted = Actual + Adjustment (use absolute reference)
ii. Determine the letter grade using the LOOKUP function into the Grade Table worksheet
c. Final Exam column
i. % = Actual ÷ Possible (use absolute reference and format as %)
ii. Adjusted = ((Actual + Adjustment) ÷ Possible) * 100
iii. Determine the letter grade using the LOOKUP function into the Grade Table worksheet
d. Final Grade column
i. Total = Decision Tool Total + (Mid Term Adjusted * .35) + (Final Exam Adjusted * .35) [FYI… grades are 30% tools, 35% MT and 35% Final]
ii. Determine the letter grade using the LOOKUP function into the Grade Table worksheet
e. Average row
i. Determine the Average for each column using the AVERAGE function
f. Max row
i. Determine the largest amount for each column using the MAX function
g. Min row
i. Determine the smallest amount for each column using the MIN function
h. Adjustment
i. Adjustment amount (provided – no additional input required) to be used as instructed above
i. Possible
i. Possible score (provided – no additional input required) for each assignment to be used as instructed above
j. Count
i. Number of numeric items listed within each column. Use the COUNT function

Grade Table worksheet
1. Title worksheet: Grade Table
2. Recreate the following grade matrix (used for the LOOKUP function):

From To Grade
0 59 F
60 62 D-
63 66 D
67 69 D+
70 72 C-
73 76 C
77 79 C+
80 82 B-
83 86 B
87 89 B+
90 92 A-
93 100 A

Grade Distribution worksheet
1. Title worksheet: Distribution
2. Use the frame as shown in Attachment 3 – Grade Distribution Frame
3. Use the COUNTIF function as per the following
= COUNTIF(graderange, currentgrade)

Where:
• graderange is the range of values from the Grade Calculation worksheet
• currentgrade is the grade category you are totaling

4. Hint: For the Grades row, count the Total column from the Final Grades area within the Grade Calculation worksheet

Mid Term Chart worksheet
1. Title worksheet: MT Chart
2. Create a bar chart in a separate worksheet
3. Use values (single series) from the MT Adj row from the Distribution worksheet
4. Provide an appropriate title on top of the chart
5. X axis labels: Grades A – F
6. Y axis labels: Number 0 – ?? (let program establish default)

Grade Chart worksheet
1. Title worksheet: Grade Chart
2. Create a line chart
3. Use values from the Final Adj row and the Grade row (2 series) from the Distribution worksheet
4. Provide an appropriate title on top of the chart
5. X axis labels: Grades A – F
6. Y axis labels: Number 0 – ?? (let program establish default)
7. Include legend at the bottom of the chart

Required – Report

Overview:
You are an analysts assigned to research the accusation that student grades do not reflect the school standards. To start out the research you have decided to take the grades from an Info 309 class and evaluate them and see if you can find any discrepancies.

Requirements:
To get a better understanding of the situation you will perform the following:

1) Utilize the information generated in the spreadsheet above
2) Generate a report to your supervisor indicating your findings. Include the following:
a) Introduction explaining why you have decided to perform this analysis
b) Methodology utilized
c) Findings of you analysis. Discuss, as a minimum, the grade distribution and what you would expect the results to look like. Feel free to discuss any other conclusions.
d) Recommendations. Describe what you think should be done to correct any issues.

Attachment 1 – List of Students and Grades

Decision Tools Mid Term Final Exam
Last Name First Name Tool 1 Tool 2 Tool 3 Actual Actual
Adams Gilbert 10 9 10 57 42
Butler Irene 9 9 10 77 44
Cooper Peggy 10 7 10 55 31
Curtis Jeffrey 7 9 10 62 34
Delgado Rosanna 10 – 10 62 36
Earp Wyatt 10 9 10 65 43
Fitzgerald Diana 10 10 10 84 47
Gagnon Mark 9 9 10 70 46
Goddard Joshua 9 10 9 65 36
Guerrero Ana 10 8 10 82 48
Hernandez Maria 10 7 8 80 45
Hong Ki Jun 10 10 9 67 46
Jackson Mike 10 10 9 84 36
Johnson Thomas 10 10 10 84 39
Kirkland Richard 7 8 – 55 –
LeDoux Maurice 9 10 9 56 31
Luna Carmen 10 8 9 84 45
Martin Mary 10 9 10 69 48
Meeks Susan 10 10 10 79 46
Nicholson Joseph 10 7 10 83 37
Padilla Jose 10 8 9 57 29
Perez Daniel 9 8 8 82 51
Pollard Francisco 9 9 9 72 49
Ramirez Diane 10 10 9 57 39
Reyes Rosemary 10 7 10 84 38
Schultz Frederick 7 8 10 63 19
Williams Robert 10 10 10 83 47
Zurcher Bradley 9 9 10 75 46

Attachment 2 – Grade Calculation Frame

Name Decision Tools Mid Term Final Exam Final Grade
Last First Tool 1 Tool 2 Tool 3 Total Actual Adjusted Grade Actual % Adjusted Grade Total Grade
Adams Gilbert 10 9 10 29 57 73 C 42 70.0% 85.0 B 84.30 B

Average
Max
Min
Adjustment 16 9
Possible 10 10 10 30 100 60 190
Count 28

Attachment 3 – Grade Distribution Frame

A A- B+ B B- C+ C C- D+ D D- F Total
MT Adj 3
Final Adj 2
Grades 3