Question

Requirements: Please finish the following questions using Excel with appropriate formulas; Please clearly specify your final answers (i.e. indicate the final decision given the criteria); All work should be completed in one single Excel file. Please refer to the last page for how to insert separate worksheets in Excel; Submission must be uploaded through Blackboard. When submit, please name your file with the format: A1_First name_Last name.

Q2.The XYZ Company is considering adding a new service facility among several possible alternatives including a child care center, a swimming pool, new locker rooms and showers, a health-oriented food court, and a spa. The following payoff table summarizes the potential returns (based on costs and increased enrollments) for each alternative service facility given three future levels of demand.

	Demand
Service Facility	Poor	Moderate	High
Child care center	$16,000	$25,000	$43,000
Swimming pool	-$75,000	$27,000	$71,000
New lockers & showers	$11,500	$37,000	$56,000
Food court	-$31,000	$17,000	$87,000
Spa	$6,000	$25,600	$33,500

Determine the best decision using the following criteria.

1. Maximax
2. Maximin
3. Hurwicz (α= 0.56)
4. Equal likelihood
5. Expected value (Poor: 0.18; Moderate: 0.56; High: 0.26)

==========================================================================

• How to insert separate worksheets in Excel

Answer 1

The below screenshot represent the consolidated solution for the given payoff table:

EMV Row Min Row Max Probabilities Child care center Swimming Pool New Lockers & Showers Food Court Spa Poor 0.18 16000 -75000 11500 31000 6000 Moderate 0.56 25000 27000 37000 17000 25600 High 0.26 43000 71000 56000 87000 33500 maximum 28060 20080 37350 26560 24126 37350 | Best EV | 16000 -75000 11500 -31000 6000 16000 maximin 43000 71000 56000 87000 33500 87000 maximax The maximum expected monetary value is 37350 given by New Lockers Showers The maximin is 16000 given by Child care center The maximax is 87000 aiven by Food Court

A) Maximax Criterion:

The Column Row Max shows the maximum value of each of the five decision alternatives considering all the three states of nature.

Maximax = Max(Row Column elements) = Max{D1, D2,D3,D4,D5} = Max{43000,71000,56000,87000,33500}

= 87000 (Under Maximax, We choose Foodcourt)

B) Maximin Criterion:

The column RowMin specifies the minimum value of each of the alternatives and it is the conservative approach.

Maximin = Max(Row Min) = Max{16000, -75000, 11500, -31000, 6000} = 16000

Under Maximin, We choose Child Care center.

C) Hurwicz (At alpha = 0.56)

Hurwiz value = alpha * Maximum + (1-alpha)* Minimum    (Max & Min values For each decision alternative)

child care center = 0.56*43000 + (1-0.56)*16000 = 31120

swimming pool = 0.56*71000 + (1-0.56)*-75000 = 6760

New Lockers = 0.56*56000 + (1-0.56)*11500 = 36420

Food court = 0.56*87000 + (1-0.56)*-31000 = 35080

Spa = 0.56*33500 + (1-0.56)*6000 = 21400

The highest return as per Hurwicz is for New lockers & showers with the return of $36,420

D) Expected Monetary value = Sumproduct (Probability of state, Return)

EMV for child care center = 0.18*16000 + 0.56*25000 + 0.26*43000 = 28060

EMV for swimming pool = 0.18*-75000 + 0.56*27000 + 0.26*71000 = 20080

Similarly,

EMV for New Lockers = 37350

EMV for Food court = 26560

EMV for Spa = 24126

The largest of the EMV values gives the expected return for the best alternative. It is for New lockers & showers at $37350