H9 IX fx B D G H 3 4 C Question 1 - The 401K Dilemma - 50 points 2 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 5 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% 10 11 12 13 15 15 17 18 19 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. 20 21 Here's the LP formation for this problem. 23 Decision Variables: 24 M Value of municipal bords cashed in 25. T: Value of US Treasury bonds cashed in 26. St Value of stock cathed in 27 2 Objedive Function Minimize 02 M+101+255 25 10 Subject to MT+5 > 200,000 SM+T OF S.M-T0 12 TMO T- MO M 90,000 MT.50 Minimetas paid. Asume amount Need at least $200.000 Total bonds cashed in can be no more than stocks Treasury bonded in can't be less than munidal honda cihed in I have only SSK innipal bords Nonegativity constraints I HAVE GIVEN YOU THE FORMULATION FOR THIS PROBLEM. NOW, INPUT THIS FORMULATION INTO SOLVER 37 Use the spreadsheet below to input the problem into Excel Solver. DO NOT CREATE YOUR OWN SPREADSHEET you create your own spreadsheet, I won't accept it. It's nearly impossible to decifer 50 different spreadsheets 19 40 me blue calls are your variables that have defined above dy input the formulas you need into the table below Infantinidine CV tha thu Q1 Q2 + Ready Paste B U A Clipboard Font Alignment Office Update To keep up-to-date with security updates, fixes, and improvements, choose H9 x & fx D E G H A B 42 Ive already input the formulas you need into the table below. 44 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 47 Be sure to select 'Simplex Algorithm'. 48 49 Be sure to check the box that all variables are non-negative, 50 51 Opt for ONLY the Sensitivity Analysis Report. 52 53 The optimal solution should automatically appear in the table below in the blue cells), along with all the relevant costs. 55 Another worksheet should automatically be generated that says Sensitivity Analysis: 56 57 The Variables 58 M T 5 54 60 61 62 Need at least 5200,000 63 Total bonds can't be more than 5 54 Moant be more than T Only have $9Okin mundpal bonds 66 67 TOTAL TAXES PAID 0 0 0 0 200 000 0 0 90,000 0 You want to minimne taxes paid 69 70 71 Poble reason you're getting an error 72 73 1. Seinfele solution. You're putting the constraints incorrectly. Check to see if you have and correctly. 74 Chederhof you have the correct cel on the left hand side of an equation and the right hand side. 75 Chued to ensure you are mining 75 77 2. Check to ensure that when you multiply, you on between what you want to mily 78 79 3. Ched to ensure you love sted for the simplex Alpeithe 20 814. you type amber for a right hand life volue, it cannot contain a com 13 Lefthandede of contraints can enly be calle, not form. Alghe hond alde con any be wille with under that you type Q1 02 Ready