7. The Chief Financial Officer of Acme Medical Devices has determined that the f

Question

7. The Chief Financial Officer of Acme Medical Devices has determined that the firm’s capital investment budget will be $5,000,000 for the upcoming year. Unfortunately, this amount is not sufficient to cover all of the positive-NPV projects available to the firm. Project ct Cost NPV A $1,132,524 $106,728 B 448,122 50,524 C 1,925,371 244,053 D 2,308,092 77,709 E 1,625,001 15,277 F 457,853 66,922 G 1,810,303 107,166 H 1,939,862 69,015 I 701,788 52,614 J 2,367,845 49,296 You have been asked to choose which investments should be made. A. Using Solver, determine which of the above projects should be included in the budget if the firm's goal is to maximize shareholder wealth. (Note: Set Solver to use the Simplex LP method, and turn off the Ignore Integer Constraints setting.) B. Now assume that the CFO has informed you that projects A and B are mutually exclusive, but one of them must be selected. Change your Solver constraints to account for this new information and find the new solution. (Use the same options as in part a.) C. Ignore the constraints from part B. The CFO has now informed you that Project I is of great strategic importance to the survival of the firm. It must be accepted. Which projects should be accepted?

I need the steps to solve in Solver!

Explanation / Answer

First cature the data in spreasheet in varioous columns
Column A - Project Name, Column B - Cost, Column C - NPV

Now to set up problem for solvver add Project quantity as column D (which in solver we shall specify to take value either 0 or 1) . Project which get selected shall have quantity 1 and which do not 0.

Now total cost of various projects which get selected is sumproduct (Column B, Column D). Let this be input in column E13. In sovler we shall subject this to toal funds available which is $ 5 Million

Similarly total NPV of various projects can be input in cell F13 as sumproduct(column C, column D)

Now we are set up to use Solver

Click on Solver

Solution to A

1. Set objective to maximise cell $F$13 since we want to maximise total NPV as it shall maximise sharehold wealth
2. by changind cells $D$2:$D$11
3.subject to following constraints a)$D$2:$D$11 >=0, b)$D$2:$D$11 <=1 (since a project can not be chosen more than once) and $E$13 <= 5,000,000 (total funds available to us)

Click on Solve. Only projects which have 1 in column D against them are thus selected which are project A,B,C,F and I. You need to ignroe fractions as projects cant be partially chosen

Solution to B

Solution to B is simialr. We just need to add one more constratints. Since project A and B are mutually exclusive but one has to be chosen, let cell D13 capture sum of qty of prjects A and B. Go to solver and add one more constraint $D$13 = 1. Click on solve. Now projects which have 1 againts them and are thus selected are A, C and F.

Solution to C

Solution to C is similar to A. Just delete row containting I and run solver with change in constraints of total cost to (5000000 - 701788) = 4298212. This will throw project A, B, C and F as having 1 in column D and thus selected

Related Questions

Navigate

• Browse (All)
• Browse 7
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.