ABCDEFGHIJKLMNOPQR
1
Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk: Test Experiment A4 and A5
2
TEST DATA using Heizer & Render Example A4 and A5 (2014, pp 708 - 710)
3
AuthorPeter Mellalieu
4
Creation date28/07/2013
5
Modification date08/08/2013
6
Version3.1TEST FORK
7
8
PURPOSE
9
This is a TEST using 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 needs removing
10
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.
11
The Best Alternative recommended depends on the choice of Decision Criterion: MaxiMax, MaxiMin, Equal Likelihood, or Expected Monetary Value.
12
The choice of decision criterion depends on the decision-maker's propensity for risk.
13
If the probabilities of the possible States of the Economy are provided, the Expected Value of Perfect Information (EVPI) is calculated.
14
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.
15
Byproduct: This spreadsheet demonstrates several features of 'good practice' spreadsheet coding and documentation.
16
17
INPUT
18
19
Table 1 Net returns depending on alternative selected and state of the economy
20
21
State of Economy:
22
AlternativeOptimisticNeutralPessimistic
23
Large Plant A1$200,000-$180,000-$99,999,999Note use of Data Validation to check that a number is entered.
24
Small Plant A2$100,000-$20,000-$99,999,999
25
Do Nothing$0$0-$99,999,999
26
(nul)-$99,999,999-$99,999,999-$99,999,999
27
(nul)-$99,999,999-$999,999,999-$99,999,999
28
29
Probability of State60.0%40.0%0.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,
30
Note use of Data Validition feature to check that each probability is between 0 and 100 per cent.
31
OUTPUT
32
33
Table 2 Best alternatives identified against several decision criteria
34
35
Decision Criterion:
36
AlternativeMaxiMaxMaxiMinEqual LikelihoodExpected Monetary Value (EMV)Reference Heizer & Render (2014) Module A: Decision Modeling pp. 703 – 710.
37
Large Plant A1$200,000-$99,999,999-$33,326,666$48,000MaxiMax finds the optimistic (MAXIMUM) value that occurs in the ROW for each Decision Alternative
38
Small Plant A2$100,000-$99,999,999-$33,306,666$52,000MaxiMin finds the worst case (MINIMUM) that occurs in the ROW for each Decision Alternative
39
Do Nothing$0-$99,999,999-$33,333,333$0Equal Likelihood assumes all States of Economy are equally likely. Note use of AVERAGE built-in function
40
(nul)-$99,999,999-$99,999,999-$99,999,999-$99,999,999Expected Monetary Value weights the return for each state of economy with the PROBABILITY that State of the Economy occurs Note use of SUMPRODUCT built-in function
41
(nul)-$99,999,999-$999,999,999-$399,999,999-$459,999,999Note use of C$nn:E$nn to refer to a fixed ROW of Probabilities
42
43
Best Criterion Value$200,000-$99,999,999-$33,306,666$52,000Maximum in column for each decision criterion
44
Best Alternative No.1122Note use of 'MATCH' built-in function to find the location of the Best Alternative in a column.
45
Best AlternativeLarge Plant A1Large Plant A1Small Plant A2Small Plant A2Note 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.
46
Several Best Alternatives?1411<< =WARNINGNote 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
47

48
Table 3 Expected Value of Perfect Information (EVPI)
49
50
State of Economy:
51
OptimisticNeutralPessimisticTotal
52
Best Outcome per State$200,000$0-$99,999,999Maximum in column for State of Economy, from Table 1
53
Probabilities60%40%0%From Table 1
54
Weighted Best Outcome$120,000$0$0$120,000= Best outcome x Probability of the outcome
55
Expected Value WITH Perfect Information (EVwPI)$120,000= 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))$52,000= Best criterion value for EMV Decision Criterion, Table 2
57
Expected Value OF Perfect Information (EVPI)$68,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 could be AVOIDED by ensure that the calculation for eg Pessimistic probability = 1 - SUM(all other probabilities). Implemented in Version 2.3 Aug 5, 2013.
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 could warn of multiple equal decisions. Use the COUNTIF builtin function. Implemented Version 3.0
65
NOTE: The spreadsheet is limited to three States of Economy and five alternative decisions. It is a straightforward task to extend this capability.
66
To EXCLUDE a decision alternative, insert a very negative value in the Table 1 of net returns.
67
68
69
QUALITY ASSURANCE
70
Ad hoc trial and error testing.
71
72
73
MODIFICATION HISTORY
74
Version 3.0 Improved checking to ensure user enters probabilities that sum to 100 per cent.
75
Version 3.0 Check for multiple Best Alternatives added to Table 1.
76
Numbering of tables adjusted to Table 1, 2, 3 etc.
77
78
79
80
81
REFERENCE
82
Barlow, J. (2006). Excel Models for Business and Operations Management. John Wiley & Sons.
83
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.
84
85
Data Validation in Excel. (2012). Retrieved from http://www.youtube.com/watch?v=uQm_CxwcOHw&feature=youtube_gdata_player
86
Excel For Beginners - Part 1: Introduction. (2011). Retrieved from http://www.youtube.com/watch?v=L7dHA_8GzKw&feature=youtube_gdata_player
87
Excel For Beginners - Part 2: Functions. (2011). Retrieved from http://www.youtube.com/watch?v=tWjk4FvYYiM&feature=youtube_gdata_player
88
Excel For Beginners - Part 3: IF Statement. (2011). Retrieved from http://www.youtube.com/watch?v=LEc3Va_ODf0&feature=youtube_gdata_player
89
90
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
91
Google spreadsheets function list. (n.d.). Retrieved August 1, 2013, from https://support.google.com/drive/table/25273?page=table.cs&rd=1
92
How to protect cells in an Excel Spreadsheet. (2013). Retrieved from http://www.youtube.com/watch?v=4f1j0oCGeRk&feature=youtube_gdata_player
93
94
95
CITATION
96
Cite or access the Google Sheets version of this spreadsheet:
97
Mellalieu, P. J. (2013, August 1). 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&usp=sharing
98
99
Download the source spreadsheet from:
100
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