Simulation, Regression Analysis And CVP Analysis

Simulation at Heartbreak Hotel

A. Heart break Hotel

Base model setup

 

Figure 1: Heartbreak Hotel room booking set up

B.

Base model solution with formula sheet with cancelletion numbers (Chen, 2016).

Figure 2: Heartbreak Hotel monthly simulated sheet for overbooked rooms = 3

The formula sheet of the base model:

Figure 3: Heartbreak Hotel monthly simulated sheet for formula

Model solution for zero rooms overbooked

Figure 4: Heartbreak Hotel room booking set up for overbooked rooms = 0

 

Figure 5: Heartbreak Hotel monthly simulated sheet for overbooked rooms = 0

Model solution for one room overbooked

Figure 6: Figure 3: Heartbreak Hotel room booking set up for overbooked rooms = 1

Figure 7:  Heartbreak Hotel monthly simulated sheet for overbooked rooms = 1

Model solution for two rooms overbooked

Figure 8:  Heartbreak Hotel room booking set up for overbooked rooms = 2

 

Figure 9: Heartbreak Hotel monthly simulated sheet for overbooked rooms = 2

Model solution for four rooms overbooked

Figure 10:  Heartbreak Hotel room booking set up for overbooked rooms = 4

 

Figure 11: Heartbreak Hotel monthly simulated sheet for overbooked rooms = 4

Model solution for five rooms overbooked

Figure 12:  Heartbreak Hotel room booking set up for overbooked rooms = 5

Figure 13: Heartbreak Hotel monthly simulated sheet for overbooked rooms = 5

D. Recommendation to the Heart Break Hotel manager

To                                                             09/05/2018

The manager

Hotel Heart Break

Optimization of average daily cost and overbooked rooms

The average daily cost of the Heart Break hotel was found to be $ 190.83.  The number of overbooked rooms was kept at three. The average number of no shows per day for the hotel was between zero and five.  A random data pattern of no show for the customers was noticed. An excel sheet (figure 2) has been generated for the purpose of analysing the effect of no shows on the daily average cost of the hotel.  The cost model was re-evaluated by varying the number of overbooked rooms in the hotel. The average daily cost was noted for five different situations. It was discovered that the daily average cost reduced by dropping the number of overbooked rooms in the hotel. The minimum simulated average daily cost was found to be $ 58.33 for zero overbooked rooms. Hence reduction in the number of overbooked rooms was profitable for the hotel (Tse & Poon, 2017).

Regression Analysis for Volkswagen Jetta

Sincerely Yours

__________________

Answer 2

A. Regression model of Price on mileage

Figure 14: Regression model for Price on Mileage

The regression equation was  where Y was the Price of second hand car and X was the mileage covered of those old cars. The negative coefficient of Mileage covered, implied that price of the old cars was inversely related to the total mileage covered by that car. The p value for the coefficient of regression, for mileage was less than 0.05. Hence, the regression model significantly explained the price of the old cars.

Regression model of Price on Age (years)

Figure 15: Regression model for Price on age

The regression equation was  where Y was the Price of second hand car and X was the age of those old cars. The negative coefficient of age, implied that price of the old cars was inversely related to the total age of those cars. The p value for the coefficient of regression, for age was less than 0.05. Hence, the regression model significantly explained the price of the old cars.

Regression model of Price on Mileaage  and Age (years)

Figure 16: Regression model for Price on age and mileage

The regression equation was  where Y was the Price of second hand car, X was the total mileage covered and S was the age of the car. The negative coefficients of age and mileage implied that price of the old cars were inversely related to the independent variables. The p values for the coefficient of regression of age and mileage were greater than 0.05. Hence, the regression model was not able to significantly explain the price of the old cars based on age of the cars and total mileage travelled, when considered together. Individual regression models explained the variance of price of the cars, better than the combined model (Draper & Smith, 2014).

b. First two models were better than the combined regression model. The adjusted R square value was greatest in the second model. Thus age of the cars was able to expalin 69.75% variation of the price of old cars, and preference was reserved for the model. The p-values for the price-mileage model were also statistically significant and therefore that model was much more statistically significant than other models. Negative coefficients of age and mileage IN regression model implied that price of the old cars were inversely related to the independent variables. The relation between price and total mileage covered, age of the car was obvious in nature (Chatterjee & Hadi, 2015).

c.

Correlation between the independent variables, Mileage and  Age

Figure 17: Correlation matrix for the two independent variables

Mileage and age of the cars were highly (positively) correlated. Hence validity score was checked for the independent variables. Selection of both the variables as independent factors was advised.

Answer 3:

Model setup and solver solution

The initial model setup with 200 numbers of sold units has been provided in figure 18 (left table). The excel solver solution with break even zero profit also has been provided in figure 18 (right table) (Ku  & Nor, 2016)

Figure 18: Initial and solver setup for profit

B. The initial model was solved by excel solver for $ 1600 profit, to obtain number of units sold as 500  units. Figure 19 describes the model.

 

Figure 19: solver setup for profit of $ 1600

C. Two products namely A and B were set up in the initial model with number  of units as 200 and 100  (2:1 ratio). The total profit was found by the sum of the profits for both the products, A and B. excel solver was used to acheive the profit of $ 20,000 in P column by changing the number of units. The solver solution has been provided in figure 20 (right table) (Anderson & Leese, 2016).

 

Figure 20: solver setup for profit based on two products

References

Anderson, J. A., & Leese, W. R. (2016). A Formula For The Units To Satisfy An Operation’s Desired Rate Of Return In CVP Analysis-A Conceptual Approach. American Journal of Business Education (Online), 9(2), 87.

Ku, I., & Nor, K. (2016). GEZ petrol station: CVP analysis and spread sheet modelling for planning and decision making.

Chatterjee, S., & Hadi, A. S. (2015). Regression analysis by example. John Wiley & Sons.

Draper, N. R., & Smith, H. (2014). Applied regression analysis (Vol. 326). John Wiley & Sons.

Tse, T. S., & Poon, Y. T. (2017). Modeling no-shows, cancellations, overbooking, and walk-ins in restaurant revenue management. Journal of foodservice business research, 20(2), 127-145.

Chen, C. (2016). Cancellation policies in the hotel, airline and restaurant industries. Journal of Revenue and Pricing Management, 15(3-4), 270-275.