Exploring E CH4 H2 Biology Department Teaching Schedule complete solutions correct answers key

Exploring E CH4 H2 Biology Department Teaching Schedule complete solutions correct answers key

Project Description:

As the department head of the Biology Department at a university, you prepare and finalize the faculty teaching schedule. Scheduling preparation takes time because you must ensure that you do not book faculty for different courses at the same time or double-book a classroom with two different classes. You downloaded the Spring 2015 schedule as a starting point and edited it to prepare the Spring 2016 schedule, and now you need to sort and filter the schedule to review it from several perspectives.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named e4_grader_h2.xlsx.
 

0

2

Freeze the top row in the Faculty sheet.
 

2

3

In the Faculty sheet, convert the data to a table and name the table Spring2016.
 

5

4

In the Faculty sheet, apply Table Style Light 14 to the table.
 

5

5

In the Faculty sheet, sort the table by Instructor, then Days, and then Start Time. Create a custom sort order for Days so that it appears in this sequence:  MTWR, MWF, MW, M, W, F, TR, T, R. (The day abbreviations are as follows: M=Monday, T=Tuesday, W=Wednesday, R=Thursday, F=Friday.)
 

10

6

In the Faculty sheet, remove duplicate records from the table.
 

5

7

Copy the Faculty sheet, place the copied worksheet to the right of the Faculty sheet, and then rename the duplicate worksheet Rooms.
 

10

8

Sort the data in the Rooms sheet by Room in ascending order, then by Days using the custom sort order you created in step 5, and finally by Start Time from earliest to latest time. Delete the ID, Course Number, and Instructor columns in the Rooms sheet.
 

5

9

Copy the Rooms sheet, place the copied worksheet to the right of the Rooms sheet, and then rename the duplicate worksheet Prime Time.
 

10

10

Filter the table in the Prime Time sheet to show only classes scheduled on any combination of Monday, Wednesday, and Friday. Do not include any combination of Tuesday or Thursday classes though.
 

6

11

Keep the existing filter and add a filter to display classes that start between 9:00 AM and 12:00 PM.
 

5

12

In the Faculty sheet, insert a field on the right side of the Room field. Type the label Capacity. Insert a lookup function that looks up the room number, compares it to the lookup table in the Room Capacity worksheet, and returns the room capacity. Make sure the function copies down the entire column.
 

15

13

Select the first three sheet tabs, set 0.2-inch left and right margins, Landscape orientation, and 95% scaling. Repeat the column labels on all pages.
Note from Instructor: After completing this step take a minute to make sure the 95% scaling was applied to all the sheets. For some reason it did not work for me when it was grouped the first time, when I tested it a second time it did work. So just check to see that it does not just apply it to the first sheet you had selected when you did your grouping of sheets.
 

10

14

On the Faculty sheet, set these column widths: 8.43 (64 Pixels) - Capacity and 25 (180 Pixels) - Instructor.
 

6

15

On the Faculty sheet, insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side.
 

6

16

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Faculty, Rooms, Prime Time, Room Capacity.  Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
 

0

Total Points

100