Room Type Ocean Bay-Window Side Additional cost/person/day $35 $30 $20 Deliverable: Modified excel file with your name and student ID on it. Modify the spreadsheet (add one column for each question from 1 to 3) to calculate: (1) The number of days that each party stayed at the inn [5 points] (2) Daily rates considering the number of guest(s), Hint: Use VLookup function. You will need to create an additional cost table using the data available to you. (5 points] (3) Revenue collected from each party for their total stay at the guest house [2.5 points] and total revenue generated for the month from all guests [2.5 points) Add one additional sheet to the spreadsheet to answer the following questions. (4) Use the SUMIF() function to calculate the revenue collected from each room type for the whole month. [5 pts] (5) Show a pie chart to display the revenue breakdown from Q(4) (2.5 points). Which room type generated highest revenue and how much revenue? [2.5 points) X Fx ID E Graduates' Inn Guest Database Collins I Guest First Name Guest Last Name 1 Barry Lloyd 2 Michael Lunstard 3 Kim Kyuong 4 Edward Holt 5 Thomas 6 Paul Bodkin 7 Randall Battenburg 4 CM . Nowotney 9 Horner Gonzalez 10 David Sanchez 11 Buster Whisler 12 Julia Martines 13 Samuel Kim 14 Arthur Gottfried 15 Darlene Shore 16 Carlyle Charleston 17 Albert Goldstone 18 Charlene Tilman 19 Everett Chad 20 Gerela Pittsfield 21 Jamal Smith 22 Louis Paris 23 Nigel Stratford 24 Peter Willington 25 Ronald Cartier 26 Trista Leven 27 Valerie Snell 28 Water Acton 29 Xavier Trezza 30 Zachary Miler Room Hayes Cleveland Coolidge Washington Lincoln Coolidge Washington Lincoln Lincoln Jafferson Jackson Reagan Truman Garfield Arthur Quincy Adams Johnson Van Buren Madison Roosevelt Tyler Jackson Eisenhower Orant Jefferson Eisenhower Adams Polk McKinley Washington Raam Type Bay window Ornan Bay-window Ocean Ocean Bay-window Ocean Ocean Ocean Bay-window Ocean Bay-window Side Side Ocean Bay-window Ocean Bay-window Ocean Ocean Bay-window Ocean | ) Ocean Bay-window Ocean Bay-window Side Side Ocean Arrival Date 12/1/18 12/1/18 124/18 12/1/18 121018 12/1/18 12/4/18 121218 12/5/18 12/6118 121618 12/10/18 12120/18 12/13/14 12/24/18 12318 121518 121518 12/10/18 125/18 12/20/18 12/10/18 12/14/12 12/19/18 12/24/18 12/17/18 123/18 12/24/18 12/14/18 12/13/18 Departure Date 12/4/10 12 /12 1277/18 12/3/18 12/1318 1212/19 12/12/18 12/18 1217/18 12/7/19 12/8/10 12/15/18 12/30/18 12/15/18 12/31/18 12/8/18 12/7/18 127119 12/14/18 12/7/18 12/2318 12/14/15 12/18/18 12/21/18 12/28/18 12/2016 12N6/19 12/31/18 12/17/18 12/18/16 No of Guests Daily Rate S150.00 3 S112.50 1 $150.00 4 $325.00 S300.00 $10.00 $292.50 $300.00 S320.00 S175.00 $250.00 S150.00 $112.50 S125.00 $199.00 $150.00 $250.00 S150.00 S275,00 S275.00 S150.00 S250.00 $200.00 S200.00 $175.00 S200.00 $10.00 S148.50 $125.00 2 S325.00 Room Type Ocean Bay-Window Side Additional cost/person/day $35 $30 $20 Deliverable: Modified excel file with your name and student ID on it. Modify the spreadsheet (add one column for each question from 1 to 3) to calculate: (1) The number of days that each party stayed at the inn [5 points] (2) Daily rates considering the number of guest(s), Hint: Use VLookup function. You will need to create an additional cost table using the data available to you. (5 points] (3) Revenue collected from each party for their total stay at the guest house [2.5 points] and total revenue generated for the month from all guests [2.5 points) Add one additional sheet to the spreadsheet to answer the following questions. (4) Use the SUMIF() function to calculate the revenue collected from each room type for the whole month. [5 pts] (5) Show a pie chart to display the revenue breakdown from Q(4) (2.5 points). Which room type generated highest revenue and how much revenue? [2.5 points) X Fx ID E Graduates' Inn Guest Database Collins I Guest First Name Guest Last Name 1 Barry Lloyd 2 Michael Lunstard 3 Kim Kyuong 4 Edward Holt 5 Thomas 6 Paul Bodkin 7 Randall Battenburg 4 CM . Nowotney 9 Horner Gonzalez 10 David Sanchez 11 Buster Whisler 12 Julia Martines 13 Samuel Kim 14 Arthur Gottfried 15 Darlene Shore 16 Carlyle Charleston 17 Albert Goldstone 18 Charlene Tilman 19 Everett Chad 20 Gerela Pittsfield 21 Jamal Smith 22 Louis Paris 23 Nigel Stratford 24 Peter Willington 25 Ronald Cartier 26 Trista Leven 27 Valerie Snell 28 Water Acton 29 Xavier Trezza 30 Zachary Miler Room Hayes Cleveland Coolidge Washington Lincoln Coolidge Washington Lincoln Lincoln Jafferson Jackson Reagan Truman Garfield Arthur Quincy Adams Johnson Van Buren Madison Roosevelt Tyler Jackson Eisenhower Orant Jefferson Eisenhower Adams Polk McKinley Washington Raam Type Bay window Ornan Bay-window Ocean Ocean Bay-window Ocean Ocean Ocean Bay-window Ocean Bay-window Side Side Ocean Bay-window Ocean Bay-window Ocean Ocean Bay-window Ocean | ) Ocean Bay-window Ocean Bay-window Side Side Ocean Arrival Date 12/1/18 12/1/18 124/18 12/1/18 121018 12/1/18 12/4/18 121218 12/5/18 12/6118 121618 12/10/18 12120/18 12/13/14 12/24/18 12318 121518 121518 12/10/18 125/18 12/20/18 12/10/18 12/14/12 12/19/18 12/24/18 12/17/18 123/18 12/24/18 12/14/18 12/13/18 Departure Date 12/4/10 12 /12 1277/18 12/3/18 12/1318 1212/19 12/12/18 12/18 1217/18 12/7/19 12/8/10 12/15/18 12/30/18 12/15/18 12/31/18 12/8/18 12/7/18 127119 12/14/18 12/7/18 12/2318 12/14/15 12/18/18 12/21/18 12/28/18 12/2016 12N6/19 12/31/18 12/17/18 12/18/16 No of Guests Daily Rate S150.00 3 S112.50 1 $150.00 4 $325.00 S300.00 $10.00 $292.50 $300.00 S320.00 S175.00 $250.00 S150.00 $112.50 S125.00 $199.00 $150.00 $250.00 S150.00 S275,00 S275.00 S150.00 S250.00 $200.00 S200.00 $175.00 S200.00 $10.00 S148.50 $125.00 2 S325.00