Homework answers / question archive / ICA001NGA • Excel Exam • Fall 2021 This test is a closed book test and is to be completed individually

ICA001NGA • Excel Exam • Fall 2021 This test is a closed book test and is to be completed individually

MS Excel

Share With

ICA001NGA • Excel Exam • Fall 2021
This test is a closed book test and is to be completed individually. Do not copy or distribute the assignment files. Save your file regularly throughout the exam to prevent any chance of lost work.

Follow the instructions from your professor to find the “2217A Excel Exam Data.xlsx” file. Make the following changes to the Excel file:

# Instructions Marks
In cells A40 and N1, type: Total 4
In cells A41 and O1, type: Average 4
In cell P1, type: Commission 2
Make the text of all cells: Verdana, 12 3
Make bold the cells in the range A1:P1 and A2:A41 4
AutoFit the width of column A, so that the its content completely visible 2
Insert four new rows on top of row one (above) 4
On the cell A2 type: SUPERSTORE -Sales Report per Salesperson 2
On the cell A3 type: Your Full Name — Student ID Number 2
Format cell A2 so it will be Verdana 15, bold 4
Merge & Center A2 to P2 (If you prefer you can use Center Across) 2
Merge & Center A3 to P3 (If you prefer you can use Center Across) 2
Right-align the cells in the range: B5:P5 and A44:A45 2
In cell N6, enter a function to calculate the Sum of the cells in the range 5 B6:M6 (sales per salesperson)
Copy the sum function in cell N6 to the cells in the range N7:N43 2
In cell B44, enter a function to calculate the Sum of the cells in range 5 B6:B43 (sales per month). The result may appear in scientific notation, that’s okay.
Copy the Sum function in cell B44to the cells in the range C44:N44 2
In cell O6, enter a function to calculate the Average of the cells in the range 5 B6:M6 (sales average per salesperson)
Copy the Average function in cell O6 to the cells in the range O7:O44 2
Continue on the other side… ->

ICA001NGA • Excel Exam • Fall 2021
#	Instructions	Marks
20	In cell B45, enter a function to calculate the Average of the cells in range B6:B43 (sales average per month)	5
21	Copy the Average function in cell B45 to the cells in the range C45:N45	2
22	To calculate the commission per salesperson, in cell P6 enter a formula that multiplies the content of cell N6 by 0.05	5
23	Copy the formula in cell P6 to the cells in the range P7:P44	2
24	Format the numbers in the range B6:P45 with the Comma number format with two decimal places	4
25	Format bold the following cell ranges: N6:N45 and B44:M44	4
26	AutoFit the width of column N, so that the its content completely visible	2
27	Make sure that columns B to P have the same Column Width as column N	3
28	In A47 write Note: All units are in CAD.	Format it in italics.	3
29	Add a Bottom Border to cells on the ranges A5:P5 and A43:P43	4
30	Sort the values in cells A6:N43 in Largest to Smallest (descending), to order the table by Total	10
31	Apply Conditional Formatting to cells B6:M43 using the Top/Bottom Rules to highlight the Bottom 10% using Light Red Fill with Dark Red Text	5
32	Create a 2-D Clustered Bar Chart from the noncontinuous (not next to each other) ranges A5:A43 and N5:N43. Place the chart to the bottom of the data and make sure the chart does not block any of the values on the worksheet	5
33	Create a 2-D Line Chart from the noncontinuous (not next to each other) ranges B5:M6 and B45:M45. Place the chart to the bottom of the data and make sure the chart does not block anything on the worksheet	5
34	a. b. c.	Save (As…) your file with your full name and student number. Submit your file by clicking on the Excel Exam link or the Submit button; both do the same. Please do not leave the MS Teams session until you confirm that your modified file has been successfully saved and submitted.	Total: 117