View the step-by-step solution to:

Question

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 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?


SharedScreenshot.jpg

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

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Let our 24/7 Statistics and Probability tutors help you get unstuck! Ask your first question.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes
A+ icon
Ask Expert Tutors