# 1 BAN-402 Spreadsheet Modeling Project: due Friday, December 24, 2016 Submit BEFORE 11:59pm to course site on Blackboard Deliverables: One Word file: Follow the requirements illustrated in the Assignment Sample. Use your name as the file name One Excel file: Use one worksheet for one scenario. Use your name as the file name Note: Late project will NOT be graded and can only get 50% of the full credits, unless a special permission is granted by the instructor. The Project: A company has $1,000,000 to invest in new R&D projects. The company wants to determine what set of projects should be selected. [Deterministic Scenario] (20 points) The following table summarizes the initial cost and revenue potential for each of the projects. Project Initial Cost Expected Revenue 1 $500,000 $1,500,000 2 $500,000 $1,500,000 3 $500,000 $1,800,000 4 $500,000 $1,800,000 a. Formulate an LP model for this problem with the objective of maximizing total profit b. Implement your model in a spreadsheet and solve it c. What is the optimal solution? 2 [Uncertain Scenario – Maximizing the Mean] (20 points) The potential revenues of all projects are in fact uncertain. The company determines that the revenue for project 1 is following a uniform distribution ranging from $1,200,000 to $2,000,000. The revenue for project 2 is distributed normally, with a mean of $1,500,000 and a standard deviation of $200,000. The revenue for project 3 follows a triangular distribution with a minimum of $1,350,000, maximum of $1,600,000, and it is most likely to be $1,500,000. The revenue for project 4 is distributed normally, with a mean of $1,800,000 and a standard deviation of $800,000. The bank wants to find the optimal solution which takes all these uncertainties into account. d. In another worksheet, modify your model from the deterministic scenario appropriately to incorporate the uncertainties. Find the optimal solution which can maximize the average uncertain profit. Paste screen shoots of your model with formulas, model with solutions, solver setting, simulation setting, and the output distribution of the uncertain profit (Hint: you will have to use Psi distribution functions, Psi statistics functions, and simulation optimization) e. What is the optimal solution that can maximize average uncertain profit? [Uncertain Scenario – Minimizing the Probability of Low Profit] (10 points) The Company also would like to know the optimal solution if the objective is to minimize the probability of having a profit that is less than $1,800,000. f. In another worksheet, modify your model from d and find the optimal solution for this new objective. Paste screen shoots of your model with formulas, solutions, and your solver setting g. What is the optimal solution that can minimize the probability of having a profit that is less than $1,600,000? [Uncertain Scenario – Maximizing the Probability of High Profit] (20 points) h. Repeat f, but with a new objective to maximize the probability of having at least $2,500,000 of profit. Paste screen shoots of your model with formulas, solutions, and your solver setting i. What is the optimal solution that can maximize the probability of having at least $2,600,000 of profit? j. Discuss the optimal solutions you found from all four scenarios. If you are the CEO of the company, which solution would you adopt and why?

1 BAN-402 Spreadsheet Modeling Project: due Friday, December 24, 2016 Submit BEFORE 11:59pm to course site on Blackboard Deliverables: One Word file: Follow the requirements illustrated in the Assignment Sample. Use your name as the file name One Excel file: Use one worksheet for one scenario. Use your name as the file name Note: Late project will NOT be graded and can only get 50% of the full credits, unless a special permission is granted by the instructor. The Project: A company has $1,000,000 to invest in new R&D projects. The company wants to determine what set of projects should be selected. [Deterministic Scenario] (20 points) The following table summarizes the initial cost and revenue potential for each of the projects. Project Initial Cost Expected Revenue 1 $500,000 $1,500,000 2 $500,000 $1,500,000 3 $500,000 $1,800,000 4 $500,000 $1,800,000 a. Formulate an LP model for this problem with the objective of maximizing total profit b. Implement your model in a spreadsheet and solve it c. What is the optimal solution? 2 [Uncertain Scenario – Maximizing the Mean] (20 points) The potential revenues of all projects are in fact uncertain. The company determines that the revenue for project 1 is following a uniform distribution ranging from $1,200,000 to $2,000,000. The revenue for project 2 is distributed normally, with a mean of $1,500,000 and a standard deviation of $200,000. The revenue for project 3 follows a triangular distribution with a minimum of $1,350,000, maximum of $1,600,000, and it is most likely to be $1,500,000. The revenue for project 4 is distributed normally, with a mean of $1,800,000 and a standard deviation of $800,000. The bank wants to find the optimal solution which takes all these uncertainties into account. d. In another worksheet, modify your model from the deterministic scenario appropriately to incorporate the uncertainties. Find the optimal solution which can maximize the average uncertain profit. Paste screen shoots of your model with formulas, model with solutions, solver setting, simulation setting, and the output distribution of the uncertain profit (Hint: you will have to use Psi distribution functions, Psi statistics functions, and simulation optimization) e. What is the optimal solution that can maximize average uncertain profit? [Uncertain Scenario – Minimizing the Probability of Low Profit] (10 points) The Company also would like to know the optimal solution if the objective is to minimize the probability of having a profit that is less than $1,800,000. f. In another worksheet, modify your model from d and find the optimal solution for this new objective. Paste screen shoots of your model with formulas, solutions, and your solver setting g. What is the optimal solution that can minimize the probability of having a profit that is less than $1,600,000? [Uncertain Scenario – Maximizing the Probability of High Profit] (20 points) h. Repeat f, but with a new objective to maximize the probability of having at least $2,500,000 of profit. Paste screen shoots of your model with formulas, solutions, and your solver setting i. What is the optimal solution that can maximize the probability of having at least $2,600,000 of profit? j. Discuss the optimal solutions you found from all four scenarios. If you are the CEO of the company, which solution would you adopt and why?