Question: Please answer in an excel document and show the formulas as well please!! Question 1 (33 pts): A winner of the Texas Lotto has decided

Please answer in an excel document and show the formulas as well please!!Please answer in an excel document and show the

Question 1 (33 pts): A winner of the Texas Lotto has decided to invest $50,000 per year in the stock market. Under consideration are stocks for a petrochemical firm and a public utility. Although a long- range goal is to get the highest possible return, some consideration is given to the risk involved with the stocks. A risk index on a scale of 1-10 (with 10 being the most risky) is assigned to each of the two stocks. The total risk of the portfolio is found by multiplying the risk of each stock by the dollars invested in the stock. The following table provides a summary of the return and risk: Stock Petrochemical Utility Estimated Return 12% 6% Risk Index 9 4 The investor would like to maximize the return on the investment (ROI), but the average risk index of the investment should not be higher than 6 (which means that the total risk of the portfolio should not be higher than 6*$50,000 = 300,000). a) (4 pts) Formulate this problem mathematically. Use P and U to represent the dollar amounts invested in the petrochemical and utility stocks, respectively. Write down or type the objective function and all constraints clearly. (Don't forget the nonnegativity constraints when applicable.) b) (8 pts) Set up this problem in Excel and use Solver to find the optimal value of P and U, i.e., the dollar amounts to be invested in the petrochemical and utility stocks, respectively, to maximize the return on investment. c) (4 pts) Based on the Solver result, how much should be invested in the petrochemical and utility stocks to maximize ROI? What is the ROI? What is the average risk index? d) Suppose that the lottery winner has changed his attitude about the investment and wishes to give greater emphasis to the risk of the investment. Now the lottery winner wishes to minimize the total risk of the investment as long as a return of at least 8% is generated. i. (2 pts) Is this a maximization or minimization problem? ISOM 201 Homework 10 (Chapter 6) ii. (4 pts) Formulate this problem mathematically. Write down or type the objective function and all constraints clearly. (Hint: the right-hand-side (RHS) of the ROI constraint is $50,000*0.08 = $4,000, which is the gain of investing $50,000 with a return rate of 8%.) (8 pts) Set up this problem in Excel and use Solver to solve it. (3 pts) Based on the Solver result, how much should be invested in each stock? What is the average risk for this investment? iii. iv

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!