Portfolio Manager Stocks 0.1 Profolio Profit Percentage: Resources: Objective Maximized Profits Bonds 0.04 CD 0.01 1 0.06 --1 ------ 1 -0.02 --1 ---1 1 --0.01 --1 -1 -- 1 ------1 1 -- Investments Total Investments VaR percentage on Stocks VaR percentage on Real Estate VaR percentage on Bonds Stocks minimum holding Real Estate minimum holding Bond minimum holding CD minimum holding CD & Bonds for liquidity Liquidity constraint on Real Estate holding Decision Variables Stocks Real Estate Bonds CD Real Estate 0.07 416666.67 300000.00 233333.33 50000.00 72500 $1,000,000.00 $25,000.00 $6,000.00 $2,333.33 $416,666.67 $300,000.00 $233,333.33 $50,000.00 $283,333.33 $300,000.00 Resources Constraint Available <= <= <= <= >= >= >= >= >= <= $1,000,000.00 $25,000.00 $15,000.00 $2,500.00 $50,000.00 $50,000.00 $50,000.00 $50,000.00 $200,000.00 $300,000.00 Left over 0 0 9000 166.666666667 -366666.66667 -250000 -183333.33333 0 -83333.333333 0 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Investment_portfolio_project.xlsx]Sheet1 Report Created: 8/14/2014 3:25:33 PM Variable Cells Cell $C$18 $C$19 $C$20 $C$21 Name Stocks Real Estate Bonds CD Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 416666.666667 0 0.1 1.00000E+030 0.06 300000 0 0.07 1.00000E+030 0.03 233333.333333 0 0.04 0.03 0.03 50000 0 0.01 0.03 1.00000E+030 Constraints Cell $H$10 $H$11 $H$12 $H$13 $H$14 $H$15 $H$6 $H$7 $H$8 $H$9 Name -- Investments -- Investments -- Investments -- Investments -- Investments -- Investments Total Investments Investments -- Investments -- Investments -- Investments Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease $416,666.67 0 50000 366666.66667 1.00000E+030 $300,000.00 0 50000 250000 1.00000E+030 $233,333.33 0 50000 183333.33333 1.00000E+030 $50,000.00 -0.03 50000 183333.33333 16666.666667 $283,333.33 0 200000 83333.333333 1.00000E+030 $300,000.00 0.03 300000 83333.333333 16666.666667 $1,000,000.00 0.04 1000000 16666.666667 83333.333333 $25,000.00 1 25000 5000 1000 $6,000.00 0 15000 1.00000E+030 9000 $2,333.33 0 2500 1.00000E+030 166.66666667 Portfolio Manager Profolio Profit Percentage: Resources: Total Investments VaR percentage on Stocks VaR percentage on Real Estate VaR percentage on Bonds Stocks minimum holding Real Estate minimum holding Bond minimum holding CD minimum holding CD & Bonds for liquidity Liquidity constraint on Real Estate holding Decision Variables Stocks Real Estate Bonds CD Objective Maximized Profits Stocks 0.1 Real Estate 0.07 Bonds 0.04 CD 0.01 Investments 1 0.06 --1 ------ 1 -0.02 --1 ---1 1 --0.01 --1 -1 -- 1 ------1 1 -- Resources Constraint Available <= <= <= <= >= >= >= >= >= <= $1,000,000.00 $25,000.00 $15,000.00 $2,500.00 $50,000.00 $50,000.00 $50,000.00 $50,000.00 $200,000.00 $300,000.00 Left over Microsoft Excel 14.0 Sensitivity Report Worksheet: [Investment_portfolio_project.xlsx]Sheet1 Report Created: 8/14/2014 3:25:33 PM Variable Cells Cell $C$18 $C$19 $C$20 $C$21 Name Stocks Real Estate Bonds CD Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 416666.666667 0 0.1 1.00000E+030 0.06 300000 0 0.07 1.00000E+030 0.03 233333.333333 0 0.04 0.03 0.03 50000 0 0.01 0.03 1.00000E+030 Constraints Cell $H$10 $H$11 $H$12 $H$13 $H$14 $H$15 $H$6 $H$7 $H$8 $H$9 Name -- Investments -- Investments -- Investments -- Investments -- Investments -- Investments Total Investments Investments -- Investments -- Investments -- Investments Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease $416,666.67 0 50000 366666.66667 1.00000E+030 $300,000.00 0 50000 250000 1.00000E+030 $233,333.33 0 50000 183333.33333 1.00000E+030 $50,000.00 -0.03 50000 183333.33333 16666.666667 $283,333.33 0 200000 83333.333333 1.00000E+030 $300,000.00 0.03 300000 83333.333333 16666.666667 $1,000,000.00 0.04 1000000 16666.666667 83333.333333 $25,000.00 1 25000 5000 1000 $6,000.00 0 15000 1.00000E+030 9000 $2,333.33 0 2500 1.00000E+030 166.66666667 Portfolio Manager Profolio Profit Percentage: Resources: Total Investments VaR percentage on Stocks VaR percentage on Real Estate VaR percentage on Bonds Stocks minimum holding Real Estate minimum holding Bond minimum holding CD minimum holding CD & Bonds for liquidity Liquidity constraint on Real Estate holding Decision Variables Stocks Real Estate Bonds CD Objective Maximized Profits Stocks 0.1 Real Estate 0.07 Bonds 0.04 CD 0.01 Investments 1 0.06 --1 ------ 1 -0.02 --1 ---1 1 --0.01 --1 -1 -- 1 ------1 1 -- Resources Constraint Available <= <= <= <= >= >= >= >= >= <= $1,500,000.00 $25,000.00 $15,000.00 $2,500.00 $50,000.00 $50,000.00 $50,000.00 $50,000.00 $200,000.00 $300,000.00 Left over Portfolio Manager Profolio Profit Percentage: Resources: Total Investments VaR percentage on Stocks VaR percentage on Real Estate VaR percentage on Bonds Stocks minimum holding Real Estate minimum holding Bond minimum holding CD minimum holding CD & Bonds for liquidity Liquidity constraint on Real Estate holding Decision Variables Stocks Real Estate Bonds CD Objective Maximized Profits Stocks 0.06 Real Estate 0.07 Bonds 0.04 CD 0.01 Investments 1 0.06 --1 ------ 1 -0.02 --1 ---1 1 --0.01 --1 -1 -- 1 ------1 1 -- Resources Constraint Available <= <= <= <= >= >= >= >= >= <= $1,000,000.00 $25,000.00 $15,000.00 $2,500.00 $50,000.00 $50,000.00 $50,000.00 $50,000.00 $200,000.00 $300,000.00 Left over Investment Type Returns Investment Amount $ Maximum Return $ VaR Stocks Real Estate 10% 7% 416,666.67 $ 300,000.00 $ 72,500.00 6% 2% Bonds CD 4% 1% 233,333.33 $ 50,000.00 1% Constraints VaR Stock VaR RealEstate VaR Bonds Var CD Min Investment Stock Min Investment RealEstate Min Investment Bonds Min Investment CD Min Investment CD + Bond Real Estate Holding Total Investment $ 25,000.00 $ 6,000.00 $ 2,333.33 $ $ 416,666.67 $ 300,000.00 $ 233,333.33 $ 50,000.00 $ 283,333.33 $ 300,000.00 $ 1,000,000.00 <= <= <= <= >= >= >= >= >= <= <= $ 25,000.00 $ 15,000.00 $ 2,500.00 $ $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 200,000.00 $ 300,000.00 $ 1,000,000.00 0% Investment Type Returns Investment Amount $ Maximum Return $ VaR Stocks Real Estate 10% 7% 416,666.67 $ 450,000.00 $ 87,000.00 6% 2% Bonds CD 4% 250,000.00 $ 1% Constraints VaR Stock VaR RealEstate VaR Bonds Var CD Min Investment Stock Min Investment RealEstate Min Investment Bonds Min Investment CD Min Investment CD + Bond Real Estate Holding Total Investment $ 25,000.00 $ 9,000.00 $ 2,500.00 $ $ 416,666.67 $ 450,000.00 $ 250,000.00 $ 383,333.33 $ 633,333.33 $ 450,000.00 $ 1,500,000.00 <= <= <= <= >= >= >= >= >= <= <= $ 25,000.00 $ 15,000.00 $ 2,500.00 $ $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 200,000.00 $ 450,000.00 $ 1,500,000.00 1% 383,333.33 0% Investment Type Returns Investment Amount $ Maximum Return $ VaR Stocks Real Estate 6% 7% 416,666.67 $ 300,000.00 $ 55,833.33 6% 2% Bonds CD 4% 233,333.33 $ 1% 50,000.00 1% Constraints VaR Stock VaR RealEstate VaR Bonds Var CD Min Investment Stock Min Investment RealEstate Min Investment Bonds Min Investment CD Min Investment CD + Bond Real Estate Holding Total Investment $ 25,000.00 $ 6,000.00 $ 2,333.33 $ $ 416,666.67 $ 300,000.00 $ 233,333.33 $ 50,000.00 $ 283,333.33 $ 300,000.00 $ 1,000,000.00 <= <= <= <= >= >= >= >= >= <= <= $ 25,000.00 $ 15,000.00 $ 2,500.00 $ $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 50,000.00 $ 200,000.00 $ 300,000.00 $ 1,000,000.00 0% Week 8 Project You are a portfolio manager for the XYZ investment fund. The objective for the fund is to maximize your portfolio returns from the investments on four alternatives. The investments include (1) stocks, (2) real estate, (3) bonds, and (4) certificate of deposit (CD). Your total investment portfolio is $1,000,000. Investment Returns Based on the returns from the past five years, you concluded that the investment annual returns on stocks are 10%, on real estates are 7% on bonds are 4% and on CD is 1%. Risk Constraints However, you also have to analyze the risks associate with each investment category. A wildly used risk measurement parameter is called Value at Risk (VaR). (Note: VaR measures the risk of loss on a specific portfolio of financial assets.) For example, given a million dollar stock investment, if a portfolio of stocks has a one-day 4% VaR, there is a 5% probability that the stock portfolio will fall in value by more than 1,000,000 * 0.004 = $4,000 over a one day period. In the portfolio, the VaR for stock investments is 6%. Similarly, the VaR for real estate investment is 2% and the VaR for bond investment is 1% and the VaR for investment in CD is 0%. To manage the portfolio, you decided that at 5% probability, your VaR for stocks cannot exceed $25,000, VaR for real estate cannot exceed $15,000, VaR for bonds cannot exceed $2,500 and the VaR for CD investment is $0. Diversification and Liquidity Constraints As a diversified investment portfolio, you also decided that each investment category must hold at least $50,000 of the total investment assets. In addition, you must hold combined CD and bond investment no less than $200,000 in order to meet liquidity requirement. The total amount of real estate holding shall not exceed 30% of the portfolio assets. A. As a portfolio manager, please formulate and solve the investment portfolio problem using linear programming technique. What are the amounts invest in (1) stocks, (2) real estate, (3) bonds and (4) CD? B. If $500,000 additional investments are available to you in your portfolio, how would you invest the capital? C. Would you maintain the portfolio investment if stock yields lowered to 6%? How would you re-distribute your investment portfolio