In the Markowitz portfolio optimization model defined in equations (8.10) through (8.19) in the text, the decision variables represent the percentage of the portfolio invested in each of the mutual funds. For example,  FS = 0.25  in the solution means that 25% of the money in the portfolio is invested in the foreign stock mutual fund. It is possible to define the decision variables to represent the actual dollar amount invested in each mutual fund or stock. Redefine the decision variables so that now each variable represents the dollar amount invested in the mutual fund. Assume an investor has $50,000 to invest and wants to minimize the variance of his or her portfolio subject to a constraint that the portfolio returns a minimum of 10%. (a) Reformulate the model given by (8.10) through (8.19) based on the new definition of the decision variables. min               ((R1 − R)2 + (R2 − R)2 + (R3 − R)2 + (R4 − R)2 + (R5 − R)2) s.t.         R1 =            R2 =            R3 =            R4 =            R5 =            FS + IB + LG + LV + SG + SV =            R =     1/5         (R1 + R2 + R3 + R4 + R5)   R ≥  50,000

A common decision is whether a company should buy equipment and produce a product in house or outsource production to another company. If sales volume is high enough, then by producing in house, the savings on unit costs will cover the fixed cost of the equipment. Suppose a company must make such a decision for a four-year time horizon, given the following data. Use simulation to estimate the probability that producing in house is better than outsourcing. If the company outsources production, it will have to purchase the product from the manufacturer for 25 per unit. This unit cost will remain constant for the next four years. The company will sell the product for 42 per unit. This price will remain constant for the next four years. If the company produces the product in house, it must buy a 500,000 machine that is depreciated on a straight-line basis over four years, and its cost of production will be 9 per unit. This unit cost will remain constant for the next four years. The demand in year 1 has a worst case of 10,000 units, a most likely case of 14,000 units, and a best case of 16,000 units. The average annual growth in demand for years 2-4 has a worst case of 7%, a most likely case of 15%, and a best case of 20%. Whatever this annual growth is, it will be the same in each of the years. The tax rate is 35%. Cash flows are discounted at 8% per year.

A European put option allows an investor to sell a share of stock at the exercise price on the exercise data. For example, if the exercise price is 48, and the stock price is 45 on the exercise date, the investor can sell the stock for 48 and then immediately buy it back (that is, cover his position) for 45, making 3 profit. But if the stock price on the exercise date is greater than the exercise price, the option is worthless at that date. So for a put, the investor is hoping that the price of the stock decreases. Using the same parameters as in Example 11.7, find a fair price for a European put option. (Note: As discussed in the text, an actual put option is usually for 100 shares.)

Although the normal distribution is a reasonable input distribution in many situations, it does have two potential drawbacks: (1) it allows negative values, even though they may be extremely improbable, and (2) it is a symmetric distribution. Many situations are modelled better with a distribution that allows only positive values and is skewed to the right. Two of these that have been used in many real applications are the gamma and lognormal distributions. @RISK enables you to generate observations from each of these distributions. The @RISK function for the gamma distribution is RISKGAMMA, and it takes two arguments, as in =RISKGAMMA(3,10). The first argument, which must be positive, determines the shape. The smaller it is, the more skewed the distribution is to the right; the larger it is, the more symmetric the distribution is. The second argument determines the scale, in the sense that the product of it and the first argument equals the mean of the distribution. (The mean in this example is 30.) Also, the product of the second argument and the square root of the first argument is the standard deviation of the distribution. (In this example, it is 3(10=17.32.) The @RISK function for the lognormal distribution is RISKLOGNORM. It has two arguments, as in =RISKLOGNORM(40,10). These arguments are the mean and standard deviation of the distribution. Rework Example 10.2 for the following demand distributions. Do the simulated outputs have any different qualitative properties with these skewed distributions than with the triangular distribution used in the example? a. Gamma distribution with parameters 2 and 85 b. Gamma distribution with parameters 5 and 35 c. Lognormal distribution with mean 170 and standard deviation 60

Suppose you currently have a portfolio of three stocks, A, B, and C. You own 500 shares of A, 300 of B, and 1000 of C. The current share prices are 42.76, 81.33, and, 58.22, respectively. You plan to hold this portfolio for at least a year. During the coming year, economists have predicted that the national economy will be awful, stable, or great with probabilities 0.2, 0.5, and 0.3. Given the state of the economy, the returns (one-year percentage changes) of the three stocks are independent and normally distributed. However, the means and standard deviations of these returns depend on the state of the economy, as indicated in the file P11_23.xlsx. a. Use @RISK to simulate the value of the portfolio and the portfolio return in the next year. How likely is it that you will have a negative return? How likely is it that you will have a return of at least 25%? b. Suppose you had a crystal ball where you could predict the state of the economy with certainty. The stock returns would still be uncertain, but you would know whether your means and standard deviations come from row 6, 7, or 8 of the P11_23.xlsx file. If you learn, with certainty, that the economy is going to be great in the next year, run the appropriate simulation to answer the same questions as in part a. Repeat this if you learn that the economy is going to be awful. How do these results compare with those in part a?

The annual demand for Prizdol, a prescription drug manufactured and marketed by the NuFeel Company, is normally distributed with mean 50,000 and standard deviation 12,000. Assume that demand during each of the next 10 years is an independent random number from this distribution. NuFeel needs to determine how large a Prizdol plant to build to maximize its expected profit over the next 10 years. If the company builds a plant that can produce x units of Prizdol per year, it will cost 16 for each of these x units. NuFeel will produce only the amount demanded each year, and each unit of Prizdol produced will sell for 3.70. Each unit of Prizdol produced incurs a variable production cost of 0.20. It costs 0.40 per year to operate a unit of capacity. a. Among the capacity levels of 30,000, 35,000, 40,000, 45,000, 50,000, 55,000, and 60,000 units per year, which level maximizes expected profit? Use simulation to answer this question. b. Using the capacity from your answer to part a, NuFeel can be 95% certain that actual profit for the 10-year period will be between what two values?

You are considering a 10-year investment project. At present, the expected cash flow each year is 10,000. Suppose, however, that each years cash flow is normally distributed with mean equal to last years actual cash flow and standard deviation 1000. For example, suppose that the actual cash flow in year 1 is 12,000. Then year 2 cash flow is normal with mean 12,000 and standard deviation 1000. Also, at the end of year 1, your best guess is that each later years expected cash flow will be 12,000. a. Estimate the mean and standard deviation of the NPV of this project. Assume that cash flows are discounted at a rate of 10% per year. b. Now assume that the project has an abandonment option. At the end of each year you can abandon the project for the value given in the file P11_60.xlsx. For example, suppose that year 1 cash flow is 4000. Then at the end of year 1, you expect cash flow for each remaining year to be 4000. This has an NPV of less than 62,000, so you should abandon the project and collect 62,000 at the end of year 1. Estimate the mean and standard deviation of the project with the abandonment option. How much would you pay for the abandonment option? (Hint: You can abandon a project at most once. So in year 5, for example, you abandon only if the sum of future expected NPVs is less than the year 5 abandonment value and the project has not yet been abandoned. Also, once you abandon the project, the actual cash flows for future years are zero. So in this case the future cash flows after abandonment should be zero in your model.)

In Problem 11 from the previous section, we stated that the damage amount is normally distributed. Suppose instead that the damage amount is triangularly distributed with parameters 500, 1500, and 7000. That is, the damage in an accident can be as low as 500 or as high as 7000, the most likely value is 1500, and there is definite skewness to the right. (It turns out, as you can verify in @RISK, that the mean of this distribution is 3000, the same as in Problem 11.) Use @RISK to simulate the amount you pay for damage. Run 5000 iterations. Then answer the following questions. In each case, explain how the indicated event would occur. a. What is the probability that you pay a positive amount but less than 750? b. What is the probability that you pay more than 600? c. What is the probability that you pay exactly 1000 (the deductible)?

If you want to replicate the results of a simulation model with Excel functions only, not @RISK, you can build a data table and let the column input cell be any blank cell. Explain why this works.

The eTech Company is a fairly recent entry in the electronic device area. The company competes with Apple. Samsung, and other well-known companies in the manufacturing and sales of personal handheld devices. Although eTech recognizes that it is a niche player and will likely remain so in the foreseeable future, it is trying to increase its current small market share in this huge competitive market. Jim Simons, VP of Production, and Catherine Dolans, VP of Marketing, have been discussing the possible addition of a new product to the companys current (rather limited) product line. The tentative name for this new product is ePlayerX. Jim and Catherine agree that the ePlayerX, which will feature a sleeker design and more memory, is necessary to compete successfully with the big boys, but they are also worried that the ePlayerX could cannibalize sales of their existing productsand that it could even detract from their bottom line. They must eventually decide how much to spend to develop and manufacture the ePlayerX and how aggressively to market it. Depending on these decisions, they must forecast demand for the ePlayerX, as well as sales for their existing products. They also realize that Apple. Samsung, and the other big players are not standing still. These competitors could introduce their own new products, which could have very negative effects on demand for the ePlayerX. The expected timeline for the ePlayerX is that development will take no more than a year to complete and that the product will be introduced in the market a year from now. Jim and Catherine are aware that there are lots of decisions to make and lots of uncertainties involved, but they need to start somewhere. To this end. Jim and Catherine have decided to base their decisions on a planning horizon of four years, including the development year. They realize that the personal handheld device market is very fluid, with updates to existing products occurring almost continuously. However, they believe they can include such considerations into their cost, revenue, and demand estimates, and that a four-year planning horizon makes sense. In addition, they have identified the following problem parameters. (In this first pass, all distinctions are binary: low-end or high-end, small-effect or large-effect, and so on.) In the absence of cannibalization, the sales of existing eTech products are expected to produce year I net revenues of 10 million, and the forecast of the annual increase in net revenues is 2%. The ePIayerX will be developed as either a low-end or a high-end product, with corresponding fixed development costs (1.5 million or 2.5 million), variable manufacturing costs ( 100 or 200). and selling prices (150 or 300). The fixed development cost is incurred now, at the beginning of year I, and the variable cost and selling price are assumed to remain constant throughout the planning horizon. The new product will be marketed either mildly aggressively or very aggressively, with corresponding costs. The costs of a mildly aggressive marketing campaign are 1.5 million in year 1 and 0.5 million annually in years 2 to 4. For a very aggressive campaign, these costs increase to 3.5 million and 1.5 million, respectively. (These marketing costs are not part of the variable cost mentioned in the previous bullet; they are separate.) Depending on whether the ePlayerX is a low-end or high-end produce the level of the ePlayerXs cannibalization rate of existing eTech products will be either low (10%) or high (20%). Each cannibalization rate affects only sales of existing products in years 2 to 4, not year I sales. For example, if the cannibalization rate is 10%, then sales of existing products in each of years 2 to 4 will be 10% below their projected values without cannibalization. A base case forecast of demand for the ePlayerX is that in its first year on the market, year 2, demand will be for 100,000 units, and then demand will increase by 5% annually in years 3 and 4. This base forecast is based on a low-end version of the ePlayerX and mildly aggressive marketing. It will be adjusted for a high-end will product, aggressive marketing, and competitor behavior. The adjustments with no competing product appear in Table 2.3. The adjustments with a competing product appear in Table 2.4. Each adjustment is to demand for the ePlayerX in each of years 2 to 4. For example, if the adjustment is 10%, then demand in each of years 2 to 4 will be 10% lower than it would have been in the base case. Demand and units sold are the samethat is, eTech will produce exactly what its customers demand so that no inventory or backorders will occur. Table 2.3 Demand Adjustments When No Competing Product Is Introduced Table 2.4 Demand Adjustments When a Competing Product Is Introduced Because Jim and Catherine are approaching the day when they will be sharing their plans with other company executives, they have asked you to prepare an Excel spreadsheet model that will answer the many what-if questions they expect to be asked. Specifically, they have asked you to do the following: You should enter all of the given data in an inputs section with clear labeling and appropriate number formatting. If you believe that any explanations are required, you can enter them in text boxes or cell comments. In this section and in the rest of the model, all monetary values (other than the variable cost and the selling price) should be expressed in millions of dollars, and all demands for the ePlayerX should be expressed in thousands of units. You should have a scenario section that contains a 0/1 variable for each of the binary options discussed here. For example, one of these should be 0 if the low-end product is chosen and it should be 1 if the high-end product is chosen. You should have a parameters section that contains the values of the various parameters listed in the case, depending on the values of the 0/1 variables in the previous bullet For example, the fixed development cost will be 1.5 million or 2.5 million depending on whether the 0/1 variable in the previous bullet is 0 or 1, and this can be calculated with a simple IF formula. You can decide how to implement the IF logic for the various parameters. You should have a cash flows section that calculates the annual cash flows for the four-year period. These cash flows include the net revenues from existing products, the marketing costs for ePlayerX, and the net revenues for sales of ePlayerX (To calculate these latter values, it will help to have a row for annual units sold of ePlayerX.) The cash flows should also include depreciation on the fixed development cost, calculated on a straight-line four-year basis (that is. 25% of the cost in each of the four years). Then, these annual revenues/costs should be summed for each year to get net cash flow before taxes, taxes should be calculated using a 32% tax rate, and taxes should be subtracted and depreciation should be added back in to get net cash flows after taxes. (The point is that depreciation is first subtracted, because it is not taxed, but then it is added back in after taxes have been calculated.) You should calculate the company's NPV for the four-year horizon using a discount rate of 10%. You can assume that the fixed development cost is incurred now. so that it is not discounted, and that all other costs and revenues are incurred at the ends of the respective years. You should accompany all of this with a line chart with three series: annual net revenues from existing products; annual marketing costs for ePlayerX; and annual net revenues from sales of ePlayerX. Once all of this is completed. Jim and Catherine will have a powerful tool for presentation purposes. By adjusting the 0/1 scenario variables, their audience will be able to see immediately, both numerically and graphically, the financial consequences of various scenarios.