Modify the spreadsheet (add at least one column for each item 1 to 3) to calculate: (1) The number of days that each party stayed at the inn. (2) Daily rates considering the number of guest(s), Hint: Use VLookUp function; create a resort fee table using the data above. (3) Revenue collected from each party for their total stay at the guest house (4) Total revenue generated for the month from all guests Add one additional sheet to the spreadsheet to address the following items: (5) Create a pivot table to calculate the total revenue categorized by each room type for December. (6) Show a pie chart to display the revenue breakdown by room type from the previous item.

Graduates' Inn Guest Database Room Type Ocean Bay Window Side Additional Cost/Person/Day $35 $30 $20 ID Guest First Name Guest Last Name 1 Barry Lloyd 2 Michael Lunsford 3 Kim Kyuong 4 Edward Holt 5 Thomas Collins 6 Paul Bodkin 7 Randall Battenburg 8 Calvin Nowotney 9 Homer 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 Tilson 19 Everett Chad 20 Gerald Pittsfield 21 Jamal Smith 22 Louis Paris 23 Nigel Stratford 24 Peter Willington 25 Ronald Cartier 26 Trista Leven 27 Valerie Snell 28 Walter Acton 29 Xavier Trezza 30 Zachary Miller Room Room Type Arrival Date Departure Date Hayes Bay-window 12/1/18 12/4/18 Cleveland Ocean 12/1/18 12/9/18 Coolidge Bay-window - 12/4/18 12/7/18 Washington Ocean 12/1/18 12/3/18 Lincoln Ocean 12/9/18 12/13/18 Coolidge Bay-window 12/1/18 12/3/18 Washington Ocean 12/4/18 12/12/18 Lincoln Ocean 12/2/18 12/4/18 Lincoln Ocean 12/5/18 12/7/18 Jefferson Bay-window 12/5/18 12/7/18 Jackson Ocean 12/5/18 12/8/18 Reagan Bay-window 12/10/18 12/15/18 Truman Side 12/20/18 12/30/18 Garfield Side 12/13/18 12/15/18 Arthur Ocean 12/24/18 12/31/18 Quincy Adams Bay-window 12/3/18 12/6/18 Johnson Ocean 12/5/18 12/7/18 Van Buren Bay-window 12/5/18 12/7/18 Madison Ocean 12/10/18 12/14/18 Roosevelt 12/5/18 12/7/18 Tyler Bay-window 12/20/18 12/23/18 Jackson Ocean 12/10/18 12/14/18 Eisenhower Ocean 12/14/18 12/16/18 Grant Ocean 12/19/18 12/21/18 Jefferson Bay-window 12/24/18 12/28/18 Eisenhower Ocean 12/17/18 12/20/18 Adams Bay-window 12/3/18 12/6/18 Polk Side 12/24/18 12/31/18 McKinley Side 12/14/18 12/17/18 Washington Ocean 12/13/18 12/16/18 No of Guests Daily Rate Length Of Stay Daily Rates WI Guests 2 $150.00 3 3 $112.50 8 1 $150.00 3 4 $325.00 2 2 2 $300.00 4 2 2 $150.00 2 2 2 $292.50 8 1 $300.00 2 5 $320.00 2 4 $175.00 2 2 $250.00 3 1 $150.00 5 3 $112.50 10 2 $125.00 2 5 $198.00 7 2 2 $150.00 3 3 $250.00 2 1 $150.00 2 2 $275.00 4 2 $275.00 2 2 $150.00 3 1 $250.00 4 2 $200.00 2 1 $200.00 2 4 $175.00 4 1 $200.00 3 1 $150.00 3 6 $148.50 7 2 $125.00 3 2 $325.00 $ 3 Ocean