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.

 

Transcribed Image Text:

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

Answer rating: 100% (QA)

1 Here simply we need to subtract arrival date from departure date 2 Use the formula i... View the full answer