Case Study: Decision Making Process, Probabilities, Sales Records, Regression Analysis Essay.

Decision Making Process and Probabilities

(a) Describe what is involved in the decision making process, explaining the steps required.

(b) What is an alternative? What is a state of nature? Give an example of each which are related.

(c) Every Thursday a fish vendor sets up a van in Wagga Wagga and sells seafood during the day. One of his best selling items is fillets of Atlantic salmon. The fillets are purchased from a city fish market for $15 per kg and sold for $30 per kg. Unsold fillets at the end of the day are disposed of for $10 per kg to a local take-away food proprietor.

Sales records for the last two years are as follows:

Sales (kg)	Number of times
10	10
15	20
20	40
25	20
30	10

1. Construct a conditional profits matrix showing the 5 possible alternatives (strategies) and the 5 possible sales levels, and fill in the profits associated with each of the 25 cells. 
   2. If the fish vendor were an optimist, how many kg would he buy each week?
   3. If the fish vendor were a pessimist, how many kg would he buy each week?
   4. If the fish vendor used the Laplace criterion, how many kg would he buy each week?
   5. If the fish vendor used the criterion of regret, how many kg would he buy each week?
   6. If the fish vendor based his decision on maximising expected monetary value, how many kg would he buy each week?
   7. Suppose that weekly demand for the fillets is normally distributed with a mean of 20 kg and a standard deviation of 5 kg. How many kg should he buy each week to maximise expected profit (to the nearest kg)?

Round probability calculations to 2 decimal places.

A firm is considering marketing a new product which will be a success or a failure. The prior probability of success is judged to be 0.3.

If the product is marketed and is a success the firm expects to earn $1,000,000, while a failure is expected to lead to a loss of $600,000.

(a) Should the product be marketed? Why? 
(b) What is the expected value of perfect information about the success or failure of the product?

The firm is considering a market survey whose results can be classified as favourable or unfavourable. Given past experience with the market survey personnel, the conditional probabilities are p(favourable|success) = 0.7 and p(unfavourable|failure) = 0.8.

(c) Revise the prior probabilities in light of these likely survey results. 
(d) What is the posterior probability of success given a favourable survey result?
(e) What is the maximum the firm should pay for the market survey?

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 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.

Sales Records

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).

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.

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.

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:

Car	Price	Mileage	Age (years)
1	$16,200	10,600	1
2	$16,000	21,800	1
3	$12,500	34,000	3
4	$11,300	41,700	3
5	$14,800	53,500	4
6	$12,900	57,200	5
7	$11.500	65,800	7
8	$9,900	72,100	6
9	$8,200	76,500	8
10	$9,500	84,700	9

(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.

(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?

1. Decision making is the game-plan picked by dissecting the assembled data about any undertaking. The elective arrangements are additionally cross checked and choice tree helps in building the whole model. There were a few stages associated with basic leadership. At first the need of the choice and point of the task is examined. At that point vital data is gathered and elective ways are recognized. The information was cross checked and decision of definite option is finished. Organization executes the design and the delayed consequences are recorded for future examination.
2. Alternate strategy is the following best arrangement acquired in choice displaying and is known as option. Prospect hypothesis clarifies the elective procedure, for instance dread of future misfortunes angers individuals more than the delight of future increases. In this way, minimization of future failure is a contrasting option to boost the future additions.
3. Conditional profit matrix for 5 alternate methodologies is given in table 1. The findings have been done using MS Excel.

Decision Making Process and Probabilities

Table 1: Conditional Profit Matrix of Fish vendor

8. Expected monetary value for each policy has been calculated and is shown in table 8. The EMV value was negative for the priori probabilities.

Table 8: Priori probabilities of the base model

Using the provided set of information, due to expected negative monetary value, the product should not be launched in the market.

1. In case of Perfect information for success, expected value will be $ 3, 00,000 and for the failure, the expected value will be [-$ 4, 20,000].
2. The priori probabilities were tailored as in table 9 for the two cases, success and failure.

1. Posterior probabilities are computed for favorable survey results. The marginal probabilities are assessed by multiplying the restrictive chances with priori probabilities (Table 10).

The posterior probabilities have been calculated using the marginal probabilities, by dividing joint probabilities by marginal probabilities (

Total expected profit for the survey was $ 81,600. The maximum survey value was found by totaling the net expected values of favorable and unfavorable conditions. The priori probabilities do not find any profit for the model (Kruschke, 2010). Therefore the maximum survey cost payable by the company was $ 81,600.

1. Hotel Heart break Daily Cost effective model formation

Base model system

Table 13: Average daily cost for room booking in Hotel Heartbreak

B. Excel formula sheet for random cancelletion numbers 

Table 14: Heartbreak Hotel monthly simulated sheet for 3 overbooked rooms

The excel sheet with the formula of calculations of the base model has been provided in figure 3.  

Table 15: Heartbreak Hotel monthly simulated excel work sheet containing formula

Table 16: Heartbreak Hotel room booking model for zero overbooked rooms

Table 17: Heartbreak Hotel monthly simulated excel sheet for zero overbooked rooms

Table 18: Heartbreak Hotel room booking model for one overbooked room

 

Table 20: Heartbreak Hotel room booking model for 2 overbooked rooms

Table 21: Heartbreak Hotel monthly simulated excel sheet for 2 overbooked rooms

Synopsis on Average daily cost and Overbooking of rooms

The reenacted normal day by day cost of the Heart Break inn was $ 203.33. Number of overbooked rooms was at first thought to be three. Normal no shows by clients was in the vicinity of zero and five, every day. The month to month information for every day cost was computed by picking the no shows of the clients in an irregular example from the rundown of no shows. The adjustment in normal day by day cost for the clients was noted. An exceed expectations spreadsheet was made to analyze the result of no shows on the day by day normal cost. The normal every day cost of the inn was re-ascertained and noted by changing the quantity of overbooked rooms in the inn. The normal day by day cost was computed for five unique estimations of no shows of clients. It was uncovered that the day by day normal cost lessened when the quantity of overbooked rooms in the hotel was diminished (Zakhary et al., 2011). The base recreated normal every day cost was observed to be $ 69.17 for one overbooked room. Henceforth diminishing number of overbooked rooms was beneficial for the hotel (Yang, Pan & Song, 2014).

Sincerely Yours

A. Regression model of Price on mileage travelled

Line of best fit was , the place Y was the cost of auto and X was the mileage secured by old autos. The negative coefficient of Mileage secured mirrored that cost of the old autos was contrarily identified with the mileage secured by that auto. The p-value for the mileage in the relapse show was under 0.05. In this way, the relapse model could clarify the cost of the old autos altogether.

Sales Records

Regression model of Price on Age of cars

Regression equation for the model was , the place Y was the cost of auto and X was age of those old autos. Negative coefficient of age of the autos demonstrated that cost of old autos was adversely identified with time of autos. The p-value for time of autos in the relapse show was under 0.05. Thusly, the regression model could portray the cost of the old autos altogether.

Regression model of Price on Mileaage  and Age of cars

The linear regression equation was the place Y demonstrated the cost of auto, X meant mileage secured and Z spoke to the age of the second hand autos. The negative coefficients of age and mileage were negative, which mirrored that expanded free factors had decrement impact on cost of autos. The p-value of the relapse demonstrate for add up to age and aggregate mileage secured were bigger than 0.05 (Montgomery, Peck & Vining, 2012). Thus the regression model was not sufficiently noteworthy to give insights about the fluctuation of cost of the old autos based. Regression models of table 14 and 15 explained the fluctuation of cost of the old autos than the aggregate model with both the free factors (Montgomery, Peck and Vining, 2012). 

b. Singular models performed superior to anything the aggregate regression model. The adjusted R-square estimation of mileage-value display was 73.11% which clarified the fluctuation of cost in the second model. The significance values of intercept and mileage at the auto cost and aggregate mileage demonstrate were likewise under 0.05. Regression coefficients of auto age and aggregate mileage secured demonstrated that cost of the old autos was adversely identified with the clarifying components. The connection nature between cost of old autos and aggregate mileage secured, time of old auto was clear because of evident realities (Seber, & Lee, 2012).

Spearman’s Correlation between total mileage covered by cars and  age of the cars

Add up to mileage voyaged and age of an old auto was positively associated. The correlation was essentially high in nature. The model with the two free factors was legitimate in nature, obvious from the connection score. Total mileage secured by the old auto was the better free factor, which was obvious from the relapse demonstrate. Both the factors were similarly vital in choosing cost of autos.

Model setup and solver solution

The principal model was setup with item A where units sold was 200. The whole situation has been given in the left most table of table18. The MS Excel solver arrangement was 300 units for break even profit for initial investment. This situation additionally has been given in table 18.

The base model was solved by utilizing MS Excel solver where the profit  was set to at   $ 1600. The solver acquired number of units sold as 500 units. Table 19 clarifies the benefit model (Williams-Gray, 2013).

1. Product B was presented in the model set up with the item A in the underlying CVP model with number of units as 200 (introductory estimation of base model) and 100units where the two qualities were in 2:1 proportion. Add up to benefit was ascertained by including the benefits for both the items An and B. Microsoft exceed expectations solver was utilized and most extreme benefit was set to $ 20,000. The solver at that point explained the model by changing the quantity of units for item A. The solver arrangement has been given in table figure 20.

Bodea, T., Ferguson, M., & Garrow, L. (2009). Data set—choice-based revenue management: Data from a major hotel chain. Manufacturing & Service Operations Management, 11(2), 356-361.

Kruschke, J. K. (2010). What to believe: Bayesian methods for data analysis. Trends in cognitive sciences, 14(7), 293-300.

Montgomery, D. C., Peck, E. A., & Vining, G. G. (2012). Introduction to linear regression analysis (Vol. 821). John Wiley & Sons.

Seber, G. A., & Lee, A. J. (2012). Linear regression analysis (Vol. 329). John Wiley & Sons.

Williams-Gray, C. H., Mason, S. L., Evans, J. R., Foltynie, T., Brayne, C., Robbins, T. W., & Barker, R. A. (2013). The CamPaIGN study of Parkinson's disease: 10-year outlook in an incident population-based cohort. J Neurol Neurosurg Psychiatry, 84(11), 1258-1264.

Yang, Y., Pan, B., & Song, H. (2014). Predicting hotel demand using destination marketing organization’s web traffic data. Journal of Travel Research, 53(4), 433-447.

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.