GROUP COURSEWORK
Computer Applications and Systems
QUESTION ONE
You are given the following information:
Note:
- All the marks provided are out of 100%
- Coursework marks are calculated by taking the higher score between test 1 and test 2,
and then adding the Assignment score to determine the final grade.
- Add a column for student name and have it populated.
- Add another column called Programme. Assign a programme (such BIS, SE,..etc) to
each of the students. This should be from a dropdown list.
- Final marks are as a result of the coursework marks (out of 40%) and the exam marks
(out of 60%)
Required
1. Using the knowledge, you acquired in Excel formulas and functions, come up with a
formulae to calculate the coursework marks.
2. By writing a correct formula, compute the final mark from the exam and coursework
marks.
3. Use a column graph to represent the above data Grades.
4. Using the Excel IF function, write a formulae that will award each student according to
their final mark column and show in another table the data arranged in ascending order.
Use the grading system indicated in the table below:
Marks Ranges Grades
80-100 A
75-80 B+
70-74 B-
65-69 C+
60-64 C-
55-59 D
50-54 E
0-49 F
QUESTION TWO | Mail Merge
ABC School offers a comprehensive curriculum with a strong emphasis on college and career
readiness. The school is going to present a certificate to the students who have scored high
marks in English, Chinese or Mathematics.
1. Create the following data source file using a spreadsheet program
2. The content of the certificate for the first student looks like: Feel free to come up with
your own design but don’t change the content.
3. Use Mail Merge to produce the certificates for all students. Include a student name on
the certificate.
4. Hand in the softcopy of the document after applying mail merge.
QUESTION THREE | Analysis
In your group, you will act as data analysts for a retail company seeking a thorough evaluation
of its quarterly sales performance across different regions. This task involves collecting data,
performing detailed computations, and extracting insights to guide strategic sales decisions.
The goal is to assess regional and product performance, identify top-performing salespeople
Generate a dataset in a table structure with the following columns:
▪ Transaction Date
▪ Region
▪ Product Category
▪ Salesperson
▪ Units Sold
▪ Unit Price
▪ Total Sales
Basic Analysis Using Formulas:
o Use SUMIF, COUNTIF, and AVERAGEIF functions for specific calculations:
▪ Total Sales by each region.
▪ Total Sales for each product category.
▪ Average Sales Value per transaction in each region.
▪ Count of Transactions by each salesperson to evaluate activity levels.
Pivot Tables and Summarization:
o Create pivot tables to analyze:
▪ Total Sales by Region and Quarter for a time-based regional
comparison.
▪ Sales by Product Category and Salesperson to identify which
salespeople specialize in specific products.
▪ Top and Bottom Performing Regions, showing the percentage
contribution to overall sales.
Data Visualization:
o Develop Bar Charts for:
▪ Quarterly sales by region.
▪ Top 5 product categories by total sales.
o Pie Charts to illustrate:
▪ Sales contribution by region.
▪ Sales contribution by product category.
Deliverables:
1. Formatted Spreadsheet:
o A well-organized, error-free Excel spreadsheet with clear labeling and
organized data.
2. Report Summary and Insights:
o A detailed report including:
o An executive summary of quarterly sales trends by region and product
category.
o Insights on top-performing products and sales regions, along with an analysis
of high and low-performing salespeople.
▪ Convert your report and excel file to pdf documents.
▪ Zip together your solutions (all questions) into one file and upload them on the E-
learning platform through the Group Coursework submission box. Only the group
leader should submit.
▪ Include a .txt file (Create this using notepad) that contains all group names and
registration numbers.