excel revise
dlc523
module8homework.fin350.xlsxP10-4
| P10-4 | ||||
| P10–4 Long-term investment decision, payback method Bill Williams has the opportunity | ||||
| to invest in project A that costs $9,000 today and promises to pay annual end-ofyear | ||||
| payments of $2,200, $2,500, $2,500, $2,000, and $1,800 over the next 5 years. | ||||
| Or, Bill can invest $9,000 in project B that promises to pay annual end-of-year payments | ||||
| of $1,500, $1,500, $1,500, $3,500, and $4,000 over the next 5 years. | ||||
| a. How long will it take for Bill to recoup his initial investment in project A? | ||||
| b. How long will it take for Bill to recoup his initial investment in project B? | ||||
| c. Using the payback period, which project should Bill choose? | ||||
| d. Do you see any problems with his choice? | ||||
| A. | Project A | |||
| year | $9,000 | |||
| 1 | $2,200 | $6,800 | 3+1800/2000 | |
| 2 | $2,500 | $4,300 | 3.9 | years |
| 3 | $2,500 | $1,800 | ||
| 4 | $2,000 | |||
| 5 | $1,800 | |||
| $11,000 | ||||
| B. | Project B | |||
| year | $9,000 | |||
| 1 | $1,500 | $7,500 | 4+1000/4000 | |
| 2 | $1,500 | $6,000 | 4.25 | years |
| 3 | $1,500 | $4,500 | ||
| 4 | $3,500 | $1,000 | ||
| 5 | $4,000 | |||
| $12,000 | ||||
| C. | Using the payback period, Bill would choose project A. This is because the longer he must wait to recover his funds, the greater chance of mishap. | |||
| D. | The problem with choice A would be that there would be a loss of funds. Project B would require a slightly longer period of time to recuperate funds but there would be an additional $1,000 at the end of five years. |
P10-10
| P10-10 | |||
| P10–10 NPV: Mutually exclusive projects Hook Industries is considering the replacement of | |||
| one of its old drill presses. Three alternative replacement presses are under consideration. | |||
| The relevant cash flows associated with each are shown in the following table. | |||
| The firm’s cost of capital is 15%. | |||
| a. Calculate the net present value (NPV) of each press. | |||
| b. Using NPV, evaluate the acceptability of each press. | |||
| c. Rank the presses from best to worst using NPV. | |||
| d. Calculate the profitability index (PI) for each press. | |||
| e. Rank the presses from best to worst using PI. | |||
| A. | |||
| Cost | of | Capital | 15% |
| Year | Press A | Press B | Press C |
| 0 | -$85,000 | -$60,000 | -$130,000 |
| 1 | $18,000 | $12,000 | $50,000 |
| 2 | $18,000 | $14,000 | $30,000 |
| 3 | $18,000 | $16,000 | $20,000 |
| 4 | $18,000 | $18,000 | $20,000 |
| 5 | $18,000 | $20,000 | $20,000 |
| 6 | $18,000 | $25,000 | $30,000 |
| 7 | $18,000 | $40,000 | |
| 8 | $18,000 | $50,000 | |
| NPV | ($24,661.21) | -$8,223.85 | -$29,309 |
| B. | Reject | Accept | Accept |
| C. | NPV (best to worst) | ||
| Press C | |||
| Press B | |||
| Press A | |||
| D. | Cash Inflows | Investment | PI |
| Press A | $144,000 | $ 85,000 | 1.69 |
| Press B | $105,000 | $ 60,000 | 1.75 |
| Press C | $260,000 | $ 130,000 | 2 |
| E. | PI (best to worst) | ||
| Press C | |||
| Press B | |||
| Press A |
P10-11
| P10-11 | |||
| P10–11 Long-term investment decision, NPV method Jenny Jenks has researched the financial | |||
| pros and cons of entering into a 1-year MBA program at her state university. The | |||
| tuition and books for the master’s program will have an up-front cost of $50,000. If | |||
| she enrolls in an MBA program, Jenny will quit her current job, which pays $50,000 | |||
| per year after taxes (for simplicity, treat any lost earnings as part of the up-front | |||
| cost). On average, a person with an MBA degree earns an extra $20,000 per year (after | |||
| taxes) over a business career of 40 years. Jenny believes that her opportunity cost | |||
| of capital is 6%. Given her estimates, find the net present value (NPV) of entering | |||
| this MBA program. Are the benefits of further education worth the associated costs? | |||
| Cost | of | Capital | 6% |
| Year | Press A | ||
| 0 | $ (100,000) | ||
| 1 | $ 20,000 | ||
| 2 | $ 20,000 | ||
| 3 | $ 20,000 | ||
| 4 | $ 20,000 | ||
| 5 | $ 20,000 | ||
| 6 | $ 20,000 | ||
| 7 | $ 20,000 | ||
| 8 | $ 20,000 | ||
| 9 | $ 20,000 | ||
| 10 | $ 20,000 | ||
| 11 | $ 20,000 | ||
| 12 | $ 20,000 | ||
| 13 | $ 20,000 | ||
| 14 | $ 20,000 | ||
| 15 | $ 20,000 | ||
| 16 | $ 20,000 | ||
| 17 | $ 20,000 | ||
| 18 | $ 20,000 | ||
| 19 | $ 20,000 | ||
| 20 | $ 20,000 | ||
| 21 | $ 20,000 | ||
| 22 | $ 20,000 | ||
| 23 | $ 20,000 | ||
| 24 | $ 20,000 | ||
| 25 | $ 20,000 | ||
| 26 | $ 20,000 | ||
| 27 | $ 20,000 | ||
| 28 | $ 20,000 | ||
| 29 | $ 20,000 | ||
| 30 | $ 20,000 | ||
| 31 | $ 20,000 | ||
| 32 | $ 20,000 | ||
| 33 | $ 20,000 | ||
| 34 | $ 20,000 | ||
| 35 | $ 20,000 | ||
| 36 | $ 20,000 | ||
| 37 | $ 20,000 | ||
| 38 | $ 20,000 | ||
| 39 | $ 20,000 | ||
| 40 | $ 20,000 | ||
| NPV | $200,925.94 | The benefits, financially, of further education are worth the associated costs. |
P10-15
| P10-15 | ||
| P10–15 Internal rate of return Peace of Mind, Inc. (PMI), sells extended warranties for durable | ||
| consumer goods such as washing machines and refrigerators. When PMI sells an extended | ||
| warranty, it receives cash up front from the customer, but later PMI must cover any repair | ||
| costs that arise. An analyst working for PMI is considering a warranty for a new line | ||
| of big-screen TVs. A consumer who purchases the 2-year warranty will pay PMI $200. | ||
| On average, the repair costs that PMI must cover will average $106 for each of the warranty’s | ||
| 2 years. If PMI has a cost of capital of 7%, should it offer this warranty for sale? | ||
| 4%<7% | ||
| Year | Project | |
| 0 | $ (200) | PMI should not offer this warranty for sale. |
| 1 | $ 106 | The cost of capital is higer than the internal rate of retun. |
| 2 | $ 106 | |
| IRR | 4% |
P10-21
| P10-21 | |||||
| P10–21 All techniques, conflicting rankings Nicholson Roofing Materials, Inc., is considering | |||||
| two mutually exclusive projects, each with an initial investment of $150,000. | |||||
| The company’s board of directors has set a maximum 4-year payback requirement | |||||
| and has set its cost of capital at 9%. The cash inflows associated with the two projects | |||||
| are shown in the following table. | |||||
| a. Calculate the payback period for each project. | |||||
| b. Calculate the NPV of each project at 0%. | |||||
| c. Calculate the NPV of each project at 9%. | |||||
| d. Derive the IRR of each project. | |||||
| e. Rank the projects by each of the techniques used. Make and justify a recommendation. | |||||
| f. Go back one more time and calculate the NPV of each project using a cost of | |||||
| capital of 12%. Does the ranking of the two projects change compared to your | |||||
| answer in part e? Why? | |||||
| A. | Project A | Project B | |||
| year | $ 150,000 | year | $ 150,000 | ||
| 1 | $ 45,000 | $ 105,000 | 1 | $ 75,000 | $ 75,000 |
| 2 | $ 45,000 | $ 60,000 | 2 | $ 60,000 | $ 10,000 |
| 3 | $ 45,000 | $ 15,000 | 3 | $ 30,000 | |
| 4 | $ 45,000 | 4 | $ 30,000 | ||
| 5 | $ 45,000 | 5 | $ 30,000 | ||
| 6 | $ 45,000 | 6 | $ 30,000 | ||
| $ 270,000 | $ 255,000 | ||||
| (3+15000)/45000 | 2+10000/30000 | ||||
| Payback Period | 3.33 | years | Payback Period | 2.33 | years |
| B. | Cost | of | Capital | 0% | |
| Year | Project A | Project B | |||
| 0 | -$150,000 | -$150,000 | |||
| 1 | $45,000 | $75,000 | |||
| 2 | $45,000 | $60,000 | |||
| 3 | $45,000 | $30,000 | |||
| 4 | $45,000 | $30,000 | |||
| 5 | $45,000 | $30,000 | |||
| 6 | $45,000 | $30,000 | |||
| NPV | $120,000.00 | $105,000.00 | |||
| C. | Cost | of | Capital | 9% | |
| Year | Project A | Project B | |||
| 0 | -$150,000 | -$150,000 | |||
| 1 | $45,000 | $75,000 | |||
| 2 | $45,000 | $60,000 | |||
| 3 | $45,000 | $30,000 | |||
| 4 | $45,000 | $30,000 | |||
| 5 | $45,000 | $30,000 | |||
| 6 | $45,000 | $30,000 | |||
| NPV | $51,866.34 | $51,112.36 | |||
| D. | |||||
| Year | Project A | Project B | |||
| 0 | $ (150,000) | $ (150,000) | |||
| 1 | $ 45,000 | $ 75,000 | |||
| 2 | $ 45,000 | $ 60,000 | |||
| 3 | $ 45,000 | $ 30,000 | |||
| 4 | $ 45,000 | $ 30,000 | |||
| 5 | $ 45,000 | $ 30,000 | |||
| 6 | $ 45,000 | $ 30,000 | |||
| IRR | 20% | 23% | |||
| E. | Based on the pay back period, Nicholson Roofing Materials, Inc., should choose project B because it required less time to recover the initial investment. | ||||
| Using the NPV criteria Project A would be the appropriate choice when the cost of capital is set at 0% and 9% because they yield the highest returns. | |||||
| Using the IRR, project B would be the appropriate choice. Since the IRR is above 0% either could be accepted but project B yields the higher of the two. | |||||
| F. | Cost | of | Capital | 12% | |
| Year | Project A | Project B | |||
| 0 | -$150,000 | -$150,000 | |||
| 1 | $45,000 | $75,000 | |||
| 2 | $45,000 | $60,000 | |||
| 3 | $45,000 | $30,000 | |||
| 4 | $45,000 | $30,000 | |||
| 5 | $45,000 | $30,000 | |||
| 6 | $45,000 | $30,000 | |||
| NPV | $35,013.33 | $37,436.61 | |||
| When the cost of capital is changed to 12%, the NPV changes and project B becomes the best choice. | |||||
| As the cost of capital percentage increases, the NPV of project A decreases and project A increases. | |||||
| This is because project A has a steady cash inflow while project B has a decreasing cash flow for the first three years. |
P10-24
| P10-24 | ||||||||||||||
| P10–24 All techniques: Decision among mutually exclusive investments Pound Industries is | ||||||||||||||
| attempting to select the best of three mutually exclusive projects. The initial investment | ||||||||||||||
| and after-tax cash inflows associated with these projects are shown in the | ||||||||||||||
| following table. | ||||||||||||||
| a. Calculate the payback period for each project. | ||||||||||||||
| b. Calculate the net present value (NPV) of each project, assuming that the firm has | ||||||||||||||
| a cost of capital equal to 13%. | ||||||||||||||
| c. Calculate the internal rate of return (IRR) for each project. | ||||||||||||||
| d. Draw the net present value profiles for both projects on the same set of axes, and | ||||||||||||||
| discuss any conflict in ranking that may exist between NPV and IRR. | ||||||||||||||
| e. Summarize the preferences dictated by each measure, and indicate which project | ||||||||||||||
| you would recommend. Explain why. | ||||||||||||||
| A. | Project A | Project B | Project C | |||||||||||
| year | $60,000 | year | $100,000 | year | $110,000 | |||||||||
| 1 | $20,000 | $40,000 | 2+20000/20000 | 1 | $31,500 | $68,500 | 3+5500/31500 | 1 | $32,500 | $77,500 | 3+12500/32500 | |||
| 2 | $20,000 | $20,000 | 3.00 | years | 2 | $31,500 | $37,000 | 3.17 | years | 2 | $32,500 | $45,000 | 3.38 | years |
| 3 | $20,000 | 3 | $31,500 | $5,500 | 3 | $32,500 | $12,500 | |||||||
| 4 | $20,000 | 4 | $31,500 | 4 | $32,500 | |||||||||
| 5 | $20,000 | 5 | $31,500 | 5 | $32,500 | |||||||||
| $100,000 | $157,500 | $162,500 | ||||||||||||
| B. | Cost | of | Capital | 13% | ||||||||||
| Year | Project A | Project B | Project C | |||||||||||
| 0 | -$60,000 | -$100,000 | -$110,000 | |||||||||||
| 1 | $20,000 | $31,500 | $32,500 | |||||||||||
| 2 | $20,000 | $31,500 | $32,500 | |||||||||||
| 3 | $20,000 | $31,500 | $32,500 | |||||||||||
| 4 | $20,000 | $31,500 | $32,500 | |||||||||||
| 5 | $20,000 | $31,500 | $32,500 | |||||||||||
| NPV | $10,344.63 | $10,792.78 | $4,310.02 | |||||||||||
| C. | Year | Project A | Project B | Project C | ||||||||||
| 0 | $ (60,000) | $ (100,000) | $ (110,000) | |||||||||||
| 1 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| 2 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| 3 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| 4 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| 5 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| 6 | $ 20,000 | $ 31,500 | $ 32,500 | |||||||||||
| IRR | 24% | 22% | 19% | |||||||||||
| D. | ||||||||||||||
| a | b | c | ||||||||||||
| 1% | 37068.6247865024 | 52883.0840387413 | 47736.5152780664 | |||||||||||
| 2% | 34269.1901700841 | 48473.9745178825 | 43187.4340263867 | |||||||||||
| 3% | 31594.1437438907 | 44260.7763966278 | 38840.4835838224 | |||||||||||
| 4% | 29036.4466203241 | 40232.4034270105 | 34684.2257580267 | |||||||||||
| 5% | 26589.5334126164 | 36378.5151248708 | 30707.9917955016 | |||||||||||
| 6% | 24247.2757113143 | 32689.4592453199 | 26901.8230308857 | |||||||||||
| 7% | 22003.9487189519 | 29156.2192323492 | 23256.4166682968 | |||||||||||
| 8% | 19854.2007415617 | 25770.3661679597 | 19763.0762050378 | |||||||||||
| 9% | 17793.0252670343 | 22524.0147955791 | 16413.6660589308 | |||||||||||
| 10% | 15815.7353881689 | 19409.7832363661 | 13200.5700057745 | |||||||||||
| 11% | 13917.9403529893 | 16420.7560559581 | 10116.6530736076 | |||||||||||
| 12% | 12095.5240469001 | 13550.4503738676 | 7155.2265762126 | |||||||||||
| 13% | 10344.6252308541 | 10792.7847385953 | 4310.016000138 | |||||||||||
| 14% | 8661.6193771692 | 8142.0505190415 | 1575.1314878999 | |||||||||||
| 15% | 7043.101960228 | 5592.8855873592 | -1054.9593146294 | |||||||||||
| 16% | 5485.8730732247 | 3140.2500903289 | -3585.4562560098 | |||||||||||
| 17% | 3986.9232545843 | 779.4041259703 | -6021.2497113005 | |||||||||||
| 18% | 2543.4204188379 | -1494.1128403302 | -8366.9418193884 | |||||||||||
| 19% | 1152.6977967362 | -3684.5009701405 | -10626.8660803037 | |||||||||||
| 20% | -187.7572016461 | -5795.7175925926 | -12805.1054526749 | |||||||||||
| E. | Using the payback period project a would be the best choice because it has the shortest return time of 3 years. | |||||||||||||
| Using the NPV, project B would be the best choice because it yields the highest return. | ||||||||||||||
| Using the IRR, project A would be the best choice because it has the highest return at 24%. | ||||||||||||||
| If I were to recommend one, I would choose project B. This is because it has the highest NPV. |
NPV Profile for Pound Industries
NPV Project A 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 37068.624786502405 34269.190170084112 31594.143743890672 29036.446620324103 26589.533412616365 24247.275711314258 22003.94871895187 19854.200741561712 17793.025267034318 15815.735388168949 13917.940352989288 12095.524046900086 10344.62523085413 8661.6193771691906 7043.101960228043 5485.8730732247204 3986.9232545843115 2543.4204188379517 1152.6977967361599 -187.75720164609083 NPV Project B 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 52883.084038741275 48473.974517882481 44260.7763966278 40232.40342701046 36378.515124870784 32689.459245319944 29156.219232349191 25770.366167959684 22524.01479557906 19409.783236366071 16420.756055958118 13550.450373867629 10792.784738595263 8142.0505190414697 5592.8855873591965 3140.2500903289329 779.40412597029353 -1494.1128403302428 -3684.5009701405361 -5795.717592592584 NPV Project C 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 47736.515278066421 43187.434026386676 38840.483583822352 34684.225758026674 30707.991795501614 26901.823030885687 23256.416668296792 19763.076205037767 16413.666058930772 13200.570005774542 10116.653073607595 7155.2265762126335 4310.01600013797 1575.1314878999256 -1054.959314629421 -3585.4562560098275 -6021.2497113004938 -8366.9418193883466 -10626.866080303749 -12805.105452674892Discount Rate %
NPV ($000)
NPV Profile for Pound Industries
NPV Project A 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 37068.624786502405 34269.190170084112 31594.143743890672 29036.446620324103 26589.533412616365 24247.275711314258 22003.94871895187 19854.200741561712 17793.025267034318 15815.735388168949 13917.940352989288 12095.524046900086 10344.62523085413 8661.6193771691906 7043.101960228043 5485.8730732247204 3986.9232545843115 2543.4204188379517 1152.6977967361599 -187.75720164609083 NPV Project B 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 52883.084038741275 48473.974517882481 44260.7763966278 40232.40342701046 36378.515124870784 32689.459245319944 29156.219232349191 25770.366167959684 22524.01479557906 19409.783236366071 16420.756055958118 13550.450373867629 10792.784738595263 8142.0505190414697 5592.8855873591965 3140.2500903289329 779.40412597029353 -1494.1128403302428 -3684.5009701405361 -5795.717592592584 NPV Project C 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 47736.515278066421 43187.434026386676 38840.483583822352 34684.225758026674 30707.991795501614 26901.823030885687 23256.416668296792 19763.076205037767 16413.666058930772 13200.570005774542 10116.653073607595 7155.2265762126335 4310.01600013797 1575.1314878999256 -1054.959314629421 -3585.4562560098275 -6021.2497113004938 -8366.9418193883466 -10626.866080303749 -12805.105452674892Discount Rate %
NPV ($000)
