Question
In: Finance
Refer to excel data given below. Modify model for projection of free cash flows from the...
- Refer to excel data given below.
Modify model for projection of free cash flows from the Home Net project along the following dimensions:
- 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%
- The equipment is sold at the end of year 4 for $0.5 m
- 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
- 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
- Assume that the Net working capital is recovered as soon as the production is over (at the end of year 4)
- Estimate NPV of the project assuming that rwacc= 12%
- Change your assumption about cost of capital and try several values above and below the initial value of 12%. Construct NPV-sensitivity-to-rwaccgraph (rwaccvalues 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 |
Solutions
Expert Solution
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:
jeff jeffy answered 2 years agoRelated Solutions
CoveAuklaOoglu, Inc. is considering a project which has net cash flows (the same as free cash flows) given below:
CoveAuklaOoglu, Inc. is considering a project which has net cash flows (the same as free cash flows) given below:Year CF ($)0 -1,000 (Initial Outlay)1 5002 4003 3004 100Given that the company’s WACC is 10%, what is the company’s NPV? (Points : 3.4)
$78.82 $109.45 $49.18 $54.06
Garrod Dickens wants to calculate the IRR for the above project (use information in Question 22) for CoveAuklaOoglu, Inc. His answer would be: (Points : 3.4)
11.8% 14.5% 12.45% 13.02%
Garrod Dickens also wants to...
Finding operating and free cash flows Consider the balance sheets and selected data from the income...
Finding operating and free cash flows Consider the balance
sheets and selected data from the income statement of Keith
Corporation that follow
.
a. Calculate the firm's net operating profit after taxes
(NOPAT) for the year ended December 31,
20192019.
b. Calculate the firm's operating cash flow (OCF) for the year
ended December 31,
20192019.
c. Calculate the firm's free cash flow (FCF) for the year
ended December 31,
20192019.
d. Interpret, compare and contrast your cash flow estimate in...
Finding operating and free cash flows Consider the balance sheets and selected data from the income...
Finding operating and free cash flows Consider the balance
sheets and selected data from the income statement of Keith
Corporation that follow
Keith Corporation Balance Sheets
Assets
2019
2018
Cash
$1,500
$1,000
Marketable securities
1800
1200
Accounts receivable
2000
1800
Inventories
2900
2800
Total current assets
$8,200
$6,800
Gross fixed assets
$29,500
$28,100
Less: Accumulated depreciation
14700
13100
Net fixed assets
$14,800
$15,000
Total assets
$23,000
$21,800
Liabilities and Stockholders' Equity
Accounts payable
$1,600
$1,500
Notes payable
2800
2200
Accruals...
Finding operating and free cash flows Consider the balance sheets and selected data from the income...
Finding operating and free cash flows Consider the
balance sheets and selected data from the income statement of Keith
Corporation that follow
LOADING...
.
a. Calculate the firm's net operating profit after
taxes (NOPAT) for the year ended December 31,
2019
2019.
b. Calculate the firm's operating cash flow (OCF) for
the year ended December 31,
2019
2019.
c. Calculate the firm's free cash flow (FCF) for the
year ended December 31,
2019
2019.
d. Interpret, compare and contrast your...
Use MS Excel to answer this. From the data given below test a hypothesis for variances...
Use MS Excel to answer this. From the data given below test a
hypothesis for variances being equal. Use α = 0.05.
Section A
Section B
15
16
26
52
52
55
53
57.5
54
58
56.5
60
61
61
61.5
70
63
70
66
71
66
72
66.5
73
69
74.5
71
75
77
75.5
77
76
78
77
79
81
81
85
86
85.5
87
86
90
88
90
88.5
91
91
91
93
94
95
96
98...
What are free cash flows for a firm? Describe the steps of estimating free cash flows...
What are free cash flows for a firm? Describe the steps of
estimating free cash flows of a firm. What does it mean when a
firms cash flow is negative? Explain with examples
Use the following information to develop a spreadsheet model that will calculate the free cash flows...
Use the following information to develop a spreadsheet model
that will calculate the free cash flows and the value of the equity
for the company. Cost of capital 12% Most recent year’s sales $1000
Nonoperating assets $100 Interest-bearing debt $250 Operating
profit margin 12% Working capital/sales 35% Fixed assets/sales 20%
Noninterest-bearing Current liabilities/sales 10% Tax rate 40%
Forecasted sales growth Years 1␣2 12% Years 3␣5 8% 6␣N 4%
Look at the cash flows for projects F and G given below. Cash Flows($) Project C0...
Look at the cash flows for projects F and G given below.
Cash Flows($)
Project
C0
C1
C2
C3
C4
C5
C6
C7
C8
IRR (%)
NPV at 10%
F
(10,000
)
6,000
6,000
6,000
0
0
0
0
0
36.3
4,921
G
(10,000
)
3,000
3,000
3,000
3,000
3,000
3,000
3,000
3,000
25.0
6,005
The cost of capital was assumed to be 10%. Assume that the
forecasted cash flows for projects of this type are overstated by
8% on...
Forecast the Statement of Cash Flows Refer to the Medtronic PLC financial statement below. Use the...
Forecast the Statement of Cash Flows Refer to the Medtronic PLC
financial statement below. Use the information to forecast
Medtronic's FY2017 statement of cash flows. Medtronic plc
Consolidated Statements of Income 12 Months Ended ($ millions) 2017
EST. Apr. 29, 2016 Net sales $35,864 $28,833 Costs and expenses
Cost of products sold 11,151 9,142 Research and development expense
2,709 2,224 Selling, general, and administrative expense 11,538
9,469 Special charges (gains), net - 70 Restructuring charges, net
218 290 Certain litigation...
Refer to the data set of 20 randomly selected presidents given below. Treat the data as...
Refer to the data set of 20 randomly selected presidents given
below. Treat the data as a sample and find the proportion of
presidents who were taller than their opponents. Use that result to
construct a 95% confidence interval estimate of the population
percentage. Based on the result, does it appear that greater
height is an advantage for presidential candidates? Why or why
not?
Construct a 95% confidence interval estimate of the percentage
of presidents who were taller than their...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- What do you believe happens during the final scene of the story between the brothers at....
- A 5.171 g sample of a solid, weak, monoprotic acid is used to make a 100.0...
- 1. Creep is one of deformation that occurred in concrete structure. Give one example that can...
- Why does a particular codon represent only one specific amino acid?
- Write a function template sort that takes two references to generic objects and switches them if...
- What 2 things happen to the melting point of a solid when an impurity is present?
- How Can Hominin Evolution be characterized as " mosaic evolution" Use examples of species from the...
ADVERTISEMENT