Question

Rocky Road Bikes is evaluating the possibility of offering a new top-of-the-line bike model. The company’s market research team estimates that first year sales of the new model will be 25,000 units at the proposed price of $750.00 per unit and estimated cost per unit of $275.00. Sales are expected to grow 4% per year in each of the following 5 years, until the new model is discontinued at the end of year 6. The market research team also estimates the introduction of the new model will erode sales of the current top model by a constant 5,000 units per year. The current top model has a price of $500.00 and associated costs of $145.00.The required tooling and machinery to manufacture the new model will cost a total of $15,000,000, and this will be depreciated on a straight-line basis over the six-year life of the project to zero. The company expects to be able to sell the machinery at the end of the project for $3,000,000. There will also be fixed costs associated with the new model of $6,000,000 per year. The new model will require an increase in working capital of $250,000 which will be returned at the end of the project. Rocky Road has a tax rate of 35%, and management believes that the discount rate for this project should be 12%.

What is the NPV of this project?

Please use excel, this is how I need to answer it and it's confusing to me

Answer 1

NPV = sum of present value of cash flows - initial investment

sum of present value of cash flows = year 1 cash flow/(1+discount rate) + year 2 cash flow/(1+discount rate)2 ... + year 6 cash flow/(1+discount rate)6

Tax on sale machinery = (sale value - book value)*tax rate

book value of machinery at the end of project's life is zero. so, tax will be applicable on entire sale value.

	Years	0	1	2	3	4	5	6
	cost of machinery	-$15,000,000	0	0	0	0	0	0
	increase in working capital	-$250,000	0	0	0	0	0	0
	Sales units	0	25,000	26,000	27,040	28,122	29,246	30,416
	Sales price/unit	0	$750	$750	$750	$750	$750	$750
	cost/unit	0	$275	$275	$275	$275	$275	$275
	Sales	$0	$18,750,000	$19,500,000	$20,280,000	$21,091,200	$21,934,848	$22,812,242
Less:	cost	$0	$6,875,000	$7,150,000	$7,436,000	$7,733,440	$8,042,778	$8,364,489
Less:	loss of sales of current model	$0	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000
Add:	savings of cost of current model	$0	$725,000	$725,000	$725,000	$725,000	$725,000	$725,000
Less:	Fixed cost	$0	$6,000,000	$6,000,000	$6,000,000	$6,000,000	$6,000,000	$6,000,000
Less:	Depreciation	$0	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000
	Pre-tax cash flow	$0	$1,600,000	$2,075,000	$2,569,000	$3,082,760	$3,617,070	$4,172,753
Less:	Taxes @35%	$0	$560,000	$726,250	$899,150	$1,078,966	$1,265,975	$1,460,464
	After-tax cash flow	$0	$1,040,000	$1,348,750	$1,669,850	$2,003,794	$2,351,096	$2,712,290
Add back:	Depreciation	$0	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000	$2,500,000
Add back:	recovery of working capital	$0	$0	$0	$0	$0	$0	$250,000
Add:	Sale of equipment	$0	$0	$0	$0	$0	$0	$3,000,000
Less:	Tax on sale of machinery	$0	$0	$0	$0	$0	$0	$1,050,000
	Operating cash flow	-$15,250,000	$3,540,000	$3,848,750	$4,169,850	$4,503,794	$4,851,096	$7,412,290
	NPV	$3,317,112.20

Calculations and formulas