Question

      A bond fund currently holds a bond portfolio with a face value of $10 million. The current market value of the portfolio is only 92.2% of face, however. The fund’s managers anticipate a rise in bond yields (interest rates) in the near future, so they desire a T-bond hedging strategy to protect themselves.

1. Given their rate expectations, should they short or go long in T-bond futures? Explain.
2. The risk managers use $100,000 face value T-bond contracts. If they use a 1-1(naïve) hedge ratio between cash and futures positions, how many contracts should they use if they hedge the market value of the portfolio??
3. The deliverable bonds are 10 ¾% with a conversion factor of 1.2922. If accrued interest is zero, what cash amount would be transacted per contract if the quoted futures price is 76-31?
4.    At close, the market value of the bond portfolio is now 90.2% of face. The cash amount transacted per contract is .97458 times the face value of the futures contract. Calculate the loss in the bond portfolio’s market value versus the change in value of the hedge.

        e. Did the hedge work? Explain

NOTE: please focus on parts C,D, AND E only. I have the rest solved.

Answer 1

Step 1 of 6 The variables and constraints for this problem are listed below. As, it can be seen by the given figure in the problem, the only legitimate location choices for the fire station are in the first coordinate or on the positive axis. So implicitly there are non-negativity constraints in this problem and it is necessary to account for them. Variables and Constraints Input Variable Town Coordinates, annual town fires Decision Variable (changing cells) Coordinates of fire station location Objective (target cell) Total annual distance travelled to the town from the fire station Other output variables Distance between towns and fire station Constraints Non-negativity constraints

Step 2 of 6 A Developing the spreadsheet Model To develop the spreadsheet model as below, follow the following steps 1 Fire-station location problem 3 Towns data 4 Town1 5 Town 2 6 Town3 7 Town4 x coordinate y-coordinate 10 60 40 80 Annual fires 20 20 30 60 20 30 40 25 9 Firestation location x coordinate y-coordinate 10 40.00001649 29.99995485 12 Towns distances from firestation 13 Town 1 14 Town 2 15 Town 3 16 Town 4 17 Total Distance 39.99997134 29.99993836 6.16443E-05 70.00002866 3450.00076

1. Inputs: Enter the given towns data that is the x and y coordinates of all the four towns locations and the annual fires in each town. 2. Coordinates of the fire station: Enter any values for the x and y coordinates of the fire station. 3. Road distance from fire station to towns: Calculating the distance as per the north-south/ east- west directions of roads to be followed while travelling by road, the distance between two points (a, b) and (c, d) on the axis is, lc -alld-bl. Therefore, enter the formula, -SUM (ABS (SBS10-B4), ABS (SCS10-C4))In cell B13 and copy it down to cell B16. This will give you the road distance between fire station and each town. 4. Total Distance: The total annual distance travelled from the fire-station to extinguish fires in all the towns is the sum over all the towns of the distance from the fire station to the town multiplied by the annual fires in the town. So to calculate the total annual distance travelled, enter the formula, SUMPRODUCT (E4:E7, B13:B16) in cell B17 Using Solver The solver for this problem is as shown below. The needed action to specify that the total distance as given in B17 needs to be minimized and the fire station location cells that is B10:C10 are the changing cells. There are non-negativity constraints which should be added as SB5100 and SC$10-0 Also since there are absolute values in this distance formula, so this objective function is non- linear, so Assume Linear Model option should not be checked.

Solver Parameters Set Target Cel: 17 Equal To: Mn Value of: 0 Ey Changing Cells: Solve Cose Guess 8510:$C$10 Subject to the Constraints: sc$10 >= 0 Cptions Add Change Reset Al Hep Comment

Step 3 of 6 A Discussion of the solution The solver solution is that the fire station should be located atx 40 and y 30. So the total distance travelled from the fire station to the four towns is 34500 sq. units. Note that the optimal fire station position coincides with the position of town 3. Intuitively, one would want to locate the fire station closest to the town which has the maximum number of fires and also want to take care that it is not too far off from the other towns. Here town 3 is the town with highest number of annual fires, which is also the fire station location optimally. Comment Step 4 of 6 b. Now, click on solver table tab and select the one way type of table. The screenshot is given as below,

b. Now, click on solver table tab and select the one way type of table. The screenshot is given as below Type of table 23 You can create a oneway or twoway table, depending on whether you want to test the sensitivity of outputs to one or two inputs. OK Cance Type of table Oneway table Twoway table Comment Step 5 of 6 A Now, click on OK button. Then, a new dialog box will open, in this select the cell E6 as input, take 10 as minimum value, take 70 as maximum value, take 5 as increment and take the cells B10 and C10 in output. Then click on OK button of it. The screenshot is given as below,

Now, click on OK button. Then, a new dialog box will open, in this selec 10 as minimum value, take 70 as maximum value, take 5 as increment and C10 in outpu t. Then click on OK button of it. The screenshot is give Parameters for oneway table OK Specify the following information about the input to be varied and the outputs to be captured. Input cel:SES Optional) Descriptivefres at town3 Cancel name for input: Values of input to use for table Base input values on following: Minimum vaue: Maximum value: 10 70 C Use the values from the folowing range: Input value range: Use the values below (separate with commas) Input values: Output cells) 8510:$C$10 Note about specifying output cels: The safest way to select multiple output cels or ranges is to put your finger on the Ctrl key and then drag as many output cel ranges as you lke. This wll automatically insert commas between the ranges you select.

After clicking on OK button, new output will be generated. The screenshot of the output is given as below Oneway analysis for Solver model in Sheet1 worksheet fires at town3 (cell SE$6) values along side, output cell(s) along top 10 60.0001 15 59.99999 20 59.99999 25 59.99998 30 59.99998 35 47.55038 29.99999 40 47.55038 29.99999 45 47.55038 29.99999 50 47.55038 29.99999 55 47.55038 29.99999 60 47.55038 29.99999 б51 47.55038 29.99999 70 47.5503 29.99999 According to the solver table report, when fires at town 3 go beyond 30 then location of the fire station will change, which will remain same till the 70 fires