In order to run this model, you must have your Excel's "solver add-in" installed to your computer. To install the "solver add-in", 1. From the menu, choose "TOOLS", 2. Choose, "ADD-INS", 3. In the "Add-Ins Available" list, have your "SOLVER ADD-IN" checked.
If you have not chosen the full installment option when you had been installing your Microsoft Office, you might need your original Microsoft Office disks during this configuration.
METIN KILIC
Investment Optimization
Risk & Return Optimization in Project Selection under Budget Constraint
Project 1 2 3 4 5 6 7 8
Present Value of the Revenues (PV's) $675,000 $1,050,000 $360,000 $720,000 $1,000,000 $90,000 $630,000 $225,000
Present value of the Investments $512,500 $780,000 $250,000 $600,000 $800,000 $80,000 $525,000 $150,000 Budget Invested Surplus Total Profit Total Risk Return over Risk
Expected Profit Standard Deviation (NPV's) of CF's $162,500 $75,000 $270,000 $115,000 $0 $250,000 $0 $120,000 $0 $100,000 $0 $20,000 $105,000 $75,000 $75,000 $15,000 $2,000,000 $1,967,500 $32,500 $612,500 $232,707 2.63
Decisions 1 1 0 0 0 0 1 1
- Do not change the cells with italic characters. There are formulas… - If you have less than 8 project to optimize, simply clear the unnecessary raws in the table. - Do not forget to fill in the correlation matrix...
The aim is to maximize the profit over risk ratio of the overall project portfolio subject to the budget constraint. After running the model, projects with decision-1 must be undertaken, and projects with decision-0 must be omitted. (See Column-H.)
Page 3
Investment Optimization
0-1 0-1 0-1 0-1 0-1 0-1 0-1 0-1
Page 4
FILL IN THE CO
PROJECTS 1 2 3 4 5 6 7 8 1 1 0.7 0.6 0.48 0.38 0.9 0.4 0.05 2 0.7 1 0.4 0.23 0.78 0.85 0.5 0.9
FILL IN THE CORRELATION MATRIX
3 0.6 0.4 1 0.57 0.32 0.54 -0.23 0.54 4 0.48 0.23 0.57 1 -0.24 0.65 0.45 0.46 5 0.38 0.78 0.32 -0.24 1 0.5 0.64 0.8 6 0.9 0.85 0.54 0.65 0.5 1 0.78 -0.05
7 0.4 0.5 -0.23 0.45 0.64 0.78 1 0.46
8 0.05 0.9 0.54 0.46 0.8 -0.05 0.46 1