Question

Sensitivity Analysis
Consider the project where the initial cost is $200,000, and the project has a 5-year life. There is no salvage. Depreciation is straight-line (Depreciation = 200,000/5 = 40,000)
Unit Sales = 6000, Price per unit = $80 (Sales = 6,000 x 80)
Variable cost per unit = $60 (Variable Costs = 6,000 x 60)
The required return is 12%, and the tax rate is 21%  What are the cash flow each year, NPV and IRR in each case, if we changed fixed costs only?

Answer 1

		Fixed Costs Considered	$40,000	$50,000	$60,000	$70,000	$80,000
			FIXED COST1	FIXED COST2	FIXED COST3	FIXED COST4	FIXED COST 5
	A	Annual Sales Revenue=6000*80=	$480,000	$480,000	$480,000	$480,000	$480,000
	B	Annual Variable Costs=6000*6000*60=	($360,000)	($360,000)	($360,000)	($360,000)	($360,000)
	C	Fixed Costs	($40,000)	($50,000)	($60,000)	($70,000)	($80,000)
	D	Annual Depreciation expense	($40,000)	($40,000)	($40,000)	($40,000)	($40,000)
	E=A+B+C+D	Before Tax Annual Profit	$40,000	$30,000	$20,000	$10,000	$0
	F=E*21%	Tax Expense/Savings	($8,400)	($6,300)	($4,200)	($2,100)	$0
	G=E+F	After Tax annual Profit	$31,600	$23,700	$15,800	$7,900	$0
	H	Add: Depreciation (Non Cash Expense)	$40,000	$40,000	$40,000	$40,000	$40,000
	CF=G+H	CASH FLOW IN EACH YEAR 1-5	$71,600	$63,700	$55,800	$47,900	$40,000
	Rate	Discount Rate=Required Return	12%	12%	12%	12%	12%
	Nper	Number of Years of Cash Inflow	5	5	5	5	5
	Pmt	Annual Cash Inflow	$71,600	$63,700	$55,800	$47,900	$40,000
	PV	Present Value of Cash Flow	$258,102	$229,624	$201,147	$172,669	$144,191
		(Using PV function of excel)
	I	Initial Cash Flow	($200,000)	($200,000)	($200,000)	($200,000)	($200,000)
	NPV=PV+I	Net Present Value	$58,102	$29,624	$1,147	($27,331)	($55,809)
	RATE	Internal Rate of Return(IRR)	23.17%	17.82%	12.23%	6.33%	0.00%
		(Using RATE function of excel)