Question 1 - The 401K Dilemma - 50 points This fall, I plan to buy a new house. Real estate values have skyrocketed. The house will cost about $500,000 but I want my mortgage loan to be no more than $300,000. Thus, I will cash in some of my 401K assets to make up the difference. But, cashing in some of my 401K means the IRS will be in hot pursuit. I want to cash in at least $200,000 of my 401K but pay as little tax as possible. My 401K consists of 1. Municipal bonds that are taxed at 2% 2. US EE Series Treasury bonds that are taxed at 10% 3. Stocks that are taxed at 25%. The total amount of municipal and US treasury bonds cashed in can be no more than the stocks I cash in. I must cash in more US Treasury bonds than municipal bonds. I have only $90,000 in municipal bonds. Here's the LP formulation for this problem. Decision Variables: M: Value of municipal bonds cashed in T: Valuet of US Treasury bonds cashed in S: Value of stocks cashed in Objective Function: Minimize Z= .02 M +.10 T +.25 S Minimize taxes paid. Assume full amount is taxable. Subject to: M+T+S >= 200,000 Need at least $200,000 S>=M +T or S-M-T>= 0 Total bonds cashed in can be no more than stocks T>= M or T- M >= 0 Treasury bonds cashed in can't be less than municipal bonds cashed in M = 0 Non-negativity constraints I HAVE GIVEN YOU THE FORMULATION FOR THIS PROBLEM. NOW, INPUT THIS FORMULATION INTO SOLVER. Use the spreadsheet below to input the problem into Excel Solver. DO NOT CREATE YOUR OWN SPREADSHEET. If you create your own spreadsheet, I won't accept it. It's nearly impossible to decifer 50 different spreadsheets. The blue cells are your 3 variables that I have defined above. I've already input the formulas you need into the table below. Look at the formulas and you'll see what cells you need to input into SOLVER for the right-hand-side and left-hand-side for each of the 4 constraints. Be sure to select 'Simplex Algorithm'. Be sure to check the box that all variables are non-negative. Opt for ONLY the Sensitivity Analysis Report. The optimal solution should automatically appear in the table below in the blue cells), along with all the relevant costs. Another worksheet should automatically be generated that says 'Sensitivity Analysis'. The Variables M T S Need at least $200,000 0 200,000 Total bonds can't be more than S 0 0 M can't be more than T 0 0 Only have $90K in muncipal bonds 0 90,000 TOTAL TAXES PAID 0 = 200,000 Need at least $200,000 S>=M +T or S-M-T>= 0 Total bonds cashed in can be no more than stocks T>= M or T- M >= 0 Treasury bonds cashed in can't be less than municipal bonds cashed in M = 0 Non-negativity constraints I HAVE GIVEN YOU THE FORMULATION FOR THIS PROBLEM. NOW, INPUT THIS FORMULATION INTO SOLVER. Use the spreadsheet below to input the problem into Excel Solver. DO NOT CREATE YOUR OWN SPREADSHEET. If you create your own spreadsheet, I won't accept it. It's nearly impossible to decifer 50 different spreadsheets. The blue cells are your 3 variables that I have defined above. I've already input the formulas you need into the table below. Look at the formulas and you'll see what cells you need to input into SOLVER for the right-hand-side and left-hand-side for each of the 4 constraints. Be sure to select 'Simplex Algorithm'. Be sure to check the box that all variables are non-negative. Opt for ONLY the Sensitivity Analysis Report. The optimal solution should automatically appear in the table below in the blue cells), along with all the relevant costs. Another worksheet should automatically be generated that says 'Sensitivity Analysis'. The Variables M T S Need at least $200,000 0 200,000 Total bonds can't be more than S 0 0 M can't be more than T 0 0 Only have $90K in muncipal bonds 0 90,000 TOTAL TAXES PAID 0
