Linear Programming
Project description
The question:
You are an Independent Financial Advisor providing expert advice to individuals on request. A client has 150,000 which he wishes to invest in income-generating securities and government bonds so as to maximise his annual return. He has selected five possible investments, all of which he considers to have reasonably high yields and stability:
Oil company A stock, paying 9 % annual dividend.
Oil company B stock, paying 7.7% annual dividend.
Electric utility A stock, paying 5% annual dividend.
Electric utility B stock, paying 6.2% annual dividend.
Government bonds, paying 5.5 % annual interest.
Since he has no plans to sell his stock in the near future, he is not concerned with their selling price. Based on the various risk levels involved, he has made the following decisions:
The total investment in oil stocks may not exceed 32,000.
The total investment in electric utilities may not exceed 60,000.
The investment in oil company A may not exceed 28,000.
The total investment in electric utility A may not exceed 35,000.
The total investment in oil stocks may not exceed the total investment in electric utilities.
The investment in oil company A and electric utility A combined may not exceed the investment in Government bonds.
As the clients financial adviser you should advise him of the amount of money he should invest in the various investments in order to maximise his annual return.
1. Formulate the problem clearly defining the variables, the objective function and the constraints.
2.Solve the problem by linear programming using an Excel spreadsheet model, remembering that the Client’s objective is to maximise total annual income. State the optimal investments plan clearly, giving the values of all the problem variables. Include a copy of your spreadsheet, making sure that the layout of the spreadsheet is easy to follow and is carefully annotated. The layout should be your own design.
3. Produce an Excel Sensitivity Report
( I have done questions 1-3 which I shall attach as a word document and excel document, I would just need for the person to check to confirm that I am right and/or make any changes).
What I need help with is the last bit of the work:
4. Give an interpretation of the values in the Variables and Constraints tables of the report. Give three examples to test the sensitivity ranges given in Excel report by changing the original problem values. Each example should consider changing only one factor (i.e., variable or constraint). State why you have chosen the particular factors that you used and include copies of any appropriate Excel output and explain your answers and conclusions.
5.For the final part of your report, in your capacity as an Independent Financial Advisor, you should present a memorandum to your client. Describe your main conclusions to him in simple, non-technical English; i.e. do not use technical terms like variable, objective function or dual price. Dont worry about repeating some or all of the points that you have already made in answer to earlier questions. The aim is to communicate your conclusions clearly to someone who is knowledgeable about the practice of investment planning, but who knows nothing about the subject of linear programming. You may use tables and charts if you wish.
Microsoft Excel 14.4 Sensitivity Report
Worksheet: [Workbook3]Sheet1
Report Created: 19/12/2014 21:44:38
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$14 CONSTRAINTS X1 28000 0 0.09 1E+30 0.013
$C$14 CONSTRAINTS X2 4000 0 0.077 0.013 0.022
$D$14 CONSTRAINTS Y1 0 -0.012 0.05 0.012 1E+30
$E$14 CONSTRAINTS Y2 60000 0 0.062 1E+30 0.007
$F$14 CONSTRAINTS Z1 58000 0 0.055 0.007 0.055
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$G$15 X1 + X2 + Y1 + Y2 + Z1 <= 150000 USED 150000 0.055 0 1E+30 30000
$G$16 X1 + X2 <= 32000 USED 32000 0.022 0 28000 4000
$G$17 Y1 + Y2 <= 60000 USED 60000 0.007 0 30000 28000
$G$18 X1 <= 28000 USED 28000 0.013 0 4000 28000
$G$19 Y1 <= 35000 USED 0 0 0 1E+30 35000
$G$20 X1 + X2 <= Y1 + Y2 USED 32000 0 0 1E+30 28000
$G$21 X1 + Y1 <= Z1 USED 28000 0 0 1E+30 30000
MA5051Project Management Group ZInterim Report Part 2
Linear Programming Formulation
Problem Variables:
Let X1 be the amount invested in the oil company A stock
Let X2 be the amount invested in the oil company B stock
Let Y1 be the amount invested in the electric utility A stock
Let Y2 be the amount invested in the electric utility B stock
Let Z1 be the amount invested in Government Bonds
Constraints:
X1 + X2 + Y1 + Y2 + Z1 = £150000
X1 + X2 = £32000 maximum invested in oil stock
Y1 + Y2 = £60000 maximum invested in electric utilities
X1 = £28000 maximum invested in oil company stock A
Y1 = £35000 maximum invested in electric utility stock A
X1 + X2 = Y1 + Y2 investment in oil stock may not exceed investment in electric utilities
X1 + Y1 = Z1 investment in oil stock A and electric utility stock A combined may not exceed investment in government bonds
X1, X2, Y1, Y2, Z1 = 0
Objective Function:
Maximise total profit: P ? Max P = 0.09X1 + 0.077X2 + 0.05Y1 + 0.062Y2 + 0.055Z1
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂