1. Refer to HomeNet.xls (use FCFs worksheet; the spreadsheet available on the blackboard.

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. Copy this to excel please.

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

Screenshot of data as shown in excel:

Data for project Formulas Data Review View Developer AutoSave OFF A S UE- Home Insert Draw Page Layout X C alibri (Body) 12 Paste BI U B O | k10 x fox A A = = = A ZA p E Wrap Text Merge & Center General t $ o % / A , SB-SE Conditional Formatting Format as Table Cell Styles Insert Delete For F 0 260 0 1 260 100,000.00 0.25 100 60 260 100,000.00 0.25 100 260 100,000.00 0.25 100 60 60 5 260 0 0.25 100 60 110 0 0 260 100,000.00 0.25 100 60 110 2,800,000.00 200,000.00 110 1 Year 2 Price per unit 3 #of units 4 Expected loss in sales of routers (%%) 5 Price per unit router 6 Cost of Goods Sold Router/unit 7 Cost of Goods Sold/unit 8 Marketing Costs/year 9 Opportunity Cost (Lab) 10 New Equipment/year 11 Depreciation schedule 12 Software Development: 13 Engineers 14 Cost/engineer 15 Design and Engineering Tax Rate 110 100 60 110 0 0 7,500,000.00 110 2,800,000.00 200,000.00 2,800,000.00 200,000.00 2,800,000.00 200,000.00 0.2 0.2 0.2 0.2 50 D 0 0 200,000.00 5,000,000.00 0.4 0 0.4 0.4 0.4 0.4 17 0 0 D D 0 0 3,525,000.00 1,425,000.00 2,100,000.00 2,100,000.00 3,525,000.00 1,425,000.00 2,100,000.00 3,525,000.00 1,425,000.00 2,100,000.00 3,525,000.00 1,425,000.00 2,100,000.00 0 2,100,000.00 18 HomeNet's Net Working Capital Requirements 19 Cash 20 Inventory 21 Receivables (15% Sales) 22 Payables (15% COGS) 23 NWC 24 Change in NWC 25 26 Sales 27 COGS 28 Gross Profit 29 Selling, General, Admin. Expnenses 30 Research and Development 31 Depreciation 32 EBIT 33 Income Tax 34 NI (Unlevered) 0 0 0 0 15,000,000.00 D -15,000,000.00 -6,000,000.00 9,000,000.00 23,500,000.00 9,500,000.00 14,000,000.00 3,000,000.00 D 1,500,000.00 9,500,000.00 3,800,000.00 5,700,000.00 23,500,000.00 23,500,000.00 23,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 14,000,000.00 14,000,000.00 14,000,000.00 3,000,000.00 3,000,000.00 3,000,000.00 0 0 1,500,000.00 1,500,000.00 1,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 3,800,000.00 3,800,000.00 3,800,000.00 5,700,000.00 5,700,000.00 5,700,000.00 1,500,000.00 1,500,000.00 600,000.00 -900,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 0 36 Plus: Depreciation 37 Less Capital Expenditures (Equipm.) 38 Less: change in NWC 39 FCF 0 7,500,000.00 D -16,500,000.00 2,100,000.00 5,100,000.00 7,200,000.00 7,200,000.00 0 -2,100,000.00 7,200,000.00 2,700,000.00 40 41 Cost of capital 42 PV of CFS 43 NPV 0.12 -16,500,000.00 5,025,967.81 4,553,571.43 5,739,795.92 5,124,817.78 4,575,730.16 1,532,052.51