Please Use Excel

The Cola Beverage Co. is considering two alternative capital projects. Project 1: expansion of its existing traditional line of cola beverages. Project 2: introduction of a new "healthy" line of beverages. The company has already spent $1,400,000 on research into the feasibility of these projects and has arrived at the following financial projections.

Both projects are estimated to have a life of 5 years. The required fixed asset investment or capital spending for project 1 is $30,000,000 and for project 2 is $35,000,000. Fixed assets will be depreciated straight line to zero. Fixed asset salvage value estimate for project 1 is $6,200,000 and for project 2 is $8,500,000

In order to support anticipated sales, either project will require an increase in inventory of $8,600,000, an increase in accounts receivable of $2,800,000, and an increase in accounts payable of $6,300,000

Cost of goods sold is projected at 35% of sales for either project. Selling, general and administrative (SG&A) expenses for project 1 is 12% of sales and for project 2 is 15% of sales. The higher SG&A % for project 2 is due to higher anticipated marketing expenses

The cost of capital applicable to either project is estimated at 8%. The firm's corporate tax rate is 35%

The sales projections for both projects are as follows:

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

Project 1: Traditional Line

13,500,000

14,500,000

15,200,000

15,700,000

16,000,000

Project 2: "Healthy" Line

16,500,000

17,800,000

18,400,000

19,300,000

20,000,000

Evaluate the two capital investment project alternatives and make your recommendation using:

a). Net Present Value (NPV)

b). Internal Rate of Return (IRR)

c). Modified Internal Rate of Return (MIRR)