Question

In: Finance

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable..) Ferengi, Inc. is subject to an applicable corporate tax rate of 21 percent, and the weighted average cost of capital (WACC) of 12.5 percent. There is no specific time constraint on investment project payback requirements. Q1: Ferengi is currently contemplating two capital investment plans. Plan A: the upgrade of an information system with an installed cost of $2,400,000. The upgrade system will be depreciated straight-line to zero over the project’s five-year life, at the end of which the system will be worth $400,000 at the market. The system upgrade will not affect sales, but will save the firm $700,000 per year in pretax operating costs; and the upgrade will increase the working efficiency and reduce the net working capital expenditure by $300,000 at the beginning year. What is the NPV of Plan A? What is the IRR of Plan A? Should Ferengi accept or reject Plan A? Q2: Instead of Plan A, Ferengi can alternatively choose Plan B: allocate the $2,400,000 capital budget to develop a new product line. The new product line will be depreciated straight-line to zero over the project’s ten-year life, at the end of which the system will be worth $100,000. The new product line will not only add the firm $830,000 per year in sales, but also add $200,000 per year in pretax operating costs; and the new project line requires an initial investment in net working capital of $300,000 at the beginning year. What would be the NPV of Plan B? What would be the IRR of Plan B? If these two plans are mutually exclusive, shall Ferengi finally choose Plan A or B? This should be done in excel and please show me how you got the formulas in excel....(PLEASE SHOW THE FUNCTIONS IN EXCEL) DO NOT JUST SHOW THE ANSWERS BUT THE ACTUAL FORMULAS USED IN EXCEL

Solutions

Expert Solution

Upgradation of system
Year 0 1 2 3 4 5
Cost of upgradation -2400000
recovery of working capital 300000
pre tax operating savings 700000 700000 700000 700000 700000
less annual depreciation = 2400000/5 480000 480000 480000 480000 480000
before tax savings 220000 220000 220000 220000 220000
after tax savings = before tax savings*(1-tax rate ) tax rate = 21% 173800 173800 173800 173800 173800
after tax salvage value =sale value*(1-tax rate) = 400000*(1-.21) 316000
net operating cash flow = after tax savings+ annual depreciation+after tax salvage value -2100000 653800 653800 653800 653800 969800
NPV = Using NPV function in MS excel NPV(12.5%,G523:K523)+F523 403257.1322
IRR = Using IRR function in MS excel IRR(F523:K523) 0.196352715
Company should accept the plan as NPV is positive and IRR is greater than required rate of return of 12.5%
New product line
Year 0 1 2 3 4 5 6 7 8 9 10
Initial investment -2400000
Investment in working capital -300000
Increased sales 830000 830000 830000 830000 830000 830000 830000 830000 830000 830000
increased cost 200000 200000 200000 200000 200000 200000 200000 200000 200000 200000
annual depreciation =2400000/10 240000 240000 240000 240000 240000 240000 240000 240000 240000 240000
operating profit 390000 390000 390000 390000 390000 390000 390000 390000 390000 390000
after tax profit = operating profit*(1-tax rate) tax rate = 21% 308100 308100 308100 308100 308100 308100 308100 308100 308100 308100
add depreciation 240000 240000 240000 240000 240000 240000 240000 240000 240000 240000
add recovery of working capital 300000
recovery of after tax sale value = 100000*(1-.21) 79000
net operating cash flow = after tax profit+depreciation + recovery of working capital +recovery of after tax sale value -2700000 548100 548100 548100 548100 548100 548100 548100 548100 548100 927100
net present value = Using NPV function in MS excel NPV(12.5%,G540:P540)+F540 451229.3217
IRR = Using IRR function in MS excel IRR(F540:P540) 16.33%
Upgradation of system New product line
NPV 403257.1322 451229.3217 new product line would be selected as NPV is greater
IRR 19.64% 16.33% As Per IRR both can be selected as IRR of both the options are greater than MARR of 12.5%
As the projects are mutually exclusive NPV favors option of new product line and IRR favors Upgradation of systme so as both projects of unequal life in this case IRR can give misleading results so NPV would be the appropriate method to the final selection of choice so in this case option of new product line would be preferred

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable). Please show excel formulas. Given the following information for Bajor Co.: Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time to maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100). Preferred stock: Bajor has not...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) Case Two (22 pts) Given the following information for Bajor Co.: Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time-to-maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100). Preferred stock: Bajor has not issued any preferred stocks....
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas You apply for a 20-year, fixed-rate (APR 6.48%) monthly-payment-required mortgage loan for a house selling for $150,000 today. Your bank requires 22% initial down payment of house value (to be paid in cash immediately),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas Case 3: We find the data for a municipal bond issued by the Illinois state government. The bond’s “last trade date” (i.e., settlement date) is June 05, 2019. The bond’s “maturity date” is March...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable. Given the following information for...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable. Given the following information for Bajor Co.: Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time-to-maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100). Preferred stock: Bajor has not issued any preferred stocks. Common stock (equity): Bajor’s equity capital consists of common stocks with the...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor Co.: Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time-to-maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100). Preferred stock: Bajor has not issued any preferred stocks. Common stock (equity): Bajor’s equity capital consists of common stocks with the most...
use Excel Solver to answer the following question.You must provide sufficient evidence that you did the...
use Excel Solver to answer the following question.You must provide sufficient evidence that you did the work on your own. For example, what equations did you use in your model? How was the excel spread sheet laid out? A student at a local university has just completed a decision modeling course. On her assignments, she has earned a 86 on the mid-term, a 94 on the final, a 93 on problem sets, and 85 for participation. She has a unique...
In part 2 you will be creating multiple functions to calculate the present value. You may...
In part 2 you will be creating multiple functions to calculate the present value. You may be asking what a "present value" is. Suppose you want to deposit a certain amount of money into a savings account and then leave it alone to draw interest for some amount of time, say 12 years. At the end of the 12 years you want to have $15,000 in the account. The present value is the amount of money you would have to...
Create an Excel spreadsheet in which you use capital budgeting tools including net present value (NPV),...
Create an Excel spreadsheet in which you use capital budgeting tools including net present value (NPV), internal rate of return (IRR), payback period, and profitability index (PI) to determine the quality of 3 proposed investment projects, as well as an analysis of your computations and recommends the project that will bring the most value to the company. The analysis of the capital projects will need to be correctly computed and the resulting decisions rational. Scenario You work as a finance...
As part of the settlement for a class action lawsuit, Hoxworth Corporation must provide sufficient cash...
As part of the settlement for a class action lawsuit, Hoxworth Corporation must provide sufficient cash to make the following annual payments (in thousands of dollars): Year 1 2 3 4 5 6 Payment 195 220 270 320 350 470 The annual payments must be made at the beginning of each year. The judge will approve an amount that, along with earnings on its investment, will cover the annual payments. Investment of the funds will be limited to savings (at...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT