Ground Rules

This project will require you to create an Excel spreadsheet and upload it to Canvas. Excel must perform all of your calculations. If you calculate a value and then type the result into Excel, or if you type a number (rather than a cell reference) into a formula, or if you type in a value more than once, you will receive no credit. Use your calculator to check your work. Your completed project must be submitted on Canvas by 11:59 pm CST on Saturday, August 4, 2020.

The Assignment

The Water Control Systems department of Poseidon, Inc. is considering a project that requires acquiring new equipment at an installed cost of $2,200,000. The equipment will be depreciated on a straight-line basis over five years, to a value of $0. The project will cause an immediate change in several of the current accounts, as follows: Accounts Receivable will increase by $450,000, Inventory will increase by $550,000, and Accounts Payable will increase by $400,000. At the end of the 5^th year, the project will be terminated, the current accounts will return to their pre-project levels, and the equipment will be sold for an estimated $500,000.

In the year just ended, the Water Control Systems department generated $4.5 million in Sales Revenue. It also incurred $2,925,000 in Cost of Goods Sold expense, and $1,066,250 in Selling, General & Administrative expenses. Poseidon expects that, without the new project, the department will cease operations and future cash flows will be zero. However, if the new project is implemented, it would cause the following changes in the departments operating results:

• Sales Revenue will increase 6.0% per year.
• COGS Expense will remain at the same percentage of sales as last year.
• Selling, General & Administrative Expenses would increase 5.5% per year.

Poseidons combined federal, state and local income tax rate is 22%.

Poseidon has 5 million shares of common stock outstanding; the current price per share is $63.05. The companys beta is 1.70. Treasury bills are yielding 0.14%. The stock market is expected to earn 7% per year.

The company issued 100,000 10-year, 4.25% coupon bonds 2 years ago. The bonds have a par value of $1,000. Coupons are paid semi-annually. The bonds are currently trading for $989.32.

The company has no other items in its capital structure.

Prepare an analysis to determine whether Poseidon should implement this capital project. Your spreadsheet should display all dollar figures rounded to the whole dollar, except for per share or per bond data, which, along with all percentage figures, should be displayed to 2 decimal places. Your analysis should provide answers to each of the following questions:

1. (10 points) What is the initial investment required to implement the project?
2. (10 points) What are the operating cash flows to be generated by the project?
3. (10 points) What is the terminal cash flow resulting from the project?
4. (10 points) What is the required rate of return, R_E, on the companys equity?
5. (10 points) What is the pre-tax required rate of return, R_D, on the companys debt?
6. (10 points) What is the companys Weighted Average Cost of Capital, (WACC)?
7. (10 points) What is the projects Net Present Value (NPV)?
8. (10 points) What is the projects Internal Rate of Return (IRR)?
9. (10 points) Should the company implement the project? Why or why not? If the company implements the project, how much will they earn (or lose)?
10. (10 points) What will be the change in the stock price if the project is implemented?