please do C and D will upvote

Calabash Community Hospital Due December 13, 2021 at Noon Download the Case study from the Harvard Business Review Website. Read the case thoroughly before attempting to answer the following questions. 1. What is discounted cash flow (DCF) analysis, and how is it used to make capital expenditure decisions? 2. Is the need to have an ambulatory surgical center (ASC) so great that it is not necessary to perform a DCF analysis? Why or why not? 3. Emily Tang is suggesting a discount rate (MARR) of 7%. Is that appropriate? Why or why not? Determine what rate should be used for the analysis. 4. Using the information in the case study and the excel template provided on Blackboard, conduct a DCF analysis. 5. Perform sensitivity analysis by creating a series of DCF analysis changing the following parameters: a. Reimbursement growth rate - Include -1%, 2%, and 5%. b. Inpatient Hospital effect on profits - $300,000 and $700,000 c. Procedures per year 2000 and 2600 d. 2030 salvage value - $7 million. 6. Display your sensitivity analysis in a summary table and any appropriate graphs. 7. What recommendations would you make regarding the ASC proposal? This needs to be a thorough analysis and summary based on the information determined in the previous questions. Submission Requirements - 1. Submit the following in a single packet word or pdf. a. Cover page b. Executive Summary c. Supporting analysis 1. Case Summary questions and answers d. References if you used anything outside of the case materials 2. Submit Excel worksheet used to perform the analysis. Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of change for reimbursment Fixed cost Annual inflation rate Variable cost Impact on hospital Years 2021 2022 Procedures per year Avg reimbursement rate Net reimbursements/ Incremental Revenue Fixed cost Variable cost Depreciation Net effect on inpatient profits Total Expenses Operating profit/ Net Cash Flow - Capital expenditure Free cash flow - Net present value (NPV) Calabash Community Discounted Cash Flow Al Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of change for reimbursment Fixed cost Capital expenditure Net present value (NPV) Calabash Community Discounted Cash Flow Al Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of hange for reimbursment Fixed cost Annual inflation rate Variable cost Impact on hospital Capital expenditure Salvage value Value of land Account receivable days Years 2023 2024 Procedures per year Avg reimbursement rate Net reimbursements/ Incremental Revenue Fixed cost Variable cost Depreciation Net effect on inpatient profits Total Expenses Operating profit/ Net Cash Flow - Capital expenditure Free cash flow Net present value (NPV) y Hospital nalysis Model Assumptions: Procedures per year Current avg net reimbursement per Title Page Exhibit 2 Exhibit 3 DCF Model ... A B C D E F G . 1 1 CXLit 2 2 Caravash Community Hospital 3 Financial Data on Comparable Companies (financial statement data from 2018) 4 5 Tenet Healthcare 6 Description: Operates 475 outpatient centers, 255 ambulatory surgery centers, 68 hospitals, 23 surgical hospitals, 36 7 8 Revenue $18.3 billion 9 Enterprise value $21.4 billion 10 Market equity value $6.8 billion 11 Total debt $14.6 billion 12 Operating margin 11.0% 13 Return on assets 5.5% 14 Beta 0.75 15 Bond rating Ba 16 17 Surgery Partners 18 Description: Operates 108 ambulatory surgical centers and 15 surgical hospitals in 31 states. 19 20 Revenue $1.8 billion 21 Enterprise value $4.7 billion 22 Market equity value $2.4 billion 23 Total debt $2.3 billion 24 Operating margin 14.7% 25 Return on assets 3.5% 26 Beta 0.80 27 Bond rating B 28 29 Sources: Descriptions and company data are from Yahoo! Finance; bond ratings are from Moody's; Beta estimates are by 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 Title Page Exhibit 2 Exhibits Model A B D 1 2 Exhibit 3 Calabash Community Hospital Capital Markets Information (December 2019) 3 4 5 6 10-Year Government Bond Yields 7 8 Australia 1.19% 9 Brazil 6.82% 10 Canada 1.44% 11 France -0.05% 12 Germany -0.35% 13 Hong Kong 1.47% 14 India 6.47% 15 Italy 1.28% 16 Japan -0.04% 17 Mexico 7.12% 18 South Korea 1.71% 19 United Kingdom 0.67% 20 United States 1.72% 21 22 23 24 25 US Corporate Bond Yields 26 2.32% 27 A 2.81% 28 Baa 3.42% 29 Ba 4.32% 30 B 5.55% 31 32 Note: The bond rating represented the creditworthiness of the bo Title Page Exhibit 2 Exhibits Model A B D E F G H 11 France -0.05% 12 Germany -0.35% 13 Hong Kong 1.47% 14 India 6.47% 15 Italy 1.28% 16 Japan -0.04% 17 Mexico 7.12% 18 South Korea 1.71% 19 United Kingdom 0.67% 20 United States 1.72% 21 22 23 24 25 US Corporate Bond Yields 26 2.32% 27 A 2.81% 28 Baa 3.42% 29 Ba 4.32% 30 B 5.55% 31 32 Note: The bond rating represented the creditworthiness of the borrower, with an "Aa" rating indicating a borrower with a v 33 34 Sources: Government bond yields are from Bloomberg.com (markets/rates-bonds) on December 3, 2019. The corporate 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 A B D E F G H J Calabash Community Hospital Discounted Cash Flow Analysis Model Discount rate Capital expenditure Salvage value Value of land Account receivable days 3 4 Assumptions: 5 Procedures per year 6 Current avg net reimbursement per procedure Annual rate of change for reimbursment 8 9 Fixed cost 10 Annual inflation rate 11 Variable cost 12 Impact on hospital 13 14 Years 15 Procedures per year 16 Avg reimbursement rate 17 Net reimbursements/ Incremental Revenue 18 19 Fixed cost 20 Variable cost 21 Depreciation 22 Net effect on inpatient profits 23 Total Expenses 2021 2022 2023 2024 2025 2026 2027 2028 24 Operating profit/ Net Cash Flow 25 Capital expenditure 26 Free cash flow 27 28 Net present value (NPV) 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 Calabash Community Hospital Due December 13, 2021 at Noon Download the Case study from the Harvard Business Review Website. Read the case thoroughly before attempting to answer the following questions. 1. What is discounted cash flow (DCF) analysis, and how is it used to make capital expenditure decisions? 2. Is the need to have an ambulatory surgical center (ASC) so great that it is not necessary to perform a DCF analysis? Why or why not? 3. Emily Tang is suggesting a discount rate (MARR) of 7%. Is that appropriate? Why or why not? Determine what rate should be used for the analysis. 4. Using the information in the case study and the excel template provided on Blackboard, conduct a DCF analysis. 5. Perform sensitivity analysis by creating a series of DCF analysis changing the following parameters: a. Reimbursement growth rate - Include -1%, 2%, and 5%. b. Inpatient Hospital effect on profits - $300,000 and $700,000 c. Procedures per year 2000 and 2600 d. 2030 salvage value - $7 million. 6. Display your sensitivity analysis in a summary table and any appropriate graphs. 7. What recommendations would you make regarding the ASC proposal? This needs to be a thorough analysis and summary based on the information determined in the previous questions. Submission Requirements - 1. Submit the following in a single packet word or pdf. a. Cover page b. Executive Summary c. Supporting analysis 1. Case Summary questions and answers d. References if you used anything outside of the case materials 2. Submit Excel worksheet used to perform the analysis. Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of change for reimbursment Fixed cost Annual inflation rate Variable cost Impact on hospital Years 2021 2022 Procedures per year Avg reimbursement rate Net reimbursements/ Incremental Revenue Fixed cost Variable cost Depreciation Net effect on inpatient profits Total Expenses Operating profit/ Net Cash Flow - Capital expenditure Free cash flow - Net present value (NPV) Calabash Community Discounted Cash Flow Al Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of change for reimbursment Fixed cost Capital expenditure Net present value (NPV) Calabash Community Discounted Cash Flow Al Assumptions: Procedures per year Current avg net reimbursement per procedure Annual rate of hange for reimbursment Fixed cost Annual inflation rate Variable cost Impact on hospital Capital expenditure Salvage value Value of land Account receivable days Years 2023 2024 Procedures per year Avg reimbursement rate Net reimbursements/ Incremental Revenue Fixed cost Variable cost Depreciation Net effect on inpatient profits Total Expenses Operating profit/ Net Cash Flow - Capital expenditure Free cash flow Net present value (NPV) y Hospital nalysis Model Assumptions: Procedures per year Current avg net reimbursement per Title Page Exhibit 2 Exhibit 3 DCF Model ... A B C D E F G . 1 1 CXLit 2 2 Caravash Community Hospital 3 Financial Data on Comparable Companies (financial statement data from 2018) 4 5 Tenet Healthcare 6 Description: Operates 475 outpatient centers, 255 ambulatory surgery centers, 68 hospitals, 23 surgical hospitals, 36 7 8 Revenue $18.3 billion 9 Enterprise value $21.4 billion 10 Market equity value $6.8 billion 11 Total debt $14.6 billion 12 Operating margin 11.0% 13 Return on assets 5.5% 14 Beta 0.75 15 Bond rating Ba 16 17 Surgery Partners 18 Description: Operates 108 ambulatory surgical centers and 15 surgical hospitals in 31 states. 19 20 Revenue $1.8 billion 21 Enterprise value $4.7 billion 22 Market equity value $2.4 billion 23 Total debt $2.3 billion 24 Operating margin 14.7% 25 Return on assets 3.5% 26 Beta 0.80 27 Bond rating B 28 29 Sources: Descriptions and company data are from Yahoo! Finance; bond ratings are from Moody's; Beta estimates are by 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 Title Page Exhibit 2 Exhibits Model A B D 1 2 Exhibit 3 Calabash Community Hospital Capital Markets Information (December 2019) 3 4 5 6 10-Year Government Bond Yields 7 8 Australia 1.19% 9 Brazil 6.82% 10 Canada 1.44% 11 France -0.05% 12 Germany -0.35% 13 Hong Kong 1.47% 14 India 6.47% 15 Italy 1.28% 16 Japan -0.04% 17 Mexico 7.12% 18 South Korea 1.71% 19 United Kingdom 0.67% 20 United States 1.72% 21 22 23 24 25 US Corporate Bond Yields 26 2.32% 27 A 2.81% 28 Baa 3.42% 29 Ba 4.32% 30 B 5.55% 31 32 Note: The bond rating represented the creditworthiness of the bo Title Page Exhibit 2 Exhibits Model A B D E F G H 11 France -0.05% 12 Germany -0.35% 13 Hong Kong 1.47% 14 India 6.47% 15 Italy 1.28% 16 Japan -0.04% 17 Mexico 7.12% 18 South Korea 1.71% 19 United Kingdom 0.67% 20 United States 1.72% 21 22 23 24 25 US Corporate Bond Yields 26 2.32% 27 A 2.81% 28 Baa 3.42% 29 Ba 4.32% 30 B 5.55% 31 32 Note: The bond rating represented the creditworthiness of the borrower, with an "Aa" rating indicating a borrower with a v 33 34 Sources: Government bond yields are from Bloomberg.com (markets/rates-bonds) on December 3, 2019. The corporate 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 A B D E F G H J Calabash Community Hospital Discounted Cash Flow Analysis Model Discount rate Capital expenditure Salvage value Value of land Account receivable days 3 4 Assumptions: 5 Procedures per year 6 Current avg net reimbursement per procedure Annual rate of change for reimbursment 8 9 Fixed cost 10 Annual inflation rate 11 Variable cost 12 Impact on hospital 13 14 Years 15 Procedures per year 16 Avg reimbursement rate 17 Net reimbursements/ Incremental Revenue 18 19 Fixed cost 20 Variable cost 21 Depreciation 22 Net effect on inpatient profits 23 Total Expenses 2021 2022 2023 2024 2025 2026 2027 2028 24 Operating profit/ Net Cash Flow 25 Capital expenditure 26 Free cash flow 27 28 Net present value (NPV) 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74