NPV AND LP

Net Present Value and Linear Programming

Net Present Value

Requirement A: Assume a discount rate of 10%. Use Excel to set up a discounted cash flow analysis of the above information, calculating the net present value (NPV) for the investment. Remember that the initial expenditure takes place in 2011

Year

2011

2012

2013

2014

2015

2016

Outflow

 

-30000

-5000

-5000

-5000

-5000

-5000

inflows

 

10,000

15,000

20,000

25,000

30,000

Net cash flow

-30000

5000

10000

15000

20000

25000

Discount rate

10%

Discounted cash flows

-30,000.00

£4,545.45

£8,264.46

£11,269.72

£13,660.27

£15,523.03

Net present value

£23,262.94 From the information in the spreadsheet only, decide whether or not this is a worthwhile investment, giving your reasons.

Answer: The above analysis shows that at 10% discount rate the net present value of all future cash flows is positive (23262), which infers that this investment would be beneficial and we should invest

Explain the effect of discounting in this case upon future inflows, outflows and the NPV

Answer: discounting of cash flows reduces the value of future cash flows as the future cash flows are treated as on zero time line (at current year)

Requirement B: Calculate the NPV for discount rates of 20%, 30%, 40% and 50%, giving your answers to two decimal places.

Year

2011

2012

2013

2014

2015

2016

Outflow

 

-30000

-5000

-5000

-5000

-5000

-5000

inflows

£

 

10,000

15,000

20,000

25,000

30,000

Net cash flow

-30000

5000

10000

15000

20000

25000

Discount rate

10%

Discounted cash flows

(£30,000.00)

£4,545.45

£8,264.46

£11,269.72

£13,660.27

£15,523.03

Net present value

£23,262.94

Discount rate

20%

Discounted cash flows

(£30,000.00)

£4,166.67

£6,944.44

£8,680.56

£9,645.06

£10,046.94

Net present value

£9,483.67

Discount rate

30%

Discounted cash flows

(£30,000.00)

£3,846.15

£5,917.16

£6,827.49

£7,002.56

£6,733.23

Net present value

£326.59

Discount rate

40%

Discounted cash flows

(£30,000.00)

£3,571.43

£5,102.04

£5,466.47

£5,206.16

£4,648.36

Net present value

(£6,005.53)

Discount rate

50%

Discounted cash flows

(£30,000.00)

£3,333.33

£4,444.44

£4,444.44

£3,950.62

£3,292.18

Net present value

(£10,534.98)

Requirement C: Using Excel and the data generated from part (b), plot NPV against the discount rate in the range 10% to 50%.

Requirement D: Use your graph to estimate the discount rate that would give a NPV of zero. What is the significance of this discount rate?

Answer: The graph shows that there is inverse relationship between discount rate and net present value. Therefore, as we increase the discount rate net present value decrease and vice versa. If we look at the graph, we find that net present value becomes zero at discount rate of approximately 30% that means if the cost of capital increases this rate than the investment would not be feasible. Therefore, the cost of capital, which is below 30%, is feasible to accept this investment (where NPV becomes zero that rate is known as “internal rate of return”)

Linear programming

Scenario

Shambles have selected the “Mythical Beasts” range and decided to concentrate on “Pegasus” and “Phoenix.” They would now like to find the right mix of these two products in order to maximise profit. Each toy has to go through two processes during manufacture: sewing and stuffing. Pegasus takes 20 minutes to sew whilst Phoenix takes 30 minutes; there are 15 hours (900 minutes) available for sewing each day. Pegasus takes 15 minutes to stuff whilst Phoenix takes 20 minutes; there are 12 hours (720 minutes) available for stuffing each day.

Decision variables

Decision variable X = Units of Pegasus to be produced

Decision variable Y: Units of Phoenix to be produced

Constraints

20x + 30y = 900 …………………………………………… Sewing

15x + 12y = 720……………………………………………. Stuffing

Objective function

Maximum profit = 5x + 7y

Optimal solution

(15) 20x ...