Part 3: 7 points. Reservation Worksheet - Instructions The Daily Charge is determined by using the Room Number and Rate Code to lookup the value in the DailyChg worksheet. Use the XLOOKUP() function with a nested XLOOKUP() function to find the correct daily charge and display it in column I of the Reservation worksheet. Use the IFS function to determine the seating surcharge (column K) based upon the value in column H. The seating codes are: CI, CL, LE, US. The CI seating style costs an addition $50, CL is the standard - for which there is no additional charges, LE is an upcharge of $125, and US costs an additional $200. Don't hardcode the values in the function, but rather, create an input area on the DailyChg worksheet. Calculate the Reservation's Total Charges using formula of Daily Charge * Booked days + Seating Surcharge. Use absolute cell references or named ranges in your formulas. Format the data, to present meaningful and an easy to consume report. B 1 2 3 E G H Megan Davis Convention Center Reservation Analysis Worksheet May 1, 2023 J K L Reservation Room 4 Number Number Customer Number Reservation Reservation Reservation Seating Style Booked Rate Code 5 R0010 AL 60001 AD Date 10/7/2023 Start Date End Date Code Daily Charge Days Seating Surcharge Total Charges 3/8/2024 3/8/2024 LE $ 450.00 1 $ 125.00 $ 575.00 6 R0010 CA C0001 AD 10/7/2023 3/8/2024 3/8/2024 CI $ 487.50 1 $ 50.00 $ 537.50 7 R0011 CO C0002 ST 10/7/2023 3/8/2024 3/8/2024 US $ 625.00 1 $ 200.00 $ 825.00 8 R0012 FL C0003 SP 10/7/2023 3/8/2024 3/8/2024 CL $ 390.00 1 $ - $ 390.00 9 R0013 AL C0004 AD 10/26/2023 4/1/2024 4/4/2024 LE $ 450.00 4 $ 125.00 $ 1,925.00 10 R0013 GA C0004 AD 10/26/2023 4/1/2024 4/4/2024 LE $ 431.25 4 $ 125.00 $ 1,850.00 11 R0014 AL C0005 AD 11/17/2023 5/2/2024 5/6/2024 CL $ 450.00 5 $ - $ 2,250.00 12 R0014 CA C0005 ST 11/17/2023 5/2/2024 5/6/2024 CL $ 650.00 5 $ - $ 3,250.00 13 R0014 CO C0005 ST 11/17/2023 5/2/2024 5/6/2024 CL $ 625.00 5 $ $ 3,125.00 A B C D E F G H J K L M 1 2 3 Megan Davis Convention Center Reservation Analysis Worksheet May 1, 2023 Reservation Room Customer 4 Number Number Number Rate Code 5 R0010 AL C0001 AD Reservation Date 10/7/2023 Reservation Reservation Start Date End Date Seating Style Code Seating 3/8/2024 3/8/2024 LE Daily Charge Booked Days Surcharge 1 Total Charges 6 R0010 CA C0001 AD 10/7/2023 3/8/2024 3/8/2024 CI 1 7 R0011 CO C0002 ST 10/7/2023 3/8/2024 3/8/2024 US 1 8 R0012 FL C0003 SP 10/7/2023 3/8/2024 3/8/2024 CL 1 9 R0013 AL C0004 AD 10/26/2023 4/1/2024 4/4/2024 LE 4 10 R0013 GA C0004 AD 10/26/2023 4/1/2024 4/4/2024 LE 4 11 R0014 AL C0005 AD 11/17/2023 5/2/2024 5/6/2024 CL 5 12 R0014 CA C0005 13 R0014 CO C0005 14 14 R0014 FL C0005 15 R0014 GA C0005 16 R0003 CA C0006 17 R0003 CO C0006 AD 18 R0004 FL C0007 19 R0005 GA C0008 20 R0006 AL C0009 21 R0007 FL C0010 AD 22 R0007 GA C0010 23 R0008 CA 11 NHL Reservation KB 6 6 99966 9999 11/17/2023 5/2/2024 5/6/2024 CL 5 ST 11/17/2023 5/2/2024 5/6/2024 CL 5 11/17/2023 5/2/2024 5/6/2024 CL 5 ST 11/17/2023 5/2/2024 5/6/2024 CL 5 AD 9/4/2023 5/7/2024 5/7/2024 LE 1 9/4/2023 5/7/2024 5/7/2024 CI 1 ST 9/6/2023 5/7/2024 5/7/2024 LE 1 9/7/2023 5/7/2024 5/7/2024 LE 1 ST 9/24/2023 5/7/2024 5/7/2024 LE 1 9/25/2023 5/8/2024 5/9/2024 LE 2 AD 9/25/2023 5/8/2024 5/9/2024 LE 2 AD 9/27/2023 5/8/2024 5/10/2024 CL 3 DailyChg ABC Payroll Pivot1 Shipping Report Pivot2 LoanTable + 1 - 2 A B C D Megan Davis Convention Center Daily Meeting Room Rates E F G 3 Room# Meeting Room AD ST SP 4 AL Alabama $450.00 $600.00 $390.00 S AU Auditorium $918.75 $1,225.00 $735.00 6 BD Boardroom $412.50 $550.00 $330.00 7 CA California $487.50 $650.00 $422.50 8 CF Conference Center $975.00 $1,500.00 $900.00 9 CO Colorado $468.75 $625.00 $375.00 10 FL Florida $450.00 $600.00 $390.00 11 GA Georgia $431.25 $575.00 $431.25 12345