Question

please put excel functions used A company is developing a new cell phone and currently has two models under consideration. Historically, 70% of their new phones have had high consumer demand and 30% have had low consumer demand. The below table shows the investment cost and revenue per high and low demands for each model. Model 1 Model 2 Requires $200k investment Requires $175k investment If demand is high, revenue = $500k If demand is high, revenue = $450k If demand is low, revenue = $160k If demand is low, revenue = $160k Exercise 16.3. Answer the following questions in Excel with detailed work Review the previous slides and examples to guide you through. a. Draw a decision tree with payoffs and probabilities, and calculate Expected Monetary Value (EMV) for each model. Write down your decision based your tree analysis Use the payoff table to reason how you make the decisions with Aggressive / Conservative / Opportunity Loss Strategies c. Calculate the EVPI

Answer 1

The answer of the question a) decision tree diagram and b in answer sheet.

A company is developing a new cell phone and currently has two models
	Model 1			Model 2
	Requires $200 invesment	Requires $175 invesment
	If demand is high, Revenue= $500K	If demand is high , Revenue= 450K
	If demand is Low , Revenue= $160K	If demand is Low , Revenue= 160K
Calculate Expected monetary value(EMV) of each model 1	Calculate Expected monetary value(EMV) of each model 2
Product- cell phone	Model 1				Product- cell phone	Model 2
calculate EMV for High demand			calculate EMV for High demand
The expected return in case of high demand is $500K		The expected return in case of high demand is $450K
Given that invesment is $200				Given that invesment is $175
The return would be $300 ( $500- $200).			The return would be $275 ( $450- $175).
But probability of the high demand is 70%.			But probability of the high demand is 30%.
The value of this node is 70% of $200 , (0.7 x$200 )= $140		The value of this node is 70% of $200 , (0.7x$275 )= $192.50
Product- cell phone	Model 1				Product- cell phone	Model 2
calculate EMV for Low demand			calculate EMV for Low demand
The expected return in case of Low demand is $160K		The expected return in case of Low demand is $160K
Given that invesment is $200				Given that invesment is $175
The return would be $-40( $160- $200).			The return would be $(-25)=( $175- $200).
But probability of the high demand is 30%.			But probability of the high demand is 30%.
The value of this node is 30% of $200 , (0.3 x$200 )= $60		The value of this node is 30% of $175 , (0.3 x$175 )= $52.50
			EMV of two Models are = ($60 +$52.50)= $ 112.50
Calculate EVPI =EPPI- EMV				Calculate EVPI =EPPI- EMV
EPPI= $60+$140= $200				EPPI= $192+$52= $244
EVPI= $200-$112.50					EVPI= $175-$244
EVPI= $87.50					EVPI= $(-69)