Financial Forecasting

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

Assumptions

Revenue Assumptions:
Year Medicare & Medicaid Other Payer
2018 0.50% 4.90%
2019 2.50% 4.90%
2020 2.50% 4.90%
1.0% Miscellaneous income
Volume Assumptions:
Year Medicare & Medicaid Other Payer
2018 0.00% 0.00%
2019 0.00% -3.00%
2020 0.00% -3.00%
Expense Assumptions:
13.30% Employee benefits
7.40% Payroll tax expense
2.50% Rent expense
25.00% Depreciation expense
0.00% Insurance expense
0.00% Professional fees
$8,000 Miscellaneous expenses
2.00% 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 $ 44.11 657 $ 28,980 99201 $ 45.21 657 $ 29,704 99201 $ 46.34 657 $ 30,447
99202 $74.51 486 $36,212 99202 $ 74.88 486 $ 36,393 99202 $ 76.75 486 $ 37,303 99202 $ 78.67 486 $ 38,235
99203 $108.19 349 $37,758 99203 $ 108.73 349 $ 37,947 99203 $ 111.45 349 $ 38,896 99203 $ 114.24 349 $ 39,868
99204 $164.67 544 $89,580 99204 $ 165.49 544 $ 90,028 99204 $ 169.63 544 $ 92,279 99204 $ 173.87 544 $ 94,586
99205 $203.80 543 $110,663 99205 $ 204.82 543 $ 111,217 99205 $ 209.94 543 $ 113,997 99205 $ 215.19 543 $ 116,847
99211 $20.41 374 $7,633 99211 $ 20.51 374 $ 7,672 99211 $ 21.02 374 $ 7,863 99211 $ 21.55 374 $ 8,060
99212 $43.89 624 $27,387 99212 $ 44.11 624 $ 27,524 99212 $ 45.21 624 $ 28,212 99212 $ 46.34 624 $ 28,918
99213 $72.81 461 $33,565 99213 $ 73.17 461 $ 33,733 99213 $ 75.00 461 $ 34,577 99213 $ 76.88 461 $ 35,441
99214 $106.83 677 $72,324 99214 $ 107.36 677 $ 72,686 99214 $ 110.05 677 $ 74,503 99214 $ 112.80 677 $ 76,365
99215 $142.90 650 $92,885 99215 $ 143.61 650 $ 93,349 99215 $ 147.20 650 $ 95,683 99215 $ 150.88 650 $ 98,075
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 $ 539,529 Totals 5892 $ 553,017 Totals 5892 $ 566,843
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 $ 46.04 198 $9,116 99201 $48.30 192 $9,276 99201 $48.30 186 $8,998
99202 $74.51 180 $13,412 99202 $ 78.16 180 $14,069 99202 $81.99 175 $14,316 99202 $81.99 169 $13,886
99203 $108.19 562 $60,803 99203 $ 113.49 562 $63,782 99203 $119.05 545 $64,900 99203 $119.05 529 $62,953
99204 $164.67 367 $60,434 99204 $ 172.74 367 $63,395 99204 $181.20 356 $64,506 99204 $181.20 345 $62,571
99205 $203.80 599 $122,076 99205 $ 213.79 599 $128,058 99205 $224.26 581 $130,303 99205 $224.26 564 $126,394
99211 $20.41 318 $6,490 99211 $ 21.41 318 $6,808 99211 $22.46 308 $6,928 99211 $22.46 299 $6,720
99212 $43.89 454 $19,926 99212 $ 46.04 454 $20,902 99212 $48.30 440 $21,269 99212 $48.30 427 $20,631
99213 $72.81 150 $10,922 99213 $ 76.38 150 $11,457 99213 $80.12 146 $11,657 99213 $80.12 141 $11,308
99214 $106.83 481 $51,385 99214 $ 112.06 481 $53,903 99214 $117.56 467 $54,848 99214 $117.56 453 $53,203
99215 $142.90 328 $46,871 99215 $ 149.90 328 $49,168 99215 $157.25 318 $50,030 99215 $157.25 309 $48,529
99385 $140.00 78 $10,920 99385 $ 146.86 78 $11,455 99385 $154.06 76 $11,656 99385 $154.06 73 $11,306
99386 $160.00 50 $8,000 99386 $ 167.84 50 $8,392 99386 $176.06 49 $8,539 99386 $176.06 47 $8,283
99387 $160.00 64 $10,240 99387 $ 167.84 64 $10,742 99387 $176.06 62 $10,930 99387 $176.06 60 $10,602
99395 $175.00 433 $75,775 99395 $ 183.58 433 $79,488 99395 $192.57 420 $80,881 99395 $192.57 407 $78,455
99396 $175.00 339 $59,325 99396 $ 183.58 339 $62,232 99396 $192.57 329 $63,323 99396 $192.57 319 $61,423
99397 $175.00 280 $49,000 99397 $ 183.58 280 $51,401 99397 $192.57 272 $52,302 99397 $192.57 263 $50,733
Totals 4881 $614,269 Totals 4881 $644,368 Totals 4735 $655,664 Totals 4593 $635,994

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 Revenue
Patient Service Revenue $1,151,114 $983,979 $992,431 Patient Service Revenue $1,202,837 $1,208,681 $1,183,897 Patient Service Revenue 99.9% 99.9% 99.9%
Miscellaneous Income 1,120 1,200 895 Miscellaneous Income 1,154 1,143 1131 Miscellaneous Income 0.1% 0.1% 0.1%
Total Revenue $1,152,234 $985,179 $993,326 Total Revenue $1,203,991 $1,209,824 $1,185,029 Total Revenue 100% 100% 100%
Expenses Expenses Expenses
Wages $ 709,086 $ 700,291 $ 630,093 Wages $ 723,268 $ 737,733 $ 752,488 Wages 60.1% 61.0% 63.5%
Employee benefits $ 93,492 $ 94,542 $ 58,249 Employee benefits $ 96,195 $ 98,118 $ 100,081 Employee benefits 8.0% 8.1% 8.4%
Payroll taxes $ 51,388 $ 53,297 $ 50,068 Payroll taxes $ 53,522 $ 54,592 $ 55,684 Payroll taxes 4.4% 4.5% 4.7%
Rent expense $ 62,050 $ 66,600 $ 48,000 Rent expense $ 66,821 $ 65,191 $ 63,601 Rent expense 5.5% 5.4% 5.4%
Depreciation $ 7,482 $ 5,589 $ 6,528 Depreciation $ 14,613 $ 11,691 $ 9,353 Depreciation 1.2% 1.0% 0.8%
Insurance $ 40,434 $ 47,887 $ 28,408 Insurance $ 40,434 $ 40,434 $ 40,434 Insurance 3.4% 3.3% 3.4%
Utilities $ 15,511 $ 15,131 $ 10,624 Utilities $ 16,460 $ 16,138 $ 15,821 Utilities 1.4% 1.3% 1.3%
Interest $ 623 $ 1,560 $ 1,832 Interest $ - 0 $ - 0 $ 52 Interest 0.0% 0.0% 0.0%
Office supplies $ 14,757 $ 12,147 $ 9,773 Office supplies $ 15,660 $ 15,353 $ 15,052 Office supplies 1.3% 1.3% 1.3%
Service contracts $ 13,921 $ 10,563 $ 8,965 Service contracts $ 14,773 $ 14,483 $ 14,199 Service contracts 1.2% 1.2% 1.2%
Cleaning & Maintenance $ 12,718 $ 14,333 $ 7,280 Cleaning & Maintenance $ 13,496 $ 13,232 $ 12,972 Cleaning & Maintenance 1.1% 1.1% 1.1%
Telephone/pager $ 11,825 $ 13,840 $ 8,667 Telephone/pager $ 12,549 $ 12,303 $ 12,062 Telephone/pager 1.0% 1.0% 1.0%
Professional fees $ 11,720 $ 4,975 $ 3,450 Professional fees $ 11,720 $ 11,720 $ 11,720 Professional fees 1.0% 1.0% 1.0%
Misc. operating expenses $ 53,712 $ 59,584 $ 37,440 Misc. operating expenses $ 57,000 $ 55,882 $ 54,786 Misc. operating expenses 4.7% 4.6% 4.6%
Total Expenses $1,098,719 $1,100,339 $909,377 Total Expenses $1,136,511 $1,146,870 $1,158,305 Total Expenses 94.4% 94.8% 97.7%
Net Income (Loss) $53,515 ($115,160) $83,949 Net Income (Loss) $67,480 $62,954 $26,723 Net Income (Loss) 5.6% 5.2% 2.3%

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 99.9% 99.9% 99.9% 99.9% 99.9% 99.9% Patient Service Revenue -0.5% 2.1% 2.8% 17.0% -0.9% 100%
Miscellaneous Income 0.1% 0.1% 0.1% 0.1% 0.1% 0.1% Miscellaneous Income 1.0% 1.0% 1.0% -6.7% 34.1% 100%
Total Revenue 100% 100% 100% 100% 100% 100% Total Revenue -0.5% 2.1% 2.8% 17.0% -0.8% 100%
Expenses Expenses
Wages 60% 61% 63% 62% 71% 63% Wages -2.0% -2.0% 6.1% 1.3% 11.1% 100%
Employee benefits 8% 8% 8% 8% 10% 6% Employee benefits -2.0% -2.0% 7.0% -1.1% 62.3% 100%
Payroll taxes 4% 5% 5% 4% 5% 5% Payroll taxes -2.0% -2.0% 8.4% -3.6% 6.4% 100%
Rent expense 6% 5% 5% 5% 7% 5% Rent expense 2.5% 2.5% 2.5% -6.8% 38.8% 100%
Depreciation 1% 1% 1% 1% 1% 1% Depreciation 25.0% 25.0% 25.0% 33.9% -14.4% 100%
Insurance 3% 3% 3% 4% 5% 3% Insurance 0.0% 0.0% 0.0% -15.6% 68.6% 100%
Utilities 1% 1% 1% 1% 2% 1% Utilities 2.0% 2.0% 2.0% 2.5% 42.4% 100%
Interest 0% 0% 0% 0% 0% 0% Interest 0.0% -100.0% -91.7% -60.1% -14.8% 100%
Office supplies 1% 1% 1% 1% 1% 1% Office supplies 2.0% 2.0% 2.0% 21.5% 24.3% 100%
Service contracts 1% 1% 1% 1% 1% 1% Service contracts 2.0% 2.0% 2.0% 31.8% 17.8% 100%
Cleaning & Maintenance 1% 1% 1% 1% 1% 1% Cleaning & Maintenance 2.0% 2.0% 2.0% -11.3% 96.9% 100%
Telephone/pager 1% 1% 1% 1% 1% 1% Telephone/pager 2.0% 2.0% 2.0% -14.6% 59.7% 100%
Professional fees 1% 1% 1% 1% 1% 0% Professional fees 0.0% 0.0% 0.0% 135.6% 44.2% 100%
Misc. operating expenses 5% 5% 5% 5% 6% 4% Misc. operating expenses 2.0% 2.0% 2.0% -9.9% 59.1% 100%
Total Expenses 94% 95% 98% 95% 112% 92% Total Expenses -0.9% -1.0% 5.4% -0.1% 21.0% 100%
Net Income (Loss) 6% 5% 2% 5% -12% 8% Net Income (Loss) 7.2% 135.6% -50.1% -146.5% -237.2% 100.0%