Question

Project A: This project requires an initial investment of $20,000,000 in equipment which will cost an additional $3,000,000 to install. The firm will use the attached MACRS depreciation schedule to expense this equipment. Once the equipment is installed, the company will need to increase raw goods inventory by $5,000,000, but it will also see an increase in accounts payable for $1,500,000. With this investment, the project will last 6 years at which time the market value for the equipment will be $1,000,000. The project will project a product with a sales price of $20.00 per unit and the variable cost per unit will be $10.00. It is estimated the sales volume for this project will be 700,000 in year 1, 1,000,000 in year 2, 650,000 in year 3, 700,000 in year 4, 650,000 in year 5 and 550,000 in year 6. The fixed costs would be $2,000,000 per year. Because this project is very close to current products sold by the business, management has expressed some favoritism towards this project and as allowed for a reduced rate of return of 2 percentage point below its current WACC as the valuation hurdle it must meet or surpass.

Create a valuation spreadsheet for each of the projects mentioned above. Evaluate each project according to the following valuation methods: a. Net Present Value of Discounted Cash Flow b. Internal Rate of Return c. Payback Period d. Profitability Index

Modified Accelerated Cost Recovery System (MACRS)

Ownership Year 5-Year Investment Class Depreciation Schedule

1 20%

2 32%

3 19%

4 12%

5 11%

6 6%

Total = 100%

WACC = 12.064%

Answer 1

a)	NPV:	0	1	2	3	4	5	6
	Unit sales		700000	1000000	650000	700000	650000	550000
	Sales revenue		14000000	20000000	13000000	14000000	13000000	11000000
	Variable cost		7000000	10000000	6500000	7000000	6500000	5500000
	Fixed cost		2000000	2000000	2000000	2000000	2000000	2000000
	Depreciation (on 23,000,000)		4600000	7360000	4370000	4370000	2530000	1380000
	Operating income		400000	640000	130000	630000	1970000	2120000
	Tax at 40%		160000	256000	52000	252000	788000	848000
	NOPAT		240000	384000	78000	378000	1182000	1272000
	Add: Depreciation		4600000	7360000	4370000	4370000	2530000	1380000
	Operating cash flow		4840000	7744000	4448000	4748000	3712000	2652000
	Capital expenditure	23000000						-1380000	(Salvage value net of tax)
	Increase in NWC (5000000-1500000)	3500000						-3500000
	Project Cash flows	-26500000	4840000	7744000	4448000	4748000	3712000	7532000
	PVIF at 10.064% (12.064-2.000)	1	0.90856	0.82549	0.75000	0.68143	0.61912	0.56251
	PV at 10.064%	-26500000	4397441	6392559	3336022	3235412	2298167	4236806	23896407
	NPV	-2603593
b)	IRR:
	IRR is that discount rate for which NPV = 0.
	It has to be found out by trial and error as below, by varying the discount rate to get 0 NPV.
	Project Cash flows	-26500000	4840000	7744000	4448000	4748000	3712000	7532000
	PVIF at 6%	1	0.94340	0.89000	0.83962	0.79209	0.74726	0.70496
	PV at 6%	-26500000	4566038	6892132	3734627	3760861	2773822	5309763	537243
	PVIF at 7%	1	0.93458	0.87344	0.81630	0.76290	0.71299	0.66634
	PV at 7%	-26500000	4523364	6763910	3630893	3622226	2646604.7	5018890	-294112
	IRR lies between 6% and 7%.
	The value of IRR can be found out by simple interpolation as below:
	IRR = 6+537423/(537243+294112) =	6.65%
c)	Payback period
	Cumulative project cash flows	-26500000	-21660000	-13916000	-9468000	-4720000	-1008000	6524000
	Payback period = 5+1008000/7532000 =	5.13	Years
d)	PI = PV of cash inflows/Initial investment = 23896407/26500000 =			0.90