Statistics: Decision Making, Hotel Modeling, Regression Analysis

This assignment presents a detailed analysis of statistical concepts through various problem-solving scenarios. It begins with an exploration of decision-making processes, including the use of conditional profit matrices, optimistic and pessimistic strategies, Laplace criterion, and EMV (Expected Monetary Value) analysis to determine optimal choices. The assignment then delves into cost-effective modeling for a hotel, simulating room bookings, analyzing overbooking scenarios, and providing suggestions to the hotel manager. Furthermore, it investigates regression models to assess the relationship between car prices and factors like mileage and age, including the evaluation of correlation between variables. Finally, the assignment concludes with model setup and solver solutions using MS Excel to determine optimal profit levels for different products.

Student Name: Student ID: 1
Problem Analysis in Statistics
Student Name: Student ID:
Unit Name: Unit ID:
Date Due: Professor Name:

Student Name: Student ID: 2
Acknowledgement

Student Name: Student ID: 3
Table of Contents
Acknowledgement...................................................................................................................................2
Answer 1..................................................................................................................................................5
Answer 2..................................................................................................................................................9
Answer 3................................................................................................................................................11
Base model setup..................................................................................................................................11
B............................................................................................................................................................12
Base model solution with excel formula sheet with cancelletion numbers...........................................12
C............................................................................................................................................................14
Hotel model solution for zero overbooked rooms.................................................................................14
Hotel model solution for one overbooked room...................................................................................16
Hotel model solution for two overbooked rooms................................................................................17
Hotel model solution for four overbooked rooms.................................................................................18
Hotel model solution for five overbooked rooms..................................................................................19
D. Suggestions to the Hotel manager of hotel Heart Break...............................................................20
Answer 4................................................................................................................................................21
A. Regression model of Price on mileage travelled............................................................................21
Regression model of Price on Age of cars..............................................................................................22
Regression model of Price on Mileaage and Age of cars......................................................................23
Correlation between independent variables, Mileage covered and Age of the cars............................24
Answer 5................................................................................................................................................25
Model setup and solver solution...........................................................................................................25
References.................................................................................................................................................29

Student Name: Student ID: 4

Student Name: Student ID: 5
Answer 1
A. Decision making is the course of action chosen by analysing the gathered information
about any project. The alternative solutions are also cross checked and decision tree helps
in building the entire model. There are some steps involved in decision making. Initially
the need of the decision and aim of the project is studied. Then necessary information are
collected and alternative ways are identified. The data are cross checked and choice of
final alternative is completed. Company implements the idea and the after effects are
recorded for future analysis.
B. Alternate strategy is the next best policy obtained in decision modeling and is known as
alternative. Prospect theory explains the alternative strategy, for example fear of future
losses irks people more than the pleasure of future gains. So, minimization of future loss
is an alternative to maximize the future gains.

Student Name: Student ID: 6
C. 1. Conditional profit matrix for 5 alternate strategy is given in figure 1. The calculations
have been done in MS Excel.
Figure 1: Conditional Profit Matrix of Wgga Wagga sale
2. Vendor will buy 25 kg fish if he follows optimistic policy.
Figure 2: Optimistic strategy pay-off matrix
3. Vendor will buy 15 kg fish if he follows pessimistic policy.
Figure 3: Pessimistic Strategy pay-off matrix
4. Vendor will buy 25 kg fish if he follows laplace criterion.

Student Name: Student ID: 7
Figure 4: Laplace criterion pay off matrix
5. Vendor will buy 25 kg fish if he follows criterion of regret.
Figure 5: Regret table for base model pay off matrix
6. Vendor will buy 25 kg fish if his decision is based on EMV.
Figure 6: EMV matrix for base model

Student Name: Student ID: 8
7. Vendor will buy 25 kg fish if he follows normal distribution with mean 20 kg and s.d of 5kg to
maximise his profit.
Figure 7: Normally Distributed pay off for base model
Answer 2
A. The EMV for each action course is shown in figure 8. The EMV value was negative for
the priori probabilities.
Figure 8: EMV of the base model
If no other information is available, then due to expected negative monetary value the
product should not be launched in the market.
B. If perfect information for success is available then expected value will be $ 3,00,000 and
for failure the expected value will be ($ -4,20,000).
C. The priori probabilities will be modified as in figure 9 for success and failure.

Student Name: Student ID: 9
Figure 9: Conditional Probabilities for the decision model
D. Posterior probabilities are calculated for favorable survey results. The marginal
probabilities are evaluated by multiplying the conditional probabilities with priori
probabilities. The values are in tabular form of figure 10 (Gelman et al., 2014).
Figure 10: Marginal Probabilities for the decision model
Using the marginal probabilities, the posterior probabilities have been calculated by means of
dividing joint probabilities by marginal probabilities. The values are in tabular form in figure 11.
Figure 11: Posterior Probabilities for the decision model
E. The total expected profit for the survey was evaluated as $ 81,600 by adding up the net
expected values of favorable and unfavorable conditions. The priori probabilities yield a
zero profit for the model. Hence maximum survey cost payable by the company was $
81,600 (Santen, Danhof & Pasqua, 2015).

Student Name: Student ID: 10
Figure 12: Expected value of Profit
Answer 3
A. Cost effective modeling for hotel Heart break
Base model setup
Figure 13: Heartbreak Hotel room booking average daily cost model set up

Student Name: Student ID: 11
B.
Base model solution with excel formula sheet with cancelletion numbers

Student Name: Student ID: 12
Figure 14: Heartbreak Hotel monthly simulated sheet for 3 overbooked rooms
The excel sheet containing the formula of calculations of the base model has been provided in
figure 3.

Student Name: Student ID: 13
Figure 15: Heartbreak Hotel monthly simulated excel work sheet containing formulae

Student Name: Student ID: 14
C.
Hotel model solution for zero overbooked rooms
Figure 16: Heartbreak Hotel room booking model for zero overbooked rooms

Student Name: Student ID: 15
Figure 17: Heartbreak Hotel monthly simulated excel sheet for zero overbooked rooms

Student Name: Student ID: 16
Hotel model solution for one overbooked room
Figure 18: Figure 3: Heartbreak Hotel room booking model for one overbooked room
Figure 19: Heartbreak Hotel monthly simulated excel sheet for one overbooked room

Student Name: Student ID: 17
Hotel model solution for two overbooked rooms
Figure 20: Heartbreak Hotel room booking model for 2 overbooked rooms
Figure 21: Heartbreak Hotel monthly simulated excel sheet for 2 overbooked rooms

Student Name: Student ID: 18
Hotel model solution for four overbooked rooms
Figure 22: Heartbreak Hotel room booking model for 4 overbooked rooms
Figure 23: Heartbreak Hotel monthly simulated excel sheet for 4 overbooked rooms

Student Name: Student ID: 19
Hotel model solution for five overbooked rooms
Figure 24: Heartbreak Hotel room booking model for 5 overbooked rooms
Figure 25: Heartbreak Hotel monthly simulated excel sheet for 5 overbooked rooms

Student Name: Student ID: 20
D. Suggestions to the Hotel manager of hotel Heart Break
To 8th May 2018
The manager
Hotel Heart Break
Synopsis on Average daily cost and Overbooking of rooms
The simulated average daily cost of the Heart Break hotel was $ 203.33. Number of
overbooked rooms was initially considered to be three. Average no shows by customers
was between zero and five, per day. The monthly data for daily cost were calculated by
choosing the no shows of the customers in a random pattern from the list of no shows.
The change in average daily cost for the customers was noted. An excel spreadsheet was
created for the purpose of analysing the outcome of no shows on the daily average cost.
The average daily cost of the hotel was re-calculated and noted by changing the number
of overbooked rooms in the hotel. The average daily cost was calculated for five different
values of no shows of customers. It was revealed that the daily average cost reduced
when the number of overbooked rooms in the hotel was reduced (Zakhary et al., 2011).
The minimum simulated average daily cost was found to be $ 69.17 for one overbooked
room. Hence lessening number of overbooked rooms was profitable for the hotel (Fouad
et al., 2014).
Sincerely Yours
__________________

Student Name: Student ID: 21
Answer 4
A. Regression model of Price on mileage travelled
Figure 26: Regression model for Price on Mileage
Equation of line of best fit was Y = −95. 53 X +17227 .29 where Y was the price of car and X was
the mileage covered by old cars. The negative coefficient of Mileage covered reflected that price
of the old cars was negatively related to the mileage covered by that car. The p-value for the
mileage in the regression model was less than 0.05. Therefore, the regression model could
explain the price of the old cars significantly.

Student Name: Student ID: 22
Regression model of Price on Age of cars
Figure 27: Regression model for Price on age
Regression equation for the model was Y = −839 . 66 X +16226 . 39 where Y was the price of car
and X was age of those old cars. Negative coefficient of age of the cars indicated that price of
old cars was negatively related to age of cars. The p-value for age of cars in the regression model
was less than 0.05. Therefore, the regression model was able to describe the price of the old cars
significantly (Sen & Srivastava, 2012).

Student Name: Student ID: 23
Regression model of Price on Mileaage and Age of cars
Figure 28: Regression model for Price on age and mileage
The linear regression equation was Y = −39 . 30 X− 507. 30 Z +16699 .52 where Y indicated the
price of car, X denoted mileage covered and Z represented the age of the second hand cars. The
regression coefficients of age and mileage were negative, which reflected that increased
independent variables had decrement effect on price of cars. The p-values of the regression
model for total age and total mileage covered were larger than 0.05. For this reason the
regression model was not significant enough to give details about the variance of price of the old
cars based. Regression models of figure 14 and 15 explicated the variance of price of the old cars
than the collective model with both the independent variables (Montgomery, Peck & Vining,
2012).

Student Name: Student ID: 24
b. Individual models performed better than the collective regression model. The adjusted R-
square value of mileage-price model was 73.11% which explained the variance of price in the
second model. The significance values of intercept and mileage for the car price and total
mileage model were also less than 0.05. Regression coefficients of car age and total mileage
covered indicated that price of the old cars was negatively related to the explaining factors. The
correlation nature between price of old cars and total mileage covered, age of old car was
apparent due to obvious facts.
c.
Correlation between independent variables, Mileage covered and Age of the
cars
Figure 29: Correlation matrix for the two independent variables

Student Name: Student ID: 25
Total mileage travelled and age of a old car were positively correlated. The correlation value was
significantly high in nature. The model with the two independent variables was valid in nature,
evident from the correlation score. Total mileage covered by the old car was the better
independent factor, which was clear from the regression model. Both the variables were equally
important in deciding price of cars.
Answer 5
Model setup and solver solution
The first model was setup with product A where units sold was 200. The entire scenario has been
provided in the left most table of figure18. The MS Excel solver solution was 300 units for break
even profit. This scenario also has been provided in the right most table of figure 18.

Student Name: Student ID: 26
1
Figure 30: Initial and solver setup for profit

Student Name: Student ID: 27
B. The base model was solved by using MS Excel solver where the profit was set to $ 1600.
The solver obtained number of units sold as 500 units. Figure 19 explains the profit
model (Mauri, 2016).
Figure 31: Excel solver solution for profit of $ 1600

Student Name: Student ID: 28
C. Product B was introduced in the model set up with the product A in the initial CVP
model with number of units as 200 (initial value of base model) and 100units where the
two values were in 2:1 ratio. Total profit was calculated by adding the profits for both the
products A and B. Microsoft excel solver was used and maximum profit was set to
$ 20,000. The solver then solved the model by changing the number of units for product
A. The solver solution has been provided in right hand table of figure 20 (Maier, 2016).
Figure 32: Excel solver solution setup for profit involving products A & B

Student Name: Student ID: 29
Bibliography
Fouad, A. M., Atiya, A. F., Saleh, M., & Bayoumi, A. E. M. M. (2014, December). A
simulation-based overbooking approach for hotel revenue management. In Computer
Engineering Conference (ICENCO), 2014 10th International (pp. 61-69). IEEE.
Zakhary, A., Atiya, A. F., El-Shishiny, H., & Gayar, N. E. (2011). Forecasting hotel arrivals and
occupancy using Monte Carlo simulation. Journal of Revenue and Pricing
Management, 10(4), 344-366.
Montgomery, D. C., Peck, E. A., & Vining, G. G. (2012). Introduction to linear regression
analysis (Vol. 821). John Wiley & Sons.
Sen, A., & Srivastava, M. (2012). Regression analysis: theory, methods, and applications.
Springer Science & Business Media.
Maier, T. A. (2016). Convention hotel food and beverage operating efficiency profile. Journal of
foodservice business research, 19(5), 514-524.
Mauri, A. G. (2016). Pricing and revenue management in hotel chains. The Routledge Handbook
of Hotel Chain Management, 262-273.
Gelman, A., Carlin, J. B., Stern, H. S., Dunson, D. B., Vehtari, A., & Rubin, D. B.
(2014). Bayesian data analysis (Vol. 2). Boca Raton, FL: CRC press.
Santen, G., Danhof, M., & Pasqua, O. D. (2015). Uncertainty and decision making in clinical
development: the impact of an interim analysis based on the posterior predictive power
on depression trials.