Final Project Healthcare Administration
lala365
FM010_Assessment_FinancialProjectionsfortheCenterforDiabetesCare-5.xlsxSheet1
| Adapted from | ||||||||||
| http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/O/PDF%20OpenDoorFinancialAnalysis.pdf | ||||||||||
| Patient-Generated Revenue by Payer | ||||||||||
| Fiscal year from April 1 to March 31 | ||||||||||
| Year 1 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income | |||
| Medicaid | 1,250 | $133.04 | $166,300 | -67.61 | $ 250,812 | 98% | $245,796 | |||
| Medicare | 2,000 | $132.70 | $265,396 | -10.48 | $ 286,356 | 95% | $272,038 | |||
| Private Insurance | 100 | $137.08 | $13,708 | $72.44 | $ 6,464 | 80% | $5,172 | Year 3 assumes CHW are at their capactiy of 7200 patient visits, or 4 CHW FTEs see 10 patients per day 4 days per week, 45 weeks a year | ||
| Self Pay (sliding fee) | 50 | $111.68 | $5,584 | $89.89 | $ 1,090 | 40% | $436 | |||
| 3,400 | $ 544,722 | $523,441 | ||||||||
| Year 2 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income | |||
| Medicaid | 1,750 | $133.04 | $232,820 | -67.61 | $ 351,137 | 98% | $344,115 | |||
| Medicare | 3,500 | $132.70 | $464,442 | -10.48 | $ 501,122 | 95% | $476,066 | $171.04 | 133.04 | 133.04 |
| Private Insurance | 400 | $137.08 | $54,834 | $72.44 | $ 25,858 | 80% | $20,686 | $170.60 | 132.6977549111 | |
| Self Pay (sliding fee) | 200 | $111.68 | $22,336 | $89.89 | $ 4,358 | 40% | $1,743 | $176.24 | 137.0847146866 | |
| 5,850 | $ 882,476 | $842,610 | $143.58 | 111.6807951356 | ||||||
| Year 3 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income | |||
| Medicaid | 2,000 | $135.70 | $271,402 | -67.61 | $ 406,622 | 98% | $398,489 | |||
| Medicare | 3,800 | $135.35 | $514,336 | -10.48 | $ 554,160 | 95% | $526,452 | |||
| Private Insurance | 900 | $139.83 | $125,844 | $72.44 | $ 60,648 | 80% | $48,518 | |||
| Self Pay (sliding fee) | 500 | $113.91 | $56,957 | $89.89 | $ 12,012 | 40% | $4,805 | |||
| 7,200 | $ 1,033,442 | $978,265 | ||||||||
| Grant Funding by Source | Year 1 | Year 2 | Year 3 | |||||||
| PPHF Grant | $100,000 | $100,000 | $100,000 | |||||||
| Foundation Grants | $75,000 | $ 50,000 | ||||||||
| Total | $175,000 | $150,000 | $100,000 | |||||||
| Non-Staffing Costs | Year 1 | Year 2 | Year 3 | |||||||
| Fringe Benefits | $ 59,609 | $ 60,802 | $ 62,018 | |||||||
| Travel | $ 5,000 | $ 5,000 | $ 10,000 | |||||||
| Training | $ 35,000 | $ 10,000 | $ 15,000 | |||||||
| Equipment | $ 5,000 | $ 2,000 | $ 2,000 | |||||||
| Supplies | $ 15,000 | $ 15,300 | $ 15,606 | |||||||
| Contractual | $ 8,000 | $ 8,160 | $ 8,323 | |||||||
| Allocated Rent | $ 23,418 | $ 23,769 | $ 24,126 | |||||||
| Depreciation | $ 26,277 | $ 35,036 | $ 35,036 | |||||||
| Insurance | $ 4,004 | $ 4,204 | $ 4,414 | |||||||
| Overhead Allocation | $ 9,167 | $ 9,442 | $ 9,725 | |||||||
| Uncollectible Income | $ - 0 | $ - 0 | $ - 0 | |||||||
| Marketing | $ 45,000 | $ 15,000 | $ 15,000 | |||||||
| Indirect Charges | $ 54,764 | $ 56,955 | $ 59,233 | |||||||
| Total Non-Staffing Costs | $ 290,240 | $ 245,668 | $ 260,481 | |||||||
| Staffing costs | ||||||||||
| Annual Salary | FTE | Year 1 | Year 2 | Year 3 | ||||||
| Endocronologist | 0.93 | $ 158,000 | $ 165,900 | $ 174,195 | ||||||
| Contract Specialists | 0.3 | $ 75,000 | $ 78,750 | $ 82,688 | ||||||
| Nurse Practitioner | 0.95 | $ 84,094 | $ 86,617 | $ 89,215 | ||||||
| Community Health Workers | 4 | $ 244,000 | $ 251,320 | $ 258,860 | ||||||
| Reception/office management | 1 | $ 35,000 | $ 35,700 | $ 36,414 | ||||||
| $ 596,094 | $ 618,287 | $ 641,371 | ||||||||
| Capital Costs | Year 1 | Year 2 | Year 3 | |||||||
| Server | $15,000 | |||||||||
| Remodel of office space | $98,550 | |||||||||
| Security System | $2,654 | |||||||||
| Furniture | $12,281 | 1000 | 1000 | |||||||
| Practice Management System | $5,000 | 2500 | 2500 | |||||||
| Computers & Cabling | $41,696 | |||||||||
| $175,181 | $3,500 | $3,500 |
patient-generated revenue
| Projected Patient-Generated Revenue by Payer | |||||||
| Fiscal year from April 1 to March 31 | |||||||
| Year 1 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income |
| Medicaid | 1,250 | $0.00 | $0 | -67.61 | $ 84,513 | 98% | $82,822 |
| Medicare | 2,000 | $0.00 | $0 | -10.48 | $ 20,960 | 95% | $19,912 |
| Private Insurance | 100 | $0.00 | $0 | $72.44 | $ (7,244) | 80% | ($5,795) |
| Self Pay (sliding fee) | 50 | $0.00 | $0 | $89.89 | $ (4,495) | 40% | ($1,798) |
| 3,400 | $ 93,734 | $95,141 | |||||
| Year 2 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income |
| Medicaid | 1,750 | $0.00 | $0 | -67.61 | $ 118,318 | 98% | $115,951 |
| Medicare | 3,500 | $0.00 | $0 | -10.48 | $ 36,680 | 95% | $34,846 |
| Private Insurance | 400 | $0.00 | $0 | $72.44 | $ (28,976) | 80% | ($23,181) |
| Self Pay (sliding fee) | 200 | $0.00 | $0 | $89.89 | $ (17,978) | 40% | ($7,191) |
| 5,850 | $ 108,044 | $120,425 | |||||
| Year 3 | number of visits | average charge per visit | total charges | average adjustment per visit | amount billed | collection rate | income |
| Medicaid | 2,000 | $0.00 | $0 | -67.61 | $ 135,220 | 98% | $132,516 |
| Medicare | 3,800 | $0.00 | $0 | -10.48 | $ 39,824 | 95% | $37,833 |
| Private Insurance | 900 | $0.00 | $0 | $72.44 | $ (65,196) | 80% | ($52,157) |
| Self Pay (sliding fee) | 500 | $0.00 | $0 | $89.89 | $ (44,945) | 40% | ($17,978) |
| 7,200 | $ 64,903 | $100,214 | |||||
| Adapted from: Chelius, L., Hook, J., & Rodriguez, M. (2010). Financial analysis of Open Door Community Health Centers’ telehealth experience. Retrieved from http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/PDF%20O/PDF%20OpenDoorFinancialAnalysis.pdf |
grant funding
| Projected Grant Funding by Source | |||
| Year 1 | Year 2 | Year 3 | |
| PPHF Grant | $100,000 | $100,000 | $100,000 |
| Foundation Grants | $75,000 | $ 50,000 | |
| Total | $175,000 | $150,000 | $100,000 |
| Adapted from Chelius, L., Hook, J., & Rodriguez, M. (2010). Financial analysis of Open Door Community Health Centers’ telehealth experience. Retrieved from http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/O/PDF%20OpenDoorFinancialAnalysis.pdf |
non-staffing costs
| Projected Non-Staffing Costs | |||
| Year 1 | Year 2 | Year 3 | |
| Fringe Benefits | $ 59,609 | $ 60,802 | $ 62,018 |
| Travel | $ 5,000 | $ 5,000 | $ 10,000 |
| Training | $ 35,000 | $ 10,000 | $ 15,000 |
| Equipment | $ 5,000 | $ 2,000 | $ 2,000 |
| Supplies | $ 15,000 | $ 15,300 | $ 15,606 |
| Contractual | $ 8,000 | $ 8,160 | $ 8,323 |
| Allocated Rent | $ 23,418 | $ 23,769 | $ 24,126 |
| Depreciation | $ 26,277 | $ 35,036 | $ 35,036 |
| Insurance | $ 4,004 | $ 4,204 | $ 4,414 |
| Overhead Allocation | $ 9,167 | $ 9,442 | $ 9,725 |
| Uncollectible Income | $ 21,281 | $ 39,865 | $ 55,177 |
| Marketing | $ 45,000 | $ 15,000 | $ 15,000 |
| Indirect Charges | $ 54,764 | $ 56,955 | $ 59,233 |
| Total | $ 311,520 | $ 285,533 | $ 315,659 |
| Adapted from Chelius, L., Hook, J., & Rodriguez, M. (2010). Financial analysis of Open Door Community Health Centers’ telehealth experience. Retrieved from http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/O/PDF%20OpenDoorFinancialAnalysis.pdf |
staffing costs
| Projected Staffing Costs | ||||
| Annual Salary | ||||
| FTE | Year 1 | Year 2 | Year 3 | |
| Endocronologist | 0.93 | $ 158,000 | $ 165,900 | $ 174,195 |
| Contract Specialists | 0.30 | $ 75,000 | $ 78,750 | $ 82,688 |
| Nurse Practitioner | 0.95 | $ 84,094 | $ 86,617 | $ 89,215 |
| Community Health Workers | 4.00 | $ 244,000 | $ 251,320 | $ 258,860 |
| Reception/Office Management | 1.00 | $ 35,000 | $ 35,700 | $ 36,414 |
| Total | $ 596,094 | $ 618,287 | $ 641,371 | |
| Adapted from: Chelius, L., Hook, J., & Rodriguez, M. (2010). Financial analysis of Open Door Community Health Centers’ telehealth experience. Retrieved from http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/PDF%20O/PDF%20OpenDoorFinancialAnalysis.pdf |
capital costs
| Projected Capital Costs | |||
| Year 1 | Year 2 | Year 3 | |
| Server | $ 15,000 | ||
| Remodel of Office Space | $ 98,550 | ||
| Security System | $ 2,654 | ||
| Furniture | $ 12,281 | $ 1,000 | $ 1,000 |
| Practice Management System | $ 5,000 | $ 2,500 | $ 2,500 |
| Computers & Cabling | $ 41,696 | ||
| Total | $ 175,181 | $ 3,500 | $ 3,500 |
| Adapted from Chelius, L., Hook, J., & Rodriguez, M. (2010). Financial analysis of Open Door Community Health Centers’ telehealth experience. Retrieved from http://www.chcf.org/~/media/MEDIA%20LIBRARY%20Files/PDF/O/PDF%20OpenDoorFinancialAnalysis.pdf |
