1. Refer to excel data given below.

Modify model for projection of free cash flows from the Home Net project along the following dimensions:

1. Assume the equipment needed for the project is depreciated according to MACRS 5-year depreciation schedule:

5-year MACRS:

Year 0: 20%

Year 1: 32.0%

Year 2: 19.2%

Year 3: 11.52%

Year 4: 11.52%

Year 5: 5.76%

2. The equipment is sold at the end of year 4 for $0.5 m
3. One of every five customers expected to buy a Home Net device would have bought a Cisco router if Home Net devices were not available
4. In order to maintain constant number of units (100,000/year) sold over the duration of the project Cisco is planning to offer an introductory price of $250/unit in the first year, and going to reduce the price to $230/unit in the fourth year. In the second and the third year, as customers get to appreciate the new device, Cisco is hoping to be able to sell the gadgets for $260 per unit
5. Assume that the Net working capital is recovered as soon as the production is over (at the end of year 4)
6. Estimate NPV of the project assuming that r_wacc= 12%
7. Change your assumption about cost of capital and try several values above and below the initial value of 12%. Construct NPV-sensitivity-to-r_waccgraph (r_waccvalues must be on the X-axis, corresponding values of NPV on the Y-axis)

Data for the question:

Year		0	1	2	3	4	5
Price per unit		260	260	260	260	260	260
# of units		0	100,000.00	100,000.00	100,000.00	100,000.00	0
Expected loss in sales of routers (%%)		0	0.25	0.25	0.25	0.25	0.25
Price per unit (router)		100	100	100	100	100	100
Cost of Goods Sold Router/unit		60	60	60	60	60	60
Cost of Goods Sold/unit		110	110	110	110	110	110
Marketing Costs/year		0	2,800,000.00	2,800,000.00	2,800,000.00	2,800,000.00	0
Opportunity Cost (Lab)		0	200,000.00	200,000.00	200,000.00	200,000.00	0
New Equipment/year		7,500,000.00	0	0	0	0	0
Depreciation schedule		0	0.2	0.2	0.2	0.2	0.2
Software Development:
Engineers		50	0	0	0	0	0
Cost/engineer		200,000.00	0	0	0	0	0
Design and Engineering		5,000,000.00	0	0	0	0	0
Tax Rate		0.4	0.4	0.4	0.4	0.4	0.4
HomeNet's Net Working Capital Requirements
Cash		0	0	0	0	0	0
Inventory		0	0	0	0	0	0
Receivables (15% Sales)		0	3,525,000.00	3,525,000.00	3,525,000.00	3,525,000.00	0
Payables (15% COGS)		0	1,425,000.00	1,425,000.00	1,425,000.00	1,425,000.00	0
NWC		0	2,100,000.00	2,100,000.00	2,100,000.00	2,100,000.00	0
Change in NWC		0	2,100,000.00	0	0	0	-2,100,000.00
Sales		0	23,500,000.00	23,500,000.00	23,500,000.00	23,500,000.00	0
COGS		0	9,500,000.00	9,500,000.00	9,500,000.00	9,500,000.00	0
Gross Profit		0	14,000,000.00	14,000,000.00	14,000,000.00	14,000,000.00	0
Selling, General, Admin. Expnenses		0	3,000,000.00	3,000,000.00	3,000,000.00	3,000,000.00	0
Research and Development		15,000,000.00	0	0	0	0	0
Depreciation		0	1,500,000.00	1,500,000.00	1,500,000.00	1,500,000.00	1,500,000.00
EBIT		-15,000,000.00	9,500,000.00	9,500,000.00	9,500,000.00	9,500,000.00	-1,500,000.00
Income Tax		-6,000,000.00	3,800,000.00	3,800,000.00	3,800,000.00	3,800,000.00	-600,000.00
NI (Unlevered)		-9,000,000.00	5,700,000.00	5,700,000.00	5,700,000.00	5,700,000.00	-900,000.00
Plus: Depreciation		0	1,500,000.00	1,500,000.00	1,500,000.00	1,500,000.00	1,500,000.00
Less:Capital Expenditures (Equipm.)		7,500,000.00	0	0	0	0	0
Less: change in NWC		0	2,100,000.00	0	0	0	-2,100,000.00
FCF		-16,500,000.00	5,100,000.00	7,200,000.00	7,200,000.00	7,200,000.00	2,700,000.00
Cost of capital		0.12
PV of CFS		-16,500,000.00	4,553,571.43	5,739,795.92	5,124,817.78	4,575,730.16	1,532,052.51
NPV		5,025,967.81