AC2118 In-Class Excel Exam: 2020

Question 1: (25 Marks) Formatting and style of tables are paramount for good data integration and analysis.With the data below you are asked to

A) Format and style so it is a well presented table

B) Calculate the total grade for each student

C) Calcuate the % grade for each student and visualise the trend of grades

D) Create a simple but effective table that shows the MAX score for each assigment and exam, MIN score for each assignment and exam, Average Score for each assignment and exam, and % Average grade for the class

Note: The Max score any one student can achieve is 400

Question 2: (25 Marks)

A) In the individual employee sales report, please provide the count of sales (K10) and sum of sales (K11) for Emily Burns from the Sales Table

B) Correct the function in cell O10 so that it will show Emily sales for the month of February.

C) Update the function in O10 so that when a month is entered into cell K13 it will give the correct sales amount for tht month

D) Correct the Q1 & Q2 sales report for total and percetnage of sales target fields. Sales target is €2255

E) On the same sheet insert a suitable chart to highlight the sales of Q1 for each rep

Note: Remember the importance of cell formatting and cell referencing (check that all data is formatted correctly)

Question 3 Data Analysis: (25 Marks)

A) For Table 1, on the same sheet, Create a Cross Tabulated Report for Order Total by Sales Region and Employee Job Title (Include a graph).

B) For Table 1, on the same sheet, Create a Report that counts how many Sales were retail and how many were wholesale (Include a graph).

C) For Table 2, on a new sheet (Rename as CrossTab) create a Cross Tabulated PivotTable report that averages the sales by Product Category and and Order dates. Group Date by Quarter only. Then add a Slicer for Region and Employee Name, and a graph to overlay the table.

Note: Check cell formatting, design, blank cells, and labels in your pivot tables

Question 4: (25 Marks)

A)You are required to complete an invoice order for the customer Fitzgeralds. They have ordered 10 hardrives, 4 monitors, 12 graphics cards, and 25 screen protectors.

In addition, they have requested that their order be delivered to their UK office. Shipping costs vary on EU/Non EU locations, and you are requried to create at least one table name using the named range option

B) Create a drop down list for customer names in the invoice

C) Create a drop down list for all products

Note: Pay attention to any error messages you may get

19.99 USD

Purchased 3 times

Completion Status 100%