Question
In: Finance
I) Initial Investment Outlay: Dunder Mifflin is considering opening a Philadelphia branch for the next five...
I)
Initial Investment Outlay:
Dunder Mifflin is considering opening a Philadelphia branch for the next five years to augment
its existing facilities in Scranton, PA. After five years, they will close the Philadelphia branch
and seek regional alternatives.
A financial analyst has estimated the following costs:
A) $100,000 market analysis (already conducted)
B) $1,000,000 purchase of distribution center
C) $250,000 fixtures and installation of equipment
II)
Changes in Net Working Capital
The managers of the Scranton branch estimated that the new branch will have the following
working capital requirements:
A) $10,000 in initial cash buffers
B) $25,000 in initial inventories
III)
Operating Cash Flows
Financial analysts have estimated the following for the Philadelphia branch:
A) The annual sales are estimated to be:
Year 1 # of reams: 200,000 price per ream: $4
Year 2 # of reams: 200,000 price per ream: $4.50
Year 3 # of reams: 200,000 price per ream: $5
Year 4 # of reams: 250,000 price per ream: $5.50
Year 5 # of reams: 300,000 price per ream: $6
B) Variable costs of $2.50 per ream
C) Fixed overhead costs of $75,000 to pay employee salaries and utilities
The managers have decided that the new equipment (being largely industrial) should be
depreciated according to the 7-year MACRS schedule. Assume taxes are 34%.
7-year
MACRs
Year 1 Depr%: 14.29
Year 2 Depr%: 24.49
Year 3 Depr%: 17.49
Year 4 Depr%: 12.49
Year 5 Depr%: 8.93
Year 6 Depr%: 8.93
Year 7 Depr%: 8.93
Year 8 Depr%: 4.45
IV)
Terminal Cash Flows
Financial analysts have estimated that the Philadelphia branch can be sold at the conclusion of
the five-year project for $500,000.
Based on this information, answer the following questions:
1) Should Dunder Mifflin go through with the expansion to Philadelphia if the relevant
discount rate is 10%?
2) What is the NPV of the expansion project? IRR?
3) What is the Payback of the expansion project? If the required payback is 4 years, what is
the decision on this project?
4) At what rate would they be indifferent to undertaking the project?
Solutions
Expert Solution
1. $100,000 market analysis (already conducted) - this cost since already incurred (sunk cost) is not relevant for the analysis
2. Purchase of distribution center = $1,000,000 in year 0
3. Fixtures and installation of equipment = $250,000 in year 0
4. Depreciation on Fixture and installation:
| Years | Depreciation rate | Depreciation value ($250,000* depreciation rate) | Book value at the end of year | Workings for Book value at the end of year | Tax shield on depreciation at 34% (Depreciation value * 34%) |
| 1 | 14.29% | 35,725 | 214,275 | (250,000-35,725) | 12,147 |
| 2 | 24.49% | 61,225 | 153,050 | (214,275-61,225) | 20,817 |
| 3 | 17.49% | 43,725 | 109,325 | (153,050-43,725) | 14,867 |
| 4 | 12.49% | 31,225 | 78,100 | (109,325-31,225) | 10,617 |
| 5 | 8.93% | 22,325 | 55,775 | (78,100-22,325) | 7,591 |
| 6 | 8.93% | 22,325 | 33,450 | (55,775-22,325) | 7,591 |
| 7 | 8.93% | 22,325 | 11,125 | (33,450-22,325) | 7,591 |
| 8 | 4.45% | 11,125 | - | (11,125-11,125) | 3,783 |
| 250,000 |
5. Terminal cash-flows (Salvage Value ) = $500,000
Book-value of equipment at the end of 5th year (as per above table) = 55,775
Profit on sale = $500,000-$55,775 = $444,225
Tax at 34% on profit on sale = $444,225*34% = $151,037
Net Terminal cash flow = Sale Value - Tax on profit on sale = $500,000-$151,037 = $348,964 in year 5
6. Working capital required:
Initial cash buffers = $10,000
Initial inventories = $25,000
Total working capital required = $10,000+$25,000 = $35,000 in year 0
Since after five years, they will close the Philadelphia branch and seek regional alternatives., we assume that the working capital will be recovered at the end of 5 years.
Working capital recovery = $35,000 in year 5
7. Operating cash flows:
| Years | Sales (units) (A) | Selling Price per unit (B) | Sales Value (C) = (A)*(B) | Variable cost per unit (D) | Variable cost (E) = (A)*(D) | Fixed Cost (F) | Operating Cash-flow (before tax) (G) = (C) -(E) - (F) |
| 1 | 200,000 | $4.0 | $800,000 | $2.5 | $500,000 | $75,000 | $225,000 |
| 2 | 200,000 | $4.5 | $900,000 | $2.5 | $500,000 | $75,000 | $325,000 |
| 3 | 200,000 | $5.0 | $1,000,000 | $2.5 | $500,000 | $75,000 | $425,000 |
| 4 | 250,000 | $5.5 | $1,375,000 | $2.5 | $625,000 | $75,000 | $675,000 |
| 5 | 300,000 | $6.0 | $1,800,000 | $2.5 | $750,000 | $75,000 | $975,000 |
8. Net Annual cash-flows = Operating Cash-flow (before tax) - Taxes on operating cash flow + Tax shield on depreciation
| Years | Operating Cash-flow (before tax) (as per (7) above) (A) | Tax on operating cash flow (B) = (A)* 34%) | Operating Cash-flow (after tax) (C) = (A)-(B) | Tax shield on depreciation at 34% (as per (4) above) (D) | Net Annual cash-flows (C) + (D) |
| 1 | $225,000 | $76,500 | $148,500 | 12,147 | $160,647 |
| 2 | $325,000 | $110,500 | $214,500 | 20,817 | $235,317 |
| 3 | $425,000 | $144,500 | $280,500 | 14,867 | $295,367 |
| 4 | $675,000 | $229,500 | $445,500 | 10,617 | $456,117 |
| 5 | $975,000 | $331,500 | $643,500 | 7,591 | $651,091 |
| 6 | 7,591 | $7,591 | |||
| 7 | 7,591 | $7,591 | |||
| 8 | 3,783 | $3,783 |
9. Discount factor at 10%
Year 1 = 1/(100%+10%)^1 = 0.9091
Year 2 = 1/(100%+10%)^2 = 0.8264
Year 3 = 1/(100%+10%)^3 = 0.7513
Year 4 = 1/(100%+10%)^4 = 0.6830
Year 5 = 1/(100%+10%)^5 = 0.6209
Year 6 = 1/(100%+10%)^6 = 0.5645
Year 7 = 1/(100%+10%)^7 = 0.5132
Year 8 = 1/(100%+10%)^8 = 0.4665
Computation of Net Present Value
| Years | Initial investment | Fixtures and installation of equipment | Working capital | Net Annual cash-flows | Terminal Value | Net Cash-flows | Discount factor at 10% | Present Value |
| Working Steps | Refer (2) above | Refer (3) above | Refer (6) above | Refer (8) above | Refer (5) above | (8)+(5)-(2)-(3)-(6) | Refer (9) above | (Net cash flows * discount factor at 10%) |
| 0 | (1,000,000) | (250,000) | (35,000) | (1,285,000) | 1.0000 | (1,285,000) | ||
| 1 | 160,647 | 160,647 | 0.9091 | 146,042 | ||||
| 2 | 235,317 | 235,317 | 0.8264 | 194,476 | ||||
| 3 | 295,367 | 295,367 | 0.7513 | 221,913 | ||||
| 4 | 456,117 | 456,117 | 0.6830 | 311,534 | ||||
| 5 | 35,000 | 651,091 | 348,964 | 1,035,054 | 0.6209 | 642,687 | ||
| 6 | 7,591 | 7,591 | 0.5645 | 4,285 | ||||
| 7 | 7,591 | 7,591 | 0.5132 | 3,895 | ||||
| 8 | 3,783 | 3,783 | 0.4665 | 1,765 | ||||
| Total | 241,597 |
Thus, NPV = $241,597 (sum of Present Value of all years)
IRR = IRR is the rate at which NPV becomes zero.
IRR can be computed using 'Goal Seek' Function in excel --->Go to 'Data' -->'What-if Analysis'-->'Goal Seek'-->'Set cell' as select the cell where NPV of $241,597 is computed -->'To value', type 0, --> 'By Changing cell' , select the cell where discount factor of 10% is keyed ( in excel, have a cell with value as '10%' and compute discount factor every year using that cell and give that 10% cell reference for 'By changing cell').
With the above method, IRR is 15.23%
Pay-back period: Pay-back is period by which an investment recovers its initial investment
| Years | Present Value | Cumulative of Present Value | Cumulative of Present Value workings |
| 0 | (1,285,000) |
 jeff jeffy answered 1 year agoRelated SolutionsDunder-Mifflin is considering opening a new branch in Columbia with an initial fixed asset cost of...Dunder-Mifflin is considering opening a new branch in Columbia
with an initial fixed asset cost of $3.66 million which will be
depreciated straight-line to a zero book value over the 10-year
life of the project.
At the end of the project the equipment will be sold for an
estimated $300,000. The project will not directly produce any sales
but will reduce operating costs by $725,000 a year. The tax rate is
21 percent. The project will require $45,000 of inventory...
Dunder Mifflin Paper Company is considering purchasing a new stamping machine that costs $450,000. This new...Dunder Mifflin Paper Company is considering purchasing a new
stamping machine that costs $450,000. This new machine will
produce cash inflows of $150,000 each year at the end of years 1
through 5, then at the end of year 7 there will be a cash
outflow of $200,000 The company has a weighted average
cost of capital of 14 percent (use this as the reinvestment
rate). What is the MIRR of the investment?
PADICO is considering an investment project. The project requires an initial $5 million outlay for equipment...
PADICO is considering an investment project. The project
requires an initial $5 million outlay for equipment and machinery.
Sales are projected to be $2.5 million per year for the next four
years. The equipment will be fully depreciated straight-line by the
end of year 4. The cost of goods sold and operating expenses (not
including depreciation) are predicted to be 30% of sales. The
equipment can be sold for $500,000 at the end of year 4.Padico also
needs to add...
PADICO is considering an investment project. The project requires an initial $5 million outlay for equipment...PADICO is considering an investment project. The project
requires an initial $5 million outlay for equipment and machinery.
Sales are projected to be $2.5 million per year for the next four
years. The equipment will be fully depreciated straight-line by the
end of year 4. The cost of goods sold and operating expenses (not
including depreciation) are predicted to be 30% of sales. The
equipment can be sold for $500,000 at the end of year 4.Padico also
needs to add...
PADICO is considering an investment project. The project requires an initial $5 million outlay for equipment...PADICO is considering an investment project. The project
requires an initial $5 million outlay for equipment and machinery.
Sales are projected to be $2.5 million per year for the next four
years. The equipment will be fully depreciated straight-line by the
end of year 4. The cost of goods sold and operating expenses (not
including depreciation) are predicted to be 30% of sales. The
equipment can be sold for $500,000 at the end of year 4.Padico also
needs to add...
18 A firm is considering an investment project which requires the initial outlay of $10 million....18 A firm is considering an investment project which requires
the initial outlay of $10 million. The 12-year project is expected
to generate annual net cash flows each year of $1 million and have
the expected terminal value at the end of the project of $5
million. The cost of capital is 6 percent, and its marginal tax
rate is 40 percent. Calculate the profitability index of this
project.
0.84
0.09
1.70
1.34
1.09
Caribbean, Inc. is considering a new investment opportunity. The project requires an initial outlay of $525,000...Caribbean, Inc. is considering a new investment opportunity. The
project requires an initial outlay of $525,000 and is expected to
bring in a $75,000 cash inflow at the end of the first year. After
the first year, annual cash flows from the project are forecasted
to grow at a constant rate of 5% until the end of the fifth year
and to remain constant forever thereafter.
The company currently has a target debt-to-equity ratio of .40, but
the industry that...
Molly is considering opening a Campus Delivery business. The initial investment for the business is $263,000,...Molly is considering opening a Campus Delivery business. The
initial investment for the business is $263,000, which includes
purchasing delivery vehicles and other investments. For tax
purposes, the projected salvage value of the delivery vehicles is
$25,000. The government requires depreciating the vehicles using
the straight-line method over the business’s life of 10 years.
Molly is trying to estimate the net cashflows after tax for this
business. She has already figured out that the business will
generate an annual after-tax...
Molly is considering opening a Campus Delivery business. The initial investment for the business is $263,000,...Molly is considering opening a Campus Delivery business. The
initial investment for the business is $263,000, which includes
purchasing delivery vehicles and other investments. For tax
purposes, the projected salvage value of the delivery vehicles is
$25,000. The government requires depreciating the vehicles using
the straight-line method over the business’s life of 10 years.
Molly is trying to estimate the net cashflows after tax for this
business. She has already figured out that the business will
generate an annual after-tax...
Universal PLC is considering two mutually exclusive project proposals for new investment. The initial outlay of...
Universal PLC is considering two mutually exclusive project
proposals for new investment. The initial outlay of both projects
is £300,000 but will yield different levels of cash flows over the
life of the project. The projects are estimated to last for five
years. They will have no residual value at the end of their lives.
Depreciation is charged on a straight-line basis. The company uses
a 6% discount rate for the cost of capital. The cash flows of both
projects...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|