Simulation, Regression Analysis, CVP Analysis Essay.

Simulation at Heartbreak Hotel

1 Simulation

This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.

You have just been hired as an analyst to assist the manager of Heartbreak Hotel. Your first assignment is to examine and report on the reservations policy in the hotel.

Heartbreak Hotel routinely experiences no-shows (people who make reservations for a room and don’t show up) during the peak season when the hotel is always full. No-shows follow the distribution shown in the attached Excel proforma in cells A3:A9 and C3:C9.

In order to reduce the number of vacant rooms the hotel overbooks three rooms, i.e. accepts three more reservations than the number of rooms available. The hotel’s policy is to send any guests who miss out on a room to a competing hotel down the street at Heartbreak’s expense of $125 for each such guest. If the number of no-shows is more than three the hotel has vacant rooms resulting in an opportunity cost of $50 per room. 

(a) Using Excel set up a model to simulate 1 month (30 days) of operation to calculate the hotel’s monthly cost due to overbooking and opportunity loss. 
You can use this template to guide you:

 

You need to complete the Cumulative probabilities.

All data are shown in rows 3:9 except for cell J9 which will contain a formula.

There should be no numbers in your model which should consist of all formulas from row 13 onwards except for column A.

Column A shows the day number (1 to 30).

In column B random numbers are generated.

In column C there is a LOOKUP function to simulate the number of no-shows to be entered in column C.

In column D compute the number of short rooms (unavailable for guests) by comparing the number of no-shows with the number of rooms decided to be overbooked: e.g. =Max($J$4-C13,0). If the number of rooms overbooked exceeds the number of no-shows there is a shortage of available rooms, else if no-shows exceed rooms overbooked there is no shortage, but possibly vacancies (the formula would be negative but by placing a maximum of zero in the formula it comes out zero (no shortage)).

The short cost in column E is found by multiplying the cell J6 by D13 etc.

In column F (vacant rooms) the formula is the reverse of the one in column D: = max(C13-$J$4,0).

Regression Analysis for Volkswagen Jetta

The cost of vacant rooms in column G is the product of the cost in J7 and the number of vacant rooms.

Total cost in column H sums col E and col G.

(a) Copy formulas down to day 30, sum the total costs in col H and divide by the 30 to put the result in I9.

(b) Now print 2 copies of your model showing row and column numbers. Copy 1 should show the output and copy 2 should show the formulas.

(c) Now test to find the number of rooms that Heartbreak Hotel should overbook each day. Test for 0,1,2,3,4,5 checking the total average daily cost each time. All you should have to do is change cell J4 and observe the change in average daily cost and tabulate them somewhere in your model. State the number of overbookings which gives minimum average daily cost over the 30 days. You need to take care of getting the same figure for each level of overbooking by either tabulating the results each time manually or using an IF statement.

(d) You present your findings to the hotel manager with your recommendation as to how many rooms should be overbooked each day. The report must be dated, addressed to the Manager and signed off by you.

QUESTION 2 Regression Analysis

Barry Smith is on a work visa in the USA for 3 years and wishes to buy a second-hand car to use over the 3-year period. He is particularly interested in buying a Volkswagen Jetta. He thinks that the market price is related to the mileage covered and the age of the car. Barry examines previous sales from the local area and compiles a list of data on 10 cars, as shown below:

 

(a) Using Excel, perform three regression analyses to regress Price against Mileage, then against Age, then against both of them simultaneously. Paste your results into Word. State the cost equation for each. Analyse and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.

(b) If you had to settle for the results of a simple regression, which one would you use and why? Explain why negative coefficients for Mileage and Age are acceptable.

(c) Should Barry use both Mileage and Age as a guide to price? The multiple regression should provide the answer to this. Also you could check correlation between the independent variables.
 

QUESTION 3 CVP Analysis

We start with a simple use of Excel in CVP analysis. Copy this model into Excel:

 

______________________

Goal seek         ?         X

Set cell          $B$14

To value         0

By changing cell $B7

          OK            Cancel

________________________

and this produces zero in cell B14 and the breakeven units in cell B7 (ie 200).

(a)
A manufacturer can make product A. The following data are available:

Selling price per unit $15, Variable cost per unit $7, Fixed cost $2,400.

Modify the model shown above and invoking Goal Seek, determine the number of units required to break even.

(b)
Use the modified model again to determine the number of units required to earn a profit before tax of $1,600.

(c) Now a second product is added, B:

Selling price per unit $20, Variable cost per unit $10, Fixed cost $3,600

 He decides to manufacture both A and B this year in the ratio of 2 of A to 1of B.

Assuming total fixed costs are the sum of the fixed costs allocated to each product, how many of each product must be sold to earn a profit of $20,000?

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.