Question

(Please show work on excel) We are evaluating a project that costs $1,68 million has a six-year life, and has no salvage value. Assume that depreciation is straight-line to zero over the life of the project. Sales are projected at 90,000 units per year. Price per unit is $37.95, variable cost per unit is $23.20, and fixed costs are $815,000 per year. The tax rate is 21 percent, and we require a return of 11 percent on this project. Calculate the base-case operating cash flow and NPV What is the sensitivity of NPV to changes in the sales figure? Explain what your answer tells you about a 500 unit decrease in projected sales. (Round the answer with 2 decimals places). What is the sensitivity of OCF to changes in the variable cost figure? (A negative answer should be indicated by a minus sign.) Explain what your answer tell you If there is $1 decrease in estimated variable costs.(Do not round intermediate calculations and round your answer to the nearest whole number, e.g., 32.) Increase in OCF $

Answer 1

NOTE: In case of multiple sub part only first 4 will be answered

depreciation
1680000/6=	280000

	1	2	3	4	5	6	total
selling price	37.95	37.95	37.95	37.95	37.95	37.95
less: variable cost	23.2	23.2	23.2	23.2	23.2	23.2
contribution	14.75	14.75	14.75	14.75	14.75	14.75
units	90000	90000	90000	90000	90000	90000
total contribution	1327500	1327500	1327500	1327500	1327500	1327500
less: fixed cost	815000	815000	815000	815000	815000	815000
profit	512500	512500	512500	512500	512500	512500
less: depreciation	280000	280000	280000	280000	280000	280000
profit after dep	232500	232500	232500	232500	232500	232500
tax 21%	48825	48825	48825	48825	48825	48825
profit after tax	183675	183675	183675	183675	183675	183675
add: dep	280000	280000	280000	280000	280000	280000
total cash inflow	463675	463675	463675	463675	463675	463675
discount 11%	0.901	0.812	0.731	0.659	0.593	0.535	4.231
PV of Cash inflow	417771.2	376504.1	338946.4	305561.8	274959.28	248066.1	1961809
cash outflow							1680000
NPV							281808.9

Sensitivity Analysis
Variable	Pessimistic	Expected	Optimistic
sale units	80000	90000	100000
contribution	14.75	14.75	14.75
total contribution	1180000	1327500	1475000
less: fixed cost	815000	815000	815000
profit	365000	512500	660000
less: depreciation	280000	280000	280000
profit after dep	85000	232500	380000
tax 21%	17850	48825	79800
profit after tax	67150	183675	300200
add: dep	280000	280000	280000
total cash inflow	347150	463675	580200
discount 11%	4.231	4.231	4.231
PV of Cash inflow	1468791.7	1961809	2454826
cash outflow	1680000	1680000	1680000
NPV	-211208.4	281808.9	774826.2

A 500 unit decrease (90000-500=89500) decreases the NPV but it remains positive.

sale units	89500
contribution	14.75
total contribution	1320125
less: fixed cost	815000
profit	505125
less: depreciation	280000
profit after dep	225125
tax 21%	47276.25
profit after tax	177848.75
add: dep	280000
total cash inflow	457848.75
discount 11%	4.231
PV of Cash inflow	1937158.1
cash outflow	1680000
NPV	257158.06