Preston Plastics is about to wrap up its capital budgeting cycle, and department managers across the company

Question:

Preston Plastics is about to wrap up its capital budgeting cycle, and department managers across the company have submitted 500 capital project requests for consideration in the next round of funding. Preston's CFO, Dan LaMontagne, is trying to decide which projects to recommend for funding to the capital projects executive committee. He has gathered all the information about each project's estimated life, initial investment, and cash flows in an Excel spreadsheet and is ready to make his decisions. Preston requires all capital projects to have a payback period of 5 years or less and uses a 12% discount rate.

Required

a. Calculate the net present value (use Excel's NPV formula with a 12% discount rate), internal rate of return (use Excel's IRR formula), payback period, and profitability index for each project and save in the worksheet.

b. How many of the submitted projects meet the payback period screening criterion? Hint: Use Excel's COUNTIF formula to easily calculate this number.

c. Copy the original data and your calculations from part (a) to a new worksheet. Sort the data by payback period and delete all projects that do not meet the screening criteria.

d. Preston has only $2 billion in capital funds to allocate this period. Will this be enough to fund all the requested projects that meet the payback screening criterion? What is the shortfall amount?

e. Suppose Preston chooses to fund capital projects based on net present value. How many projects will be funded? What is the total amount of capital funds that will be allocated? Hint: Sort the data from part (c) by net present value and calculate a running sum of project investment to help you determine which projects can be funded.

f. Suppose Preston chooses to fund capital projects based on internal rate of return. How many projects will be funded? What is the total amount of capital funds that will be allocated? Hint: Sort the data from part (c) by internal rate of return and calculate a running sum of project investment to help you determine which projects can be funded.

g. Suppose Preston chooses to fund capital projects based on profitability index. How many projects will be funded? What is the total amount of capital funds that will be allocated? Hint: Sort the data from part (c) by profitability index and calculate a running sum of project investment to help you determine which projects can be funded.

h. Which of the three metrics do you recommend that Preston use to allocate the $2 billion to fund the capital projects? Why?

i. Dan has just learned that projects 16808 and 17011 are required to meet new regulatory requirements, and therefore must be funded regardless of the metrics. How will this information change your funding recommendations?

The Excel data files for answering this problem can be found in WileyPLUS.

Net Present Value
What is NPV? The net present value is an important tool for capital budgeting decision to assess that an investment in a project is worthwhile or not? The net present value of a project is calculated before taking up the investment decision at... Internal Rate of Return
Internal Rate of Return of IRR is a capital budgeting tool that is used to assess the viability of an investment opportunity. IRR is the true rate of return that a project is capable of generating. It is a metric that tells you about the investment... Capital Budgeting
Capital budgeting is a practice or method of analyzing investment decisions in capital expenditure, which is incurred at a point of time but benefits are yielded in future usually after one year or more, and incurred to obtain or improve the... Payback Period
Payback period method is a traditional method/ approach of capital budgeting. It is the simple and widely used quantitative method of Investment evaluation. Payback period is typically used to evaluate projects or investments before undergoing them,...