Decision Support Using Excel Solver PREVIEW You are a new investment manager working for a giant mutual fund. You will have $50 million to invest. You want to buy securities that are profitable but safe. In this case, you will use Excel to determine the best mix of securities to buy for your fund. PREPARATION Review spreadsheet concepts discussed in class and in your textbook. Complete any exercises that your instructor assigns. Complete any part of Tutorial D that your instructor assigns, or refer to Tutorial D as necessary. Review file-saving procedures for Windows programs, as discussed in Tutorial C. Refer to Tutorials E and F as necessary. BACKGROUND You work for a gigantic mutual fund that manages billions of dollars. The money is parceled out to its investment managers to be invested. Managers who invest well are retained by the company. You have recently been appointed as an investment manager, and you will have $50 million to invest for the year. You want to consider the following kinds of investments: Bonds issued by the U.S. governmentThese bonds earn interest and are considered very safe, because the government has a printing press and can always repay its debts. AAA corporate bondsThese bonds earn interest. The credit worthiness of corporations is rated. AAA is the highest rating in the markets, so these bonds are co that an investor buys 1,000 shares of ABC stock at $80 dollars per share. If ABC shares then fall to $10 per share, the loss of principal is 1,000 times $70, or a loss of $70,000. You understand that an investor who faces a higher level of risk will demand a higher rate of return. Your funds Research Department has estimated the expected annual rate of return for the three kinds of securities and for cash. The rates are shown in Figure 8-1. For example, suppose that $1 million is invested in U.S. government bonds. During the year, 3.5% of that amount$35,000will be received on that investment. That $35,000 will be revenue in the income statement. As another example, suppose that $1 million is invested in blue-chip stocks. If the prices of the stocks go up an average of 4.75% per year, the increase will yield $47,500 of revenue in the income statement. Notice that the rates of return cited here are annual rates. The funds Research Department has investment managers assigned to monitor the expected levels of risk for different kinds of securities. Risk is denoted by a numerical Expected Risk Indicator. The greater this number is, the higher the risk of losses. The expected risk indicators are shown in Figure 8-2. As shown in Figure 8-2, U.S. government bonds have very low risk because the government can be expected to repay its debts (though the market value of any bond can decline). AAA corporate bonds have some risk, but not much, because the best companies almost always pay their debts. Blue-chip stocks carry some riskthe risk on stock prices is generally higher than on bond prices. There is almost no risk of losing cash that is invested in money-market instruments, so zero risk is assumed. Expenses Operating the mutual fund generates two kinds of expenses. First, the fund must work with a separate management company that keeps financial records on computers, provides frequent data and reports from Wall Street, and so on. Your funds management company charges three-quarters of one percent of the amount invested in non-cash securities. For example, if you invested $49 million in non-cash securities, you would incur a management fee of 0.0075 * $49,000,000. Second, you and your analysts must watch each kind of security. The cost of this activity (salaries, office expenses, and so on) depends on the kind of security. Assume that high-risk securities require more oversight, so the expenses associated with a high-risk security are greater than those for a low-risk security. The funds Research Department has estimated the expense factors for securities and cash (see Figure 8-3). Type of Investment Expected Rate of Return U.S. government bonds .0350 AAA corporate bonds .0450 Blue-chip stocks .0475 Cash .0050 FIGURE 8-1 Expected rates of return Type of Investment Expected Risk Indicator U.S. government bonds 1 AAA corporate bonds 2 Blue-chip stocks 3 Cash 0 FIGURE 8-2 Expected risk indicators 146 Case 8 For example, suppose that $1 million is invested in U.S. government bonds. During the year, 0.15% of that sum$1,500will be spent monitoring the $1 million. Thus, the $1,500 will be a variable expense in your income statement for the year. Your manager has given you a performance goal: The net income to total investment ratio must be at least 2.5% (.025). That is, the funds net income after taxes, divided by the amount of dollars invested, must be at least .025. For example, if yearly net income after taxes is $1 million on $10 million invested, then the net income to total investment would be 10%, and your manager would be satisfied. By contrast, if yearly net income after taxes is $100,000 on the $10 million invested, your manager would not be satisfied with the 1% ratio. Investment Requirements You will have $50 million to invest for the year, and you must invest it all. Your manager says that you must put at least $250,000 but no more than $5 million into cash. You must invest at least $5 million in U.S. government bonds, at least $5 million in AAA corporate bonds, and at least $5 million in blue-chip stocks. Your non-cash investments must total at least $45 million. The ratio of dollars invested in U.S. government bonds and AAA corporate bonds to the total non-cash investment must be at least 0.50. For example, if $15 million is invested in each of U.S. government bonds, AAA corporate bonds, and blue-chip stocks, the ratio would be 30/45 .667. Your manager has set a quantifiable risk-level boundary. The weighted average risk level on the money invested each month must be at least 1.5, but should not exceed 3.5. The weighted average risk level is computed by the following procedure: weight the expected risk indicators by the amount invested in each kind of security add the weighted amounts divide the total by the total amount invested To use a simplified example, suppose that only U.S. government bonds and blue-chip stocks are purchased. Suppose that $5 million is invested in the bonds and $5 million in the stocks. The weighted average risk level would be 2.0: $5 000 000 1 $5 000 000 3 $10 000 000 2 0 (This example only considers two kinds of securities, but you will actually invest in more than two kinds.) Your manager thinks that the scenario outlined here is a proper plan for you in the near future. She calls this scenario the base case. However, you should know that executives in the funds senior management think that fund managers should be more aggressive, and this attitude may change the rules in the future. Your manager wants you to set up a base case plan for your $50 million of investments. The goal should be to maximize your investments net income after taxes without assuming too much risk. Your manager knows that you can model this problem in the Solver because you said you could when you were interviewed for the job! Type of Investment Expense Factor U.S. government bonds .0015 AAA corporate bonds .0035 Blue-chip stocks .0055 Cash .0005 FIGURE 8-3 Investment expense factors The New Fund Manager Investment Mix Decision 147

ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce spreadsheets that model the business decision. In Assignment 1A, you will create a Solver spreadsheet to model the investment decision. This model will be the base case. In Assignment 1B, the extension case, you will create a second Solver spreadsheet to model the investment decision given more aggressive operating rules. In Assignments 2 and 3, you will use the spreadsheet models to develop information needed to recommend the best mix for your investments. In Assignment 2B, you will document your recommendations in a memorandum. In Assignment 3, you will give your recommendations in an oral presentation. Your spreadsheets for this assignment should include the following sections. You will be shown how to set up each section before entering cell formulas. Your spreadsheets will also include decision constraints that you will enter using the Solver. Changing Cells Constants Calculations Income Statement Assignment 1A: Creating the SpreadsheetBase Case A discussion of each spreadsheet section follows. You will learn how to set up each section and learn the logic of the formulas in the sections cells. When you enter data in the spreadsheet skeleton, follow the order shown in this section. The spreadsheet skeleton is available for you to use; you can choose to type in it or not. To access the base case spreadsheet skeleton, go to your data files, select Case 8, and then select FundManager.xlsx. Changing Cells Section Your spreadsheet should include the changing cells shown in Figure 8-4. You will ask the Solver model to compute how many dollars to invest in each kind of security during the year. Start with a 1 in each cell. The Solver will change each 1 as it computes the answer. The Solver can recommend investing a fractional part of a dollar for a security. FIGURE 8-4 Changing Cells section 148 Case 8 Constants Section Your spreadsheet should include the constants shown in Figure 8-5. An explanation of the line items follows the figure. Tax RateThe tax rate is applied to income before taxes. Rate of ReturnThe rate of return for each type of security. The rate of return is used to compute the revenue from each security. Management FeeA flat percentage of the total amount invested, except that the fee is not charged on cash invested. (There is nothing to manage in that case.) Risk Level AssignedThe risk indicator for each type of security. Expense FactorThis percentage is used to compute the expense associated with each investment. Calculations Section Your spreadsheet should calculate the amounts shown in Figure 8-6. These amounts will be used in the Income Statement section and the Constraints section. Calculated values may be based on the values of the changing cells, the constants, and other calculations. An explanation of the line items follows the figure. FIGURE 8-5 Constants section The New Fund Manager Investment Mix Decision 149 RevenueCompute the revenue for each security. Revenue is a function of the amount invested in the security and the securitys annual rate of return. The amount invested is shown in the Changing Cells section. The rate of return is taken from the Constants section. Variable ExpensesCompute each securitys variable expense, which is a function of the amount invested and the securitys expense ratio. The expense ratio is taken from the Constants section. Weighted Avg Risk FactorCompute the weighted average risk factor for the four investment types. Follow the logic of the example explained earlier. Total Non-Cash InvestedCompute the total dollars invested in non-cash securities. Total InvestedCompute the total invested in all securities (including cash). Total U.S. Government and AAACompute the total invested in U.S. government bonds and AAA corporate bonds. Percent in U.S. Government and AAACompute the ratio of U.S. government bonds and AAA corporate bonds to total non-cash dollars invested. Net Income to Total Invested RatioCompute the ratio of net income after taxes to total dollars invested in all securities. Income Statement Section The statement shown in Figure 8-7 is the projected net income for a year on the amount invested. An explanation of the line items follows the figure. FIGURE 8-6 Calculations section 150 Case 8 Total RevenueThis amount is computed by totaling the previously calculated revenues. Management FeeA function of the management fee expense rate (from the Constants section) and non-cash amounts invested. Variable ExpensesThis amount is computed by totaling the previously calculated variable expenses. Total ExpensesThe sum of the management fee and variable expenses. Income Before TaxesTotal revenue minus total expenses. Income Tax ExpenseThis expense is zero if income before taxes is zero or less; otherwise, apply the tax rate to income before taxes. Net Income After TaxesThis amount is income before taxes minus income taxes. Constraints and Running the Solver In this part of the assignment, you determine the decision constraints. Enter the base case decision constraints using the Solver. Run the Solver and ask for the Answer Report when the Solver has found a solution that satisfies the constraints. When you finish, print the entire workbook, including the Solver Answer Report sheet. Save the workbook by opening the File menu and selecting Save; FundManager.xlsx is suggested as the filename. Then, to prepare for the extension case, open the File menu and select Save As to create a new spreadsheet. (FundManager2.xlsx would be a good filename.)

Assignment 1B: Creating the SpreadsheetExtension 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 corporations bonds. The fund manager, for some reason, is concerned 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 funds 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. FIGURE 8-7 Income Statement section The New Fund Manager Investment Mix Decision 151 Assume that management thinks you should insure at least $50 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 spreadsheet 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 eventual 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.

ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT You have built the base case and extension case models because you want to know the investment mix for each scenario and which scenario yields the highest net income after taxes, consistent with perceived risks. You will now complete the case by using the Answer Reports to gather the data needed to make the investment mix decisions and by documenting your recommendations in a memorandum. Assignment 2A: Using the Spreadsheet to Gather Data You have printed the Answer Report sheets for each scenario. Each sheet reports the dollar amount of each kind of security to purchase in a month, plus the target net income and risk in each case. Summarize key data in a table in your memorandum. The table format is shown in Figure 8-9. FIGURE 8-8 Extension case Changing Cells section 152 Case 8

Assignment 2B: Documenting Your Recommendation in a Memorandum Use Microsoft Word to write a brief memorandum to your manager. State the results of your analysis and recommend which investment strategy to adopt: the base case or the extension case. Keep in mind that your management ranks investment managers by their net income ratio and that a default on $50 million in bonds, while unlikely, would completely wipe out the value of your investment portfolio. Is the easy CDS money worth the apparently small risk? In your memo, observe the following requirements: Set up your memorandum as discussed in Tutorial E. In the first paragraph, briefly define the situation and state the purpose of your analysis. Then describe your results and state your recommendations. State the logic that supports your recommendations. Support your statements graphically by including the summary table shown in Figure 8-9. The procedure for creating a table in Word is described in Tutorial E.

ASSIGNMENT 3: GIVING AN ORAL PRESENTATION Your instructor may request that you present your analysis and recommendations in an oral presentation. If so, assume that your manager wants you to explain your analysis and recommendations to her and other investment managers in 10 minutes or less. Use visual aids or handouts that you think are appropriate. Tutorial F explains how to prepare and give an oral presentation. DELIVERABLES Assemble the following deliverables for your instructor: 1. A printout of your memorandum 2. Spreadsheet printouts 3. Electronic media such as a USB key or CD, which should include your Word memo and your Excel spreadsheet file Staple the printouts together with the memorandum on top. If you have more than one .xlsx file on your electronic media, write your instructor a note that identifies the name of your models .xlsx file.

EXCEL FILE BELOW:

FUND MANAGER INVESTMENT MIX PROBLEM
		BASE CASE
CHANGING CELLS
DOLLARS INVESTED IN U.S. BONDS		$1
DOLLARS INVESTED IN AAA CORPORATE BONDS		$1
DOLLARS INVESTED IN BLUE CHIP STOCKS		$1
DOLLARS INVESTED IN CASH		$1
CONSTANTS
TAX RATE		0.20
RATE OF RETURN:		---
U.S. BONDS		0.0350
AAA CORPORATE BONDS		0.0450
BLUE CHIP STOCKS		0.0475
CASH		0.0050
MANAGEMENT FEE %		0.0075
RISK LEVEL ASSIGNED:		---
U.S. BONDS		1
AAA CORPORATE BONDS		2
BLUE CHIP STOCKS		3
CASH		0
EXPENSE FACTOR:		---
U.S. BONDS		0.0015
AAA CORPORATE BONDS		0.0035
BLUE CHIP STOCKS		0.0055
CASH		0.0005
CALCULATIONS
REVENUE:		---
U.S. BONDS
AAA CORPORATE BONDS
BLUE CHIP STOCKS
CASH
VARIABLE EXPENSES:		---
U.S. BONDS
AAA CORPORATE BONDS
BLUE CHIP STOCKS
CASH
WEIGHTED AVG RISK FACTOR
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
INCOME STATEMENT
TOTAL REVENUE
EXPENSES:		---
MANAGEMENT FEE
VARIABLE EXPENSES
TOTAL EXPENSES
INCOME BEFORE TAXES
INCOME TAX EXPENSE
NET INCOME AFTER TAXES