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

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.

	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.

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...

Subjects