PLEASE SOLVE USING EXCEL AND SHOW COMPUTATIONS! Swanson Biotech Company is evalu

Question

PLEASE SOLVE USING EXCEL AND SHOW COMPUTATIONS!

Swanson Biotech Company is evaluating several development projects for experimental drugs. Although the cash flows are difficult to forecast, the company has come up with the following estimates of its initial capital requirements and NPVs for the projects. Given a wide variety of staffing needs, the company has also estimated the number of research scientists required for each development product ( all cost values re given in millions of dollars).

Project Number Initial capital No. of Research scientists NPV

I $10 2 $10.1

II 15 3 $19.0

III 15 4 $22.0

IV 20 3 $25.0

V 30 12 $60.2

a. Suppose that Swanson has a total capital budget of $60 million. How should it prioritize these projects?

b. Suppose that Swanson currently has 12 research scientists and does not anticipate being able to hire more in the near future. How should Swanson prioritize these projects?

Explanation / Answer

In this question our total budget = $60 million

We have given various projects with their NPV and their cost

So we will choose a combination of projects that will maximize total NPV and is within our budget

So we have to make combination of projects that will be in budget of $60 million and out of them we will choose the combination with highest NPV

a) In this case the combination are following

All other combination will be having total budget of either less than $60 million or more

We can take combination of project with less than total budget but in this case all those combinations are producing less total NPV so we are considering only the above combinations

In Combination 1 total NPV = 10.1+19+22+25 = 76.1

In Combination 2 total NPV = 10.1+25+60.2 = 95.3

In Combination 3 total NPV = 19+22+60.2 = 101.2

So from all these combinations we will choose combination 3 as this is producing highest NPV od 101.2 million

Therefore the projects choosen are Project 2, Project 3 and Project 5

b) In this case with capital budget we also have constraint of number of research scientist i.e. 12

So we can either choose project 5 or all other projects

Project 5 - Number of research scientist = 12 and total capital of 30 and it will produce NPV of 60.2

All other projects - Number of research scientist = 2+3+4+3 = 12 and total capital = 10+15+15+20 = 60 and it will produce NPV of 10.1+19+22+25 = 76.1

As all other projects are producing higher NPV so we will choose that

Therefore, combination of projects choosen is Project1, Project 2, Project 3, Project 4

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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