Question

I am not able to upload a image of the question or in tables as it is formated. Any suggestions how i can do this? Thank you

Penny’s Rent A Car
Penny’s Rent a Car offers three rental plans as shown in Table 1. Please note that the fixed cost is provided in terms of months. Later, you will be asked to construct a cost table in terms of annual cost.
Table 1. Rental plan characteristics
Plan
Fixed Monthly Payment
Additional costs per annual miles
I
$200
$0.095 per mile
II
$300
$0.061 for each of the first 12,000 miles and $0.05 per mile thereafter
III
$150
$0.10 for each of the first 12,000 miles and $0.20 per mile thereafter
A customer is considering which option to take. This customer estimates the likely annual mileage as shown in Table 2.
Table 2. Estimated annual mileage probabilities
Annual Miles
10,000
15,000
20,000
25,000
30,000
Probability
0.1
0.1
0.2
0.3
0.3
Create a cost matrix for this problem using total annual cost. Show all costs as negative values (i.e. accounting format).
1) What alternative is selected using the Maximax criterion?
2) What alternative is selected using the Maximin criterion?
3) What alternative is selected using the Expected Monetary Value criterion?
4) What alternative is selected using the Minimax Regret criterion?
5) What alternative is selected using the Expected Regret (or Expected Opportunity Loss) criterion?
HINT: Be sure to answer to develop a spreadsheet that is robust and is developed in a dynamic manner. Develop formulas to make it clear which decision that would be made based on the methods described. Remember, everything that is a cost should be negative (i.e. accounting format).

Above is the question and below is what is on the excel sheet to start answering the question. Can you please show all steps (showning the excel formulas)?

	Problem Information	Values
	Months Per Year	12
	Miles in contract	12,000
	Plan	Fixed Cost Per Month	Cost Per Mile for First 12000 miles	Cost Per Mile After 12000 miles
	I	$                         (200.00)	$                                                     (0.095)	$                                               (0.095)
	II	$                         (300.00)	$                                                     (0.061)	$                                               (0.050)
	III	$                         (150.00)	$                                                     (0.100)	$                                               (0.200)
	STATE OF NATURE	10,000	15,000	20,000	25,000	30,000
	Probabilities	10%	10%	20%	30%	30%
Show work below

Answer 1

Soln : Please refer the table :

Plan	Fixed Cost Per Month	Cost Per Mile for First 12000 miles	Cost Per Mile After 12000 miles	Annual Fixed	Cost per mile(x)	10000	15000	20000	25000	30000
I	$                         (200.00)	$                                                     (0.095)	$                                               (0.095)	2400	x*0.095	3350	3825	4300	4775	5250
II	$                         (300.00)	$                                                     (0.061)	$                                               (0.050)	3600	12000*0.061 +(x-12000)*0.050	4210	4482	4732	4982	5232
III	$                         (150.00)	$                                                     (0.100)	$                                               (0.200)	1800	12000*0.1 + (x-12000)*0.2	2135	3600	4600	5600	6600

	Probability	0.10	0.10	0.2	0.3	0.3
Annual costs in different state of nature	I	-335.00	-382.50	-860.00	-1432.50	-1575.00
	II	-421.00	-448.20	-946.40	-1494.60	-1569.60
	III	-213.50	-360.00	-920.00	-1680.00	-1980.00

In the 2nd table we have calculated each value by multiplying the probability with the values calculated for each option

(a) Maximax is the state where we will choose the max. value from each plan and decide the max. value among them, like here for Plan I = - 335, Plan 2 = - 421, plan 3 = -213.50

So, plan 3 is the best as per this criteria

(2) Max. min criteria that is pessimistic one, we choose the best from the worst

Worst values for plans are I = -1575, II = -1569.60, III = -1980

Now, the best value among these is for Plan 2 i.e. -1569.60. Hence plan 2 is the best alternative

(3) As per expected monetary value , all the probabilities to be multiplied by impact and summation. And choose alternative with least value : for above table we will get values :

Summation

Plan 1 -4585.00

Plan 2 -4879.80

Plan 3 -5153.50

Least Value is of Plan1, hence that will be best alternative

(4) In this criteria, we will calculate the opportunity loss, as we will select the max. value and subtract the values of each plan in each state of nature to get the values:

Values for calculating opportunity losses to get Maximin regret						Max.
I	121.50	22.50	0.00	0.00	5.40	121.50
II	207.50	88.20	86.40	62.10	0.00	207.50
III	0	0	60.00	247.50	410.40	410.40

So, among the max. values we should choose the alternative with min. value i.e. plan 1 is the right option here.

5) For Expected regret criteria , we will calculate the value of probability with regret values and add them for each plan. the min. value of plan will give the best alternative

Here we get the follwing for plan I,II, III

Min.

16.02

65.48

209.37

Here smallest value is of plan1 , which will be best alternative.