help is very appreciated

The Reservation_Analysis sheet displays data regarding reservations made by customers during the week of September 4,2022 through September 10,2022 . You must help the manager of CVRCC complete the Reservation_Analysis sheet. Use the IFS function to multiply the correct percentage discount times the Room_Rate. If the customer is not a rewards member and does not have a coupon, then he/she does not receive any discount. (Hint: AND functions retum a True or a False. The logical tests in the IFS any discount. (Hint: AND functions return a True or a False. The logical tests in the IFS function should evaluate whether the above names equal True.) Copy the formula in cell L5 to cell range L6:L 36 . Place a formula in cell M5 of the Reservation_Analysis sheet that calculates the room total. The room total equals the discounted room rate times the number of nights. (The discounted room rate equals the room rate minus the discount.) Copy the formula in cell M5 down the CVRCC is charged a fee by the credit card issuer when a customer pays their bill with a credit card. This credit card fee reduces the revenue that CVRCC earns. The credit card fees are based on a percentage of the Room_Total. The percentages are listed on the Rates\&Fees sheet. Use Create from Selection to name cells B2:B4 on the Rates\&Fees sheet. The cells should be named based on the labels in the left column (i.e., the labels in cells A2:A4). Make sure you use Create from Selection to complete this step; otherwise, your range names may not be correct, and you will not receive credit for this step. may not be correct, and you will not receive credit for this step. Enter a formula in cell N5 of the Reservation_Analysis sheet that calculates the credit card fee IFS function.) The credit card fee equals the appropriate credit card percentage times the room total. The credit card percentage depends on the Payment_Type (i.e., credit card used). Use the names created in step 14 above to refer to the credit card percentages in your formula. Copy the formula in cell N5 to cell range N6:N36. Customer Service Representatives are assigned to each customer who stays at CVRCC. The representatives are responsible for making sure that customers' needs are met. The manager of CVRCC would like to randomly assign a customer service representative to each reservation (Transaction_Code) on the CustRepAssign sheet. Each representative must be assigned to the number of reservations shown in cell J14 of the CustRepAssign sheet. 16 Enter the RAND function in cells D5:D36 of the CustRepAssign sheet. Copy and paste the random number values into DS:D36. Use these random numbers to perform calculations that assign each customer to one of the customer service representative IDs that appears in cells J5:J8 of the CustRepAssign sheet. Use the ROUNDUP and RANK.EQ functions in cells E5:E36. Use the INDEX function in cells F5:F36. The customer service representative IDs should be displayed in cells F5:F36. (An example of the formulas can be found on slide 15 of the ACIS 2504 Excel Chapter 12 PowerPoint Slides.) The manager of CVRCC for would like to see descriptive statistics for the room revenue amounts on the Reservation_Analysis sheet. Use the Analysis ToolPak to display the Summary Statistics for Room_Revenue. Include the second largest and second smallest values in the statistics. Place the Summary Statistics on the sheet named Descriptive Statistics. The top left corner of the Summary Statistics should be in cell A1 of the Descriptive Statistics sheet. The Activities worksheet displays a list of activities offered by CVRCC. Cell range J4:J39 shows the average weekly customer participation in the activities. The manager of CVRCC would like to classify the activities as either Extremely Popular, Very Popular, Somewhat Popular or Not Popular depending on the value of Ave_Weekly_Particpation. Use a VLOOKUP function in cell K4 to classify the activity. Before entering the VLOOKUP formula in cell K4, you must create the lookup table (i.e., the table_array). The top left corner of the data for the table_array must be in cell R.3 on the Activities worksheet. Place appropriate labels for the table_array in cells R2 and S2 of the Activities sheet. Use the following information to create the table array: 1mportant Nore: ino not smply copy and paste the above table into your homework assignment. The table is not set up correctly. You must rearrange it. Copy the VLOOKUP formula in cell K4 to cell range K5:K39. The classifications (Extremely Popular, Very Popular, Somewhat Popular and Not Popular must be spelled and capitalized correctly. Place a formula in cell O4 of the Activities sheet that displays the Activity_ID for the activity description that appears in cell O3. The formula should be able to find the Activity_ID for any valid description entered in cell O3. Use the INDEX and MATCH functions to do this. Nest the formula inside an IFERROR function. If the description of an Activity that is not on the Activities sheet is entered in cell O3, the following text should be displayed: Activity not found. (Note: Only the A in Activity should be capitalized. Do not include a period at the end.) Copy the formula from O4 to cell range O5:O13 so that the Category, Type, Start_Time, Number_of_Hours, Restrictions, Cost_per_Adult, Cost_Per_Child, Ave_Weekly_Participation and Popularity are displayed for the description in cell O3. Note: Do NOT use a lookup function for any part of this step. The manager of CVRCC would like to know what total Room_Revenue is for the DBL-Suite in building BML and the DBL-Suite in building SRL Enter a formula in cell R2 of the Database sheet that uses a DATABASE function to calculate this total. Use the data on the Reservation_Analysis sheet. You must complete the criteria range in cells A1:O3 of the Database sheet before entering the formula. Format the value as currency. The manager of CVRCC would like to know the total number of transactions involving a rewards member who has a coupon and stays more than 5 nights. Enter a formula in cell R7 of the Database sheet that uses a DATABASE function to calculate this total. Use the data on the Reservation_Analysis sheet. You must complete the criteria range in cells A6.07 of the Database sheet before entering the formula. The manager of CVRCC is considering adding three deluxe tours to the activities offered by the resort. The manager would like to know how many customers must go on each tour in order to maximize total net income from the tours, Go to the Tours worksheet, which is used to calculate revenues, expenses, and net income for each tour. Use Solver to maximize total net income (cell I18). Use the number of customers on each tour (range D5:F5) as the changing variable cells. Enter the constraints based on the information provided in the table below. Use GRG Nonlinear as the solving method to find a global optimal solution. Save the Solver model in cell H21. Solve the model, keep the Solver solution and produce an Answer Report. The name of the sheet containing the Answer Report must be Answer Report 1 . The manager of CVRCC would like to know the maximum number of nights a specific room type in a specific building was reserved. Enter a MAXIFS function in cell B28 of the Summary sheet that displays the maximum Number_of_Nights reserved in a DBL-Reg room in building BML. The formula must reference cells A28 and B27 and it must use the data on the Reservation_Analysis sheet. Place appropriate $ signs in the formula so that you can copy the formula to cells C28:F28 and cells B29:F31. The manager of CVRCC would like to see a list of the extremely popular family activities offered by the resort. Use the FILTER function to display the list. Enter the FILTER function in cell B35 of the Summary sheet. The formula must reference cells A35 and A.36 and it must use the data on the Activities sheet. Click the Documentation worksheet and update cells A20:C23 of the Change Log. Briefly describe at least four major modifications made to the workbook. Each modification should relate to a different sheet in the workbook and should be described on a separate row of the Change Log. Enter the modification date and your name along with a description of each modification. If necessary, wrap the text in the cell containing the description. You do not need to enter a Last Version Backup Name Weekly Reservation Report and Activity List MatdWDs A. 15 Blve Mountain Lodge Rates 29 Hickory Lake Lodge Rates 36 Silver Ridge Lodge Rates \begin{tabular}{|l|r|r|} \hline 37 & Room_Type & Nightly_Room_Rate \\ \hline 38 & D8L-Reg & $275.00 \\ 39 & King-Reg + & $290.00 \\ 40 & DBL-Suite & $305.00 \\ 41 & King-Suite & $330.00 \\ \hline 42 & & \end{tabular} 43 Tall Oaks Lodge Rates 49 50 51 52 Honor Code Documentation Customer_Data Reservation_An Catawba Valley Resort and Convention Center Summary for 9/3/20022 turush 6/1er2022 The Reservation_Analysis sheet displays data regarding reservations made by customers during the week of September 4,2022 through September 10,2022 . You must help the manager of CVRCC complete the Reservation_Analysis sheet. Use the IFS function to multiply the correct percentage discount times the Room_Rate. If the customer is not a rewards member and does not have a coupon, then he/she does not receive any discount. (Hint: AND functions retum a True or a False. The logical tests in the IFS any discount. (Hint: AND functions return a True or a False. The logical tests in the IFS function should evaluate whether the above names equal True.) Copy the formula in cell L5 to cell range L6:L 36 . Place a formula in cell M5 of the Reservation_Analysis sheet that calculates the room total. The room total equals the discounted room rate times the number of nights. (The discounted room rate equals the room rate minus the discount.) Copy the formula in cell M5 down the CVRCC is charged a fee by the credit card issuer when a customer pays their bill with a credit card. This credit card fee reduces the revenue that CVRCC earns. The credit card fees are based on a percentage of the Room_Total. The percentages are listed on the Rates\&Fees sheet. Use Create from Selection to name cells B2:B4 on the Rates\&Fees sheet. The cells should be named based on the labels in the left column (i.e., the labels in cells A2:A4). Make sure you use Create from Selection to complete this step; otherwise, your range names may not be correct, and you will not receive credit for this step. may not be correct, and you will not receive credit for this step. Enter a formula in cell N5 of the Reservation_Analysis sheet that calculates the credit card fee IFS function.) The credit card fee equals the appropriate credit card percentage times the room total. The credit card percentage depends on the Payment_Type (i.e., credit card used). Use the names created in step 14 above to refer to the credit card percentages in your formula. Copy the formula in cell N5 to cell range N6:N36. Customer Service Representatives are assigned to each customer who stays at CVRCC. The representatives are responsible for making sure that customers' needs are met. The manager of CVRCC would like to randomly assign a customer service representative to each reservation (Transaction_Code) on the CustRepAssign sheet. Each representative must be assigned to the number of reservations shown in cell J14 of the CustRepAssign sheet. 16 Enter the RAND function in cells D5:D36 of the CustRepAssign sheet. Copy and paste the random number values into DS:D36. Use these random numbers to perform calculations that assign each customer to one of the customer service representative IDs that appears in cells J5:J8 of the CustRepAssign sheet. Use the ROUNDUP and RANK.EQ functions in cells E5:E36. Use the INDEX function in cells F5:F36. The customer service representative IDs should be displayed in cells F5:F36. (An example of the formulas can be found on slide 15 of the ACIS 2504 Excel Chapter 12 PowerPoint Slides.) The manager of CVRCC for would like to see descriptive statistics for the room revenue amounts on the Reservation_Analysis sheet. Use the Analysis ToolPak to display the Summary Statistics for Room_Revenue. Include the second largest and second smallest values in the statistics. Place the Summary Statistics on the sheet named Descriptive Statistics. The top left corner of the Summary Statistics should be in cell A1 of the Descriptive Statistics sheet. The Activities worksheet displays a list of activities offered by CVRCC. Cell range J4:J39 shows the average weekly customer participation in the activities. The manager of CVRCC would like to classify the activities as either Extremely Popular, Very Popular, Somewhat Popular or Not Popular depending on the value of Ave_Weekly_Particpation. Use a VLOOKUP function in cell K4 to classify the activity. Before entering the VLOOKUP formula in cell K4, you must create the lookup table (i.e., the table_array). The top left corner of the data for the table_array must be in cell R.3 on the Activities worksheet. Place appropriate labels for the table_array in cells R2 and S2 of the Activities sheet. Use the following information to create the table array: 1mportant Nore: ino not smply copy and paste the above table into your homework assignment. The table is not set up correctly. You must rearrange it. Copy the VLOOKUP formula in cell K4 to cell range K5:K39. The classifications (Extremely Popular, Very Popular, Somewhat Popular and Not Popular must be spelled and capitalized correctly. Place a formula in cell O4 of the Activities sheet that displays the Activity_ID for the activity description that appears in cell O3. The formula should be able to find the Activity_ID for any valid description entered in cell O3. Use the INDEX and MATCH functions to do this. Nest the formula inside an IFERROR function. If the description of an Activity that is not on the Activities sheet is entered in cell O3, the following text should be displayed: Activity not found. (Note: Only the A in Activity should be capitalized. Do not include a period at the end.) Copy the formula from O4 to cell range O5:O13 so that the Category, Type, Start_Time, Number_of_Hours, Restrictions, Cost_per_Adult, Cost_Per_Child, Ave_Weekly_Participation and Popularity are displayed for the description in cell O3. Note: Do NOT use a lookup function for any part of this step. The manager of CVRCC would like to know what total Room_Revenue is for the DBL-Suite in building BML and the DBL-Suite in building SRL Enter a formula in cell R2 of the Database sheet that uses a DATABASE function to calculate this total. Use the data on the Reservation_Analysis sheet. You must complete the criteria range in cells A1:O3 of the Database sheet before entering the formula. Format the value as currency. The manager of CVRCC would like to know the total number of transactions involving a rewards member who has a coupon and stays more than 5 nights. Enter a formula in cell R7 of the Database sheet that uses a DATABASE function to calculate this total. Use the data on the Reservation_Analysis sheet. You must complete the criteria range in cells A6.07 of the Database sheet before entering the formula. The manager of CVRCC is considering adding three deluxe tours to the activities offered by the resort. The manager would like to know how many customers must go on each tour in order to maximize total net income from the tours, Go to the Tours worksheet, which is used to calculate revenues, expenses, and net income for each tour. Use Solver to maximize total net income (cell I18). Use the number of customers on each tour (range D5:F5) as the changing variable cells. Enter the constraints based on the information provided in the table below. Use GRG Nonlinear as the solving method to find a global optimal solution. Save the Solver model in cell H21. Solve the model, keep the Solver solution and produce an Answer Report. The name of the sheet containing the Answer Report must be Answer Report 1 . The manager of CVRCC would like to know the maximum number of nights a specific room type in a specific building was reserved. Enter a MAXIFS function in cell B28 of the Summary sheet that displays the maximum Number_of_Nights reserved in a DBL-Reg room in building BML. The formula must reference cells A28 and B27 and it must use the data on the Reservation_Analysis sheet. Place appropriate $ signs in the formula so that you can copy the formula to cells C28:F28 and cells B29:F31. The manager of CVRCC would like to see a list of the extremely popular family activities offered by the resort. Use the FILTER function to display the list. Enter the FILTER function in cell B35 of the Summary sheet. The formula must reference cells A35 and A.36 and it must use the data on the Activities sheet. Click the Documentation worksheet and update cells A20:C23 of the Change Log. Briefly describe at least four major modifications made to the workbook. Each modification should relate to a different sheet in the workbook and should be described on a separate row of the Change Log. Enter the modification date and your name along with a description of each modification. If necessary, wrap the text in the cell containing the description. You do not need to enter a Last Version Backup Name Weekly Reservation Report and Activity List MatdWDs A. 15 Blve Mountain Lodge Rates 29 Hickory Lake Lodge Rates 36 Silver Ridge Lodge Rates \begin{tabular}{|l|r|r|} \hline 37 & Room_Type & Nightly_Room_Rate \\ \hline 38 & D8L-Reg & $275.00 \\ 39 & King-Reg + & $290.00 \\ 40 & DBL-Suite & $305.00 \\ 41 & King-Suite & $330.00 \\ \hline 42 & & \end{tabular} 43 Tall Oaks Lodge Rates 49 50 51 52 Honor Code Documentation Customer_Data Reservation_An Catawba Valley Resort and Convention Center Summary for 9/3/20022 turush 6/1er2022