Reference no: EM131434699

Assignment- Fitness Classes

Project Description:

The hotel has started facilitating fitness classes for hotel guests. Guests can register for classes prior to their arrival or when they arrive to the hotel. Several instructors lead the classes, and each instructor has multiple skills. The fitness center manager would like to have a spreadsheet developed that will track class enrollment. The spreadsheet will provide an overview of guest enrollment with some analysis, which will be used for better decision-making.

Instructions

1 Start Excel. Open the downloaded Excel file named e03ps1_grader_h1_start.xlsx. Save the file with the name e03ps1Fitness_LastFirst replacing LastFirst with your name.

2 Click the Input Data worksheet tab. Select range B11:H14. Create named ranges using the top row as the name for your named ranges. Each range is the list of instructors that can teach individual classes.

3 Select range B4:H8. Create named ranges using the top row as the name for your named ranges. Each range includes fitness class details.

4 Click the Enrollment worksheet tab. In cell A14, insert a table using range A13:D55 and the top row as the headers.

5 In cell D14, enter a HLOOKUP function that will look up the class number in cell B14 and use the ClassInfo table to return the Fee from the fifth row of the table with an exact match. Copy the formula down to cell D55, if necessary.

6 In cell E13, create a new column header named Class Name. In cell E14, enter a HLOOKUP function that will look up the class number in cell B14 and use the ClassInfo table to return the Class Category name from the second row of the table with an exact match. Copy the formula down to cell E55, if necessary.

7 Select A13:E55, and then name the entire range Enrollment. Create the range so that it refers to the cell range itself and not the table name. Create named ranges using the top row as the name for your named ranges. Copy range A13:E13, and then paste in range A1:E1 to set up the advanced filter criteria area. In cell C2, type F. In cell E2, type Yoga.

8 In cell B6, create a DCOUNTA function to count the Student_ID field in the Enrollment table that meet the filter criteria specified in range A1:E2.

In cell B7, create a DAVERAGE function to average the Fee field in the Enrollment table that meet the filter criteria specified in range A1:C2. This will average all fees collected from females.

In cell B8, create a DSUM function to sum the Fee field in the Enrollment table that meet the filter criteria specified in range A1:E2. This will total all yoga fees collected from females.

9 Click the Report worksheet tab. Use the following information to create calculations that will help hotel employees manage the fitness class enrollments. The user will put an "x" in range E4:E10, indicating which class to report upon and an "x" in range H4:H5 if employees want a report on a specific gender. In cell A4, use a MATCH function nested in an INDEX function to retrieve the Class that was selected in E4:E10. The MATCH should find the row where the "x" is located and would be used within the INDEX to pull the associated Class value from the same row within range F4:F10.

In cell B4, use a MATCH nested in an INDEX function to retrieve the Gender that was selected in H4:H5, looking at the "x" in column H and returning the F or M for the Gender criteria. Using a MATCH nested in an INDEX function, retrieve the gender that was selected in H4:H5. Nest the MATCH and INDEX formula inside of the IFERROR function, in case the user does not select a specific gender. The IFERROR should leave the cell blank, using", if a gender is not selected.

10 In cell C4, create a COUNTIF formula that counts the enrollment for the named range Class that has the class number listed in A4. The range criteria should reference the Class named range.

In cell B7, create a HLOOKUP formula that will look up the Class in A4 within the ClassInfo named range and return the maximum enrollment, which is in the third row of that table. The value should be looking for an exact match.

11 In cell B8, create a HLOOKUP formula that will use the Class in A4 and retrieve the Class Category type from the ClassInfo named range in row 2, also looking for an exact match.

In cell B11, create an IF function to indicate the availability of reservations. If the number enrolled in C4 is greater than or equal to the maximum enrollment in B7, then FULL OR OVERBOOKED should display. Otherwise, Spots Available should display.

12 The instructors for each class are listed on the Input Data worksheet in range B12:H14. The instructors for the Aerobics class need to be counted. In cell B12, create a complex function that will determine the number of instructors for the class listed in A4. Use the COUNTA, INDIRECT, INDEX, and MATCH functions.

Click cell B13. Using an HLOOKUP nested in an AND function nested in an IF function, return either Split Class or Can't Split based on business options. Two conditions are needed to determine if a class can be split. Using the ClassInfo table, one row shows if a class can be split. That condition can be determined with a HLOOKUP. The second is if there is more than one instructor as shown in cell B12. If both conditions are met, the class can be split. Otherwise, the class cannot be split.

13 Insert a PivotTable using the Enrollment named range on the Enrollment worksheet. Insert the PivotTable on a new worksheet named Pivot Analysis. Add the Student_ID, Gender, and Class Name fields to the PivotTable. Move Gender to the COLUMNS area. Move Student_ID to the VALUES area. In cell B3, replace Column Labels with Gender. In cell B4, replace F with Female. In cell C4, replace M with Male. In cell A3, replace Count of Student_ID with Total Students. In cell A4, replace Row Labels with Classes. Resize the column widths as needed. Apply Pivot Style Light 10 to the PivotTable.

14 Insert a Clustered Column PivotChart on the Pivot Analysis worksheet. Reposition the PivotChart so the upper left corner is in the top left corner of cell F3. Drag the lower right corner so the PivotChart fills column N. Change the color of the PivotChart to Color 13. Apply Style 8 to the PivotChart. Add a chart title above the chart. Replace Chart Title with Enrollment by Class and Gender.

15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Pivot Analysis, Enrollment, Input Data, Report. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Attachment:- mosken_e03ps1_grader_h1_start.xlsx