DISC 112: Midterm***
(75 Marks)
Duration: 2 Hours
Download the excel file as from LMS. Rename it as rollnumber-sectionnumber (eg.
20110029-Sec2) and save it.
All questions have to be attempted individually for this exam.
All answers must be given in the Excel workbook. Only submit the Excel workbook.
You can submit Task 1 and Task 2 in 2 separate Excel workbooks to avoid your system
from hanging.
This exam must be solved without external help – it only includes content that has
been taught in class.
Part 1: (57 Marks)
The “Students Grades” worksheet has data for all the SDSB students in a semester. Each student
is represented by a student id and the different columns have the total marks that student has
obtained for that respective course. Use this data to achieve the following:
1. Calculate the mean and standard deviation (population) for each course’s marks. (3
Marks)
2. Plot the distribution of the grades for each course as a separate histogram (you will plot 5
histograms). An example for one of these histograms will be as follows: (10 Marks)
3. Comment on how these differ. How can you describe your data? What insights can you
generate from these distributions? (3 Marks)
4. Calculate the grade cutoffs for each course. This will be achieved by: (4 Marks)
a. Mean subtracted by half of the St Dev
b. Mean added by half of the St Dev
c. Adding or subtracting the St Dev from the adjacent values till you reach 0 or 100
on either side (if the addition or subtraction is leading to a value greater than 100
or less than 0, manually type in the 100 or 0). A pictorial representation of how
the formulas may look like is:
5. Assign the grades to each student for each course. For simplicity, we will assume that B+
is being assigned on the mean grade (represented by the red arrow in the table above) for
each course.
Grades change at each step (each row in the table given above). (Grade cutoffs for each
course will be different and won’t necessarily cover the entire range from A+ to F).
Tip: Create the source data first – a table with the grade cutoffs and their corresponding
grades for each course. The logic is explained in the image below:
Assign the grades to all students. Example of the resulting dataset is given below (your
exact values may differ): (10 Marks)
6. Plot Pie-charts for each course, representing how many students got each grade. An
example of one pie chart is given below. (You will have 5 plots, one for each course).
Edit the labels and legend correctly. (10 Marks)
7. Using the information given on the ‘Additional Information’ worksheet, assign the GPA
grade points to each student for each course. An example of how your table will look is
as follows (your values may differ): (5 Marks)
8. Let’s calculate the GPA for each student for this semester. Assume equal weightage for
each course. This means that the formula to calculate the GPA will simply be an average.
(2 Marks)
9. Create a new column, indicating who qualified for DHL and who is now on a
probationary semester. Print ‘DHL’ for those with a GPA greater than or equal to 3.6.
Print ‘Probation’ for those with a GPA less than 2. Print ‘Satisfactory’ for the rest. (3
Marks)
10. Plot a bar chart for the frequency of those who qualified for DHL, are on probation, and
the remaining students. An example is given below (your values may vary): (3 Marks)
11. Comment on what you have achieved through this part of the exam. What were we trying
to achieve? Are there any insights? (4 Marks)
Part 2: (18 Marks)
The Worksheet ‘Purchases Data’ has data for purchases made form an e-store. Use this data to
complete the following tasks:
1. Create a search bar to search the details for a purchase using the invoice number.
Example: (5 Marks)
2. Get a list of unique ‘Products’ in the dataset. Clearly mark your answer. (1 Mark)
3. Summarize and calculate the average revenue for each product in the resulting list. Add
data bars in the column (example for data bars:) (5 Marks)
4. Create a pivot table, summarizing the total revenue by Customer Id. (2 Marks)
5. Use this data to plot a pivot table, including only the customers with top 5 revenues. Edit
all axis labels, titles, legends correctly. (3 Marks)
6. Create data validation rule for the ‘invoice number’, allowing only numeric values to be
entered. Circle the incorrect values. (2 Marks)
