In this exercise, you will use database software to analyze the reservation transactions for a hotel and use that information to fine-tune the hotels business strategy and marketing activities.

In Blackboard under Homework Assignment 2, you will find a excel sheet for hotel reservation transactions developed in Microsoft Excel with information about the Presidents Inn in Cape May, New Jersey. At the Inn, 4 rooms overlook side streets, 10 rooms have bay windows that offer limited views of the ocean, and the remaining 16 rooms in the front of the hotel face the ocean. Room rates are based on room choice, length of stay, and number of guests per room.

The owners currently use a manual reservation and bookkeeping system, which has caused many problems. Use the excel sheet database to develop summary reports.

Summary Report

On a separate sheet show average length of stay per room type, average number of visitors per room type, average base revenue per room type (i.e., length of visit multiplied by the daily rate), Total number of visitors per room type and total revenue per room type in a tabular format as shown in the screenshot

Charts

Show 2 charts under the summary report table

1. Show a clustered bar chart depicting all the above summary data in the bar chart for each room type. The chart should like the one in the solution screen shot
2. Show 2^nd chart as stacked area chart to depict the trend of visitors coming to the hotel per day in the month of December. The chart should resemble the one in the solution screen shot

Submit your excel file as an attachment to the homework assignment 2 submission

M23 X v fx H ID 1 2 3 4 Ocean 5 6 7 8 9 10 11 $750.00 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Guest First Name 27 Valerie 15 Darlene 2 Michael 3 Kim 6 Paul 26 Trista 23 Nigel 14 Arthur 24 Peter 1 Barry 11 Buster 22 Louis 25 Ronald 10 David 17 Albert 9 Homer 5 Thomas 8 Calvin 19 Everett 29 Xavier 28 Walter 16 Carlyle 12 Julia 20 Gerald 13 Samuel 21 Jamal 18 Charlene 30 Zachary 4 Edward 7 Randall D E Guest Last Name Room Room Type Snell Adams Bay-window Shore Arthur Ocean Lunsford Cleveland Kyuong Coolidge Bay-window Bodkin Coolidge Bay-window Leven Eisenhower Ocean Stratford Eisenhower Ocean Gottfried Garfield Side Willington Grant Ocean Lloyd Hayes Bay-window Whisler Jackson Ocean Paris Jackson Ocean Cartier Jefferson Bay-window Sanchez Jefferson Bay-window Goldstone Johnson Ocean Gonzalez Lincoln Ocean Collins Lincoln Ocean Nowotney Lincoln Ocean Chad Madison Ocean Trezza Trera McKinley Side Acton Polk Side Charleston Quincy Adam: Bay-window Martines Reagan Bay-window Pittsfield Roosevelt Ocean Kim Truman Side Smith Tyler Bay-window Tilson Van Buren Bay-window Miller Washington Ocean Holt Washington Ocean Battenburg Washington Ocean Arrival Date Departure Date Departure Date No of Guests Daily Rate Length of stay Base Revenue 12/3/18 12/6/18 1 150 3 $450.00 12/24/18 12/31/18 5 198 7 $1,386.00 12/1/18 12/9/18 3 112.5 8 $900.00 12/4/18 12/7/18 1 150 3 $450.00 12/1/18 12/3/18 2 150 2 $300.00 12/17/18 12/20/18 1 200 3 3 $600.00 12/14/18 12/16/18 2 200 2 2 $400.00 12/13/18 12/15/18 2 125 2 $250.00 12/19/18 12/21/18 1 200 2 $400.00 12/1/18 12/4/18 2 150 3 $450.00 12/5/18 12/8/18 2 250 3 . 12/10/18 12/14/18 1 250 4 $1,000.00 co 12/24/18 12/28/18 4 175 4 no $700.00 Showice 12/5/18 12/7/18 2 175 2 $350.00 12/5/18 12/7/18 3 250 2 $500.00 12/5/18 12/7/18 5 320 2 $640.00 12/9/18 12/13/18 2 2 300 4 300 $1,200.00 12/2/18 12/4/18 1 300 2 $600.00 12/10/18 12/14/18 2 275 4 4 $1,100.00 12/14/18 12/17/18 2 125 2 3 6 148.5 7 7 $1,039.50 12/3/18 12/6/18 2 150 3 $450.00 12/10/18 12/15/18 1 150 5 $750.00 12/5/18 12/7/18 2 275 2 $550.00 12/20/18 12/30/18 3 112.5 $1,125.00 12/20/18 12/23/18 2 150 3 $450.00 12/5/18 12/7/18 1 150 2 $300.00 12/13/18 12/16/18 2 325 3 $975.00 12/1/18 12/3/18 4 325 2 $650.00 12/4/18 12/12/18 2 292.5 8 $2,340.00 $375.00 12/24/18 12/31/18 26 10 27 28 29 30 31 32 Room Type Bay-window Ocean Side Average length of stay 3 3.625 5.5 Average number of visitors 1.8 2.375 3.25 Average base revenue 465 874.44 697.38 Total number of visitors 18 38 13 Total Revenue 4650 13991 2789.5 Average and Total Summary No of Guests / day 7 Total Revenue 6 5 Total number of visitors 4 Average base revenue 3 2 Average number of visitors 1 Average length of stay 0 0 2000 4000 6000 8000 10000 12000 14000 16000 Side Ocean Bay-window 12/1/18 12/2/18 12/3/18 12/4/18 12/5/18 12/6/18 12/7/18 12/8/18 12/9/18 12/10/18 12/11/18 12/12/18 12/13/18 12/14/18 12/15/18 112/16/18 12/17/18 12/18/18 12/19/18 12/20/18 12/21/18 12/22/18 12/23/18 12/24/18 M23 X v fx H ID 1 2 3 4 Ocean 5 6 7 8 9 10 11 $750.00 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Guest First Name 27 Valerie 15 Darlene 2 Michael 3 Kim 6 Paul 26 Trista 23 Nigel 14 Arthur 24 Peter 1 Barry 11 Buster 22 Louis 25 Ronald 10 David 17 Albert 9 Homer 5 Thomas 8 Calvin 19 Everett 29 Xavier 28 Walter 16 Carlyle 12 Julia 20 Gerald 13 Samuel 21 Jamal 18 Charlene 30 Zachary 4 Edward 7 Randall D E Guest Last Name Room Room Type Snell Adams Bay-window Shore Arthur Ocean Lunsford Cleveland Kyuong Coolidge Bay-window Bodkin Coolidge Bay-window Leven Eisenhower Ocean Stratford Eisenhower Ocean Gottfried Garfield Side Willington Grant Ocean Lloyd Hayes Bay-window Whisler Jackson Ocean Paris Jackson Ocean Cartier Jefferson Bay-window Sanchez Jefferson Bay-window Goldstone Johnson Ocean Gonzalez Lincoln Ocean Collins Lincoln Ocean Nowotney Lincoln Ocean Chad Madison Ocean Trezza Trera McKinley Side Acton Polk Side Charleston Quincy Adam: Bay-window Martines Reagan Bay-window Pittsfield Roosevelt Ocean Kim Truman Side Smith Tyler Bay-window Tilson Van Buren Bay-window Miller Washington Ocean Holt Washington Ocean Battenburg Washington Ocean Arrival Date Departure Date Departure Date No of Guests Daily Rate Length of stay Base Revenue 12/3/18 12/6/18 1 150 3 $450.00 12/24/18 12/31/18 5 198 7 $1,386.00 12/1/18 12/9/18 3 112.5 8 $900.00 12/4/18 12/7/18 1 150 3 $450.00 12/1/18 12/3/18 2 150 2 $300.00 12/17/18 12/20/18 1 200 3 3 $600.00 12/14/18 12/16/18 2 200 2 2 $400.00 12/13/18 12/15/18 2 125 2 $250.00 12/19/18 12/21/18 1 200 2 $400.00 12/1/18 12/4/18 2 150 3 $450.00 12/5/18 12/8/18 2 250 3 . 12/10/18 12/14/18 1 250 4 $1,000.00 co 12/24/18 12/28/18 4 175 4 no $700.00 Showice 12/5/18 12/7/18 2 175 2 $350.00 12/5/18 12/7/18 3 250 2 $500.00 12/5/18 12/7/18 5 320 2 $640.00 12/9/18 12/13/18 2 2 300 4 300 $1,200.00 12/2/18 12/4/18 1 300 2 $600.00 12/10/18 12/14/18 2 275 4 4 $1,100.00 12/14/18 12/17/18 2 125 2 3 6 148.5 7 7 $1,039.50 12/3/18 12/6/18 2 150 3 $450.00 12/10/18 12/15/18 1 150 5 $750.00 12/5/18 12/7/18 2 275 2 $550.00 12/20/18 12/30/18 3 112.5 $1,125.00 12/20/18 12/23/18 2 150 3 $450.00 12/5/18 12/7/18 1 150 2 $300.00 12/13/18 12/16/18 2 325 3 $975.00 12/1/18 12/3/18 4 325 2 $650.00 12/4/18 12/12/18 2 292.5 8 $2,340.00 $375.00 12/24/18 12/31/18 26 10 27 28 29 30 31 32 Room Type Bay-window Ocean Side Average length of stay 3 3.625 5.5 Average number of visitors 1.8 2.375 3.25 Average base revenue 465 874.44 697.38 Total number of visitors 18 38 13 Total Revenue 4650 13991 2789.5 Average and Total Summary No of Guests / day 7 Total Revenue 6 5 Total number of visitors 4 Average base revenue 3 2 Average number of visitors 1 Average length of stay 0 0 2000 4000 6000 8000 10000 12000 14000 16000 Side Ocean Bay-window 12/1/18 12/2/18 12/3/18 12/4/18 12/5/18 12/6/18 12/7/18 12/8/18 12/9/18 12/10/18 12/11/18 12/12/18 12/13/18 12/14/18 12/15/18 112/16/18 12/17/18 12/18/18 12/19/18 12/20/18 12/21/18 12/22/18 12/23/18 12/24/18