please show work

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_wacc graph (r_wacc values must be on the X-axis, corresponding values of NPV on the Y-axis)

0 260.00 260.00 260.00 260.00 260.00 0.00 100,000.00 100,000.00 100,000.00 100,000.00 0.00 0.25 0.25 0.25 0.25 100.00 100.00 100.00 100.00 100.00 60.00 60.00 60.00 60.00 60.00 110.00 110.00 110.00 110.00 110.00 0.00 2,800,000.00 2,800,000.00 2,800,000.00 2,800,000.00 0.00 200,000.00 200,000.00 200,000.00 200,000.00 7,500,000.00 0.00 0.00 0.00 0.00 0.00 0.20 0.20 0.20 0.20 260.00 0.00 0.25 100.00 60.00 110.00 Year Price per unit # of units Expected loss in sales of routers (% Price per unit (router) Cost of Goods Sold Router/unit Cost of Goods Sold/unit Marketing Costs/year Opportunity Cost (Lab) New Equipment/year Depreciation schedule Software Development: Engineers Cost/engineer Design and Engineering Tax Rate 0.00 0.20 0.00 50.00 200,000.00 5,000,000.00 0.40 0.00 0.00 0.00 0.40 0.00 0.00 0.40 0.00 0.00 0.00 0.40 0.00 0.00 0.00 0.40 HomeNet's Net Working Capital Requirements Cash 0.00 0.00 0.00 0.00 0.00 Inventory 0.00 0.00 0.00 0.00 0.00 0.00 Receivables (15% Sales) 0.00 3,525,000.00 3,525,000.00 3,525,000.00 3,525,000.00 0.00 Payables (15% COGS) 0.00 1,425,000.00 1,425,000.00 1,425,000.00 1,425,000.00 0.00 NWC 0.00 2,100,000.00 2,100,000.00 2,100,000.00 2,100,000.00 0.00 Change in NWC 0.00 2,100,000.00 0.00 0.00 0.00 -2.100,000.00 0.00 Sales COGS Gross Profit Selling. General Admin. Expnenses Research and Development Depreciation EBIT Income Tax NI (Unlevered) 0.00 23,500,000.00 23,500,000.00 23,500,000.00 23,500,000.00 0.00 0.00 9,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 0.00 14,000,000.00 14,000,000.00 14,000,000.00 14,000,000.00 0.00 0.00 3,000,000.00 3,000,000.00 3,000,000.00 3,000,000.00 0.00 15,000,000, 00 0 .00 0.00 0.00 0.00 0.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 - 15,000,000.00 9,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 -1,500,000.00 -6,000,000.00 3,800,000.00 3,800,000.00 3,800,000.00 3,800,000.00 -600,000.00 -9,000,000.00 5,700,000.00 5,700,000.00 5,700,000.00 5,700,000.00 -900,000.00 0.00 Plus: Depreciation Less Capital Expenditures (Equipm Less: change in NWC SCF 0.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 7,500,000.00 0.00 0.00 0.00 0.00 0.00 0.00 2,100,000.00 0.00 0.00 0.00 -2,100,000.00 -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 PV of CFS NPV 0.12 -16,500,000.00 4,553,57143 5.739,795.92 5,124,81778 4,575,730,16 1,532,052 511 5,025,967.81 main 100.00 260.00 0.00 0.00 100.00 60.00 110.00 0.00 0.00 7.500,000.00 0.00 260.00 100,000.00 0.25 100.00 60.00 110.00 2,800,000.00 200,000.00 0.00 0.20 260.00 260.00 100,000.00 100,000.00 0.25 0.25 100.00 100.00 60.00 60.00 110.00 110.00 2,800,000.00 2.800,000.00 200,000.00 200,000.00 0.00 0.00 0.20 0.20 260.00 100,000.00 0.25 100.00 60.00 110.00 2,800,000.00 200.000.00 0.00 0.20 0.00 50.00 200,000.00 5,000,000.00 0.40 88888 1818 0.00 18181818 0.00 Year Price per unit # of units Expected loss in sales of routers (%) Price per unit (router) Cost of Goods Sold Router/unit Cost of Goods Sold/unit Marketing Costs/year . Opportunity Cost (Lab) New Equipment/year Depreciation schedule Software Development: Engineers Cost/engineer Design and Engineering Tax Rate HomeNet's Net Working Capital Requirements Cash Inventory Receivables 15% Sales) Payables (15% COGS) NWC Change in NWC Sales COGS Gross Profit Selling, General, Admin. Expenses Research and Development Depreciation EBIT Income Tax NI (Unlevered Plus: Depreciation Less Capital Expenditures Equipm Less: change in NWC 0.40 0.00 0. 000 .00 0.00 0.00 0.00 0.003 ,525,000.00 3.525,000.00 3.525 000,00 3.525,000. 00 0 000 0001 .425,000.00 1.425,000.00 1,425,000.00 1.425,000, 00 0 .00 0.00 2.100.000,00 2.100,000.00 2.100,000.00 2.100.000.00 0 .00 0.00 2.100.000.00 0.00 0.00 000 -2.100,000.00 0.00 23,500,000.00 23,500,000.00 23.500,000.00 23,500,000.00 0.00 0.00 9.500,000.00 9.500,000.00 9.500,000.00 9.500.000. 00 0 .00 0.00 14,000,000.00 14,000,000.00 14,000,000.00 14,000,000.00 000 0.00 3,000,000.00 3.000.000,00 3,000,000.00 3,000,000.00 0.00 15.000.000. 000 . 000. 000.00 0.00 0.00 1.500,000.00 1.500.000,00 1.500,000.00 1.500.000,00 1,500,000.00 -15,000,000.00 9,500,000.00 9.500,000.00 9.500,000.00 9.500,000.00 1,500,000.00 -6,000,000.00 3,800,000,00 3,800,000,00 3.800,000.00 3.800,000.00 500.00000 -9,000,000.00 5.700,000.00 5.700,000.00 5.700,000.00 5,700,000.00 -900,000.00 0.00 1.500,000.00 1.500,000.00 1.500,000.00 1,500,000.00 1,500,000.00 7,500,000.00 0.001 0.001 0.00 0.002 100,000.00 0.00-2 100,000.00 -16.500,000.00 5,100,000.00 7,200,000.00 7,200,000.00 7.200,000.00 2,700,000.00 0.00 0.00 COto Capital -16.500.000,00 4,553 571.43 5,739,795.925, 124,817.784.573.730.16 0 260.00 260.00 260.00 260.00 260.00 0.00 100,000.00 100,000.00 100,000.00 100,000.00 0.00 0.25 0.25 0.25 0.25 100.00 100.00 100.00 100.00 100.00 60.00 60.00 60.00 60.00 60.00 110.00 110.00 110.00 110.00 110.00 0.00 2,800,000.00 2,800,000.00 2,800,000.00 2,800,000.00 0.00 200,000.00 200,000.00 200,000.00 200,000.00 7,500,000.00 0.00 0.00 0.00 0.00 0.00 0.20 0.20 0.20 0.20 260.00 0.00 0.25 100.00 60.00 110.00 Year Price per unit # of units Expected loss in sales of routers (% Price per unit (router) Cost of Goods Sold Router/unit Cost of Goods Sold/unit Marketing Costs/year Opportunity Cost (Lab) New Equipment/year Depreciation schedule Software Development: Engineers Cost/engineer Design and Engineering Tax Rate 0.00 0.20 0.00 50.00 200,000.00 5,000,000.00 0.40 0.00 0.00 0.00 0.40 0.00 0.00 0.40 0.00 0.00 0.00 0.40 0.00 0.00 0.00 0.40 HomeNet's Net Working Capital Requirements Cash 0.00 0.00 0.00 0.00 0.00 Inventory 0.00 0.00 0.00 0.00 0.00 0.00 Receivables (15% Sales) 0.00 3,525,000.00 3,525,000.00 3,525,000.00 3,525,000.00 0.00 Payables (15% COGS) 0.00 1,425,000.00 1,425,000.00 1,425,000.00 1,425,000.00 0.00 NWC 0.00 2,100,000.00 2,100,000.00 2,100,000.00 2,100,000.00 0.00 Change in NWC 0.00 2,100,000.00 0.00 0.00 0.00 -2.100,000.00 0.00 Sales COGS Gross Profit Selling. General Admin. Expnenses Research and Development Depreciation EBIT Income Tax NI (Unlevered) 0.00 23,500,000.00 23,500,000.00 23,500,000.00 23,500,000.00 0.00 0.00 9,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 0.00 14,000,000.00 14,000,000.00 14,000,000.00 14,000,000.00 0.00 0.00 3,000,000.00 3,000,000.00 3,000,000.00 3,000,000.00 0.00 15,000,000, 00 0 .00 0.00 0.00 0.00 0.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 - 15,000,000.00 9,500,000.00 9,500,000.00 9,500,000.00 9,500,000.00 -1,500,000.00 -6,000,000.00 3,800,000.00 3,800,000.00 3,800,000.00 3,800,000.00 -600,000.00 -9,000,000.00 5,700,000.00 5,700,000.00 5,700,000.00 5,700,000.00 -900,000.00 0.00 Plus: Depreciation Less Capital Expenditures (Equipm Less: change in NWC SCF 0.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 7,500,000.00 0.00 0.00 0.00 0.00 0.00 0.00 2,100,000.00 0.00 0.00 0.00 -2,100,000.00 -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 PV of CFS NPV 0.12 -16,500,000.00 4,553,57143 5.739,795.92 5,124,81778 4,575,730,16 1,532,052 511 5,025,967.81 main 100.00 260.00 0.00 0.00 100.00 60.00 110.00 0.00 0.00 7.500,000.00 0.00 260.00 100,000.00 0.25 100.00 60.00 110.00 2,800,000.00 200,000.00 0.00 0.20 260.00 260.00 100,000.00 100,000.00 0.25 0.25 100.00 100.00 60.00 60.00 110.00 110.00 2,800,000.00 2.800,000.00 200,000.00 200,000.00 0.00 0.00 0.20 0.20 260.00 100,000.00 0.25 100.00 60.00 110.00 2,800,000.00 200.000.00 0.00 0.20 0.00 50.00 200,000.00 5,000,000.00 0.40 88888 1818 0.00 18181818 0.00 Year Price per unit # of units Expected loss in sales of routers (%) Price per unit (router) Cost of Goods Sold Router/unit Cost of Goods Sold/unit Marketing Costs/year . Opportunity Cost (Lab) New Equipment/year Depreciation schedule Software Development: Engineers Cost/engineer Design and Engineering Tax Rate HomeNet's Net Working Capital Requirements Cash Inventory Receivables 15% Sales) Payables (15% COGS) NWC Change in NWC Sales COGS Gross Profit Selling, General, Admin. Expenses Research and Development Depreciation EBIT Income Tax NI (Unlevered Plus: Depreciation Less Capital Expenditures Equipm Less: change in NWC 0.40 0.00 0. 000 .00 0.00 0.00 0.00 0.003 ,525,000.00 3.525,000.00 3.525 000,00 3.525,000. 00 0 000 0001 .425,000.00 1.425,000.00 1,425,000.00 1.425,000, 00 0 .00 0.00 2.100.000,00 2.100,000.00 2.100,000.00 2.100.000.00 0 .00 0.00 2.100.000.00 0.00 0.00 000 -2.100,000.00 0.00 23,500,000.00 23,500,000.00 23.500,000.00 23,500,000.00 0.00 0.00 9.500,000.00 9.500,000.00 9.500,000.00 9.500.000. 00 0 .00 0.00 14,000,000.00 14,000,000.00 14,000,000.00 14,000,000.00 000 0.00 3,000,000.00 3.000.000,00 3,000,000.00 3,000,000.00 0.00 15.000.000. 000 . 000. 000.00 0.00 0.00 1.500,000.00 1.500.000,00 1.500,000.00 1.500.000,00 1,500,000.00 -15,000,000.00 9,500,000.00 9.500,000.00 9.500,000.00 9.500,000.00 1,500,000.00 -6,000,000.00 3,800,000,00 3,800,000,00 3.800,000.00 3.800,000.00 500.00000 -9,000,000.00 5.700,000.00 5.700,000.00 5.700,000.00 5,700,000.00 -900,000.00 0.00 1.500,000.00 1.500,000.00 1.500,000.00 1,500,000.00 1,500,000.00 7,500,000.00 0.001 0.001 0.00 0.002 100,000.00 0.00-2 100,000.00 -16.500,000.00 5,100,000.00 7,200,000.00 7,200,000.00 7.200,000.00 2,700,000.00 0.00 0.00 COto Capital -16.500.000,00 4,553 571.43 5,739,795.925, 124,817.784.573.730.16