SPECIFIC TEST INSTRUCTIONS You have been hired as a new mutual fund manager and have $100 million to invest. In this case, you will use Excel Solver to determine the best mix of securities to buy.

BACKGROUND You have been hired as a new fund manager by a large investment company that runs a number of mutual funds. You have been given $100 million to invest and have been assigned to start a new fund. It is up to you to invest the money wisely. A mutual fund invests its money in securities, such as stocks and bonds. For example, a mutual fund might have $10 million to invest. Assume that the entire amount is invested in individual shares of common stocks from companies such as General Motors, DuPont, and Bank of America. The mutual fund companys own common stock is also traded on the stock exchange. To continue the example, assume that the mutual fund starts with 1 million shares outstanding. The starting price of a share of its common stock would be $10 million divided by 1 million shares, or $10 a share. Assume further that the value of the purchased common stocks increases and is now worth $11 million. The value of a share of the mutual funds common stock would now be $11 million divided by 1 million shares, or $11.00. A mutual fund must have an investment strategy that governs its investment purchases. For example, a fund might invest in corporate bonds or government bonds, or the fund might invest in stocks of well-established industrial companies. A fund might also invest in stocks of companies devoted to real estate developmentsuch funds are usually called REITs (real estate investment trusts). Instead of buying shares of individual companies, a mutual fund can buy shares of other mutual funds. Such a fund is called a fund of funds. A diversified fund of funds would invest some of its money in funds devoted to bonds, in funds devoted to well-established industrial companies, in funds devoted to REITs, and in other kinds of funds. You decide to adopt this diversified fund of funds strategy. You will invest in funds oriented to industrial companies, funds for high-grade bonds, REITs, and funds oriented to precious metals, such as gold and silver. You expect each kind of fund to have different rates of return, different burdens for administrative expenses, and different risk levels. Your decision making will be constrained by certain rules imposed by management. Each of these factors is described next.

RATES OF RETURN

A fund could gain revenue in three ways: (1) from interest earned on bonds and other fixed-income securities, (2) from dividends on common stocks, and (3) from increases in the value of securities owned. For example, say that shares of XYZ Company sell for $100, and that a fund invests $100,000 in 1,000 shares of XYZ stock. XYZ pays a dividend of $1.00 per share, which means that the fund receives $1,000 from XYZ. Also, the shares increase in value by $10 per share, which is a total of $10,000. The fund would recognize revenue of $1,000 in dividends and $10,000 in increased market value, for a total of $11,000. Comparing this total with the initial value of $100,000 means that the funds rate of return is $11,000 divided by $100,000, or 11 percent. As another example, say that a 10-year government bond with a face value of $1,000 pays interest at 4 percent. A fund that invests in these bonds would earn a 4 percent rate of return, assuming that the market value of the bond does not change. Of course, the market value can change, which would influence the overall rate of return. Note that the market value of securities can also decrease, which means that rates of return can be low or even negative. If a fund invests in other funds, its rate of return will be the average from the funds in which you invested. Below shows the average rates of returns that you expect from the various investments. As an example of how this data will be used, assume that your fund of funds invests $10 million in funds that own shares of precious metals companies. The average rate of return for those funds is expected to be 10 percent, which means your investments would create $1 million of revenue from your fund of funds.

Administrative Expense Burden

Your new employer has many analysts who follow the performance of investment securities you might buy. You call on these analysts to suggest funds for purchase, follow the daily progress of the funds, and follow the stocks and bonds that the funds own. These analysts are expensive; they have high salaries as well as office expenses, computer service expenses, and so on. Some kinds of securities are easier to analyze than others, so their administrative costs depend on the kind of fund. Assume that stocks generally require more oversight than bonds, and that some kinds of stocks require more work than others. The expected administrative cost rates for each kind of fund are shown below. As an example of how this data would be used, assume that your fund of funds invests $10 million in funds that own shares of precious metals companies. The expected administrative cost rate for the funds you own is expected to be one-half of one percent (0.5%). This means that precious metals investments in your fund of funds would cost $50,000 in administrative expenses.

Risk Levels

The value of a security can decrease. If a security has lost value and then is sold, the owner will lose money. Risk is a term that indicates the likelihood of losing money on an investment. Some kinds of investments are riskier than others. For example, stocks are generally thought to be riskier investments than high-grade bonds. Your companys research department has assigned risk factors to the different kinds of funds you want to buy. Factor values range from 1 to 5. A risk factor of 1 represents low risk, and a risk factor of 5 represents high risk. Assigned risk factors are shown below. As an example of how this data would be used, assume that your fund of funds invests $2 million in each type of fund. An average risk factor could be computed for the fund of funds, as shown below. Management Rules Your management said you would have a free hand when building your fund, but that statement is not completely true. You must follow certain rules that your boss says are intended to reduce risk. You have $100 million to invest. You can keep $1 million in cash, which means you must invest at least $99 million.

You must invest at least $10 million in each kind of fund, and your average risk factor cannot exceed 3.1.

The sum of the amount invested in industrial stock funds and bond funds must be at least 60 percent of the total invested in all types of funds. These rules will constrain your investment decisions. These rules reflect managements consensus thinking on todays investment environment. However, interest rates on corporate and U.S. government bonds have been very low for a number of years. Some of your managers think that rates will rise soon, and that the risk factor assigned to bonds is too low.

QUESTION 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 fund of funds investment decision. This spreadsheet will be the base case. In Assignment 1B, the extension case, you will create a Solver spreadsheet to model the investment decision with a different outlook on interest rates. In questions 2 and 3, you will use the spreadsheet models to develop information needed to recommend the best investment mix for your mutual fund. In Assignment 2, you will document your recommendation in a memorandum; in Assignment 3, you will give your recommendations in an oral presentation. Your spreadsheets for this assignment should include the cells explained in the following sections. You will set up the cells in each of the following spreadsheet sections before entering cell formulas.

Your spreadsheets will also include decision constraints, which you will enter using Solver. Changing Cells Constants Calculations Income Statement Creating the Spreadsheet for the Base Case A discussion of each spreadsheet section follows. The discussion explains how to set up each section and explains the logic of the formulas in the sections cells. You can enter the data yourself or use the spreadsheet skeleton to save time. To access the spreadsheet skeleton, select Case 9 in your data files, and then select FundOfFundsBase.xlsx. Changing Cells Section Your spreadsheet should have the changing cells shown below. You will ask the Solver model to compute how much money to invest in each kind of mutual fund. Start with a value of $1.00 in each cell. Solver will change each $1 value as it computes the answer. Note that Solver might recommend a fractional part of a dollar for some answers.

Constants Section Your spreadsheet should have the constants shown below. An explanation of the line items follows the figure. Cash available to investYou have $100 million to invest. Minimum investment in each type of fundYou must invest at least $10 million in each type of mutual fund. Expected Rates of ReturnThe expected average rate of return is different for each kind of fund. For example, the rate is 8 percent for funds that invest in industrial company common stocks.

Administrative Cost RateThe expected average expense rate is different for each kind of fund. For example, the rate is eight-tenths of one percent for funds that invest in industrial company common stocks.

Risk FactorThe expected average risk factor is different for each kind of fund. For example, the factor is 3 for funds that invest in industrial company common stocks. Calculations Section Your spreadsheet should calculate the amounts shown below. These values will be used later in the income statement or as constraints. Calculated values may be based on the values of the changing cells, the constants, and other calculations. Total Amount InvestedAmounts invested are shown in the changing cells.

Percentage invested in Stocks and BondsThis value is the sum of amounts invested in stock and bond funds versus the total invested in all kinds of funds. Note that precious metals funds are not included in stock funds. Revenue earnedThe revenue earned for a type of fund is a function of the amount invested in it and its average rate of return.

Administrative CostsThe administrative cost for a type of fund is a function of the amount invested in it and its average administrative expense rate. Risk PointsThe risk points for a type of fund is a function of the amount invested in it and its assigned risk factor.

Total Risk Points to Total amount InvestedThis total is the average risk factor for the fund of funds. It is the total risk points for all types of funds divided by the total amount invested in all types of funds. Income Statement Section The statement shown below is the projected net annual income on the amounts invested in the fund of funds. Total RevenueThis amount is the sum of the revenues earned, as shown in the Calculations section.

Total Administrative CostsThis amount is the sum of administrative costs, as shown in the Calculations section.

Net Income Before TaxThis amount is the difference between Total Revenue and Total Administrative Costs. Constraints and Running Solver Next, you must determine the constraints to implement managements investment rules. Use Solver to enter the decision constraints for the base case. You want to maximize net income before taxes, subject to the various investment and risk constraints. Run Solver and ask for the Answer Report when Solver reports a solution that satisfies the constraints. When you finish, print the entire spreadsheet, including the Solver Answer Report sheet. Save the file one more time using the Save command in the File tab; you can keep FundOfFundsBase.xlsx as the filename. Then, to prepare for the extension case, use the Save As command in the File tab to create a new spreadsheet named FundOfFundsExtension.xlsx.

Question 2: Creating the Spreadsheet for the Extension Case Next, you prepare the extension case. Some members of your management team think that interest rates are about to rise significantly. This increase would affect the overall investment environment and is the main difference between the base case and the extension case. Rising interest rates on newly issued debt would cause the market prices of existing debt to fall, which would create a lower rate of return for the bond funds owned by your fund of funds. Risk factors associated with bond funds would be expected to change. Typically, rising interest rates also lead to lower common stock prices in the long run. Thus, you would expect a significantly lower rate of return on the stock funds owned by your fund of funds. Costs associated with analyzing stocks and bonds would also change as analysts learn about changes in market conditions. Below shows the average rates of returns that you expect from the kinds of funds in which you invested for the extension case. The expected administrative cost rates for each kind of fund in the extension case are shown below. Assigned risk factors for the extension case are shown below. Some of managements rules for the base case must be adjusted in the extension case. The average risk factor limit increases to 3.5 in recognition of increasing market uncertainties. Also, you no longer need to invest 60 percent in stock and bond funds; that rule can be dropped. Modify the extension case spreadsheet to handle the changes. Change the constant values and constraints as needed. Run Solver, and then ask for the Answer Report when Solver reports a solution that satisfies the constraints. When you finish, print the entire spreadsheet, including the Solver Answer Report sheet. Save the file one more time, close it, and exit Excel.

Question 3: Using the spreadsheet for decision support You have built models for the base case and extension case because you want to know the investment mix for each scenario and which scenario yields the higher net income before taxes, consistent with perceived risks. You will now complete the case by using the Answer Reports to gather the data you need to make the investment mix decisions.

Question 4: Using the Spreadsheet to Gather Data Each sheet reports how much of each kind of fund to purchase, the expected net income before taxes in each case, and the weighted average risk ratio in each case. Your management wants to know how much the extension case results differ from the base case results.

Question 5: Documenting Your Recommendation Prepare a Microsoft Word file explaining your recommendation using the following criteria: In the first paragraph, briefly outline the investment mix decision and the purpose of your analysis. Tell management which scenario yields the higher net income before taxes, and which yields the lower overall risk ratio. Tell management how a rise in interest rates would be expected to change net income before taxes and the overall expected risk level. Tell management how investment allocations would differ by comparing the base case with the extension case.