Question

Problem 9-07 (Algorithmic) As part of the settlement for a class action lawsuit, Hoxworth Corporation must provide sufficient cash to make the following annual payments (in thousands of dollars) Year 1 23 4 5 6 Payment 190 215 240 285 315 460 The annual payments must be made at the beginning of each year. The judge will approve an amount that, along with earnings on its investment, will cover the annual payments. Investment of the funds will be limited to savings (at 4% annually) and government securities, at prices and rates currently quoted in The Wall Street Journal Hoxworth wants to develop a plan for making the annual payments by investing in the following securities (par value $1000). Funds not invested in these securities will be placed in savings. Security| Current Price Rate(%)Years to Maturity $1055 5.75 $1000 5.125 Assume that interest is paid annually. The plan will be submitted to the judge and, if approved, Hoxworth will be required to payよtrustee the amount that will be required to fund the plan. a. Use linear programming to find the minimum cash settlement necessary to fund the annual payments. Do not round your answers. If required, only round your decision variables to a whole dollar amount. For subtractive or negative numbers use a minus sign even if there is a sign before the blank. (Example: -300) Let F-total funds required to meet the six years of payments G1-units of government security 1 62- units of government security 2 Snvestment in savings at the beginning of year i Note: All decision variables are expressed in thousands of dollars. Min F G1 0675GI05125 05125 | V G2 51 4 0675 G1+ G2 + G2 + G2 + red Current investment requi Investment in government security 1 Investment in government security 2 $ Investment in savings for year 1$ Investment in savings for year 2 Investment in savings for year 3 Investment in savings for year 4 Investment in savings for year 5 Investment in savings for year 6 b, use the shadow price to determine how much more Hoxworth should be willing to pay now to reduce the payment at the beginning of year 6 to $400,000. c. Use the shadow price to determine how much more Hoxworth should be willing to pay to reduce the year 1 payment to $150,000 Hoxworth should be willing to pay anything less than$ d. Suppose that the annual payments are to be made at the end of each year. Reformulate the model to accommodate this change. Do not round your answers. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300) Note: All decision variables are expressed in thousands of dollars. Min G2 + 1) 3) 52 + G1 + G2 5) G2 6) 7) S6+ How much would Hoxworth save if this change could be negotiated? If required, round your answer to a whole dollar amount

Answer 1

a. Payment to be made at the Beginning Of Year (BoY) Algebraic Formulation: Min 1 F s.t. 1 F -1.055 G1 -1 G2 1 S1 190 0.0675 G1+0.05125 G2 1.04 S1 -1 S2215 0.0675 G1+0.05125 G2 1.04 S2 -1 S3-240 1.0675 G10.05125 G2 1.04 S3-1 S4 285 1.05125 G21.04 S4-1 S5 315 10 1.04 S5 -1 S6 460 Solution using Excel Solver: 12 13 LHS 14 G1 G2 S1 S2 S3 S4 S5 S6 RHS Year 1 1.055 190 190 16 Year 2 0.0675 0.05125 1.0400 215 215 17 0.0675 0.05125 1.04001 240 240 Year 3 1.0675 0.05125 1.04001 Year 4 285 285 1.05125 19 Year 5 1.04001 315 315 1.04001 Year 6 460 460 21 Objective 1,484.967 1,484.967 232.394 720.388 329.404 180.1860 0 442.3080 Formula 25 Copy to L15 =SUMPRODUCT($C$23:SK$23,C15:K15) L15:L21 Optimal Solution: 28 29 1,484,967 Current investment required S 232,394 30 Investment in government secu $ 720,388 31 Investment in government secu $329,404 $ 180,186 Investment in savings for year 1 32 Investment in savings for year 2 Investment in savings for year 3 Investment in savings for year 4 $442,308 Investment in savings for year 5 Investment in savings for year 6 Refer sensitivity report below, shadow price of year 6 payment is 0.785511666 Therefore, to reduce the payment at the beginning of year 6 to $400,000 Hoxworth should be willing to pay now $ 0.785511666 (460000-400000) 40 $47,130.7 42 43 C. Refer sensitivity report below, shadow price of year 1 payment is 1 Therefore, to reduce the payment at the beginning of year 1 to $150,000 Hoxworth should be willing to pay now anything less than-$ 1"(190000-150000-$ 40,000 45Ц

Solver Parameters Set Objective: To: O Max OMin OValue Of By Changing Variable Cells: $C$23:$K$23 Subject to the Constraints: 轧$15:SL $20-SN$15:SN$20 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth Help Solve Close

1 d. Payment to be made at the End Of Year (EoY) Algebraic Formulation: Min 1 F 1 F -1.055 G1 - 1 G2 1 S1 0 0.0675 G1+0.05125 G21.04 S1 -1S2 190 0.0675 G1 + 0.05125 G2 + 1.04 S2-1 S3 215 1.0675 G10.05125 G2 1.04 S3 - 1 S4-240 1.05125 G21.04 S4-1 S5 285 1.04 S5-1S6 315 10 .04 S6-1 S7 460 12 13 Solution using Excel Solver: 14 15 LHS RHS G2 16 G1 S1 S2 S3 S4 S5 S6 Year 1 1.055 Year 2 0.0675 0.051251.0400 190 190 18 Year 3 0.0675 0.05125 1.0400 215 215 19 Year 4 .0675 0.05125 1.0400 240 240 20 285 Year 5 1.05125 1.0400 285 21 315 315 Year 6 1.0400 Year 7 Objective 23 1.0400 460 460 1,417.739 24 25 DV: 1,417.739 178.554 963.786 265.578 147.648 0.000 0.000 728.180 442.308 0.000 26 Formula Copy to 28 17-SUMPRODUCT(SC$26:SLS26,C17:L17) M17:M24 29 30 Optimal Solution: 1,417,739 $178,554 $ 963,786 s 265,578 147,648 Current investment required 32 Investment in government security 1 Investment in government security 2 Investment in savings for year 1 35 Investment in savings for year 2 Investment in savings for vear 3 37 Investment in savings for year 4 38 $ 728,180 442,308 Investment in savings for year 5 39 Investment in savings for year 6 40 41 Hoxworth would save if this change could be negotiated 1484967 1417739$ 67,228 42 43

Solver Parameters Set Objective: To: O Max OValue of OMin By Changing Variable Cells: $C$26:$L$26 Subject to the Constraints: SM$17:SM$23 $O$17:$O$23 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close