Question

You work in the Finance Division of a medium size company that is considering a project to supply a customer with 50,000 widgets annually. You will need an initial investment of $4,000,000 in new equipment to get the project started and you estimate that this project will remain active for five years.

The Accounting Department estimated $1,000,000 in annual fixed costs and a variable costs of $200 per unit. Additionally, they told you they will depreciate the initial fixed asset investment straight-line to zero over the five-year project life. Additionally, they are expecting a salvage value of $500,000 after dismantling costs.

The Marketing Department is confident that they will be able to negotiate a contract with the customer to pay $300 per unit. Finally, the Engineering Department informed that they will need an initial net working capital investment of $350,000.

You require a return of 15 percent and face a marginal tax rate of 40 percent on this project.

1) Suppose you’re confident about your own projections, but you’re not sure if your customer really needs the 50,000 widgets annually.

A) What is the sensitivity of the project OCF to changes in the quantity supplied?

B) What about the sensitivity of NPV to changes in quantity supplied?

C) Given the sensitivity number you calculated, is there some minimum level of output below which you wouldn’t want to operate? Why?

D) Find the degree of operating leverage for the company using the base case of 50,000 widgets. How does this number compare to the sensitivity figure from 3? Check that either approach will give you the same OCF figure at a new quantity level. You can use the following formula to compute the degree of operating leverage:

DOL = 1 + [FC × (1 − T) − T × D]/OCF

*Please No Handwritten explanations. Also, please provide explanation on how to asses and answer these types of questions.*

Answer 1

a.No.of widgets	50000
b.Selling price/widget	300
c.Variable cost/widget	200
Year	0	1	2	3	4	5
1.Initial Investment	-4000000
2.NWC reqd. &recovered	-350000					350000
3.After-tax salvage(500000*(1-40%))						300000
Operating cash flows:
4.Sales revenue(a*b)		15000000	15000000	15000000	15000000	15000000
5.Variable cost(a*c)		-10000000	-10000000	-10000000	-10000000	-10000000
6.Fixed costs		-1000000	-1000000	-1000000	-1000000	-1000000
7.Depreciation(4000000/5)		-800000	-800000	-800000	-800000	-800000
8.EBT(sum4 to 8)		3200000	3200000	3200000	3200000	3200000
9.Tax at 40%(Row 8*40%)		-1280000	-1280000	-1280000	-1280000	-1280000
10.EAT(8+9)		1920000	1920000	1920000	1920000	1920000
11. Add back: Depn.(Row 7)		800000	800000	800000	800000	800000
12. Operating Cash flow		2720000	2720000	2720000	2720000	2720000
13.Net annual FCFs(1+2+3+12)	-4350000	2720000	2720000	2720000	2720000	3370000
14.PV F at 15%(1/1.15^Yr.n	1	0.86957	0.75614	0.65752	0.57175	0.49718
15.PV at 15%(13*14)	-4350000	2365217	2056711	1788444	1555169	1675486
16. NPV(sum of Row 15)	5091027

1.A. Sensitivity of OCF to changes in quantity supplied
	Units	OCF	Increase/(Decrease) in OCF(Case OCF-Base Case OCF)	Inc./(Dec.)/10000 units
Base case	50000	2720000
Increase of 10000 units	60000	3320000	600000	60
Decrease of 10000 units	40000	2120000	-600000	-60
Thus for a change of 1 widget supplied , OCF changes by $ 60

1.B. Sensitivity of NPV to changes in quantity supplied
	Units	NPV	Increase/(Decrease) in NPV	Inc./(Dec.)/10000 units
Base case	50000	5091027
Increase of 10000 units	60000	7102320	2011293	201.13
Decrease of 10000 units	40000	3079734	-2011293	-201.13
Thus for a change of 1 widget supplied , NPV changes by $ 201.13

1.C.YES.

The minimum level, is that Quantity supplied , at which the NPV of the project is 0

That quantity , we can find by using the GOAL SEEK function in EXCEL

ie.in the Excel sheet, we must choose --under Data --- under What-if analysis-- Click Goal seek

Under Goal Seek-- pl. do the foll.

Under --Set cell -- select the cell containing the NPV Value , (here, row 16 , 2nd column)

to Value ----0

By changing Cell--- cell no. containing the quantity of widgets( here, the very first row a.2nd column.)

You can see the quantity cell displaying the minimum qty.

ie. 24687.79

or 24688 widgets

Alternatively, instead of Goal Seek,

$ 201.13 is NPV for   1 widget

so,the $ 5091027 NPV becomes 0

if the qty. is reduced by

5091027/201.13= 25312.12 widgets

ie. When the qty. is

50000-25312.12=

24687.88

24688

widgets

1.D. DOL = 1 + [FC × (1 − T) − T × D]/OCF

DOL =1+(1000000*(1-40%)-(40%*800000)/2720000)=

600001

Both are same (600000)

For such types of sensitivity analysis, you need to construct the Base case ,in such a way, when you change the variable(which you are asked to vary), the required output is obtained.

Like here, I've kept the qty., selling price & variable cost , in separate cells & further calculations , for the operating cash flows sections, are doen by referring to these cell-values.

You need to link the cells , by calculating for the first column --ie., here I calculated , with reference to the above cells, for the yr. 1 column & put Yr2 =yr. 1 & copied formula for yr. 2 to the rest of the columns, ie. Which will take values from teh previous cell.Thus when a value is changed in teh Yr. 1 cell, all the other 4 yrs' cells automatically.