Hawaii Pacific MS 6000 MS6000 Final Exam DETAILS AND INSTRUCTIONS: The final exam consists of 3 sections, A, B and C.
MS6000 Final ExamDETAILS AND INSTRUCTIONS:The final exam consists of 3 sections, A, B and C.Questions 1 – 10 in section A are worth 3 points each, for a total of 30 points.Sections B and C contain problems you need to model in excel and using Solver (Section B) and/ or Macros (Section C). In total,they are each worth 35 points.Please follow these instructions carefully: Your final exam submission should consist of 1 single Excel document. The first tab shouldcontain your answers to all questions in the final exam, where one column corresponds to the question # and the column to the right of itcontains your answer. The next two tabs should contain your models for sections B and C, respectively. Your Excel file needs to benamed with your name, the course # and the words ‘final exam’ as: lastname_firstname_MS6000_final exam.xlsx. The file needs to beemailed to me as an attachment by 11:59pm May 07, 2016 or you will not receive credit.Good Luck!SECTION A: Multiple Choice and True/FalseUse the below decision tree illustrating the R&D process for a new drug to answer questions 1 and 2:Provided Dollar amounts in the decision tree are in millions.1) Which of the following is a decision node?A) whether or not clinical trials are successfulC) whether the market is large, medium, or smallB) whether or not to seek FDA approvalD) whether or not FDA approved12) What is the probability that the drug reaches to a large market?A) 0.054B) 0.700C) 0.108D) 0.0183) Which of the following best defines payback period?A) It is the number of time periods before the net expenditure exceeds the amount of the initial investment.B) It is the number of time periods before the amount of the initial investment exceeds the cash inflows of aproposed project.C) It is the number of time periods before the cash inflows of a proposed project exceed the amount of theinitial investment.D) It is the number of time periods before the cash inflows of a proposed project equal the amount of theinitial investment.Use the below payoff table with four mortgage options to answer question 4:4) Which of the following decisions has the best average payoff?A) 1-year ARMB) 30-year fixedC) 3-year ARMD) 5-year ARM5) In linear optimization models, an objective function is the.A) unknown value that the model seeks to determineB) limitation or requirement that decision variables must satisfyC) set of values that satisfies all the constraintsD) quantity that we seek to minimize or maximize6) An optimal solution is.A) the limitation or requirement that decision variables must satisfyB) also known as the constraint functionC) the quantity that we seek to minimize or maximizeD) any set of decision variable values that maximizes or minimizes the objective function7) Theis the difference between the right- and left-hand sides of a constraint.A) shadow priceB) slackC) optimal solution2D) allowable increase8) Integer linear optimization models differ from nonlinear optimization models in that integer linear optimizationmodels.A) contain terms that cannot be written as constant times a variableB) contain some or all variables that are restricted to being whole numbersC) use only equalities to describe the constraintsD) use only inequalities to describe the constraints9) What is Monte Carlo simulation?A) It is a ratio of the departure of an estimated parameter from its notional value and its standard error.B) It is a measure of the correlation between two variables X and Y that falls exclusively between the values+1 and -1.C) It is the process of generating random values for uncertain inputs in a model, computing the outputvariables of interest, and repeating this process for many trials in order to understand the distribution ofthe output results.D) It is an approach for developing a comprehensive understanding and awareness of the risk associatedwith a particular variable of interest.10) Risk analysis seeks to examine the impacts of uncertainty in the estimates and their potential interaction withone another on the output variable of interest. True or False?3SECTION B: Linear Optimization ModelingProblem: Burger Office Equipment produces two types of desks: standard and deluxe. Deluxe desks haveoak tops and more expensive hardware and require additional time for finishing and polishing. Standarddesks require 80 sq. ft. of pine and 10 hours of labor, while deluxe desks require 60 sq. ft. of pine, 18 sq.ft. of oak, and 16 hours of labor. For the next week, the company has 5,000 sq. ft. of pine, 750 sq. ft. ofoak, and 400 hours of labor available. Standard desks net a profit of $150, while deluxe desks net a profitof $320. All desks can be sold.1) Develop a linear mathematical optimization model to determine how many of each desk thecompany should make next week to maximize profit contribution.2) Implement your model on a spreadsheet and find an optimal solution using Solver.3) Explain the reduced cost associated with standard desks.4) What constraints are binding?5) If 25% of the oak is determined to be cosmetically defective, how will the optimal solution beaffected?6) The shop supervisor has suggested that his workers be allowed to work an additional 50 hours atan overtime premium of $12/hr â is this a good idea? Why or why not?SECTION C: Monte Carlo SimulationProblem: Suppose an employee starts working after completing her MBA at age 30 at a starting salary of$50,000. She expects an annual salary increase to be at minimum 1%, at maximum 5%, with a uniformdistribution. Her retirement plan requires that she contribute 8% of her salary, and her employer matchesthat by adding an additional 35% of her contribution. She anticipates an annual return on her retirementportfolio (i.e., return on investment) to be a normal distribution with a mean of 4% and standard deviationof 3.5%. She plans to retire at age 60. Create a spreadsheet model to forecast her average return oninvestment (i.e., retirement account balance) when she retires at age 60 based on 5,000 simulation trials.1) What is the expected average balance of her retirement account when she retires at age 60?2) What is the probability that her ending retirement balance at age 60 will be over $450k?Generate a histogram or density chart that shows this.SECTION D: Extra Credit- Monte Carlo Simulation with Linear Optimization (+15 points) â Saveyour workbook as macro enabled.Problem: You want to optimize the quantity of product shipped to your 11 retail stores from your fourdistribution centers to minimize the cost. However, you do not know what demand will be at your retailstores. You have some idea of expected demand (mean) and the dispersion (standard deviation) and1) Want to estimate what your maximum expected costs will be 70% of the time?2) You want to know the probability of costs being no more than $50,000?4