The Excel file Graduates' Inn Guest provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month. Room rates are the same for one or two guests; however, additional guests must pay an additional amount (based on the room type as shown in the table below) per person per day for meals and other miscellaneous expenses. Guests staying for seven days or more receive a 10% discount (applied to revenue per party transaction).

Room Type Additional cost/person/day

Ocean $35

Bay-Window $30

Side $20

Add one additional sheet to the spreadsheet to answer the following questions.

(1)Use the SUMIF() function to calculate the revenue collected from each room type for the whole month.

(2)Show a pie chart to display the revenue breakdown from the last question. Which room type generated highest revenue and how much revenue?

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