urgent help
2QA3 / 3DA3 Midterm exam (C01)
Jul 14 / 2021
Case study (40 marks total)
An investor is interested to purchase stocks in Canada. The total investment cannot exceed $100,000 and the investor does not want to spend more than $30,000 on any individual stock. The investor also does not want to invest more than $40,000 in low rating stocks (rating of 3 or 4). The investor can purchase a fractional quantity of any of the stocks.
What portfolio the investor should buy in order to maximize his total return?
|
Stock |
Rate of return |
Rating |
|
1 |
6.5% |
2-Very Good |
|
2 |
8% |
3-Good |
|
3 |
5.9% |
1-Excellent |
|
4 |
9.2% |
4-Fair |
Tasks
1. Develop a Linear Programming model to find the optimal investment in each stock
a. Clearly write down all components of the model (incl. the objective function, constraints and decision variables, etc.)
2. Solve the LP model using Excel Solver
a. Include a screenshot of your Solver parameters in the same Excel worksheet where you solved the problem
3. Run the Solver Sensitivity report. Suppose the rate of return of Stock 1 changes from 6.5% to 7.0%. Will the solution provided by Solver remain optimal? Write 1-3 sentences in the same Excel worksheet where you solved the problem
4. Upload your completed Excel file back to the Avenue Assignments section before the exam’s end