Please help plug in the excel formulas in the correct sections. Assignment Sheet: Internal Medicine Associates, Case Requirements Create a pro forma Income Statement for

Please help plug in the excel formulas in the correct sections. image text in transcribed

Assignment Sheet: Internal Medicine Associates, Case Requirements Create a pro forma Income Statement for the next three years using the Excel template and the information provided in the data input page provided by your instructor. You must link everything in your Excel cells to the Assumptions tab. Do not simply calculate your numbers outside of Excel and type them into the cells. First, you will prepare a Revenue Forecast for the next three years using the following reimbursement and volume projections data: MIPS payment adjustment: 20X1 (-4%); 20x2 (-3%); 20x3 (0%) Medicare volume: 20X1 (+5%); 20x2 (+4%); 20x3 +1%) Commercial insurance reimbursement rate: 20X1 (+2%); 20x2 (+2%); 20x3 (+2%) Commercial insurance volume: 20X1 (-3%); 20x2 (-4%); 20x3 (-1%) Next, using the revenue forecast from Part 1 and the expense assumptions provided below, prepare a pro forma income statement for the next three years and complete a common size analysis on all years of Income Statement data. a. Miscellaneous income increases 1.00% annually b. Employee benefits are expected to be 16% of wages c. Payroll tax expense will be 7.40% of wages d. Rent expense will increase 2.50% annually e. Depreciation expense will increase 25% annually f. Insurance is based on a percentage of patient service revenues. For forecasting purposes apply the 20X0 percentage of insurance to patient services revenue to all future years (carry to two decimal places) g. The Practice expects to repay all debt outstanding by the end of 20X0. Therefore, there will be no interest expense moving forward. h. Professional fees should remain constant at the 20X0 rate of 11720 USD i. Miscellaneous operating expenses are expected to increase by 8000 USD per year for the next three years. j. All other expenses not specifically listed above should increase by 2% annually. Finally, prepare a 2-page single-spaced memo in proper form to Dr. Carl analyzing the pros and cons of this decision and providing your recommendations for his future and the future of the Practice. Discuss major financial findings and any changes in assumptions that would increase or decrease the doubt about the Practice's financial sustainability. Discuss any ethical and/or social responsibility issues that arise from your recommendation and how Dr. Carl, current and future patients, and employees would be affected. Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Associates Assumption Table Revenue Assumptions: Year Medicare Commercial 2011 2022 0.00 0.00% ON Miscellaneous incense 004 Volume Assumptions: Medicare Year 2020 . Comercial LORE OOO OUR Expense Assumptions! 0.00%|Employee benefits 0.00 Payroll tax expen DON Rent expense Depreciation 000 inwarance expense 50 interesse 000 Professionales 30 Miscellaneous Brother Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Forecasted - Medicare IMA REVENUE Year Ended December 31, 2022 Reim Patie CPT Code Volume Revenue WT Forecasted - Medicare/Medical IMA REVENUE Year Ended thecember 11, 2023 Rrimi, Patient CPT Code Rocus Rate Volum Provided bata - Medicare IMA HEVENUE Year Ended December 31, 2020 CPT Patient Reimb. Hale Code Reven 01 05 5.36 99203 $7451 46 0212 99203 STOK 19 $17,758 SOM S16467 34 SW.0 99303 50 30 STIUM 32041 374 09 54389 624 $27:35 57281 461 $33,565 W2214 STO) 07 25 512 650 3. Forecasted Medicare IMA REVENUE Year Ended December 11, 2021 CPT Code Reim. Rate Patient Volume Rever 99201 99202 99203 99204 09.05 99211 990 9913 w 915 99203 99204 2303 99203 9304 205 1211 99312 9223 99212 99203 VS WAS Total Medicare 5893 5516.15 Total - Medicare Total Medicar Total Medicare Forecasted-Commerical murance TIMA REVENUE Year Ended December 31, 2022 CPT Code Reim Patica Here Rate Volume Ferested - Commerical Insurance IMA REVENUE Year Ended December 31, 2021 CPT Code Reimh Rate Patie Volume Rev 99201 99302 w 2014 995 9911 Forecasted-Commerical Insurance IMA REVENUE Year Ended December 31, 2023 Reim Patient CPT Code Hans Vol Revenue 201 WO 0 ou 205 2.22 22.30 25 Provided Dats. Commerical Insurance IMA HEVENUE Year Ended December 31, 2010 CPT Heimb. Rate Patient Code Vome Rever 9201 $75.00 198 $14.50 02 595 00 IND STATO 500 5 5W30 204 SENSO 962 567 99203 $200.00 So S119.00 55000 3150 52000 214 57ND 300 180 312750 1214 4 307340 99215 $175.00 556700 309.30 STO SR ST 64 $10,210 TO SIYO 30 5 7 MISTE 20 SRO TARI NE SJES 22 23 21 1921 9914 92275 20 5 900 WA 99 www WW PIRES PRO 17 Total Commercial Total Total Total Plent Service Here SIBUS, Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Amoriates FORCASTED Income Statement For the wars ended December 31, 2021 through 2023 2022 Revenue Patient Service Reven Miscellaneous Income Internal Medicine Associates Income Statement For the year ended December 31, 2030 through 2018 2018 Revenue Patient Service Revenue $1,225.170 $08.979 $992.431 Miscellaneous Income 1.120 1.200 95 Total Rese Total Rem $1,226.200 5985.179 5993.326 Express Wage Employee benefits Payroll taxes Rent expert Deprecation Inse Utilities Interes One wpplies Service contact Cleaning & Maintenan Telephone Professional fees Misc Total Expat Ways Employee benefits Payrolles Rent experte Depreciate Insane Utilities Inter Office Service to Clan Main Telephone Professional Music Total Expres 5709.06 $106.36 $3.5 63,050 7.463 0,414 19.511 63 14.95 13,031 13.IN 11,25 11.720 53,713 31.10. 5700 201 4,542 53.197 00 3,500 47, 15.131 1.10 12.147 10,163 14,113 13,840 4.975 30,14 31.1039 5610,003 38.200 30 400 6,52 28.403 10414 1232 9.27) 7.30 N161 3740 59.97 Net Income (I.) 50 Net Income.com) $112.00 SLIK, SR5,99 Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Amore COMMON SIZE ANALYSIS For the years ended December 31, 2018 through 2023 302 2022 2021 2010 2018 Revenue Pet Service Revue Miscellaneous Income Total Reve Kupe Employee Payroll Rem tyret Insurance Uma) Office plier Service Cleaning & Maintenance Telephone Professional fees Misperating expert Total Expres Net Income Lox) Assignment Sheet: Internal Medicine Associates, Case Requirements Create a pro forma Income Statement for the next three years using the Excel template and the information provided in the data input page provided by your instructor. You must link everything in your Excel cells to the Assumptions tab. Do not simply calculate your numbers outside of Excel and type them into the cells. First, you will prepare a Revenue Forecast for the next three years using the following reimbursement and volume projections data: MIPS payment adjustment: 20X1 (-4%); 20x2 (-3%); 20x3 (0%) Medicare volume: 20X1 (+5%); 20x2 (+4%); 20x3 +1%) Commercial insurance reimbursement rate: 20X1 (+2%); 20x2 (+2%); 20x3 (+2%) Commercial insurance volume: 20X1 (-3%); 20x2 (-4%); 20x3 (-1%) Next, using the revenue forecast from Part 1 and the expense assumptions provided below, prepare a pro forma income statement for the next three years and complete a common size analysis on all years of Income Statement data. a. Miscellaneous income increases 1.00% annually b. Employee benefits are expected to be 16% of wages c. Payroll tax expense will be 7.40% of wages d. Rent expense will increase 2.50% annually e. Depreciation expense will increase 25% annually f. Insurance is based on a percentage of patient service revenues. For forecasting purposes apply the 20X0 percentage of insurance to patient services revenue to all future years (carry to two decimal places) g. The Practice expects to repay all debt outstanding by the end of 20X0. Therefore, there will be no interest expense moving forward. h. Professional fees should remain constant at the 20X0 rate of 11720 USD i. Miscellaneous operating expenses are expected to increase by 8000 USD per year for the next three years. j. All other expenses not specifically listed above should increase by 2% annually. Finally, prepare a 2-page single-spaced memo in proper form to Dr. Carl analyzing the pros and cons of this decision and providing your recommendations for his future and the future of the Practice. Discuss major financial findings and any changes in assumptions that would increase or decrease the doubt about the Practice's financial sustainability. Discuss any ethical and/or social responsibility issues that arise from your recommendation and how Dr. Carl, current and future patients, and employees would be affected. Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Associates Assumption Table Revenue Assumptions: Year Medicare Commercial 2011 2022 0.00 0.00% ON Miscellaneous incense 004 Volume Assumptions: Medicare Year 2020 . Comercial LORE OOO OUR Expense Assumptions! 0.00%|Employee benefits 0.00 Payroll tax expen DON Rent expense Depreciation 000 inwarance expense 50 interesse 000 Professionales 30 Miscellaneous Brother Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Forecasted - Medicare IMA REVENUE Year Ended December 31, 2022 Reim Patie CPT Code Volume Revenue WT Forecasted - Medicare/Medical IMA REVENUE Year Ended thecember 11, 2023 Rrimi, Patient CPT Code Rocus Rate Volum Provided bata - Medicare IMA HEVENUE Year Ended December 31, 2020 CPT Patient Reimb. Hale Code Reven 01 05 5.36 99203 $7451 46 0212 99203 STOK 19 $17,758 SOM S16467 34 SW.0 99303 50 30 STIUM 32041 374 09 54389 624 $27:35 57281 461 $33,565 W2214 STO) 07 25 512 650 3. Forecasted Medicare IMA REVENUE Year Ended December 11, 2021 CPT Code Reim. Rate Patient Volume Rever 99201 99202 99203 99204 09.05 99211 990 9913 w 915 99203 99204 2303 99203 9304 205 1211 99312 9223 99212 99203 VS WAS Total Medicare 5893 5516.15 Total - Medicare Total Medicar Total Medicare Forecasted-Commerical murance TIMA REVENUE Year Ended December 31, 2022 CPT Code Reim Patica Here Rate Volume Ferested - Commerical Insurance IMA REVENUE Year Ended December 31, 2021 CPT Code Reimh Rate Patie Volume Rev 99201 99302 w 2014 995 9911 Forecasted-Commerical Insurance IMA REVENUE Year Ended December 31, 2023 Reim Patient CPT Code Hans Vol Revenue 201 WO 0 ou 205 2.22 22.30 25 Provided Dats. Commerical Insurance IMA HEVENUE Year Ended December 31, 2010 CPT Heimb. Rate Patient Code Vome Rever 9201 $75.00 198 $14.50 02 595 00 IND STATO 500 5 5W30 204 SENSO 962 567 99203 $200.00 So S119.00 55000 3150 52000 214 57ND 300 180 312750 1214 4 307340 99215 $175.00 556700 309.30 STO SR ST 64 $10,210 TO SIYO 30 5 7 MISTE 20 SRO TARI NE SJES 22 23 21 1921 9914 92275 20 5 900 WA 99 www WW PIRES PRO 17 Total Commercial Total Total Total Plent Service Here SIBUS, Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Amoriates FORCASTED Income Statement For the wars ended December 31, 2021 through 2023 2022 Revenue Patient Service Reven Miscellaneous Income Internal Medicine Associates Income Statement For the year ended December 31, 2030 through 2018 2018 Revenue Patient Service Revenue $1,225.170 $08.979 $992.431 Miscellaneous Income 1.120 1.200 95 Total Rese Total Rem $1,226.200 5985.179 5993.326 Express Wage Employee benefits Payroll taxes Rent expert Deprecation Inse Utilities Interes One wpplies Service contact Cleaning & Maintenan Telephone Professional fees Misc Total Expat Ways Employee benefits Payrolles Rent experte Depreciate Insane Utilities Inter Office Service to Clan Main Telephone Professional Music Total Expres 5709.06 $106.36 $3.5 63,050 7.463 0,414 19.511 63 14.95 13,031 13.IN 11,25 11.720 53,713 31.10. 5700 201 4,542 53.197 00 3,500 47, 15.131 1.10 12.147 10,163 14,113 13,840 4.975 30,14 31.1039 5610,003 38.200 30 400 6,52 28.403 10414 1232 9.27) 7.30 N161 3740 59.97 Net Income (I.) 50 Net Income.com) $112.00 SLIK, SR5,99 Assumptions Revenue Forecast Income Statement Forecast Financial Analysis Internal Medicine Amore COMMON SIZE ANALYSIS For the years ended December 31, 2018 through 2023 302 2022 2021 2010 2018 Revenue Pet Service Revue Miscellaneous Income Total Reve Kupe Employee Payroll Rem tyret Insurance Uma) Office plier Service Cleaning & Maintenance Telephone Professional fees Misperating expert Total Expres Net Income Lox)