Answer the following question in EXCEL: The Islander Fishing Company purchases c
ID: 3289425 • Letter: A
Question
Answer the following question in EXCEL: The Islander Fishing Company purchases clams for $1.50 per pound from fishermen and sells them to various restaurants for $2.50 per pound. Any clams not sold to the restaurants by the end of the week can be sold to a local soup company for $0.50 per pound. The company can purchase 500, 1000, or 2000 pounds. The probabilities of various levels of demand are as follows:
Demand (Pounds) Probability
500 0.20
1000 0.40
2000 0.40
Construct a Payoff Table, an Opportunity Loss Table an Expected Monetary Value Table, and the Expected Value of Perfect Information calculations and answer. Probabilities are .2, .4, & .4. Use the setup below.
500
1000
2000
500
1000
2000
Hint: Some payoffs will be the difference between cost of clams and clams sold to the restaurants; other payoffs will be the difference between the cost of clams and both clams sold to the restaurants AND clams left over that can be sold to the soup kitchen.
500
1000
2000
500
1000
2000
Explanation / Answer
Solution
Purchase cost, c = $1.5
Sales price to various restaurants, Pr = $2.5
Sales price to local soup company, Ps = $0.5
For the payoff table we wil consider below
When purchase quantity is less than or equal to the demand,
payoff = purchase quantity* (Pr-c) = purchase quantity*(2.5–1.5) = 1*purchase quantity
When purchase quantity is greater than the demand,
payoff = Demand* (Pr-c) + (Purchase quantity – demand)*(Ps-c) = Demand*(2.5 – 1.5) + (purchase quantity – demand)*(0.5-1.5) = 1*Demand – purchase quantity + demand = 2*demand – purchase quantity
The payoff table is given as
Purchase quantity
Demand
Probability
500
1000
2000
500
0.2
500
0
-1000
1000
0.4
500
1000
0
2000
0.4
500
1000
2000
Eg. For 500 purchase quantity and 500 demand,
Purchase quantity < demand, so Payof = 1*purchase quantity = 500
For 2000 purchase quantity and 500 demand,
Purchase quantity > demand, so Payof = 2*Demand - purchase quantity = 2*500 – 2000 = - 1000
Opportunity loss table
Opportunity loss value for each sell is the difference between the maximum value of that row and the cell value.
Eg. Opportunity loss value for the cell depicting Purchase quantity of 2000 and demand of 500 is
Opportunity loss = 500 – (-1000) = 1500
The opportunity loss table is
Purchase quantity
Demand
Probability
500
1000
2000
500
0.2
0
-500
-1500
1000
0.4
-500
0
-1000
2000
0.4
-1500
-1000
0
Expected monetary value
Expected monetary value of purchase quantity of 500 = 500*0.2 + 500*0.4 + 500*0.4 = 500
Expected monetary value of purchase quantity of 1000 = 0*0.2 + 1000*0.4 + 1000*0.4 = 800
Expected monetary value of purchase quantity of 2000 = (-1000)*0.2 + 0*0.4 + 2000*0.4 = 600
Purchase quantity
Demand
Probability
500
1000
2000
500
0.2
500
0
-1000
1000
0.4
500
1000
0
2000
0.4
500
1000
2000
Expected monetary value
500
800
600
Expected value of perfect information
Expected monetary value of the whole process = maximum of EMV of all decisions = $800
When perfect information is available, i.e we know for certain what will be the demand,
When demand is 500, the best strategy is to purchase a quantity of 500 units
When demand is 1000, the best strategy is to purchase a quantity of 1000 units
When demand is 2000, the best strategy is to purchase a quantity of 2000 units
So, expected value under perfect information = 0.2*500 + 0.4*1000 + 0.4*1000 = $1700
So, expected value of perfect information = expected value under perfect information – expected monetary value = $1700 - $800 = $900
Purchase quantity
Demand
Probability
500
1000
2000
500
0.2
500
0
-1000
1000
0.4
500
1000
0
2000
0.4
500
1000
2000