Task 1 The following are instructions for creating financial decision analysis models in Excel This section will introduce how to create financial decision analysis models. 1. Open the assignment Excel workbook

to the worksheet "Task 1_DT 3SN Intro" 2. Read the "Instructions." The instructions provide you an overview of the components required in the task. 3. Review the Problem Statement and Table provided in the "Variables" section. 4. Complete the following steps Model formulation steps in the "Model" section. Model Formulation Steps 5. The Data Table 1 a. Define the decision variables i. These are the cells in which we will add decision variable information specific to individual scenarios. Note: the orange cells define the location of user input. ii. Criterion of Realism 1. The criterion of realism is an attempt to make a tradeoff between the complete risk indifferences of the Maximax rule and the total risk aversion of the Maximin rule. 2. The alpha is a decimal number from 0 to 1. Values close to 1 place an emphasis on risky outcomes. Values close to 0 place an emphasis on low outcomes. 3. For the example, the alpha=0.8 iii. Column headers describing the states of nature. 1. For the example, the states of nature have been provided: strong market, fair market, and poor market. iv. Probabilities assessed for each state of nature. 1. Individual probabilities are decimal numbers ranging from 0 to 1; However, the sum of all probabilities must equal 1. 2. When a probability value has numerous decimal places (e.g., 0.333 or 1/3), the values should be entered as a fraction (e.g., -1/3) to ensure the probabilities sum to 1. v. Probability total. 1. The probability total is a ready check to ensure the probabilities sum to 1 (100%). 2. Excel Formula: =SUM(probabilities) vi. Row titles describing the alternatives. 1. For the example, the alternatives have been provided: Large, Medium, Small, and No Facility. vii. Estimated profits (or Costs) 1. The estimated profits are provided in Table 1 for the various alternative / state of nature combinations./nData Table Format 5.a.vi Data Table Criterion of Realism (Hurwicz Alpha] Profit Probability Large Medium Small No facility [Do nothing option) 6. Alternatives Table c. Maximin d. Maximax f. Maximum row a. Alternative titles are linked via cell reference to the Data Table titles. b. EMV Alternatives Table Format Alternatives Table 0.3 i. Excel Formula: =SUMPRODUCT (Probability cells, Respective Alternative row cells) Alternative Large Medium Small No facility (Do nothing option) Maximum 5.a.ii.3 $ 550,000.00 $ 300,000.00 $ 200,000.00 i. Excel Formula: =MIN(Respective Alternative row cells) Expected Monetary 7. Expected Value Table Strong Market Faiz Market Poor Market Probability Total 0.3333 i. Excel Formula: =MAX(Respective Alternative row cells) e. Weighted Average i. Excel Formula: =Alpha*Maximax + (1-Alpha)*Maximin 5.a.iv 5.a.vii i. For all columns, the Excel Formula: =MAX(column cells) 5.a.iii Expected Value Table Format Expected Value Table Best Value for Column Condition Expected value with perfect information Best expected monetary value Expected value of perfect information 5.a.v Pessimistic Optimistic Hurwicz Criterion Value EMV Maximin Marlmas Weighted Average $ 116,666.67 $(310,000.00)| $ 550,000.00 $ 378,000.00 6b 6c 6d бе $ 378.000.00 a. Best value for column condition i. Excel Formula: =MAX(of all expected profits in the Data Table for given state of nature) 7d b. Expected value with perfect information i. Excel Formula: =SUMPRODUCT (Probability cells, Best value for column condition cells) c. Best expected monetary value i. Excel Formula: =Cell for EMV Maximum d. $ 550,000.00 $ 129,000.00 $ $ 226,333.33 116,666.67 7b $ 109,666.67 7c 6f Expected value of perfect information i. Excel Formula: =Expected value with perfect information - Best expected monetary value 7a/n8. Regret (Opportunity Loss) Table a. Best value for column condition i. Excel Formula: =MAX(of all expected profits in the Data Table for given state of nature) b. For each row, Alternative (Large, Medium, Small, and Do Nothing) i. Excel Formula: =Best value for column condition - Estimated profit for alternative (e.g., Large) / state of nature combination (e.g., strong market) c. d. Expected opportunity loss Repeat for each state of nature. i. First row (Best value for column condition) has no values. ii. Excel Formula: =SUMPRODUCT(Probability cells in Data Table, Cells for each Alternative row (e.g., Large) in the Regret Table) e. Minimax Regret i. First row (Best value for column condition) has no values. ii. Excel Formula: =MAX(Cells for each Alternative row (e.g., Large) in the Regret Table) f. Minimum i. Excel Formula: =MIN(Regret column, do not include first row) Regret (Opportunity Loss) Table Format 8a Regret (Opportunity Loss) Table Best Value for Column Condition Large Medium Small No facility (Do nothing option) 8b $ 550,000.00 $ $ 250,000.00 $ 350,000.00 $ 550,000.00 9. Interpreting the results 8c Minimum Expected Opportunity Loss $ Minimax Regret 109,666.67 $ 310,000.00 8d 8e $ 250,000.00 8f a. The results of the Expected Value Table are reported as presented. b. The results for EMV, Maximin, Maximax, Weighted Average, Expected Opportunity Loss and Minimax Regret are reported in the dollar amounts of the corresponding highlighted cell. In addition, the respective alternative is mentioned. c. For example, "The expected monetary value is $116,666.67 when selecting the large facility." d. The corresponding alternative is found by matching the value in the highlighted cell to the corresponding row / alternative. Result Statements 10. Answer the question(s) in this section. a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height can be used to ensure the entire answer is displayed. 11. Task 1 is complete.