Assignment 1B: Creating the Spreadsheet-Extension Case Some senior managers want investment managers to invest in Credit Default Swaps (CDS) because they are seen as a low-risk and relatively inexpensive way to increase the bottom line. If you do not know what a credit default swap is, consider the following example. Assume that a large pension fund invests in $1 million of XYZ corporation's bonds. The fund manager, for some reason, is con- cerned that XYZ will not pay off the bonds when they come due. In other words, the manager is worried that XYZ will "default and that bondholders (creditors) will then lose their investment. The pension manager is willing to pay a fee to insure against losing $1 million. He looks for someone to agree (for a fee) to pay the value of the $1 million bonds if XYZ defaults. Assume that the KLM company agrees to provide this insurance. If XYZ does not default, KLM pockets the fee and has no expense. But if XYZ defaults, KLM must pay $1 million to the pension fund. In reality, corporations rarely default on their bonds, so CDS fees are easy money for the insurer. In other words, the insurer rarely has to pay the value of the bonds it insured. Wall Street has an active CDS market, and many huge pension funds want to have their investments insured. Your fund's senior management wants to get involved as a CDS insurer so the company can collect the fees. Senior managers say that only AAA debt would be insured and that they are not afraid of the default risk. In your changing cells, you specify the total dollar value of credit that you will insure. The changing cells would look like Figure 8-8. 1 FUND MANAGER INVESTMENT MIX PROBLEM EXTENSION CASE 3 CHANGING CELLS 4 DOLLARS IN U.S. BONDS $1 5 DOLLARS IN AAA CORPORATE BONDS $1 6 DOLLARS IN BLUE CHIP STOCKS 7 DOLLARS IN CASH $1 8 PENSION DOLLARS INSURED BY CDS $1 FIGURE 8-8 Extension case Changing Cells section Assume that management thinks you should insure at least 850 million but not more than $100 million. The rate of return on CDS would be 3%, so CDS revenue would be 3% of the amount insured. (The spread- sheet should have a blank row for this factor.) Assume that you would not monitor the status of the bonds insured, so the variable expense rate for CDS would be zero. Note that you would not be "investing in the bonds insured; you would merely insure against their even- tual default. Thus, the total invested would remain at $50 million. Although management thinks the risk of default is low, they think the insured bonds should be included in the calculation of the weighted average risk factor. The same risk level used for corporate bonds should be used for bonds insured. The spreadsheet should have a blank row for this risk level.) The weighted average risk should stay between 1.5 and 3.5 in the extension case. The same minimum investment levels would prevail in this extension case, and the management fee would be computed in the same way. In this more aggressive model, management does not think you should worry about the relationship of U.S. government and AAA bonds to total investment. Again, the goal is to maximize net income. The ratio of net income to total investment must be at least 2.5%, as it was in the base case. Management thinks that achieving this goal will be easier in the extension case; after all, revenue should be augmented by the easy CDS money! Modify the extension case spreadsheet to handle the more aggressive scenario. Modify the constraints as needed. Run the Solver and ask for the Answer Report when the Solver finds a solution that satisfies the constraints. When you finish, print the entire workbook, including the Solver Answer Report sheet. Save the workbook, close the file, and exit Excel. BASE CASE 1 1 1 1 1 FUND MANAGER INVESTMENT MIX PROBLEM 2 3 CHANGING CELLS 4 DOLLARS INVESTED IN U.S. BONDS 5 DOLLARS INVESTED IN AAA CORPORATE BONDS 6 DOLLARS INVESTED IN BLUE CHIP STOCKS 7 DOLLARS INVESTED IN CASH 8 9 10 CONSTANTS 11 TAX RATE 12 RATE OF RETURN: 13 U.S. BONDS 14 AAA CORPORATE BONDS 15 BLUE CHIP STOCKS 16 CASH 17 0.2 --- 0.035 0.045 0.0475 0.005 0.0075 --- 1 O w N A 2 3 --- 19 MANAGEMENT FEE % 20 RISK LEVEL ASSIGNED: 21 U.S. BONDS 22 AAA CORPORATE BONDS 23 BLUE CHIP STOCKS 24 CASH 25 26 27 EXPENSE FACTOR: 28 U.S. BONDS 29 AAA CORPORATE BONDS 30 BLUE CHIP STOCKS 31 CASH 32 33 34 CALCULATIONS 35 REVENUE: 36 U.S. BONDS 37 AAA CORPORATE BONDS 38 BLUE CHIP STOCKS 39 CASH 40 0.0015 0.0035 0.0055 0.0005 --- =C4*C13 =C5 C14 =C6C15 =C7*C16 --- =C4*C28 =C5*C29 =C6*C30 =C7*C31 41 42 VARIABLE EXPENSES: 43 U.S. BONDS 44 AAA CORPORATE BONDS 45 BLUE CHIP STOCKS 46 CASH 47 48 49 WEIGHTED AVG RISK FACTOR 50 TOTAL NON-CASH INVESTED 51 TOTAL INVESTED 52 TOTAL U.S. GOVERNMENT AND AAA 53 PERCENT IN U.S. GOVERNMENT AND AAA 54 NET INCOME TO TOTAL INVESTED RATIO 55 56 57 INCOME STATEMENT 58 TOTAL REVENUE 59 EXPENSES: 60 MANAGEMENT FEE 61 VARIABLE EXPENSES 62 TOTAL EXPENSES = {(C4*C21)+(C5*C22)+(C6*C23)+(C7* =SUM(C4:06) =SUM(C4:07) =SUM(C4:05) =SUM(C4:C5)/SUM(C4:C6) =C65/C51 =SUM(C36:C39) =C19*SUM(C4:06) =SUM(C43:C46) =C60+C61 TOTAL NON-CASH INVESTED TOTAL INVESTED TOTAL U.S. GOVERNMENT AND AAA PERCENT IN U.S. GOVERNMENT AND AAA NET INCOME TO TOTAL INVESTED RATIO =SUM(C4:C6) =SUM(C4:07) =SUM(C4:05) =SUM(C4:C5)/SUM(C4:06) =C65/C51 =SUM(C36:C39) SES INCOME STATEMENT TOTAL REVENUE EXPENSES: MANAGEMENT FEE VARIABLE EXPENSES TOTAL EXPENSES INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME AFTER TAXES =C19*SUM(C4:06) =SUM(C43:046) =C60+C61 =C58-C62 =IF(C63