The following is a problem in capital budgeting. Please use a spreadsheet to solve the problem and attach the printout to your solutions. The Finance Director at the Cola Inc. has to decide whether to offer a new drink to the US market. Cola Inc. has thus far spent $500,000 in test marketing this drink. The test marketing results indicate a favorable response from consumers.

Cola Inc. estimates that it can sell a 6-pack (1 unit) of the new drink for $2.00. Forecasted sales of this product are 250,000 units in the first year, 2 million units in the second year, 7.5 million units in the third year, 17.5 million units in year 4 and 20 million units in years 5 and 6. It has also estimated the variable cost of producing the drink at $1.20 per unit and the fixed costs are estimated to be $150,000 per year. In order to produce the new drink, the company must purchase new equipment at a cost of $1,000,000. This equipment is depreciable on a straight line basis over 6 years and is expected to have a salvage value of $30,000 in six years.

Cola Inc. will need an investment of $20,000 up front for net working capital. It estimates the increase in net working capital to be $15,000 in the first year, $30,000 in the second year, and 10% of revenues after the first two years. At the end of the life of the project, the investment in working capital can be recovered. The tax rate for Cola Inc. is 50% and the required rate of return on the investment is 16%.

What is the NPV of the project? Should Cola Inc. introduce the new drink into the market?