Course Project - Week 5 Guidelines: Start with your Excel workbook from week #4 and create two new tabs: (5th), titled Capital Requirements and Depreciation, and (6th) titled Loan and Interest. Use these tabs to show your detailed calculations. Grading: 100 points based on correctness of your answer- capital requirements, depreciation, loan and interest expenses Create 2 tabs as stated above use information below. Given Information: Capital Requirements and Depreciation: Use the data given below to calculate the following: o Calculate the total capital requirements for the listed assets plus any cash requirements for start-up of the new Ambulatory Surgery Center building. Start-up cash is used to establish a beginning inventory and cover payroll costs for staff orientation and training prior to the opening date. o Calculate the annual depreciation and total depreciation for each item up through the end of its useful life, or year ten, whichever is shorter. Also sum total depreciation for ALL items for each year. Note: Start up cash is not depreciated- do not include it in this portion. Assumptions o For the number of operating rooms, refer to your tab Personnel Expenses. Be sure to use the number of operating rooms that you calculated for 2020 (rounded up to the next whole number) - that is the number that you will need to build and equip to accommodate the planned growth for the ASC . o The recovery room space will be one large room with partitions for each patient. o Building and equipment depreciation is recorded on the balance sheet as PP&E during construction, but the depreciation expense does not start until the building is put into use. Therefore, for 2017, record depreciation as 0. Because we anticipate a January 2018 start date, record an entire year of depreciation starting in 2018. o Assume straight line depreciation and no salvage value. Loan and interest expenses : Use the data given below to calculate the following for EACH option: Part One: o Calculate the annual payment using the PMT function (hint: use 0 for type), and sum the total payments and total interest expense over the life of the loan. o Write a 1-2 sentence analysis of which option you would choose and why. o Under Option A, 50% of the loan is being funding by equity (cash the hospital has on hand). That equity would otherwise be invested and earn investment income at a rate of 5%/year. Calculate the amount of interest income that the hospital will lose over the term of the loan by using their equity for financing. Hint: first calculate the total value of the investment, then subtract the beginning balance to calculate the interest earned. o Write a 1-2 sentence analysis of which option you would choose now, and why. Part Two: o Create a loan amortization table over the length of the financing. You should use the example from the asynchronous lecture. While that slide was shown in months, your analysis for this project should be shown in years. Hint: If your table is correct, the total payments and total interest should equal the numbers you calculated in the above step, and remaining principal should equal zero in the final year. o Debt will be acquired to fund construction, and payments, will begin in January 2015. Assumptions o The hospital only needs to fund the capital equipment, the startup costs are being funded from existing cash. o The hospital has an S&P credit rating of A that was used to determine interest rates. o The length of a bank loan (Options A&B) is tied to the useful life of the asset that is being financed. The items in this project have a variety of useful lives. For this project, we are assuming an average useful life of 15 years, so the entire loan will be over 15 years. In reality, your bank may require more than one loan, based on the useful life of each different group of building/equipment. o Bonds (Option C) are not restricted by the useful life of the asset they are financing, so they are able to cover long terms- 20 years for our project.