Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk - Model Answer 2014-02 (Revised)
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
Still loading...
ABCDEFGHIJKLMNOPQR
1
Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk - 2014-02 (Revised)
2
3
AuthorPeter Mellalieu
4
Creation date28/07/2013
5
Modification date04/08/2013
6
Version4.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 factor0
20
State of Economy:
21
AlternativeOptimisticNeutralPessimistic
22
Employ labour$80,000$70,000$50,000Note 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,000Use 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 State20.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
AlternativeMaxiMaxMaxiMinEqual LikelihoodExpected Monetary Value (EMV)Reference Heizer & Render (2014) Module A: Decision Modeling pp. 703 – 710.
36
Employ labour$80,000$50,000$66,667$63,000MaxiMax finds the optimistic (MAXIMUM) value that occurs in the ROW for each Decision Alternative
37
Subcontract labour$120,000$45,000$78,333$68,750MaxiMin finds the worst case (MINIMUM) that occurs in the ROW for each Decision Alternative
38
Lease robot$120,000$55,000$95,000$87,250Equal 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,00030,000$86,667$74,500Expected 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,500Note 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,500Maximum in column for each decision criterion
43
Best Alternative No.4535Note use of 'MATCH' built-in function to find the location of the Best Alternative in a column.
44
Best AlternativeBuy robotSubcontract manufactureLease robotSubcontract manufactureNote 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?1111Note 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
OptimisticNeutralPessimisticTotal
51
Best Outcome per State$130,000$110,000$80,000Maximum in column for each State of Economy, from Table 1
52
Best AlternativeBuy robotLease robotSubcontract manufactureNote 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 State20%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,000OKEVPI = 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:
Loading...
 
 
 
Sheet1
Sheet2
Sheet3