Problem 1

Consider modeling a typical retirement plan. Suppose than an employee starts working after college at 22 a a starting salary of $\$ 50,000$. She expects an average salary increase of \(3 \%\) each year. Her retirement plan requires that she contribute \(8 \%\) of her salary, and her employer adds an additional \(15 \%\) of her contributions. She anticipates an annual return of \(8 \%\) on her retirement portfolio.

1. Build a spreadsheet model to determine the size of the portfolio when she is 50 . This is a bit tricky in terms of when the investments are made and when they earn return. Make the appropriate assumption and proceed. Explain your assumption.
2. Modify the above spreadsheet to include the assumptions that the annual salary increase is triangular with parameters \((1 \%, 3 \%, 5 \%)\), and that the annual investment return is triangular with parameters \((5 \%, 8 \%, 9 \%)\). Answer the following questions for the revised model.

1. How much money should she expect to have in her retirement fund at age 50 ?
2. What is the probability that she will have more than $\$ 500,000$ in her retirement fund when she reaches age 50 ?
3. Use the decision tool in CB to change employer contributions to \(10 \%, 15 \%\), and \(20 \%\). Answer part (b) above for the three levels of contributions.

Problem 2 : Aggie Clothing Company manufactures shirts, shorts, pants, skirts, and jackets. Each type of clothing requires them to acquire the appropriate type of machinery. The machinery needed to manufacture each type of clothing must be rented at the weekly rates shown in Table I. This table also lists the amounts of cloth and labor required per unit of clothing, as well as the selling price and the unit variable cost for each type of clothing. There are 4000 labor hours and 4500 square yards (sq yd) of cloth available in a given week. The company wants to find a solution that maximizes its weekly profit.

Develop a linear model for Solver that can be used to maximize the company's profit, correctly accounting for fixed costs and staying within resource availabilities.

Problem 3

PrimeCo produces tomato sauce at five different plants. The capacity (in tons) of each plant is given in Table I. The tomato sauce is stored at one of three warehouses. The cost per ton of producing tomato sauce at each plant and shipping it to each warehouse is given in Table II. PrimeCo has four customers. The cost of shipping a ton of sauce from each warehouse to each customer is given in Table III. Each customer must receive the amount (in tons) of sauce given in Table TV. The annual fixed cost of operating each plant and warehouse is listed in Table V. PrimeCo's goal is to minimize the annual cost (variable and fixed) of meeting customer demands. The company wants to determine which plants and warehouses to open, as well as the optimal shipping plan.

Problem 4

Set up the Monte Carlo simulation model for the Gentle Lentil Restaurant case in Chapter of 5 (Bertsimas and Freund; pages 219-222) and answer the following assignment questions (ignore the assignment questions in the book):

1. Without considering the partnership opportunity, what would be Sanjay's expected monthly salary at Gentle Lentil? What would be a \(95 \%\) confidence interval for Sanjay's monthly salary?
2. Repeat (a) considering Sanjay's partnership opportunity with his aunt.
3. Based on your analysis in (a) and (b), how should Sanjay proceed? Go with the consulting firm, sole proprietorship or partnership with his aunt? Defend your decision with a thorough explanation.
4. The questions above relate to estimating Sanjay's one month salary. I'd like you to revise your simulations above to estimate Sanjay's annual salary. In other words, you should modify your simulation models above for 12 months (you will now have 12 columns, instead of one) with the following stipulations:

The number of meals sold changes each month according a normal distribution with mean =3000 and standard deviation =1000.

Revenue per meal stays the same each month. This means you will generate revenue per meal once in Month 1, according to the distribution in Table 5.12, and use it for all 12 months.

Labor costs stay the same for each month. This means you will generate labor costs once in Month 1, according to the uniform distribution in the case, and use it for all 12 months.

All other input are as before.

Now repeat your analysis in (a), (b), and (c) above for the annual salary case.

Problem 5

Capital Budgeting at Franklin Chemicals: Division managers at Franklin Chemicals are proposing a phased expansion of their manufacturing facilities. They plan to build a new, commercial-scale plant immediately to exploit innovations in processing technology. And then they anticipate further investments, three years out, to expand the plant's capacity and to enter two new markets. The initial investment (Phase I) is obviously strategic because it creates the opportunity for subsequent growth. The Phase II investment is discretionary in that they have the option not to invest if the market conditions are not favorable. Table 1 below contains the cash flow projections for the entire project (initial investment plus subsequent investment in three years). Table 2 (provided as an Excel spreadsheet) contains rearranged cash flows where the entire project is unbundled into Phase I investment and Phase II investment.

While some of the questions below involve Monte Carlo simulation, for each question what I would like to see is a well-reasoned and thorough discussion of your answer and your decision process. Explain your answers and position as if you are entrusted with the responsibility of making the final call on this investment.

1. What is the terminal value of the free cash flows at a \(5 \%\) growth rate and \(12 \%\) discount rate?
2. What is the NPV for this project at \(12 \%\) ? What is the NPV at \(12.03 \%\) ? Should Franklin invest in this project? Discuss why.
3. Review the data carefully and give me the reasons why using the static NPV (the way you calculate it now) might be problematic for this capital budgeting decision. How do you propose we try to overcome these problems to the extent possible?
4. The cash flow in Year 3 looks very lumpy due to the additional investment for expansion. Because the investment is discretionary (we have the right to invest but not the obligation) should the cash flow in Year 3 be discounted at the same rate as other cash flows? Explain.
5. Table II provides the unbundled cash flows (Phase I and Phase II). Compute the NPV (at \(12 \%\) ) for each phase separately. Add the two NPVs to obtain the NPV for the entire project.
6. The uncertainty in the discount rate will play an important role in the uncertainty of the value of this investment (NPV). Now introduce discount rate as an assumption in Crystal Ball with a normal distribution of mean \(12.0 \%\) and standard deviation \(0.5 \%\) (use Table 1 for your simulation). Notice we are introducing a small but realistic uncertainty into the discount rate. What is the probability that Franklin Chemical should accept this investment opportunity? Contrast this probability with your answer in (2) above and make some observations about the NPV from the expectation world (without any uncertainty) and NPV with uncertainties incorporated.
7. What is the current tax rate used for this DCF analysis? Change the tax rate as an assumption with a uniform distribution between \(34 \%\) and \(38 \%\). What is the probability that the NPV is positive with these uncertainties on discount and tax rates? View this as a continuation of (6) with the distribution on discount rate still in place.

Price: $48.76

Solution: The downloadable solution consists of 25 pages, 2376 words and 21 charts.
Deliverable: Word Document