Hi i need to complete this excel file with the following instructions:

As a sales manager of the SHM Hotel, want to know the number of customers or the total room sales that meet specific conditions. When using the functions to answer the following questions, do not hard code. Instead, use the given cells that contain each condition.

Formatting :

1. Format the tables (title, numbers, borders, cell styles etc.)
2. Freeze panes so that the title row (row 3) is always visible.

Conditional functions (60 points)

1. Compute the number of guests staying at the hotel for 3 nights or more.
2. Compute the total amount of outstanding balance by guests residing in king bed rooms.
3. Compute the total amount of outstanding balance by guests paying with points.
4. Compute the number of guests staying in the hotel for more than 3 nights and paying with credit cards.
5. Compute the total amount of outstanding balance by guests residing in double bed rooms and staying for more than 2 nights.
6. You want to provide 10% discounts to customers’ outstanding balances if they stayed for more than 4 nights. Otherwise, they get a 0% discount. Based on this condition, complete the “discount amount” column. Make sure you use the condition cells that is already given in the L12, M12, and N12.

Using range names in the functions :

Name the ranges: “nights_present”, “outstanding_balance”, “discount_amount"

1. Compute total amount due column (=outstanding balance – discount amount).
2. Find the highest outstanding balance.
3. Find the highest number of nights a guest has stayed at the hotel.

Using conditional formatting :

12. Highlight the rows where the “total amount due” is greater or equal to $1,000.

Bonus question  – Customize number format:

13. Customize the number formats in “Total Due” column as follow:

Color is blue. It has two decimals and has a text “Total” next to the number. So for example, the results should something like $1234.05 Total.

SHM Hotel
Last Name First Name Tom
Aaron
Jacob
Thomas
Joseph
Nancy
Stephanie
Deborah
Alvin
Ava
Bernard
Rachel
Natasha
Chelsea
Bonita
Cheryl
Tom
Kristin
Kristen
Beverly
Tom
Benjamin
Thomas
Holden
Patricia
Diane
Brynn
Kerrie
Daniel
Susan
Suzanne
Christopher
Randall
Connie
Dennis
Eric
Rita
Troy
Craig
Gregory
Michael
Phillip
Kimberly Brown
Smith
Jones
Miller
Chang
Wang
Williams
Miller
Jones
Smith
Thomas
White
Harris
Clark
Lewis
Lee
Rodriguez
Walker
Walker
Lee
Smith
Jones
Wilson
Baker
Baker
Campbell
Campbell
Jones
Miller
Johnson
Moore
Moore
Thomas
Murphy
Ward
Watson
Ward
Murphy
Ford
Owens
Jordan
Ford
Myers Nights Outstanding
Present Balance ($)
5
3
2
6
1
1
1
2
3
5
4
7
1
5
6
1
1
2
2
3
4
6
4
1
2
3
5
10
2
3
6
1
1
1
1
7
8
1
4
2
3
6
5 625
375
280
840
140
140
140
280
420
700
560
875
320
625
750
125
140
280
280
960
1280
1920
560
140
250
375
625
1250
250
375
750
125
125
125
140
980
1120
140
500
250
375
750
625 Question 8
Discount
Amount ($) Question 9
Total
Due ($) RoomType
DOUBLE
DOUBLE
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
DOUBLE
SUITE
DOUBLE
DOUBLE
DOUBLE
QUEEN
QUEEN
QUEEN
SUITE
SUITE
SUITE
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
KING
QUEEN
QUEEN
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE Timothy
Ellen
Elizabeth
Jennifer
Carolyn
David
Glenna
Glenn
Barry
Barbara
Erin
Aaron
Nicole
Candice
Corinne
June
Thomas
Russell
Barbara
Kaitlyn
Robert
Bobby
Richard
Jamie
Liberty
Donna
Sherri
Cheryl
Bradley
Buddy
Burt
Calvin
Cameron
Anthony
Santino
Carey
Diane
William
Lee
Brice
Brock
Brody
Braden
Betty
Elizabeth
Liza
Sally
Patricia Woods
Woods
Cole
Lucas
Hicks
Emel
Smithson
Johnson
Mills
Grant
Knight
Jackman
Cruise
Owens
Ferguson
Willits
Fields
Fields
Ryan
Ryan
Banks
Howell
Dean
Lynch
James
Holland
Douglas
Bush
Parks
Dawson
Tate
Garner
Blake
Maxwell
Paul
Zimmerman
French
Ramirez
Wong
Moran
Cain
O'Brien
O'Donnell
Skinner
Kirby
Bond
Bruce
Lang 9
1
11
12
3
3
2
2
3
4
4
1
1
1
1
1
2
2
3
4
1
11
6
5
5
4
3
2
9
1
2
3
3
3
4
5
5
5
5
4
1
3
2
2
2
2
3
3 1125
140
1540
1680
375
375
250
250
375
500
500
125
125
125
125
125
250
250
420
500
125
1375
750
625
625
560
375
280
1125
125
250
375
375
375
500
625
625
625
625
500
140
420
280
280
280
250
375
375 DOUBLE
QUEEN
QUEEN
QUEEN
KING
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
KING
KING
KING
DOUBLE
DOUBLE
DOUBLE
DOUBLE
QUEEN
KING
KING
KING
KING
KING
KING
QUEEN
DOUBLE
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE Allan
Kenneth
Heidi
Loraine
Lucy
Crystal
Alesha
Karen
Nikki
Tina
Christina
Marie
John
Jonathan
Su
Perry
Angela
Sara
Tracy
Stacy
Blakely
Jillian
Patrick
Laura
Sang
Daisy
Cynthia
Brynn
Kerrie
Daniel
Susan
Suzanne
Christopher
Randall
Connie
Dennis
Eric
Rita
Troy
Craig
Gregory
Michael
Phillip
Kimberly
Timothy
Ellen
Elizabeth
Jennifer Baxter
Snow
Hartman
Woodard
Gavlock
Humphries
Snow
Moore
Jones
Brown
Smith
Jones
Miller
Chang
Wang
Williams
Miller
Jones
Smith
Thomas
White
Harris
Clark
Lewis
Lee
Rodriguez
Walker
Walker
Lee
Smith
Jones
Wilson
Baker
Baker
Campbell
Campbell
Jones
Miller
Johnson
Moore
Moore
Thomas
Murphy
Ward
Watson
Ward
Murphy
Ford 1
1
4
5
7
7
7
1
6
3
2
5
1
1
1
1
1
1
3
3
3
3
3
3
2
2
2
2
2
2
1
1
6
6
6
5
5
5
5
5
1
1
1
1
1
1
3
3 125
125
500
625
875
875
980
140
840
375
250
625
125
125
125
125
140
140
420
375
420
420
420
960
640
250
250
250
250
250
125
125
750
750
750
625
625
625
625
625
125
125
125
125
125
125
375
375 DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
QUEEN
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
QUEEN
QUEEN
QUEEN
DOUBLE
QUEEN
QUEEN
QUEEN
SUITE
SUITE
KING
KING
KING
DOUBLE
DOUBLE
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
DOUBLE
DOUBLE
KING
KING
KING
KING
KING Carolyn
David
Glenna
Glenn
Barry
Barbara
Victoria
Daniel
Amanda
Sandra
Pandora
Melissa
Lisa
Kenneth
Allan
James
Timothy
Carey
Alex
Alec
Barry
Sara
Joseph
Arnold
Franklin
John
Dylan
Lawrence
Nancy
Cheryl
Jarrod
Archie
Heather
Russell
Donald
Howard
Robin
Richard
John
Gary
Elisa
Aaron
B.D.
Nikki
Tina
Christina
Aaron
Joseph Owens
Jordan
Ford
Myers
Woods
Woods
Cole
Lucas
Hicks
Emel
Smithson
Johnson
Mills
Grant
Knight
Jackman
Cruise
Owens
Ferguson
Willits
Fields
Fields
Ryan
Ryan
Banks
Howell
Dean
Lynch
James
Holland
Douglas
Bush
Parks
Dawson
Tate
Garner
Blake
Maxwell
Paul
Zimmerman
French
Ramirez
Wong
Jones
Brown
Smith
Smith
Chang 3
3
3
2
2
2
2
2
2
2
10
4
4
4
4
7
7
7
7
5
5
5
5
5
4
4
3
3
3
3
1
1
1
2
2
2
2
1
1
1
1
3
3
3
5
5
5
5 375
375
420
280
280
280
250
250
640
640
3200
1280
500
500
500
875
875
875
875
625
625
625
625
625
500
1280
375
375
375
375
125
125
125
250
250
250
250
125
125
125
125
375
375
375
625
625
625
625 QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
QUEEN
DOUBLE
DOUBLE
SUITE
SUITE
SUITE
SUITE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
KING
KING
KING
DOUBLE
DOUBLE
KING
KING
SUITE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
DOUBLE
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING
KING Nancy
Stephanie
Deborah
Alvin
Ava
Bernard
Rachel
Kristin
Kristen
Dennis
Eric
Thomas
Russell Wang
Williams
Miller
Jones
Smith
Thomas
White
Walker
Walker
Ward
Watson
Fields
Fields 5
9
1
1
1
2
2
2
3
3
3
4
6 625
1260
140
125
125
250
250
640
960
960
375
500
750 KING
QUEEN
QUEEN
DOUBLE
DOUBLE
DOUBLE
DOUBLE
SUITE
SUITE
SUITE
DOUBLE
DOUBLE
DOUBLE Payment
Method
CASH
CREDIT
DEBIT
POINTS
DEBIT
POINTS
DEBIT
POINTS
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CASH
CASH
CASH
DEBIT
CREDIT
CREDIT
CASH
CASH
CASH
CASH
CASH
CASH
CASH
DEBIT
DEBIT
COMP
COMP
POINTS
CREDIT
DEBIT
CREDIT
CREDIT
POINTS
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT Question 3
Question 4
Question 5 Question 6
Question 7
Question 8 Question 10
Question 11 Question 12 CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
DEBIT
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
COMP
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
DEBIT
DEBIT
DEBIT DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
CASH
CASH
CASH
CASH
CASH
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
CASH
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
POINTS
COMP
COMP
COMP
COMP
COMP
COMP
COMP
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT
DEBIT DEBIT
DEBIT
DEBIT
CASH
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT
CREDIT Number of guests that have been in the hotel for 3 nights or more
Total outstanding balance by guests residing in king bed rooms
Total outstanding balance by guests paying with points Number of guests that have been in the hotel for 3 nights or more and pay with credit cards
Total outstanding balance by guests residing in double bed rooms and staying for more than 2 nights.
Provide 10% discount on the outstanding balance, if customers stayed more than 4 nights. Otherwise 0%. Highest outstanding balance
Highest number of nights a guest has stayed at the hotel Highlight the rows with a total due greater or equal to $1,000 Condition 1
>=3
KING
POINTS Anwers Condition 1
>3
DOUBLE Condition 2
CREDIT
>2 Anwers 4 10% 0% Anwers Condition
$
1,000.00

• 1493263185-Lecture 7 - Conditional functions Homework.xlsx

ComputerScienceExpert

(11)

Status NEW Posted 27 Apr 2017 03:04 AM My Price 10.00

-----------

Attachments

file 1493263188-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer