Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk - Model Answer 2014-02 (Revised) : Sheet1

1 | Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk - 2014-02 (Revised) | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

2 | ||||||||||||||||||

3 | Author | Peter Mellalieu | ||||||||||||||||

4 | Creation date | 28/07/2013 | ||||||||||||||||

5 | Modification date | 04/08/2013 | ||||||||||||||||

6 | Version | 4.1 | ||||||||||||||||

7 | ||||||||||||||||||

8 | PURPOSE | |||||||||||||||||

9 | This Decision Support System identifies the BEST ALTERNATIVE to select given a table of net economic returns for three conditions of uncertainty, designated State of the Economy. | |||||||||||||||||

10 | The Best Alternative recommended depends on the choice of Decision Criterion: MaxiMax, MaxiMin, Equal Likelihood, or Expected Monetary Value. | |||||||||||||||||

11 | The choice of decision criterion depends on the decision-maker's propensity for risk. | |||||||||||||||||

12 | If the probabilities of the possible States of the Economy are provided, the Expected Value of Perfect Information (EVPI) is calculated. | |||||||||||||||||

13 | The Expected Value of Perfect Information is the maximum money the decision-maker would be prepared to pay to learn EXACTLY which State of the Economy WILL occur in the future. | |||||||||||||||||

14 | Byproduct: This spreadsheet demonstrates several features of 'good practice' spreadsheet coding and documentation. | |||||||||||||||||

15 | ||||||||||||||||||

16 | INPUT | |||||||||||||||||

17 | ||||||||||||||||||

18 | Table 1 Net returns depending on alternative selected and state of the economy | |||||||||||||||||

19 | Reduction factor | 0 | ||||||||||||||||

20 | State of Economy: | |||||||||||||||||

21 | Alternative | Optimistic | Neutral | Pessimistic | ||||||||||||||

22 | Employ labour | $80,000 | $70,000 | $50,000 | Note use of Data Validation to check that a number is entered. | |||||||||||||

23 | Subcontract labour | $120,000 | $70,000 | $45,000 | ||||||||||||||

24 | Lease robot | $120,000 | $110,000 | $55,000 | ||||||||||||||

25 | Buy robot | $130,000 | $100,000 | $30,000 | ||||||||||||||

26 | Subcontract manufacture | $110,000 | $90,000 | $80,000 | Use EXACTLY $99,999,999 to signal an invalid Alternative. Avoid using ZERO or BLANK cells, unless those values are the consequence of DO NOTHING. | |||||||||||||

27 | ||||||||||||||||||

28 | Probability of State | 20.0% | 35.0% | 45.0% | 100% | Note use of IF and AND built-in functions to check probabilities sum close to 100 per cent. Note use of SUM and MAX to calculate automatically the probability for 'Pessimistic' PROVIDED that the sum of Optimistic and Neutral is LESS THAN or EQUAL to 100 per cent, | ||||||||||||

29 | Note use of Data Validition feature to check that each probability is between 0 and 100 per cent. | |||||||||||||||||

30 | OUTPUT | |||||||||||||||||

31 | ||||||||||||||||||

32 | Table 2 Best alternatives identified against several decision criteria | |||||||||||||||||

33 | ||||||||||||||||||

34 | Decision Criterion: | |||||||||||||||||

35 | Alternative | MaxiMax | MaxiMin | Equal Likelihood | Expected Monetary Value (EMV) | Reference Heizer & Render (2014) Module A: Decision Modeling pp. 703 – 710. | ||||||||||||

36 | Employ labour | $80,000 | $50,000 | $66,667 | $63,000 | MaxiMax finds the optimistic (MAXIMUM) value that occurs in the ROW for each Decision Alternative | ||||||||||||

37 | Subcontract labour | $120,000 | $45,000 | $78,333 | $68,750 | MaxiMin finds the worst case (MINIMUM) that occurs in the ROW for each Decision Alternative | ||||||||||||

38 | Lease robot | $120,000 | $55,000 | $95,000 | $87,250 | Equal Likelihood assumes all States of Economy are equally likely. The AVERAGE built-in function could have been used. The more complex use of SUMIF and COUNTIF deals with the case when there is a ZERO probability of state, so you wish to EXCLUDE all outcomes for that state. | ||||||||||||

39 | Buy robot | $130,000 | 30,000 | $86,667 | $74,500 | Expected Monetary Value (EMV) weights the return for each state of economy with the PROBABILITY that State of the Economy occurs. Note use of SUMPRODUCT built-in function. Note use of C$nn:E$nn to refer to a fixed ROW of Probabilities in the EMV column. | ||||||||||||

40 | Subcontract manufacture | $110,000 | $80,000 | $93,333 | $89,500 | Note use of C$nn:E$nn to refer to a fixed ROW of Probabilities | ||||||||||||

41 | ||||||||||||||||||

42 | Best Criterion Value | $130,000 | $80,000 | $95,000 | $89,500 | Maximum in column for each decision criterion | ||||||||||||

43 | Best Alternative No. | 4 | 5 | 3 | 5 | Note use of 'MATCH' built-in function to find the location of the Best Alternative in a column. | ||||||||||||

44 | Best Alternative | Buy robot | Subcontract manufacture | Lease robot | Subcontract manufacture | Note use of 'INDEX' built-in function to look-up the name of the Best Alternative. Note use of $Bnn:$Bnn to reference a fixed COLUMN of Alternatives. | ||||||||||||

45 | Several Best Alternatives? | 1 | 1 | 1 | 1 | Note use of COUNTIF built-in function to identify if there are more than 1 identically best alternatives. 'Conditional formatting' displays a count of "1" as colour WHITE | ||||||||||||

46 | ||||||||||||||||||

47 | Table 3 Expected Value of Perfect Information (EVPI) | |||||||||||||||||

48 | ||||||||||||||||||

49 | State of Economy: | |||||||||||||||||

50 | Optimistic | Neutral | Pessimistic | Total | ||||||||||||||

51 | Best Outcome per State | $130,000 | $110,000 | $80,000 | Maximum in column for each State of Economy, from Table 1 | |||||||||||||

52 | Best Alternative | Buy robot | Lease robot | Subcontract manufacture | Note use of 'ugly' (error-prone!) combination of INDEX and MATCH to display the Best Alternative associated wth the Best Outcome for the State, lookedup from Table 1. | |||||||||||||

53 | Probability of State | 20% | 35% | 45% | From Table 1 | |||||||||||||

54 | Expected Value of Best Outcome | $26,000 | $38,500 | $36,000 | $100,500 | = Best outcome per state x Probability of the outcome state | ||||||||||||

55 | Expected Value WITH Perfect Information, EVwPI | = | $100,500 | = Sums of [Expected Value WITH Perfect Information for ALL States of Economy]. Should be the same value as the previous row. Note use of SUMPRODUCT built-in function This is a checksum calculation to test the earlier calculations are correct. | ||||||||||||||

56 | Maximum Expected Monetary Value, Max (EMV) | Subcontract manufacture | $89,500 | = Best criterion value for EMV Decision Criterion, Table 2 | ||||||||||||||

57 | Expected Value OF Perfect Information (EVPI) | $11,000 | OK | EVPI = EVwPI - Maximum EMV. See Heizer & Render (2014), Module A, pp. 709 - 710. | ||||||||||||||

58 | ||||||||||||||||||

59 | LIMITATIONS | |||||||||||||||||

60 | SERIOUS WARNING: If the user enter probabilities that DO NOT sum to 100 per cent, then INCORRECT values for EMV (Table 2) and EVPI (Table.3) will be calculated. | |||||||||||||||||

61 | This error is AVOIDED by ensuring that the calculation for eg Pessimistic probability = 1 - SUM(all other probabilities). Implemented in Version 2.3 Aug 5, 2013. Still not fool-proof if the user enters say 60% then 50 % for the first two probabilities! | |||||||||||||||||

62 | SERIOUS WARNING: Missing data in Table 1 causes a divide by zero error message. Missing data is not to be interpreted as "zero". | |||||||||||||||||

63 | WARNING: If the Net Returns (Table 1) contains IDENTICAL values in a column then the first (top-most) row containing the duplicate value will be identified as the Best Alternative for the four decision criteria. WARNING added in Version 3.0 | |||||||||||||||||

64 | A message warns of multiple equal decisions, using the COUNTIF builtin function. Implemented Version 3.0. | |||||||||||||||||

65 | ||||||||||||||||||

66 | INSTRUCTIONS | |||||||||||||||||

67 | NOTE: The spreadsheet is limited to three States of Economy and five alternative decisions. It is a straightforward task to extend this capability. | |||||||||||||||||

68 | To EXCLUDE a decision alternative, insert a very negative value (eg $-99,999,999) in the Table 1 of net returns. | |||||||||||||||||

69 | To EXCLUDE a State of the Economy enter $0 for VALID alternatives, enter $-99,999,999 in the remainder of the column. Set the probability to zero. However, MaxiMin MIGHT be calculated INCORRECTLY. MaxiMax, EMV and EVPI will calculate correctly. See QA Test A4 and A5 below. | |||||||||||||||||

70 | ||||||||||||||||||

71 | QUALITY ASSURANCE | |||||||||||||||||

72 | The following uses TEST DATA from Heizer & Render Example A4 and A5 (2014, pp 708 - 710). RESULT: MiniMax and EMV and EVPI function correctly. MaxiMin and Equal Likelihood fail. The Pessimistic option would need removing for this example, since the example has just two 'States of Nature' | |||||||||||||||||

73 | https://docs.google.com/spreadsheet/ccc?key=0AjwyjX8GotyadDFPOUNielI1WHBKbW9OX1V0eTExSGc&newcopy | |||||||||||||||||

74 | https://docs.google.com/spreadsheet/pub?key=0AjwyjX8GotyadDFPOUNielI1WHBKbW9OX1V0eTExSGc&single=true&gid=0&output=html | |||||||||||||||||

75 | ||||||||||||||||||

76 | MODIFICATION HISTORY | |||||||||||||||||

77 | Version 3.0 Improved checking to ensure user enters probabilities that sum to 100 per cent. | |||||||||||||||||

78 | Version 3.0 Check for multiple Best Alternatives added to Table 1. | |||||||||||||||||

79 | Version 3.0 Numbering of tables adjusted to Table 1, 2, 3 etc. | |||||||||||||||||

80 | Version 3.1 Link to Test Data version provided. See QA. | |||||||||||||||||

81 | Version 3.1 Improvements to documentation | |||||||||||||||||

82 | Version 4.1 Adjusted Table 3 to include display of Best Alternative for Best Outcome, and Best Alternative for Max (EMV). Error check for EVwPI added. | |||||||||||||||||

83 | Version 4.2 Adjusted Table 2 to calculate Equal Likelihodd to avoid using cells containing (null) ie -$99,999,999 value, using COUNTIF and SUMIF. In the new Google Sheets, you could use AVERAGIF. | |||||||||||||||||

84 | ||||||||||||||||||

85 | REFERENCE | |||||||||||||||||

86 | Barlow, J. (2006). Excel Models for Business and Operations Management. John Wiley & Sons. | |||||||||||||||||

87 | Heizer, J., & Render, B. (2014). Decision Modeling (Part 4 - Module A). In Operations Management: Sustainability and Supply Chain Management [Global Edition] (11th ed.). Pearson Education. | |||||||||||||||||

88 | ||||||||||||||||||

89 | Data Validation in Excel. (2012). Retrieved from http://www.youtube.com/watch?v=uQm_CxwcOHw&feature=youtube_gdata_player | |||||||||||||||||

90 | Excel For Beginners - Part 1: Introduction. (2011). Retrieved from http://www.youtube.com/watch?v=L7dHA_8GzKw&feature=youtube_gdata_player | |||||||||||||||||

91 | Excel For Beginners - Part 2: Functions. (2011). Retrieved from http://www.youtube.com/watch?v=tWjk4FvYYiM&feature=youtube_gdata_player | |||||||||||||||||

92 | Excel For Beginners - Part 3: IF Statement. (2011). Retrieved from http://www.youtube.com/watch?v=LEc3Va_ODf0&feature=youtube_gdata_player | |||||||||||||||||

93 | ||||||||||||||||||

94 | French, T. (n.d.). Formulas and Functions. About.com Spreadsheets. Retrieved August 1, 2013, from http://spreadsheets.about.com/od/excelformulas/u/formulas_functions_user_path.htm | |||||||||||||||||

95 | Google spreadsheets function list. (n.d.). Retrieved August 1, 2013, from https://support.google.com/drive/table/25273?page=table.cs&rd=1 | |||||||||||||||||

96 | How to protect cells in an Excel Spreadsheet. (2013). Retrieved from http://www.youtube.com/watch?v=4f1j0oCGeRk&feature=youtube_gdata_player | |||||||||||||||||

97 | Conditional formatting - Theatre booking system 6 - Google Spreadsheet. (2012). | |||||||||||||||||

98 | ||||||||||||||||||

99 | CITATION | |||||||||||||||||

100 | Cite or access the Google Sheets version of this spreadsheet: | |||||||||||||||||

101 | Mellalieu, P. J. (2013). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [Google Sheets Fork]. Retrieved from https://docs.google.com/spreadsheet/ccc?key=0AjwyjX8GotyadDgxa0VxekEwUXV0TUw4TVdfa2UtSGc&newcopy | |||||||||||||||||

102 | ||||||||||||||||||

103 | Download the source spreadsheet from: | |||||||||||||||||

104 | Mellalieu, P. J. (2013, August 1). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [DropBox Excel]. Retrieved July 31, 2013, from https://www.dropbox.com/s/h43t3udu8d4y61q/Decision%20Modeling%20FINAL%20-%20PUBLIC.xls | |||||||||||||||||

105 | ||||||||||||||||||

106 | Web 'view only' version | |||||||||||||||||

107 | Mellalieu, P. J. (2013, August 1). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [Google Sheets Fork Web Display]. Retrieved July 31, 2013, from https://docs.google.com/spreadsheet/pub?hl=en&key=0AjwyjX8GotyadDgxa0VxekEwUXV0TUw4TVdfa2UtSGc&hl=en&gid=0 |