Financial Forecasting

profileAhmedllk
IMAExcelTemplate.xlsx
Home>Business & Finance homework help>Financial Forecasting

Assumptions

Revenue Assumptions:
Year Medicare & Medicaid Other Payer
2018
2019
2020
Miscellaneous income
Volume Assumptions:
Year Medicare & Medicaid Other Payer
2018
2019
2020
Expense Assumptions:
Employee benefits
Payroll tax expense
Rent expense
Depreciation expense
Insurance expense
Professional fees
Miscellaneous expenses
All other expenses

Revenue Forecast

Provided Data - Medicare/Medicaid Forecasted - Medicare/Medicaid Forecasted - Medicare/Medicaid Forecasted - Medicare/Medicaid
IMA REVENUE IMA REVENUE IMA REVENUE IMA REVENUE
Year Ended December 31, 2017 Year Ended December 31, 2018 Year Ended December 31, 2019 Year Ended December 31, 2020
CPT Code Reimb. Rate Medicare & Medicaid Patient Volume Medicare & Medicaid Revenue CPT Code Reimb. Rate Medicare & Medicaid Patient Volume Medicare & Medicaid Revenue CPT Code Reimb. Rate Medicare & Medicaid Patient Volume Medicare & Medicaid Revenue CPT Code Reimb. Rate Medicare & Medicaid Patient Volume Medicare & Medicaid Revenue
99201 $43.89 657 $28,836 99201 $ 43.89 657 $ 28,836 99201 $ 43.89 657 $ 28,836 99201 $ 43.89 657 $ 28,836
99202 $74.51 486 $36,212 99202 $ 74.51 486 $ 36,212 99202 $ 74.51 486 $ 36,212 99202 $ 74.51 486 $ 36,212
99203 $108.19 349 $37,758 99203 $ 108.19 349 $ 37,758 99203 $ 108.19 349 $ 37,758 99203 $ 108.19 349 $ 37,758
99204 $164.67 544 $89,580 99204 $ 164.67 544 $ 89,580 99204 $ 164.67 544 $ 89,580 99204 $ 164.67 544 $ 89,580
99205 $203.80 543 $110,663 99205 $ 203.80 543 $ 110,663 99205 $ 203.80 543 $ 110,663 99205 $ 203.80 543 $ 110,663
99211 $20.41 374 $7,633 99211 $ 20.41 374 $ 7,633 99211 $ 20.41 374 $ 7,633 99211 $ 20.41 374 $ 7,633
99212 $43.89 624 $27,387 99212 $ 43.89 624 $ 27,387 99212 $ 43.89 624 $ 27,387 99212 $ 43.89 624 $ 27,387
99213 $72.81 461 $33,565 99213 $ 72.81 461 $ 33,565 99213 $ 72.81 461 $ 33,565 99213 $ 72.81 461 $ 33,565
99214 $106.83 677 $72,324 99214 $ 106.83 677 $ 72,324 99214 $ 106.83 677 $ 72,324 99214 $ 106.83 677 $ 72,324
99215 $142.90 650 $92,885 99215 $ 142.90 650 $ 92,885 99215 $ 142.90 650 $ 92,885 99215 $ 142.90 650 $ 92,885
99385 $140.00 99385 99385 99385
99386 $160.00 99386 99386 99386
99387 $160.00 99387 99387 99387
99395 $175.00 99395 99395 99395
99396 $175.00 99396 99396 99396
99397 $175.00 99397 99397 99397
Totals 5892 $536,845 Totals 5892 $ 536,845 Totals 5892 $ 536,845 Totals 5892 $ 536,845
Provided Data - Other Payer Forecast - Other Payer Forecast - Other Payer Forecast - Other Payer
IMA REVENUE IMA REVENUE IMA REVENUE IMA REVENUE
Year Ended December 31, 2017 Year Ended December 31, 2018 Year Ended December 31, 2019 Year Ended December 31, 2020
CPT Code Reimb. Rate Other Payer Patient Volume Other Payer Patient Revenue CPT Code Reimb. Rate Other Payer Patient Volume Other Payer Patient Revenue CPT Code Reimb. Rate Other Payer Patient Volume Other Payer Patient Revenue CPT Code Reimb. Rate Other Payer Patient Volume Other Payer Patient Revenue
99201 $43.89 198 $8,690 99201 99201 99201
99202 $74.51 180 $13,412 99202 99202 99202
99203 $108.19 562 $60,803 99203 99203 99203
99204 $164.67 367 $60,434 99204 99204 99204
99205 $203.80 599 $122,076 99205 99205 99205
99211 $20.41 318 $6,490 99211 99211 99211
99212 $43.89 454 $19,926 99212 99212 99212
99213 $72.81 150 $10,922 99213 99213 99213
99214 $106.83 481 $51,385 99214 99214 99214
99215 $142.90 328 $46,871 99215 99215 99215
99385 $140.00 78 $10,920 99385 99385 99385
99386 $160.00 50 $8,000 99386 99386 99386
99387 $160.00 64 $10,240 99387 99387 99387
99395 $175.00 433 $75,775 99395 99395 99395
99396 $175.00 339 $59,325 99396 99396 99396
99397 $175.00 280 $49,000 99397 99397 99397
Totals 4881 $614,269 Totals 0 $0 Totals 0 $0 Totals 0 $0

Income Statement Forecast

Internal Medicine Associates Internal Medicine Associates Internal Medicine Associates
Income Statement FORCASTED Income Statement COMMON SIZE Income Statement
For the years ended December 31, 2015 through 2017 For the years ended December 31, 2018 through 2020 For the years ended December 31, 2015 through 2020
2017 2016 2015 2020 2019 2018 2020 2019 2018
Revenue Revenue
Patient Service Revenue $1,151,114 $983,979 $992,431 Patient Service Revenue
Miscellaneous Income 1,120 1,200 895 Miscellaneous Income
Total Revenue $1,152,234 $985,179 $993,326 Total Revenue
Expenses Expenses
Wages $709,086 $700,291 $630,093 Wages
Employee benefits 93,492 94,542 58,249 Employee benefits
Payroll taxes 51,388 53,297 50,068 Payroll taxes
Rent expense 62,050 66,600 48,000 Rent expense
Depreciation 7,482 5,589 6,528 Depreciation
Insurance 40,434 47,887 28,408 Insurance
Utilities 15,511 15,131 10,624 Utilities
Interest 623 1,560 1,832 Interest
Office supplies 14,757 12,147 9,773 Office supplies
Service contracts 13,921 10,563 8,965 Service contracts
Cleaning & Maintenance 12,718 14,333 7,280 Cleaning & Maintenance
Telephone/pager 11,825 13,840 8,667 Telephone/pager
Professional fees 11,720 4,975 3,450 Professional fees
Misc. operating expenses 53,712 59,584 37,440 Misc. operating expenses
Total Expenses $1,098,719 $1,100,339 $909,377 Total Expenses
Net Income (Loss) $53,515 ($115,160) $83,949 Net Income (Loss)

Financial Analysis

Internal Medicine Associates Internal Medicine Associates
COMMON SIZE ANALYSIS HORIZONTAL ANALYSIS
For the years ended December 31, 2015 through 2020 For the years ended December 31, 2015 through 2020
2020 2019 2018 2017 2016 2015 2020 2019 2018 2017 2016 2015
Revenue Revenue
Patient Service Revenue Patient Service Revenue
Miscellaneous Income Miscellaneous Income
Total Revenue Total Revenue
Expenses Expenses
Wages Wages
Employee benefits Employee benefits
Payroll taxes Payroll taxes
Rent expense Rent expense
Depreciation Depreciation
Insurance Insurance
Utilities Utilities
Interest Interest
Office supplies Office supplies
Service contracts Service contracts
Cleaning & Maintenance Cleaning & Maintenance
Telephone/pager Telephone/pager
Professional fees Professional fees
Misc. operating expenses Misc. operating expenses
Total Expenses Total Expenses
Net Income (Loss) Net Income (Loss)