A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | Author | Peter Mellalieu | ||||||||||||||||
4 | Creation date | 28/07/2013 | ||||||||||||||||
5 | Modification date | 08/08/2013 | ||||||||||||||||
6 | Version | 3.1 | TEST 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 | Alternative | Optimistic | Neutral | Pessimistic | ||||||||||||||
23 | Large Plant A1 | $200,000 | -$180,000 | -$99,999,999 | Note 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 State | 60.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 | Alternative | MaxiMax | MaxiMin | Equal Likelihood | Expected 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,000 | MaxiMax 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,000 | MaxiMin finds the worst case (MINIMUM) that occurs in the ROW for each Decision Alternative | ||||||||||||
39 | Do Nothing | $0 | -$99,999,999 | -$33,333,333 | $0 | Equal 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,999 | Expected 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,999 | Note 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,000 | Maximum in column for each decision criterion | ||||||||||||
44 | Best Alternative No. | 1 | 1 | 2 | 2 | Note use of 'MATCH' built-in function to find the location of the Best Alternative in a column. | ||||||||||||
45 | Best Alternative | Large Plant A1 | Large Plant A1 | Small Plant A2 | Small Plant A2 | 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. | ||||||||||||
46 | Several Best Alternatives? | 1 | 4 | 1 | 1 | << =WARNING | 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 | |||||||||||
47 | ||||||||||||||||||
48 | Table 3 Expected Value of Perfect Information (EVPI) | |||||||||||||||||
49 | ||||||||||||||||||
50 | State of Economy: | |||||||||||||||||
51 | Optimistic | Neutral | Pessimistic | Total | ||||||||||||||
52 | Best Outcome per State | $200,000 | $0 | -$99,999,999 | Maximum in column for State of Economy, from Table 1 | |||||||||||||
53 | Probabilities | 60% | 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,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 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 |