Marriott Inn Business Analysis Report

This report uses business data analysis and simulation techniques to address two key issues at the Marriott Inn. The first part analyzes the cost implications of overbooking hotel rooms, considering the probabilities of no-shows and the costs associated with both overbooking and underbooking. A simulation model is developed in Excel to estimate the average monthly cost due to overbooking and to determine the optimal number of rooms to overbook. The second part focuses on the ATM queuing system in the hotel lobby. Using queuing theory and simulation, the report analyzes the average waiting time for customers using the ATM and the probability of waiting longer than one minute, as per the hotel's service standard. The simulation model helps determine if one ATM is sufficient or if additional ATMs are needed to meet the service standard. The report concludes with recommendations for both issues, suggesting an optimal overbooking strategy and potential solutions for improving the ATM queuing system.

Executive Summary
Objective:
The Marriott inn has awesome notoriety and superb qualities, yet there are a few
issues. For instance, they are confronting no-shows clients who reserve a spot and
afterward never appear. In this report, the proficient way with regards to this matter
will be proposed taking into account business insights. Additionally, the inn's VP has
a worry that amid occupied periods numerous clients may need to hold up to utilize
the ATM in the lodging. As indicated by the inn's administration rule, the normal
client holding up time ought to be one moment or less for an ATM and this issue will
be dealt with. With regards to undertaking 1, it appears that keeping its two rooms
overbooked is prudent. In assignment 2, the likelihood that a client will need to sit
tight for an administration is 0.5. Likewise, the likelihood that client need to sit tight
for over one moment for taking administration of ATM is 0.98. In this circumstance,
the lodging's VP's rules can't be met. Accordingly, Managers can settle on choices
effectively by utilizing this valuable device like recreation and it ought to be known
broadly.
Table of Content
1 | P a g e

1
Introduction 4
2 Methodology.....................................................................................................................5
2.1 Task 1.........................................................................................................................5
a..................................................................................................................................6
b.................................................................................................................................6
2.1.1 Recommendation ............................................................................................7
2.2 Task 2.......................................................................................................................7
a................................................................................................................................7
b .............................................................................................................................8
c...............................................................................................................................8
2.2.1 Recommendation............................................................................................8
3 Key findings .....................................................................................................................8
4 Appendix...........................................................................................................................9
BUISNESS DATA ANALYSIS
2 | P a g e

1.Introduction
J. Willard Marriot opened the nine stool beer stand evolved about into hot shopper’s
restaurant chain. For the next couple of decades he expanded his firms into
emerging restaurant chain in the world having numerous no of properties all over the
world. He was also keen serving the customer first and pursuing excellence and
embracing the change, acting with integrity and serving the world and be a part of
the Marriott history and culture. Despite all this reputation there where some
problems in the finance department in a scenario where there is no shows when
there is costumer is not showing up, so this was issue was attracted by their
management. So they asked the finance department look into this problem. The
finance department started collect data and records of customer reservations and
came up with a no of shows destitution table. To reduce the no of vacant room they
decide to add three rooms for the reservation but then they were cases in which
some costumer couldn’t get the room because of the full reservations and
sometimes they were less reservations. In this case the hotel have to send the
costumer to nearby competitive hotel next to the street which they need to spend
$125. If the case is opposite and they have vacant rooms in the hotel then they going
to lose $50 per room. For the above situation we need to simulate the average
monthly loss due to the over booking and need to replicate the average monthly cost
200 times.
In the lobby of the Marriott hotel there is an automated teller machine (ATM) in the
busy times the costumer have to wait for using the machine in which it tend to be
queuing. This led the hotel management to study about the ATM queuing system.
The hotel vice president wants determine whether one ATM will be sufficient for the
all customers. The hotel established the serving time for the customer should be less
than one minute or less. One probabilistic input in the ATM simulation find the arrival
times of the customer who using the ATM. In queuing simulation is determined by
the randomly generating the time between two successive arrivals, referred to as the
interval time. The customer inter arrival times is be uniformly distributed between 0
and 5 minutes. Another probabilistic input is the ATM simulation is the service time,
which is the time the customer using the ATM machine. From the past data from
ATM indicates the mean of 2 minutes and standard deviation of 0.5. For the above
situation we need to design the excel sheet to operate the simulation of the ATM
system for 1000 customers. What is the waiting time of the customer for the service?
Critically analyse the average waiting time of the customer using the ATM and the
probability that he have to wait more than a minute.
2Methodology
3 | P a g e

2.1 Task 1: In the report we have given the probability distribution table for the no
shows from that we need to find the total coast and average monthly costs that hotel
have when no shows are there
NO SHOWS PROBABILITY
0 0.1
1 0.13
2 0.31
3 0.16
4 0.21
5 0.09
Table 1.0 probability distribution of no shows
Above is the distribution table of giving data about the no of shows and in the report
it is mentioned about the total no of rooms which is 10 and the rooms overbooked
are 3, the no of allowable reservation is 13. If the hotel have short room they will loss
an amount off $125 and when they have a vacant room they will lose $50 per room.
Which is mentioned in (table1.1)
Cost per short room $125.00
Cost per vacant room $50.00
No of rooms 10
Rooms overbooked 3
Allowable reservations 13
Table 1.1 Cost of short room & vacant room, no of rooms,
Room’s overbooked, allowable reference.
With all this data in hand no we need to calculate no of simulated shows that occur
and no of vacant room, short room, total costs, guests find room. For that first we
need to find the no of simulated no shows for which we need to calculate probability
distribution table and find the random upper limit and lower limit. To find this what we
do is the min value is 0 and the upper limit value is adding the probability distribution
value into upper limit we can find the random upper limit. Doing this vice versa you
can find the limit for the whole upper limit and lower limit.
NO
SHOW
S
PROBABILITY
RANDOM
LOWER
LIMIT
RANDOM
UPPER
LIMIT
NO
SHOWS
0 0.1 0 0.09 0
1 0.13 0.1 0.22 1
2 0.31 0.23 0.53 2
3 0.16 0.54 0.69 3
4 0.21 0.7 0.9 4
5 0.09 0.91 0.99 5
Table 1.2 Probability of No shows
Using vlookup function we can find the no of simulated no shows using formula
=VLOOKUP(B8,$O$7:$Q$13,3), .In this way we can find the no of simulated no
4 | P a g e

shows. Allowable reservation is already mentioned in the table 1.1 which is 13. Next
we need to find the guest find room for that we need to use if statement in which we
need to do is allowable reservation minus simulated no shows minus total no of
rooms we will get the no of short rooms following is the formula. ie,IF(D3-C3-
F3>=0,D3-C3-F3,0).Short room is same as the guests find room. To find the vacant
room we need use the if statement what is the change instead of using greater than
we will use less than value so that will get the vacant no of rooms ie,IF(D3-C3-
F3<=0,(D3-C3-F3)*(-1),0). We need to multiply by -1 is because to avoid having
negative values in the excel. To find the cost of short room we just need to multiply
no of short room in to $125 which is mentioned in(table1.1). For vacant room also we
do the same that is no of vacant room multiply by $50(table1.1) for total costs want
we do is just adding the short room plus vacant room.
a) To find the monthly costs due to monthly cost due to opportunity loss what we
need to do is find the average of the total costs of 30 simulation.
AVG Cost of 1 Month 122.5
b) To find the replication cost of 200 average monthly cost is what we do is first
from the average we find out we need to replication we use What if analysis in
excel in which we are it does the replication from that click the data table
before that click the average coast and select no of cells which is equal to the
no of times we want the replication. By this way we can find the replication.
Below is the replication table of scenario.
NO OF
REPLICATIO
N
TOTAL
COSTS
1 122.5
2 151.6667
3 153.3333
4 136.6667
5 129.1667
6 152.5
7 154.1667
8 160.8333
9 123.3333
10 124.1667
11 165
12 127.5
13 116.6667
Table 1.3 200 replication table
for finding the average of the monthly costs want we need to do is to select all
total costs from the 200 replication and put average formula.
Rooms Overbooked 0 1 2 3 4 5
AVG Costs 130 95.83 67.5 91 224.17 275
5 | P a g e
AVG of 200 replication 126.58

1.4 overbooked table
The above table is the for the no of rooms to be overbooked.
2.1.1 Recommendation
In the above situation we can find that the if the rooms are overbooked either by one
or two it gives the less average costs so on my recommendation its better to keep
either one or two rooms for reservation rather putting three so that they can reduces
there loss.
2.2 Task 2: In the second task we need to find the service time which what is the
amount of time customer spend in the ATM machine. For this we have given some
data ie
2.0 inter arrival time and service time
We are given with inter arrival time of a customer coming and going and the largest
value and smallest value of it and there is the mean and standard deviation of the
service time that a costumer will use the ATM machine.
a) In the first task we need to make a worksheet to simulates the operation
of the ATM for 1000 customers
Custome
r
arrival
time
inter arrival
time
servic
e time
service start
time
waiting
time
time in
system
compl
time
1 3.5 3.5 1.5 3.5 0.0 1.5 5.0
2 6.3 2.8 2.9 6.3 -1.3 2.9 9.2
3 7.8 1.5 2.3 9.2 1.4 3.7 11.5
4 11.7 3.9 1.9 11.7 0.0 1.9 13.5
5 12.5 0.8 1.5 13.5 1.1 2.6 15.1
6 15.1 2.6 1.6 15.1 0.0 1.6 16.7
7 19.7 4.6 1.9 19.7 0.0 1.9 21.6
8 22.4 2.7 2.1 22.4 0.0 2.1 24.6
9 24.1 1.7 2.3 24.6 0.5 2.8 26.9
10 27.3 3.2 2.3 27.3 0.0 2.3 29.7
2.1 operation time first 10 customer out off 1000
Applying the queuing theory information In this spreadsheet we have
find out the arrival time, inter arrival time, service time, waiting time,
service start time, completion time and time in system. For finding the
inter arrival time with given data we apply the formula =0+RAND()*(5-0)
6 | P a g e
INTER ARRIVAL TIME
UNIFROM DIST
smallest value 0.0
largest value 5.0
SERVICE TIME (NORMAL
DIST)
mean 2.0
standard deviation 0.5

In which we can find the inter arrival time for the first customer the
arrival time and service start time will be the same be when it reaches
the second customer inter arrival is added to arrival time of the first
customer. For finding the service time we use the formula by generating
rand no and the mean and standard deviation
=NORM.INV(RAND(),2,0.5) for the service start time we do it by if
statement =IF(H3>B4,H3,B4), for the waiting time it is the completion
time minus the exceeding arrival time. Time in system is the completion
time minus arrival time. Completion time is the service start time adding
service time. This way we can simulates the operation of ATM of 1000
customers.
b) For the waiting time of the first 100 customers is 1.2 minutes and for
the 900 costumers they are in steady state period so for them there is
no waiting time rest of the customers and they all will be having the
same average waiting time as the first hundred customers
c) Critically analysing the average waiting time and its shows for the
customers to wait for the more than an minute. So as per the hotel
manager guideline it’s okay if the customer is waiting the for more than
minutes or so .
2.2.1 Recommendation
As for the ATM its better to keep one more ATM counter in the floor to avoid the
queuing situation in the hotel so that manager guideline can be satisfied.
3 Key findings
In this report we have find out the following things:
1) We have find the total monthly costs of the hotel due to the overbooking.
2) Finding the average monthly costs
3) Finding the other suitable numbers for overbook
4) Operations of the 1000 customers using the ATM
5) Finding the average waiting time
4 Appendix1
7 | P a g e

1 0.14 1 13 2 10 2 0 250 0 250
2 0.69 3 13 0 10 0 0 0 0 0
3 0.71 4 13 0 10 0 1 0 50 50
4 0.45 2 13 1 10 1 0 125 0 125
5 0.37 2 13 1 10 1 0 125 0 125
6 0.08 0 13 3 10 3 0 375 0 375
7 0.34 2 13 1 10 1 0 125 0 125
8 0.55 3 13 0 10 0 0 0 0 0
9 0.71 4 13 0 10 0 1 0 50 50
10 0.82 4 13 0 10 0 1 0 50 50
11 0.12 1 13 2 10 2 0 250 0 250
12 0.29 2 13 1 10 1 0 125 0 125
13 0.86 4 13 0 10 0 1 0 50 50
14 0.04 0 13 3 10 3 0 375 0 375
15 0.04 0 13 3 10 3 0 375 0 375
16 0.98 5 13 0 10 0 2 0 100 100
17 0.23 1 13 2 10 2 0 250 0 250
18 0.06 0 13 3 10 3 0 375 0 375
19 0.99 5 13 0 10 0 2 0 100 100
20 0.00 0 13 3 10 3 0 375 0 375
21 0.35 2 13 1 10 1 0 125 0 125
22 0.01 0 13 3 10 3 0 375 0 375
23 0.98 5 13 0 10 0 2 0 100 100
24 0.05 0 13 3 10 3 0 375 0 375
25 0.61 3 13 0 10 0 0 0 0 0
26 0.47 2 13 1 10 1 0 125 0 125
27 0.60 3 13 0 10 0 0 0 0 0
28 0.23 1 13 2 10 2 0 250 0 250
29 0.97 5 13 0 10 0 2 0 100 100
30 0.27 2 13 1 10 1 0 125 0 125
MARRIOT HOTEL
DAY RN SIMULATED
NO SHOWS
ALLOWABLE
RESERVATION
GUESTS FIND
ROOM TOTOAL NO OF ROOMS SHORT
ROOMS
VACANT
ROOM
COST OF
SHORT COST OF VACANT ROOM TOTAL COSTS
Table 4.1 30 days of Average cost
8 | P a g e