please show excel formulas

B D F I H E Assigned Problem 3 California Health Center, a for-profit hospital, is evaluating the purchase of new diagnostic equipment. The equipment, which costs $600,000, has an expected life of five years and an estimated pretar salvage value of $200,000 at that time. The equipment is expected to be used 15 times a day for 250 days a year for each year of the project's life. On average, each procedure is expected to generate $80 in collections, which is net of bad debt losses and contractual allowances, in its first year of use. Thus, net revenues for Year 1 are estimated at 15 1 250 1 580 = $300,000. Labor and maintenance costs are expected to be $100,000 during the first year of operation, while utilities will cost another $10,000 and cash overhead will increase by $5,000 in Year 1. The cost for expendable supplies is expected to average $per procedure during the first year. All costs and revenues, except depreciation, are expected to increase at a 5 percent Inflation rate after the first year. The equipment falls into the MACRS five-year class for tax depreciation and hence is subject to the following depreciation allowances: Year 2 3 Allowance 1 0.2 4 0.12 3 0.11 6 0.06 0.32 0.19 The hospital's tax rate is 40 percent and its corporate cost of capital is 10 percent. u. Estimate the project's net cash flows over its five-year estimated life, b. What are the project's NPV and IRR? (Assume that the project has average risk.) ANSWER Data Procedures per day Days per year Equipment cost Pretas equipment salvage value Net revenues Labor/maintenance costs Utilities costs Supplies per procedure Incremental overhead Tax rate Inflation rate D E F G H J 1 C 39. Eration rate 40 CCC 41 42 43 Annual project's cash outflows and inflows 44 45 Equipment cost 46 Net revenue 47 Labor and maintenance costs 48 Untities costs 49 Supplies 50 Incremental overhead 51 Depreciation 52 Operating Income 53 Tere 54 Net operating income 55 Depreciation 58 Pretax equipment salvage value 57 MACRS equipment age value 58 Difference 50 Times (on the DIY) 80 Aftertas quipment sale value 61 Net cash flow -12 -332-157 -154-155-160 63 56 65 NPV 66 TRR 67 58 60 B D F I H E Assigned Problem 3 California Health Center, a for-profit hospital, is evaluating the purchase of new diagnostic equipment. The equipment, which costs $600,000, has an expected life of five years and an estimated pretar salvage value of $200,000 at that time. The equipment is expected to be used 15 times a day for 250 days a year for each year of the project's life. On average, each procedure is expected to generate $80 in collections, which is net of bad debt losses and contractual allowances, in its first year of use. Thus, net revenues for Year 1 are estimated at 15 1 250 1 580 = $300,000. Labor and maintenance costs are expected to be $100,000 during the first year of operation, while utilities will cost another $10,000 and cash overhead will increase by $5,000 in Year 1. The cost for expendable supplies is expected to average $per procedure during the first year. All costs and revenues, except depreciation, are expected to increase at a 5 percent Inflation rate after the first year. The equipment falls into the MACRS five-year class for tax depreciation and hence is subject to the following depreciation allowances: Year 2 3 Allowance 1 0.2 4 0.12 3 0.11 6 0.06 0.32 0.19 The hospital's tax rate is 40 percent and its corporate cost of capital is 10 percent. u. Estimate the project's net cash flows over its five-year estimated life, b. What are the project's NPV and IRR? (Assume that the project has average risk.) ANSWER Data Procedures per day Days per year Equipment cost Pretas equipment salvage value Net revenues Labor/maintenance costs Utilities costs Supplies per procedure Incremental overhead Tax rate Inflation rate D E F G H J 1 C 39. Eration rate 40 CCC 41 42 43 Annual project's cash outflows and inflows 44 45 Equipment cost 46 Net revenue 47 Labor and maintenance costs 48 Untities costs 49 Supplies 50 Incremental overhead 51 Depreciation 52 Operating Income 53 Tere 54 Net operating income 55 Depreciation 58 Pretax equipment salvage value 57 MACRS equipment age value 58 Difference 50 Times (on the DIY) 80 Aftertas quipment sale value 61 Net cash flow -12 -332-157 -154-155-160 63 56 65 NPV 66 TRR 67 58 60