ESC4201 Excel W24

.pdf

School

Algonquin College *

*We aren’t endorsed by this school

Course

4201

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

6

Uploaded by DrStarlingMaster645

Report
1 ESC4201 W23 Excel Assignment Due: Feb 2 nd , 6am [All the files you need for this assignment are available: Excel] ESC4201 Excel W24.docx Student Files - Excel – W24.zip (containing four Excel files) This assignment is out of 145 marks worth 5% of your term grade When you download the Student Files.zip and unzip it, rename the folder ASAP to Lastname firstname o Failure to follow naming convention will reduce your grade by 1% (1 out 5%) You must leave these files inside this folder in order to complete this assignment. Once your Excel assignment is complete, you will need to re-zip your folder o This folder must be labelled as Lastname firstname o This folder will have the 4 Excel files Your zip file will be submitted through BrightSpace >> Assignments >> Excel (5%) o Details at end of this document Failure to submit file by Feb 2 nd , 6am will result in 0 out of 5% o No exceptions You are required to use the following four Excel files 1.Data Set.xlsx 2.Ontario.xlsx 3.Quebec.xlsx 4.Data Summary.xlsx Using the data available in 1. Data Set.xlsx file, you will populate and conduct tasks in the following two files ( 2. Ontario.xlsx & 3. Quebec.xlsx ). Once this is done, you will use those two files ( 2. Ontario.xlsx & 3. Quebec.xlsx ) and populate the 4. Data Summary.xlsx file. These files will now be dynamically linked, so when one file is updated, this could impact the other files. Because of this, it is critical that all your Excel files be in the same folder. NOTE: the numbers and values in this Word document are not the same as your Excel data
2 Task A (0 marks) In the file ( 1. Data Set.xlsx ), you will pick your data set by matching your last digit of your Student # to the worksheet. For example, if your student number is 00000053 8 , you will be using the Eight data worksheet. NOTE: Failure to use the assigned data related to your Student # will end up receiving zero for the assignment (0/145) Task B B1 (25 marks) You will copy the data from 1. Data Set.xlsx into the two files ( 2. Ontario.xlsx & 3. Quebec.xlsx ). For example, copy the data from 1. Data Set.xlsx/Toronto column into the 2. Ontario.xlsx/Toronto column. 1. Data Set.xlsx 2. Ontario.xlsx 3. Quebec.xlsx NOTE: Do not Copy & Paste . You must use absolute referencing when coping your data from the 1. Data Set.xlsx into 2. Ontario.xlsx & 3. Quebec.xlsx files. If not, you’ll receive zero for section B1 (0/25). Even if four columns are referenced properly, but one column was Copied & Pasted, you’ll lose the 25 marks.
3 The image below is an example of what absolute referencing between two difference files will look like. B2 (35 marks) The following steps must be completed for all five locations (Toronto, Ottawa, London, Montreal & Quebec City) and the two summary worksheets using these two files: 2. Ontario.xlsx 3. Quebec.xlsx Task for both files Calculate the Low/Mid/High KPIs for the twelve months and the Totals for the five locations (7 marks) Calculate the Summary worksheet’s Revenue, Low/Mid/High KPIs and Totals (14 marks) For the seven worksheets, add a ‘Line’ sparkline in cell B17 (7 marks) Each ‘Line” sparkline must show the min & max for the 12 months (7 marks) Deductions Number formatting must be currency with two decimal places (7-mark deduction) The KPIs (C18-E18) must be two decimals. For example, 0.85 or 85.00% are OK. (7-mark deduction) NOTE: if one part of the formatting is missing, you will lose the full deduction
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help