Question

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

Answer 1

Following table gives the values mentioned in the question:

Year	0	1	2	3	4	5
Price per unit		250	260	260	230	0
# of units	0	1,00,000.00	1,00,000.00	1,00,000.00	1,00,000.00	0
Expected loss in sales of routers (%%)	0%	25%	25%	25%	25%	25%
Price per unit (router)	100	100	100	100	100	100
# of units (router)	0	20000	20000	20000	20000	0
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	28,00,000.00	28,00,000.00	28,00,000.00	28,00,000.00	0
Opportunity Cost (Lab)	0	2,00,000.00	2,00,000.00	2,00,000.00	2,00,000.00	0
New Equipment/year	75,00,000.00	0	0	0	0	0
Depreciation schedule	20%	32%	19%	12%	12%	6%
Software Development:
Engineers	50	0	0	0	0	0
Cost/engineer	2,00,000.00	0	0	0	0	0
Design and Engineering	50,00,000.00	0	0	0	0	0
Tax Rate	40%	40%	40%	40%	40%	40%

The following table give HomeNet's Net Working Capital Requirements:

Year	0	1	2	3	4	5
Cash	-	-	-	-	-	-
Inventory	-	-	-	-	-	-
Receivables (15% Sales)	-	37,50,000	39,00,000	39,00,000	34,50,000	-
Payables (15% COGS)	-	14,25,000	14,25,000	14,25,000	14,25,000	-
NWC	-	23,25,000	24,75,000	24,75,000	20,25,000	-
Change in NWC	-	23,25,000	1,50,000	-	-4,50,000	-20,25,000

The following table give HomeNet's Profit & loss statement:

Year	0	1	2	3	4	5
Sales	-	2,70,00,000	2,80,00,000	2,80,00,000	2,50,00,000	-
COGS	-	1,22,00,000	1,22,00,000	1,22,00,000	1,22,00,000	-
Gross Profit	-	1,48,00,000	1,58,00,000	1,58,00,000	1,28,00,000	-
Selling, General, Admin. Expnenses	-	30,00,000	30,00,000	30,00,000	30,00,000	-
Research and Development	1,50,00,000	-	-	-	-	-
Depreciation	15,00,000	24,00,000	14,40,000	8,64,000	8,64,000	4,32,000
EBIT	-1,65,00,000	94,00,000	1,13,60,000	1,19,36,000	89,36,000	-4,32,000
Income Tax	-	37,60,000	45,44,000	47,74,400	35,74,400	-
NI (Unlevered)	-1,65,00,000	56,40,000	68,16,000	71,61,600	53,61,600	-4,32,000

The NPV calculation and its sensitivity are as follows:

Plus: Depreciation	15,00,000	24,00,000	14,40,000	8,64,000	8,64,000	4,32,000
Less:Capital Expenditures (Equipm.)	75,00,000	-	-	-	-	-
Less: change in NWC	-	23,25,000	1,50,000	-	-4,50,000	-20,25,000
FCF	-2,25,00,000	57,15,000	81,06,000	80,25,600	66,75,600	20,25,000
Cost of capital	12%
PV of CFS	-2,25,00,000	51,02,679	64,62,054	57,12,464	42,42,464	11,49,039
NPV	1,68,700

The Sensitivity of NPV to the Cost of Capital

Cost of Capital	NPV
12.00%	1,68,700
10.00%	12,41,270
10.25%	11,02,809
10.50%	9,65,633
10.75%	8,29,726
11.00%	6,95,073
11.25%	5,61,659
11.50%	4,29,468
11.75%	2,98,487
12.00%	1,68,700
12.25%	40,093
12.50%	-87,349
12.75%	-2,13,637
13.00%	-3,38,788
13.25%	-4,62,814
13.50%	-5,85,728
13.75%	-7,07,544
14.00%	-8,28,274

The below graph is a graphical representation of the NPV sensitivity: