Hello,
I have a difficult homework assignment in Healthcare Finance. Can you please help me figure out the excel spreadsheet I have to do for homework?
DPH 757 Healthcare Finance II Case 3 Week 4 - Spring 2021 FFWCC Scenario Analysis: The FFWCC ad hoc committee submitted the results of its sensitivity analysis to TPMH management. After the presentation further discussion ensued surrounding the revenue and cost assumptions included in the committees 5-year projection. The baseline projection was accepted as the most likely outcome of a restructuring of the wound care service line, however, two scenarios were discussed in order to better identify the risk associated with attaining or not, the baseline projection. Scenario #1 focuses on assessing revenue performance above the baseline, while Scenario #2 focuses on maintaining baseline revenues and absorbing additional cost. The comparative information for the first year of operations is presented below. Under Scenario #1 addition capacity is added on an annual basis to accommodate up to 5,500 wound care visits and up to 7,500 oxygen treatments. Under Scenario #2 patient capacity would be held constant and cost would be revised to test for cost increases. Because of FFWC's market position, no revisions were being proposed for payments under either scenario. PROPOSED SCENARIO #1 #2 Physician Hours in Clinic 15 15 15 Mid-Level Hours in Clinic 25 25 25 Heal Rate 90% 90% 90% 30 days 30 days 30 days Length of Stay in Program Patients per Year 415 475 415 Provider Visits per patient 9 10 9 Physician 4 4 4 Mid-Level 5 6 5 Debridement per year 200 200 200 Facility Services 9 9 9 Wound treatments per patient Oxygen therapy per treatment 1.33 1.50 1.33 Debridement per year 200 200 200 Payments for Services Physician Visit $50 $50 $50 Mid-Level Visit $40 $40 $40 Oxygen Therapy - Facility $75 $75 $75 Debridement - Facility $250 $250 $250 Cost and Expenses $100 $100 $110 $60 $60 $70 15% 15% 15% Physician Cost per Visit Mid-level Cost per Visit Provider Fringe Benefits Supply Cost per visit Staff Cost per Year General/Administrative per Year $15 $18 $20 $230,000 $240,000 $240,000 $60,000 $60,000 $60,000 Miscellaneous Per Year $45,000 $45,000 $45,000 Projections for years 2-5 under both scenarios is supply cost at 3% per year; physician, mid-level and staff cost at 5%. G&A and Miscellaneous are expected to remain flat. TPMH management requested comparative 5-year projections , ROI and NPV information be available for the next meeting where final resource allocations would be decided. REVENUES ARE TO INCREASE BY 5% PER YEAR IN YEARS 2-5. B E M N 0 F Year P H Year 3 Year All Years Year 2 NPV Year 5 ACTUAL PROPOSED Invest 0 -100000 Year 1 25381 Year 2 Raseline Sensitivity 1 100000 109999 1 2 3 Patient Service Revenues 4 Providers 5 5 Physician Visits 6 Physician Procedures 7 Mid-Level Visits 8 9 Facility 10 Wound Treatments 11 Oxygen Treatments 12 Procedures 12 S $ S 5 $ 103,500.00 15,000.00 41,400.00 159,900.00 $ $ $ S $ 83,000.00 $ 95,000.00 30,000.00 $ 30,000.00 $ 83,000.00 $ 114,000.00 $ 190,000.00 $ 200,000.00 $ 83,000.00 $ 40,000.00 $ 83,000.00 $ 206,000.00 $ S $ $ $ 210,300.00 $ $ $ $ 227,115.00 $ $ 238,470.79 Sensitivity 2 100000 39177 Sensitivity 2 -25000 -22449 S 155,250.00 $ 309,723.75 5 25,000.00 S 499,973. $ 186,750.00 $ 237,500.00 $ 185,750.00 $ $ 372,566.25 $ 534,375.00 $ 372,600.00 $ $ 50,000.00 $ 10,000.00 $ 10,000.00 $ $ 609,216.25 $ 811,975.00 $ 599,350.00 $ $ $ S 629,317.50 S $ $ $ 660,783.38 693,822.54 $ 649,873.75 S 805,316.25 $ 845,582.06 $ 887,861.17 $ 932,254.22 978,855.94 $1,027,810.26 $ 4,572,374.67 First BL 1 1 ROU 00.00% 15.00 15.00% 10.00 100.000 2 3 V 1 M V $ 5 S Second 15 Total Patient Service Revenues 10 17 Cost and Expenses: 16 Provider Cost 19 Physician Salaries 20 Mid-Level Salaries 21 Fringe Bungits 22 Total Provider Cost 23 Supplies/Pharmacy 24 Staff Cost 25 General and Administrative 26 Miscellanous 27 28 29 Income or Loss 30 31 ROI V 207,000.00 62,100.00 40,365.00 309,465.00 110,020.00 230,000.00 60,000.00 45,000.00 754,485.00 $ 166,000.00 $ 124,500.00 $ 43,575.00 $ 334,075.00 $ 340,756.50 $ 347,571.63 $ $ 133,538.25 $ 137,544.40 $ 141,670.73 $ S 230,000.00 $ 226,900.00 $ 244,007.00 S 60,000.00 $ 60,000.00 $ 60,000.00 $ $ 45,000.00 $ 45,000.00 $ 45,000.00 $ 802,613.25 $ 820,200.90 $ 338,249.36 $ $ $ $ $ 5 S BL f It 1 354,523.06 145,920.85 5 251,127.21 60,000.00 $ 45,000.00 $ 856,771.12 $ 361,613,52 S 150, 298.48 258,867.03 $ 60,000.00 $ 45,000.00 $ 873,779.03 $ 368,845.79 154,807.43 206,633.04 60,000.00 45,000.00 895,286,20 ROL 20.00% 30.00% 30.00 20.00% 1 2 2 1 $ ROL $ (104,611.25) $ 2,702.00 $ 25,381.17 $ 49,611.01 75,483.10 $ 102,087.91 $ 122.524.02 $ 396,000.00 $273,484.27 -16% 0.34% 3.0% 5.6% 8.15 10.5% 12.9% 8.26% 16.60% 33 Sensitivities: 34 Sensitivity 1 - Revenue 10% 35 Sensitivity 1 Income or Loss Sensitivity 2 - Revenue -10% 27 Sensitivity 2 - Income or Loss 38 39 Sensitivity 3 - Variable Cost 109 45 $ 920,140.27 $ 976,647.20 $ 1,025,479.65 $ 1,076,753.63 $ 1,130,591.31 $ $ 109,939.37 $ 138,397.92 $ 168,708.52 $ 200,974.00 $ 235,305.05 $ $ 761,023.86 $ 799,075.05$ 839,028.80 $ 880,980.21 $ 925,029.25 $ $ ) (59,177.04 $ 29,174.31) S (17,742.22) S 5,201.21 $ 29,742.99 $ 5,139,612.14 353,325.47 $624,450.82 1,205,137.20 (91.149.47 $ (77,482.29) -1.93% N2 ACTUAL PROPOSED 9 10 47 Assumption Table 46 Provider Services 49 Physician So Midlevel 51 Patients 9 4 9 4 5 415 3 345 5 415 6 475 $50 $40 2070 5 1025 $ 3106 50.00 40.00 53 Physician visits 54 Mid Level Visits SS Total Visits 56 37 Physician Procedures 1660 2075 1900 2950 47.50 1660 20/5 3735 3735 $130 100 $ 150.00 200 200 200 9 1.33 9 1.5 100 1.33 100 1.33 100 100 59 Facility Services co Wound Treatments Per Pt 61 Oxygen Therapy Per Visit 62 Procedures 63 64 Wound Treatments cs Oxygen Therapy 66 Procedures 67 All Procedures $50 $ $350 $ 3105 $ 4130 S 100 $ S 7235 50.00 79.00 250.00 3735 4965 200 8902 4750 7125 200 12075 3735 4959 200 8903 Cost and Expenses $100 960 15% $100 Sbo 15% 100 60 15% $110 $70 15% 69 Physician Cost Per Visit 70 Mid-Level Cost per Visit 71 Fringe Renofits 72 73 Supply Cost Per Treatment 74 75 $13 $ 15.00 $ 18.00 $ 20.00 DPH 757 Healthcare Finance II Case 3 Week 4 - Spring 2021 FFWCC Scenario Analysis: The FFWCC ad hoc committee submitted the results of its sensitivity analysis to TPMH management. After the presentation further discussion ensued surrounding the revenue and cost assumptions included in the committees 5-year projection. The baseline projection was accepted as the most likely outcome of a restructuring of the wound care service line, however, two scenarios were discussed in order to better identify the risk associated with attaining or not, the baseline projection. Scenario #1 focuses on assessing revenue performance above the baseline, while Scenario #2 focuses on maintaining baseline revenues and absorbing additional cost. The comparative information for the first year of operations is presented below. Under Scenario #1 addition capacity is added on an annual basis to accommodate up to 5,500 wound care visits and up to 7,500 oxygen treatments. Under Scenario #2 patient capacity would be held constant and cost would be revised to test for cost increases. Because of FFWC's market position, no revisions were being proposed for payments under either scenario. PROPOSED SCENARIO #1 #2 Physician Hours in Clinic 15 15 15 Mid-Level Hours in Clinic 25 25 25 Heal Rate 90% 90% 90% 30 days 30 days 30 days Length of Stay in Program Patients per Year 415 475 415 Provider Visits per patient 9 10 9 Physician 4 4 4 Mid-Level 5 6 5 Debridement per year 200 200 200 Facility Services 9 9 9 Wound treatments per patient Oxygen therapy per treatment 1.33 1.50 1.33 Debridement per year 200 200 200 Payments for Services Physician Visit $50 $50 $50 Mid-Level Visit $40 $40 $40 Oxygen Therapy - Facility $75 $75 $75 Debridement - Facility $250 $250 $250 Cost and Expenses $100 $100 $110 $60 $60 $70 15% 15% 15% Physician Cost per Visit Mid-level Cost per Visit Provider Fringe Benefits Supply Cost per visit Staff Cost per Year General/Administrative per Year $15 $18 $20 $230,000 $240,000 $240,000 $60,000 $60,000 $60,000 Miscellaneous Per Year $45,000 $45,000 $45,000 Projections for years 2-5 under both scenarios is supply cost at 3% per year; physician, mid-level and staff cost at 5%. G&A and Miscellaneous are expected to remain flat. TPMH management requested comparative 5-year projections , ROI and NPV information be available for the next meeting where final resource allocations would be decided. REVENUES ARE TO INCREASE BY 5% PER YEAR IN YEARS 2-5. B E M N 0 F Year P H Year 3 Year All Years Year 2 NPV Year 5 ACTUAL PROPOSED Invest 0 -100000 Year 1 25381 Year 2 Raseline Sensitivity 1 100000 109999 1 2 3 Patient Service Revenues 4 Providers 5 5 Physician Visits 6 Physician Procedures 7 Mid-Level Visits 8 9 Facility 10 Wound Treatments 11 Oxygen Treatments 12 Procedures 12 S $ S 5 $ 103,500.00 15,000.00 41,400.00 159,900.00 $ $ $ S $ 83,000.00 $ 95,000.00 30,000.00 $ 30,000.00 $ 83,000.00 $ 114,000.00 $ 190,000.00 $ 200,000.00 $ 83,000.00 $ 40,000.00 $ 83,000.00 $ 206,000.00 $ S $ $ $ 210,300.00 $ $ $ $ 227,115.00 $ $ 238,470.79 Sensitivity 2 100000 39177 Sensitivity 2 -25000 -22449 S 155,250.00 $ 309,723.75 5 25,000.00 S 499,973. $ 186,750.00 $ 237,500.00 $ 185,750.00 $ $ 372,566.25 $ 534,375.00 $ 372,600.00 $ $ 50,000.00 $ 10,000.00 $ 10,000.00 $ $ 609,216.25 $ 811,975.00 $ 599,350.00 $ $ $ S 629,317.50 S $ $ $ 660,783.38 693,822.54 $ 649,873.75 S 805,316.25 $ 845,582.06 $ 887,861.17 $ 932,254.22 978,855.94 $1,027,810.26 $ 4,572,374.67 First BL 1 1 ROU 00.00% 15.00 15.00% 10.00 100.000 2 3 V 1 M V $ 5 S Second 15 Total Patient Service Revenues 10 17 Cost and Expenses: 16 Provider Cost 19 Physician Salaries 20 Mid-Level Salaries 21 Fringe Bungits 22 Total Provider Cost 23 Supplies/Pharmacy 24 Staff Cost 25 General and Administrative 26 Miscellanous 27 28 29 Income or Loss 30 31 ROI V 207,000.00 62,100.00 40,365.00 309,465.00 110,020.00 230,000.00 60,000.00 45,000.00 754,485.00 $ 166,000.00 $ 124,500.00 $ 43,575.00 $ 334,075.00 $ 340,756.50 $ 347,571.63 $ $ 133,538.25 $ 137,544.40 $ 141,670.73 $ S 230,000.00 $ 226,900.00 $ 244,007.00 S 60,000.00 $ 60,000.00 $ 60,000.00 $ $ 45,000.00 $ 45,000.00 $ 45,000.00 $ 802,613.25 $ 820,200.90 $ 338,249.36 $ $ $ $ $ 5 S BL f It 1 354,523.06 145,920.85 5 251,127.21 60,000.00 $ 45,000.00 $ 856,771.12 $ 361,613,52 S 150, 298.48 258,867.03 $ 60,000.00 $ 45,000.00 $ 873,779.03 $ 368,845.79 154,807.43 206,633.04 60,000.00 45,000.00 895,286,20 ROL 20.00% 30.00% 30.00 20.00% 1 2 2 1 $ ROL $ (104,611.25) $ 2,702.00 $ 25,381.17 $ 49,611.01 75,483.10 $ 102,087.91 $ 122.524.02 $ 396,000.00 $273,484.27 -16% 0.34% 3.0% 5.6% 8.15 10.5% 12.9% 8.26% 16.60% 33 Sensitivities: 34 Sensitivity 1 - Revenue 10% 35 Sensitivity 1 Income or Loss Sensitivity 2 - Revenue -10% 27 Sensitivity 2 - Income or Loss 38 39 Sensitivity 3 - Variable Cost 109 45 $ 920,140.27 $ 976,647.20 $ 1,025,479.65 $ 1,076,753.63 $ 1,130,591.31 $ $ 109,939.37 $ 138,397.92 $ 168,708.52 $ 200,974.00 $ 235,305.05 $ $ 761,023.86 $ 799,075.05$ 839,028.80 $ 880,980.21 $ 925,029.25 $ $ ) (59,177.04 $ 29,174.31) S (17,742.22) S 5,201.21 $ 29,742.99 $ 5,139,612.14 353,325.47 $624,450.82 1,205,137.20 (91.149.47 $ (77,482.29) -1.93% N2 ACTUAL PROPOSED 9 10 47 Assumption Table 46 Provider Services 49 Physician So Midlevel 51 Patients 9 4 9 4 5 415 3 345 5 415 6 475 $50 $40 2070 5 1025 $ 3106 50.00 40.00 53 Physician visits 54 Mid Level Visits SS Total Visits 56 37 Physician Procedures 1660 2075 1900 2950 47.50 1660 20/5 3735 3735 $130 100 $ 150.00 200 200 200 9 1.33 9 1.5 100 1.33 100 1.33 100 100 59 Facility Services co Wound Treatments Per Pt 61 Oxygen Therapy Per Visit 62 Procedures 63 64 Wound Treatments cs Oxygen Therapy 66 Procedures 67 All Procedures $50 $ $350 $ 3105 $ 4130 S 100 $ S 7235 50.00 79.00 250.00 3735 4965 200 8902 4750 7125 200 12075 3735 4959 200 8903 Cost and Expenses $100 960 15% $100 Sbo 15% 100 60 15% $110 $70 15% 69 Physician Cost Per Visit 70 Mid-Level Cost per Visit 71 Fringe Renofits 72 73 Supply Cost Per Treatment 74 75 $13 $ 15.00 $ 18.00 $ 20.00
