Question

In: Finance

Calculate the following problems using Microsoft® Excel®: Calculate the NPV for each project and determine which project should...

Calculate the following problems using Microsoft® Excel®:

Calculate the NPV for each project and determine which project should be accepted.

Project A Project B Project C Project D
Inital Outlay (105,000.000) (99,000.00) (110,000.00) (85,000.00)
Inflow year 1 53,000.00 51,000.00 25,000.00 45,000.00
Inflow year 2 50,000.00 47,000.00 55,000.00 50,000.00
Inflow year 3 48,000.00 41,000.00 15,000.00 30,000.00
Inflow year 4 30,000.00 52,000.00 21,000.00 62,000.00
Inflow year 5 35,000.00 40,000.00 35,000.00 68,000.00
Rate 7% 10% 13% 18%

Your company is considering three independent projects. Given the following cash flow information, calculate the payback period for each. If your company requires a three-year payback before an investment can be accepted, which project(s) would be accepted?

Project D Project E Project F
Cost 205,000.00 179,000.00 110,000.00
Inflow year 53,000.00 51,000.00 25,000.00
Inflow year 2 50,000.00 87,000.00 55,000.00
Inflow year 3 48,000.00 41,000.00 21,000.00
Inflow year 4 30,000.00 52,000.00 9,000.00
Inflow year 5 24,000.00 40,000.00 35,000.00

Using market value and book value (separately), find the adjusted WACC, using 30% tax rate.

Component Balance Sheet Value Market Value Cost of Capital
Debt 5,000,000.00 6,850,000.00 8%
Preferred Stock 4,000,000.00 2,200,00.00 10%
Common Stock 2,000,000.00 5,600,000.00 13%

Solutions

Expert Solution

1.

Year Project A PVF @ 7 % Present value Project B PVF @ 10 % Present value Project C PVF @ 13 % Present value Project D PVF @ 18 % Present value
1 53000 0.935 49532.710 51000 0.909 46363.636 25000 0.885 22123.894 45000 0.847 38135.593
2 50000 0.873 43671.936 47000 0.826 38842.975 55000 0.783 43073.068 50000 0.718 35909.221
3 48000 0.816 39182.298 41000 0.751 30803.907 15000 0.693 10395.752 30000 0.609 18258.926
4 30000 0.763 22886.856 52000 0.683 35516.700 21000 0.613 12879.693 62000 0.516 31978.910
5 35000 0.713 24954.516 40000 0.621 24836.853 35000 0.543 18996.598 68000 0.437 29723.427
Present value of cash in flow        180,228.32        176,364.07        107,469.00        154,006.08
Cash ouflow at the Beginning -105000 -99000 -110000 -85000
NPV          75,228.32 NPV          77,364.07 NPV          (2,531.00) NPV          69,006.08

If company want to select only one project then , Project B should be selected because it has highest NPV.

If company can select multiple project then A, B and D should be selected.

2.

Project D Project E Project F
Years Inflows Cummulative Inflow Inflows Cummulative Inflow Inflows Cummulative Inflow
1 53000 53000 51000 51000 25000 25000
2 50000 103000 87000 138000 55000 80000
3 480000 583000 41000 179000 21000 101000
4 30000 613000 52000 231000 9000 110000
5 24000 637000 40000 271000 35000 145000
Cost -205000 -179000 -110000

Project D payback period = 2 + (( 205000-103000))/480000 = 2.2125 years

Project E payback period = 3 years

Project F payback period = 4 years

If your company requires a three-year payback before an investment then D and E should be accepted.

3.

WACC using Book weights
Balance sheet value Proportionate (A) Cost of capital Cost ( Net of tax) (B) WACC (A) * (B)
Debt 5000000 0.4545 8% 5.6% 0.0255
Preferred Stock 4000000 0.3636 10% 10% 0.0364
Common Stock 2000000 0.1818 13% 13% 0.0236
total 11000000 1 0.0855
8.55%
WACC using Market value
Market value Proportionate ( A) Cost of capital Cost ( Net of tax) (B) WACC (A) * (B)
Debt 6850000 0.4676 8% 5.6% 0.0262
Preferred Stock 2200000 0.1502 10% 10% 0.0150
Common stock 5600000 0.3823 13% 13% 0.0497
Total 14650000 1.0000 0.0909 i. e. 9.09 %

Only debt cost is net tax because it is allowed for tax saving.

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Calculate the NPV for each project and determine which project should be accepted. Project A Project...
Calculate the NPV for each project and determine which project should be accepted. Project A Project B Project C Project D Inital Outlay (105,000.000) (99,000.00) (110,000.00) (85,000.00) Inflow year 1 53,000.00 51,000.00 25,000.00 45,000.00 Inflow year 2 50,000.00 47,000.00 55,000.00 50,000.00 Inflow year 3 48,000.00 41,000.00 15,000.00 30,000.00 Inflow year 4 30,000.00 52,000.00 21,000.00 62,000.00 Inflow year 5 35,000.00 40,000.00 35,000.00 68,000.00 Rate 7% 10% 13% 18% Your company is considering three independent projects. Given the following cash flow information,...
Calculate the NPV for each project and determine which project should be accepted. Project A Project...
Calculate the NPV for each project and determine which project should be accepted. Project A Project B Project C Project D Initial Outlay (105,000.00) (99,000.00) (110,000.00) (85,000.00) Inflow year 1 53,000.00 51,000.00 25,000.00 45,000.00 Inflow year 2 50,000.00 47,000.00 55,000.00 50,000.00 Inflow year 3 48,000.00 41,000.00 15,000.00 30,000.00 Inflow year 4 30,000.00 52,000.00 21,000.00 62,000.00 Inflow year 5 35,000.00 40,000.00 35,000.00 68,000.00 Rate 7% 10% 13% 18% NPV = $75,228.32 $77,364.07 -$2,531.00 $69,006.08 Answer: Project B How to show work...
QUESTION: Using Microsoft® Excel®, run a chi square Goodness of Fit test to determine whether these...
QUESTION: Using Microsoft® Excel®, run a chi square Goodness of Fit test to determine whether these observed starting salaries are significantly different. What do the findings tell you? Expected Salaries Observed Salaries Applicant 1 $20,000 $17,500 Applicant 2 $20,000 $20,000 Applicant 3 $20,000 $22,000 Applicant 4 $20,000 $20,500 Applicant 5 $20,000 $20,000
1. Using the Data Set, create and calculate the following in Excel®: Determine the range of...
1. Using the Data Set, create and calculate the following in Excel®: Determine the range of values in which you would expect to find the average weekly sales for the entire sales force in your company 90% of the time, and calculate the following: A. The impact of increasing the confidence level to 95% B. The impact of increasing the sample size to 150, assuming the same mean and standard deviation, but allowing the confidence level to remain at 90%...
Calculate the NPV for the following project. The company wants to determine whether it should expand...
Calculate the NPV for the following project. The company wants to determine whether it should expand and purchase a new piece of equipment (20% CCA Rate). The equipment will cost $100,000, have a useful life of 20 years, and have a projected salvage value of $5,000. By purchasing the equipment, the company can increase its net revenues by $7,500 per year. Using a discount rate of 10% and a tax rate of 30%, should the company purchase the equipment?
Calculate the following stock valuation problems using Microsoft Excel: Company X is paying an annual dividend of...
Calculate the following stock valuation problems using Microsoft Excel: Company X is paying an annual dividend of $1.35 and has decided to pay the same amount forever. How much should you pay for the stock, if you want to earn an annual rate of return of 9.5% on this investment? You want to purchase common stock of Company X and hold it for 7 years. The company just announced they will be paying an annual cash dividend of $6.00 per share...
Using Microsoft® PowerPoint®, Microsoft® Word, or a drawing program of your choice, draw a diagram/model of...
Using Microsoft® PowerPoint®, Microsoft® Word, or a drawing program of your choice, draw a diagram/model of a business process with which you are familiar. Write a 1- to 3-page paper that describes the business process. Describe the diagram/model. Propose improvements to the business process. Draw a revised process diagram/model that reflects the improved business process. Format your paper according to APA guidelines. Submit your paper and the two diagrams/models as one Microsoft® Word document to the Assignment
Create a Mind Map using a tool such as Popplet®, Mindmeister®, GoConqr®, or a Microsoft® Word...
Create a Mind Map using a tool such as Popplet®, Mindmeister®, GoConqr®, or a Microsoft® Word document in which you do the following: Define each economic system. Provide an example of each economic system. Compare the pros and cons of each economic system. Identify the economic system we have in the United States. Explain why it is important to have an economic system. Your Mind Map should be between 450 to 525 words total.
1. Calculate the net present value (NPV) for both projects, anddetermine which project should be...
Use the following information to answer the next three questions. Consider the cash flows from two mutually exclusive projects: YearProject AProject B0-$420,000-$420,0001$140,000$400,0002$230,000$110,0003$331,000$140,000The appropriate discount rate is 8.5%.1. Calculate the net present value (NPV) for both projects, and determine which project should be accepted based on NPV. Round both NPVs to the nearest dollar.2. Calculate the internal rate of return (IRR) for both projects, and determine which project should be accepted based on IRR.3. Calculate the net present value (NPV) for both projects...
IN EXCEL< SHOW THE FORMULAS > Determine the Payback period, NPV and IRR for both project...
IN EXCEL< SHOW THE FORMULAS > Determine the Payback period, NPV and IRR for both project A and B (show work). Which Project would you select and why? Be specific. Project A will require an initial investment of $ 200,000 and Project B will require and initial investment of $ 325,000. The cost of capital for both projects is 12%. The cash inflows for the next 5 years are listed below: Project A Project B 0 ($200,000) ($325,000) 1 $50,000...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT