Question

Question 2: Chapter 9 Better Mousetraps has developed a new trap. It can go into production for an initial investment in equipment of $6 million. The equipment will be depreciated straight-line over 6 years to a value of zero, but, in fact, it can be sold after 6 years for $500,000. The firm believes that working capital at each date must be maintained at a level of 10% of next year's forecast sales. The firm estimates production costs equal to $1.50 per trap and believes that the traps can be sold for $4 each. Sales forecasts are given in the following table. The project will come to an end in 5 years when the trap becomes technologically obsolete. The firm's tax bracket is 35%, and the required rate of return on the project is 12% Year: 0 1 2 3 4 5 6 Thereafter Sales (millions of traps) 0 0.5 0.6 1.0 1.0 0.6 0.2 0 a. What is project NPV? b. What is the IRR? c. Payback in Years?

need it in excel format to understand what is going completly need it broken down all the way

Answer 1

Year	0	1	2	3	4	5	6
cost of machine	-6000000
sales -units sold*selling price		2000000	2400000	4000000	4000000	2400000	800000
less production cost = units sold*production cost		750000	900000	1500000	1500000	900000	300000
less annual depreciation = (6000000/6)		1000000	1000000	1000000	1000000	1000000	1000000
operating profit		250000	500000	1500000	1500000	500000	-500000
less taxes-35%		87500	175000	525000	525000	175000	-175000
profit after taxes		162500	325000	975000	975000	325000	-325000
add depreciation		1000000	1000000	1000000	1000000	1000000	1000000
after tax sales proceed of machine -=500000*(1-.35)							325000
annual investment in working capital	-200000	-40000	-160000	0	160000	160000	80000
net operating cash flow	-6200000	1122500	1165000	1975000	2135000	1485000	1080000
present value factor at 12% = 1/(1+r)^n	1	0.892857143	0.79719388	0.711780248	0.635518078	0.567427	0.506631
present value of cash flow = net operating cash flow*present value factor	-6200000	1002232.143	928730.867	1405765.989	1356831.097	842628.9	547161.6
1-net present value = sum of present value of cash flow	-116649.41
2-IRR =Using IRR function in MS excel	IRR(I3217:O3217)	-0.57%
Year	0	1	2	3	4	5	6
net operating cash flow	-6200000	1122500	1165000	1975000	2135000	1485000	1080000
cumulative cash flow		-5077500	-3912500	-1937500
				amount to be recovered in year 4
3-payback period = year before final year of recovery+(amount to be recovered/net operating cash flow of final year of recovery)	3+(1937500/2135000)	3.91
sales -units sold*selling price		2000000	2400000	4000000	4000000	2400000	800000
investment in working capital-10% of next year sale	200000	240000	400000	400000	240000	80000	0
annual investment in working capital	-200000	-40000	-160000	0	160000	160000	80000