Question

on must provide suf- 7. As part of the settlement for a class action lawsuit, Hoxworth Corporation must ficient cash to make the following annual payments (in thousands of dollars): 6 Year 1 2 Payment | 190 215 3 240 4 285 5 315 460 judge will approve an The annual payments must be made at the beginning of each year. The judge will apr amount that, along with earnings on its investment, will cover the annual payments. I. ment of the funds will be limited to savings (at 4% annually) and government securities 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 Years to Maturity Current Price $1055 $1000 Rate (%) 6.750 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 a 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. b. Use the dual value 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 dual value to determine how much more Hoxworth should be willing to pay reduce the year 1 payment to $150,000. d. Suppose that the annual payments are to be made at the end of each year. Refom the model to accommodate this change. How much would Hoxworth save if the could be negotiated?

Answer 1

ANSWER

As per the given question.

| G | H | I | J | K | ι Μ Ν Ι ο ο WN 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 S2 = 215 0.0675 G1 + 0.05125 G2 + 1.04 S2 - 1 S3 = 240 1.0675 G1 + 0.05125 G2 + 1.04 S3 - 1 S4 = 285 1.05125 G2 + 1.04 S4 - 1 S5 = 315 1.04 S5 - 1 S6 = 460 00 12 Solution using Excel Solver: S1 S2 S3 S4 S5 S6 14 15 / 1 16 G1 -1.055 0.0675 0.0675 1.0675 1.0400 G2 -1 0.05125 0.05125 0.05125 1.05125 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Objective -1 1.0400 17 RHS 190 215 240 285 18 LHS 190 = 215 240 285 315 460 = 1,484.967 -1 1.0400 -1 1.0400 19 315 20 -1 1.0400 -1 460 21 1 22 23 DV: 1,484.967 232.394 720.388 329.404 180.186 0 0 442.308 0 Formula L15 =SUMPRODUCT($C$23:$K$23, C15:K15) Copy to L15:L21 Optimal Solution: 29 Current investment required 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 $ 1,484,967 $ 232,394 $ 720,388 $ 329,404 $ 180, 186 $ 442,308 $ 40 D. Refer sensitivity report below, shadow price of year 6 payment is 0.78551166 Therefore, to reduce the payment at the beginning of year 6 to $400,000, Hoxworth should be willing to pay now = $ 0.78551166* (460000 - 400000) = $ 47,130.7 41 42 43 44 45 46 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

Solver Parameters Set Objective: SL$21 TO: O Max Min value of: © By Changing Variable Cells: $C$23:$K$23 Subject to the Constraints: $$15:$L$20 = $$15:$$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. Solve | Close

WPS Office Sensitivity Report Worksheet: (sheet1.xlsx] Report Created: 10/19/2018 2:36:36 AM Variable Cells 1 Cell Name $D$24 DV: F $E$24 DV: G1 $F$24 DV: G2 $G$24 DV: S1 $H$24 DV: S2 $1$24 DV: S3 $J$24 DV: S4 $K$24 DV: S5 $L$24 DV: S6 Final Reduced Value Cost 1484.966554 232.3935592 720.3878167 329.4035324 180.1861146 0 0.020765905 0 0.019419724 4 42.3076923 0 0.785511666 Objective Coefficient 1 0 0 0 0 Allowable Allowable Increase Decrease 1E+30 0.021315003 0.019730204 0.019629794 0.858799904 0.654301835 0.019803685 1.283438215 0.02026396 1E+30 0.020765905 1 E+30 0.019419724 1E+30 0.816932133 1 E+30 0.785511666 0 0 Constraints Cell Name $M$16 Year 1 LHS $M$17 Year 2 LHS $M$18 Year 3 LHS $M$19 Year 4 LHS $M$20 Year 5 LHS $M$21 Year 6 LHS Final Shadow Value Price 190 215 0.961538462 240 0.924556213 285 0.869029142 315 0.816932133 460 0.785511666 Constraint R.H. Side 190 215 240 285 315 460 Allowable Increase Decrease 1E+30 1484.966554 1E+30 342.5796737 1 E+30 187.3935592 2762.03307 248.0801244 3824.236886 757.3076923 3977.206361 460

A B C D E F | G | H | I | J | K | L M N O P N ON 60 d. Payment to be made at the End Of Year (EoY) Algebraic Formulation: Min 1 F s.t. 1 F -1.055 G1 - 1 G2 - 1 S1 = 0 0.0675 G1 + 0.05125 G2 + 1.04 S1 - 1 S2 = 190 0.0675 G1 + 0.05125 G2 + 1.04 S2 - 1 S3 = 215 1.0675 G1 + 0.05125 G2 + 1.04 S3 - 1 S4 = 240 1.05125 G2 + 1.04 S4 - 1 S5 = 285 1.04 S5 - 1 S6 = 315 1.04 S6 - 1 S7 = 460 13 Solution using Excel Solver: 15 F S2 S3 S4 S5 S6 S7 G1 -1.055 0.0675 0.0675 1.0675 S1 - 1 1.0400 -1 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Objective: G2 -1 0.05125 0.05125 0.05125 1.05125 1.0400 RHS O 190 215 240 285 315 460 1.0400 LHS | 0 190 215 240 285 = 315 460 1,417.739 -1 1.0400 1.0400 -1 E 1.0400 -1 DV: 1,417.739 178.554 963.786 265.578 147.648 0.000 0.000 728.180 442.308 0.000 Formula M17 =SUMPRODUCT($C$26:$L$26, C17:L17) Copy to M17:M24 33 33 34 35 Optimal Solution: Current investment required 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 $ 1,417,739 $ 178,554 $ 963,786 $ 265,578 $ 147,648 $ - $ - $ 728,180 $ 442,308 37 41 Hoxworth would save if this change could be negotiated = 1484967 - 1417739 = $ 67,228 43

Solver Parameters Set Objective: $M$24 To: O Max Min O Value of: By Changing Variable Cells: $C$26:$L$26 Subject to the Constraints: $M$17: $M$23 = $0$17:$0$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 Help [ Solve Close Close

Thank you for the question....kindly rate...it helps me a lot