You will need to use Excel or Google sheets for all of the analysis. You should show all you work (FCF and NPV calculations) for each question so that I can give you partial credits at least.

Iceman Inc.

Your boss, Mr. Kuklinski, believes that Southern California will soon run short of water. One of the ramifications, other than widespread rioting, is that there will be high demand for fresh, potable water. He is considering starting an iceberg transport company named Iceman Inc. to address this need and has asked you to help him analyze the project.

The timeline for the proposed business is as follows: In Year 0 (i.e., this year), Mr. Kuklinski would acquire the necessary assets and set up operations. In Years 1, 2, 3, and 4, the business would be in full operation, shuttling icebergs from Alaska to Long Beach. He plans to sell the ships at the end of Year 4. In Year 5 nothing will happen except he will collect all the outstanding working capital. He will then move to Costa Rica, where they have plenty of water for both drinking and surfing.

Over the past year, Mr. Kuklinski has paid $200,000 to consultants to write a report with all the relevant figures for this project. This report suggests that he can purchase eight ships in Year 0 for $2,250,000 each. Ships dont lose much value if they are well maintained, so it is expected that the ships will have a resale value of $2,000,000 at the end of Year 4. The ships are quite durable so they will be straight-line depreciated to zero over 20 years.

The consulting report predicts that the company will be able to sell water at $0.90 per gallon. The water will be melted from the iceberg at the rate of 8.35 pounds of ice to one gallon of water. However, 4% of the ice will be lost due to evaporation during transit.

The plan is to pick up 8 million pounds of icebergs with each ship in Year 1. As water becomes more scarce, the consultants expect the cargo to increase by 10% in Year 2, and then by another 10% in Year 3, and by another 10% in Year 4. The tax rate is 21%, and the required rate of return for this type of businesses is 11% per year. The majority of Iceman Incs expenses are related to operating and maintaining the ships. Variable costs (including gasoline, maintenance and repairs, insurance, hourly wages for the captain and crew, etc.) are expected to be 50% of same-year sales. Working capital requirements (including accounts receivable and inventory) are expected to be $200,000 in Year 0, 10% of same-year sales in Years 1 to 4, and $0 in Year 5. Iceman Inc. will sign a four-year lease for a slip in the Port of Long Beach at the rate of $600,000 per year in each of Years 1 through 4, and this is expected to be the only fixed cost for the business.

1) What is the after tax salvage value of the ships? Show your work in the spreadsheet. (5 points)

2) Based on the assumptions in the attached case description, calculate the NPV of this project. Should your company accept the project? (12 points)

3) The last possibility that the consultants raised in their report is that Iceman Inc might face increased competition from desalination technologies in the 4th year of the project. There is a 30% chance that Iceman Inc. would need to cut their prices by 10 cents per gallon to sell the number of gallons projected for year 4? What is the resulting expected value of the NPV considering the two scenarios? (8 points)