Reference no: EM131400845

Assignment: Managing Students at a Local Music Studio

1.Project Description:

Managers at the hotel have been using a simple dashboard to keep track of revenues and room reservations. You have been asked to enhance the dashboard by adding some form controls, a Bing map, and VBA.

Instructions:

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

1 Start Excel. Open the downloaded Excel file named e03ws05_grader_h1.xlsx. Save the file with the name e03ws05Lessons_LastFirst replacing LastFirst with your name. In the Security Warning bar, click Enable Content.

2 On the Student Data worksheet, select the range A7:K17, and then name the range Student_Data. With range A7:K17 selected, create a named range for each column in the Student_Data named range using the top row as the range values.

3 Click the Data Inputs worksheet tab. Select the range A3:B3, and then create a named range using the left column as the name. Select the range A6:E10, and then create a named range using Lesson_Pricing as the name. Select the range A13:E17, and then create a named range using Trans_Fees as the name. Select the range A20:G24, and then create a named range using Uniform_Fees as the name. Select the range D3:F4, and then create a named range using Entry_Fee as the name.

4 Click the Student Data worksheet tab. In cell E5, use the COUNTIF function to count the Teacher field in the Student_Data table that meets the filter criteria in cell E4. Copy the formula in cell E5 over to cell I5. In cell K5, use the DCOUNTA function to count the StudentID field in the Student_Data table that meets the filter criteria in the range D1:K2.

5 In cell K8, use the VLOOKUP function nested in an IFERROR function to retrieve the price per hour located in column C in the Lesson_Pricing named range for each student based on the student's skill level located in the Skill_level field. The value should be looking for an exact match. To prevent an error from being displayed when the skill level is not known, use the IFERROR function to leave the cell blank. Copy the formula in cell K8 down to K17.

6 Click the Lesson Data worksheet tab. In cell F3, add a MATCH function nested in an INDEX function to retrieve the skill level of each student listed in column B. To prevent zeros from displaying when the skill level is not known, nest the INDEX and MATCH functions in an IF function to leave the cell blank when the skill level equals 0. Copy the formula down to F17.

7 In cell G3, add a MATCH function nested in an INDEX function to retrieve the total fee located in the Lesson_Pricing table on the Data Inputs worksheet for each student listed in column B. To prevent an error from being displayed when the skill level is not known, nest the INDEX and MATCH functions inside an IFERROR function and display the default total fee of $50. Copy the formula in cell G3 down to G17.

8 Click the Report worksheet tab. In cell B3, use the VLOOKUP function to look up the teacher's name in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match. In cell B4, use the AVERAGEIF function to calculate the teacher's average fee in the Price_Per_Hour field for the teacher listed in cell B3.In cell B5, use the VLOOKUP function to look up the student's skill level in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match.

9 In cell B6, use the COUNTIF function to count the StudentID field on the Lesson Data worksheet that meet the filter criteria in cell B2.In cell B7, use the SUMIF function to sum the Total Fee field on the Lesson Data worksheet that meet the filter criteria in cell B2.In cell B8, use a nested IF function to determine how many free lessons the student has earned. If the student has had less than 5 total lessons, display 0 earned. If the student has had between 5 and 10 lessons, display 1 earned; otherwise, display 2 earned.

10 In cell E5, use the VLOOKUP function to look up the student's uniform size in the Student_Data table for the student listed in cell B2. The value should be looking for an exact match.

In cell E7, use the HLOOKUP function to retrieve the student's registration fee in the Entry_Fee named range for the registration type in cell E3. The value should be looking for an exact match.

In cell I13, use the COUNTIF function to count the number of uniforms in the Student_Data table that meet the filter criteria in cell H12.

11 In cell I14, add a MATCH function nested in an INDEX function to retrieve the uniform fee located in the Uniform_Fee table on the Data Inputs worksheet that meet the filter criteria in cells H12 and I13. To prevent an error from displaying when no uniforms of the size located in H12 are needed, nest the INDEX and MATCH functions inside an IFERROR function to leave the cell blank.

12 In cell B12, use the VLOOKUP function to retrieve the student's first name in the Student_Data table that meets the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in B12 down to B17.In cell C12, use the VLOOKUP function to retrieve the student's last name in the Student_Data table that meet the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in C12 down to C17.

13 In cell D12, use the VLOOKUP function to retrieve whether or not the student has a permission slip on file in the Student_Data table that meets the filter criteria in cell A12. The value should be looking for an exact match. Copy the formula in D12 down to D17.In cell E12, use the VLOOKUP function nested in an IF function to retrieve the student's uniform size. To prevent zeros from being displayed when the uniform size is not known, nest the VLOOKUP function in an IF function to leave the cell blank when the skill level equals 0. The value should be looking for an exact match. Copy the formula in E12 down to E17.

14 In cell F12, use the AND function nested in an IF function to determine if the student has a permission slip and a uniform. If the student has a permission slip and the uniform size is not blank, then display Yes in the field; otherwise, display No. Copy the formula in F12 down to F17.

15 In cell F18, use the COUNTIF function to determine how many students are currently traveling to the competition as indicated by Yes in cell range F12:F17.

16 In cell E8, use the MATCH function nested in an INDEX function to retrieve the transportation fee located in the Trans_Fee table on the Data Inputs worksheet that meet the filter criteria in cell E4 and F18.

17 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Student Data, Lesson Data, Data Inputs, Report. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Attachment:- mosken_e03ws05lessons_lastfirst.xlsx