Question

Plasticon manufactures plastic containers used to package a variety of liquid consumer products (such as fabric softener, cleaners, shampoo, hair spray, and liquid soap).

Plasticon has received five proposals for capital investment projects. Your job is to evaluate these proposals and rank them in the order in which they should be funded. Begin your analysis by computing the average rate of return and cash payback period for each proposal. Any project that has an average rate of return of less than 15 percent or a cash payback period of longer than five years should be eliminated from further consideration. After this initial screening, compute the net present value (using a 15 percent discount rate) and internal rate of return for the remaining projects. Rank the projects based on both their profitability and overall merit to the corporation (qualitative factors).

Projects: A B C D E

Project A: This proposal requests funds to purchase hardware and software that will allow the accounting department to process payroll in-house. Paychecks are currently processed by an outside payroll service company. The annual increase in net income and cash flows will result from cost savings if the payroll function is no longer contracted to an outside company.

Project B: This proposal requests funds for new manufacturing equipment. This equipment will allow Plasticon to make containers as large as ten gallons. Currently, Plasticon can not make containers that are larger than three gallons.

Project C: This proposal requests funds for equipment to make stick-on labels that are applied to the plastic containers. Currently, all stick-on labels are ordered from another company. This supplier has not proven very reliable in meeting delivery deadlines.

Project D: This proposal requests funds for automated manufacturing equipment that will reduce the cycle time from receipt of a customer order to delivery of that order. Plasticon’s cycle time is currently seven days. The automated equipment will reduce that time to four days while saving costs due to the elimination of five jobs. It will also make Plasticon more competitive; the company’s major competitor currently has a cycle time of five days.

Project E: This proposal requests funds for computerized drafting and design equipment that will allow engineers to complete manufacturing instructions on special orders more quickly. This equipment should reduce Plasticon’s cycle time from seven to five days.

Present Value of an Annuity of $1 at Compound Interest

Period 12% 13% 14% 15% 16% 17% 18%

8. 4.968 4.799 4.639 4.487 4.344 4.207 4.078

10.   5.650 5.426 5.216 5.019 4.833 4.659 4.494

Capital Investment Analysis

Calculate Average Rate of Return, Cash Payback, NPV and IRR for each project. Show your work below.

Projects
	A	B C		D	E
Average Rate of Return
Cash Payback
Net Present V alue
IRR

Which Projects should be considered?

Answer 1

Formulas:

B8 =B6/B2

B10 =B2/B6

B12 =(1.15^B3-1)/0.15/1.15^B3

B13 =-B2+B6*B12

B15 =((1+B17)^B3-1)/B17/(1+B17)^B3

B16 =-B2+B15*B6

These formulas are repeated for cells of each project.

IRR is obtained by using Goal seek in Excel. Screenshot is included to show Goal seek setup for project A. Similarly, Goal seek is used to determine the IRR for each of the projects separately.

Based on the analysis, projects A and B have negative NPV at 15% and projects B and C have payback greater than 5 years. Therefore, projects A, B and C are excluded from further analysis.

Out of the remaining two projects D and E, project D gives the higher NPV @ 15%, Therefore, from NPV perspective, project D is recommended. However, from Average rate of return, cash payback and IRR perspective, project E is recommended.