Use Solver in MS Excel to analyze the situations described below by answering each of the questions. Submit your solution documents here. They should include:
Your MS Excel spreadsheets containing the models and Solver inputs used to analyze the questions
An MS Word document responding in full to each question, including images of your models in the spreadsheet and the filled-out Solver dialog box supporting your conclusions and recommendations
Huff Enterprises has a capital account for projects and is considering a number of possibilities. Specifically, each project under consideration will draw on the capital account during each of its first three years, but in the long run, each is predicted to achieve a positive net present value (NPV). Listed here are the project alternatives, their net present values, and their capital requirements (all figures are in thousands of dollars). In addition, the amount of capital available in each of the next three years is predicted to be 10,200, 8,400, and 9,600, respectively.
Note that 1) selecting a project means you will fund it all three years, and 2) unused budget from one year is not carried over and added to the budget for the next year–instead, it is reclaimed by corporate.
Projects: One-Phase Expansion Two-Phase Expansion Test Market Advertising Campaign Basic Research Purchase Equipment
NPV: 5,400 6,800 9,600 4,400 8,700 3,500
Year 1 Capital: 3,000 2,500 6,000 2,000 5,000 1,000
Year 2 Capital: 1,000 3,500 4,000 1,500 1,000 500
Year 3 Capital: 4,000 3,500 5,000 1,800 4,000 900
Individual Assignment Portfolio Shell.xlsx
a. Assuming that any combination of the projects is permitted, which ones should Huff select to maximize NPV? What IS the NPV?
b. Copy your model from part a to a new tab in the spreadsheet. Suppose the two expansion projects are mutually exclusive and only one of them can be made. How does this alter the solution in part a?
To add this ability to your model in part a., you only need to add one additional constraint, using the sum of the “investment decisions” of the two expansion projects. Think about all the possibilities for the sum and which you of them you need to exclude. (For example, if one-phase is selected but two-phase is not, the sum of the decisions in cells F2 and F3 will be 1.)
c. Copy your model from part a. to a new tab in the spreadsheet. Suppose that the test market cannot be carried out unless the advertising campaign is also adopted. How does this contingency alter the solution to part a?
To add this ability to your model in part a., you again only need to add one additional constraint: You never want Test Market to be 1 when Ad Campaign is 0. That’s the only combination that would not be accepted. Said in the positive, you always want the Test Market decision value to be less than or equal to the Ad Campaign decision value. (Verify for yourself that this works by mentally checking all four combinations of 0’s and 1’s.) That’s a simple inequality–but remember that in Excel, you must move all the variables on the LHS of the inequality with just a number on the RHS. The LHS will then be a formula in a cell, just like we’ve done all along for any constraint.
