Fin 336: Mini-case #2 Coastal Hydraulics Project Instructions Coastal Hydraulics Corporation wants to expand its operation into a new geographic area. The expansion plans include the purchase of a warehouse and new equipment. The project is placed into service and begins generating income in January 2021 and will terminate at the end of 2030. The details are provided below: *Warehouse cost is ..$1,150,000 *Apply the MACRS TABLES schedule for the warehouse: MACRS Table 7 Non-residential real property -- 39 Years (Month placed in service is January) *Equipment cost installed) is. ..$250,000 ** Apply the MACRS TABLES schedule for the equipment (using the schedule for a 15-year useful asset life): MACRS Table 1 MACRS Rates Half-Year Convention (Use zero decimals for all dollar figures and two decimals for all percent figures, eg. $200,000 and 2.53%) Sales Revenues are expected to be $300,000 in year 1, and are expected to increase by 10 percent per year for the next 3 years, remain unchanged for 3 more years, and then decline by 7 percent per year for the last 3 years. Annual COGS are expected to be 20 percent of sales for the year (every year). Annual SG&A costs are expected to be 8 percent of sales for the first 5 years, and then decrease by 10 percent per year from the year 5 level after that. The project will require an initial investment (time period 0) in Net Working Capital $20,000. The balance in the NWC account each year after that will be 10 percent of that year's sales. At the end of 10 years, the Corporation plans on selling the warehouse and equipment and the owner will retire. The warehouse will be sold for $800,000 and the equipment will be sold for 80 percent of its book value. Coastal Hydraulics WACC is 10%. Their tax rate is 35%. Assignment: 1) Create an Excel Spreadsheet with all of the data contained on one page (Use landscape as your page format). 2) Show the cash flows from year 2021 through year 2030. 3) Starting with revenues, work down the spreadsheet, through costs, EBIT, taxes, NOPAT, OCF, NWC, and finally arrive at Net Cash Flow (NCF). 4) Use NCF to calculate: NPV, IRR, and Payback. 5) Include in your spreadsheet a section showing the initial investment, a section showing the depreciation percent for both assets each year (along with the book value of the asset after depreciation), and a section showing the NPV, IRR, and Payback. 6) Be sure that all of this appears on a single spreadsheet. Fin 336: Mini-case #2 Coastal Hydraulics Project Instructions Coastal Hydraulics Corporation wants to expand its operation into a new geographic area. The expansion plans include the purchase of a warehouse and new equipment. The project is placed into service and begins generating income in January 2021 and will terminate at the end of 2030. The details are provided below: *Warehouse cost is ..$1,150,000 *Apply the MACRS TABLES schedule for the warehouse: MACRS Table 7 Non-residential real property -- 39 Years (Month placed in service is January) *Equipment cost installed) is. ..$250,000 ** Apply the MACRS TABLES schedule for the equipment (using the schedule for a 15-year useful asset life): MACRS Table 1 MACRS Rates Half-Year Convention (Use zero decimals for all dollar figures and two decimals for all percent figures, eg. $200,000 and 2.53%) Sales Revenues are expected to be $300,000 in year 1, and are expected to increase by 10 percent per year for the next 3 years, remain unchanged for 3 more years, and then decline by 7 percent per year for the last 3 years. Annual COGS are expected to be 20 percent of sales for the year (every year). Annual SG&A costs are expected to be 8 percent of sales for the first 5 years, and then decrease by 10 percent per year from the year 5 level after that. The project will require an initial investment (time period 0) in Net Working Capital $20,000. The balance in the NWC account each year after that will be 10 percent of that year's sales. At the end of 10 years, the Corporation plans on selling the warehouse and equipment and the owner will retire. The warehouse will be sold for $800,000 and the equipment will be sold for 80 percent of its book value. Coastal Hydraulics WACC is 10%. Their tax rate is 35%. Assignment: 1) Create an Excel Spreadsheet with all of the data contained on one page (Use landscape as your page format). 2) Show the cash flows from year 2021 through year 2030. 3) Starting with revenues, work down the spreadsheet, through costs, EBIT, taxes, NOPAT, OCF, NWC, and finally arrive at Net Cash Flow (NCF). 4) Use NCF to calculate: NPV, IRR, and Payback. 5) Include in your spreadsheet a section showing the initial investment, a section showing the depreciation percent for both assets each year (along with the book value of the asset after depreciation), and a section showing the NPV, IRR, and Payback. 6) Be sure that all of this appears on a single spreadsheet