Site hosted by Angelfire.com: Build your free website today!

Background

 

Acron is a large drug company. One of its new drugs, Niagara is coming to the market and Acron needs to determine how much annual production to build for this drug. Government regulations make it difficult to add capacity at a later date. Acron wants to develop desires to develop a spreadsheet model of its 20 year cash flow.

 

Objective

 

Acron wish to generate a twenty year cash flow spreadsheet.

 

1. What capacity level should be chosen?

2. How does a change in the discount rate affect the optimal capacity level?

3. How realistic is the model?

 

 

 

Decision and External Variables

 

The decision variable is capacity level. The External variables are demand and monetary values

 

 

Spreadsheet Model

 

Data table for NPV as a function of capacity

Capacity

NPV

 

$146,107

10000

$108,995

11000

$118,259

12000

$126,741

13000

$134,442

14000

$140,697

15000

$146,107

16000

$150,391

17000

$153,708

18000

$156,259

19000

$158,142

20000

$159,335

21000

$159,378

22000

$158,183

23000

$155,483

24000

$151,507

25000

$146,618

26000

$140,907

27000

$134,105

28000

$126,124

29000

$117,490

30000

$108,320

31000

$98,509

32000

$88,019

33000

$77,162

34000

$65,683

35000

$53,740

36000

$41,484

37000

$28,576

38000

$15,527

39000

$1,821

40000

-$12,004

Line Callout 2: 21000 capacity level

 

 

 

Analyses

 

  1. When it comes to determining the capacity, one should create a table (data) and a corresponding chart. Please view the above chart for results.  In order to maximize the NPV, a capacity level of 21000 units would do the trick.
  2. As you change the discount rate, it is difficult for a company to determine the affects as well as the sensitivity of rate. Once again, by setting up a two way table along with a chart you can determine how the discount rate and  capacity level complement one another.
  3. As for the model being realistic, you can say that. A major limitation or constraint involved with the model is uncertainty.

 

Recommendations

 

    Develop a model that addresses uncertainties. A tool that can be used is @Risk or possibly optimization simulations.