Case 11 Student Spreadsheet (3)

xlsx

School

Virginia Commonwealth University *

*We aren’t endorsed by this school

Course

HADM-608

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by PresidentDiscovery9235

Model Page 1 CASE 11 CENTURY OAKS HEALTH Budgeting Concepts Student Version This case illustrates the use of budgeting to propose a new clinic site and help make operating de model extends out to Column AR. The model consists of a complete base case analysis--no changes need to be made to the existin spreadsheet have been replaced by zeros. Students must select appropriate input values and en the base case solution will appear. INPUT DATA: Operation Volume Assumptions: MD AP 1st 6 Months 12 Months 1st 6 Months Clinic Days / Week 5 5 5 Clinic Hours / Day 4.5 4.5 6 Visits / Hour 2.5 3 2.5 Inpatient Consults Days / Week 5 5 Inpatient Consults Hours / Day 2.5 2.5 Inpatient Consults / Hour 2 2 Note: Diagnostic Reading is unpredictable and small percentage of effort. Working Weeks 18 48 5.1 % New Patient Visits (NPV) 70% 70% 10% % Return Patient Visits (RPV) 30% 30% 90% Information to Drive Revenue Assumptions Revenue Ass New Patient Visits (NPV)- Clinic CPT Code Office Visit Description wRVU Value Charge Average Reve 99201 NPV, Level I 0.45 35.46 $ 106.38 99202 NPV, Level II 0.88 54.16 99203 NPV, Level III 1.34 99.30 99204 NPV, Level IV 2.30 154.63 99205 NPV, Level V 3.00 188.36 MODEL-GENERATED DATA section. However, values in the I NPUT DATA section of the studen them into the cells with values colored red . After this is done, any error cells will be corrected and

Model Page 2 Return Patient Visits (RPV)- Clinic CPT Code Office Visit Description wRVU Value Charge Average Reve 99211 RPV, Level I 0.17 3.03 $ 60.92 99212 RPV, Level II 0.45 30.45 99213 RPV, Level III 0.92 57.59 99214 RPV, Level IV 1.42 89.00 99215 RPV, Level V 2.00 124.51 Inpatient Consults (IC) CPT Code Office Visit Description wRVU Value Charge Average Reve 99251 IC, Level I 1.00 94.61 $ 152.55 99252 IC, Level II 1.50 118.57 99253 IC, Level III 2.27 135.28 99254 IC, Level IV 3.25 189.43 99255 IC, Level V 4.00 224.85 Expense Assumptions MD APP Annual Provider Compensation $ 425,000 $ 120,000 % Benefits 18% 21% Benefit Expense $ 76,500 $ 25,200 Total Annual Provider Expense $ 501,500 $ 145,200 # Months Worked in 1st 6 Months 6 3 1st 6 Months Expense $ 250,750 $ 36,300 Staffing Expenses Annual % FTE 12 Month Comp Clinic Coordinator $ 70,000 0.50 $ 35,000 CMA $ 36,000 1.00 $ 36,000 Clinical Support Specialist $ 40,000 1.00 $ 40,000 Total Staffing Expenses $ 146,000 $ 111,000 IT Operations Upfront Costs: PC & Peripheral Hardware $ 6,865 Network Equipment $ 27,540 Telecommunications $ 1,296 AV $ - Misc $ 6,419 Facilities Upfront Costs: Furniture $ 3,000 Exam tables $ 4,800 $ 800 per table 6 To complete assumption calculation o location. On amounts an

Model Page 3 AED $ 1,200 Misc $ 1,000 Ongoing Operational Expenses Annual Annual Lease $ 60,800 $16 per sq ft 3,800 Additional Operating Expenses Annual Monthly IT $ 83,748 $ 6,979 Utilities $ 1,500 $ 125 Cleaning $ 2,820 $ 235 Supplies $ 600 $ 50 MODEL-GENERATED DATA: Budget Analysis: Revenue Calculations based upon Volume and Revenue Assumptions Physician Volumes MD Volumes- Clinic APP Volumes 1st 6 Months 12 Months 1st 6 Months Days/Week- Clinic 5 5 5 Ambulatory Hours/Day 4.5 4.5 6 Clinic Visits/Hour 2.5 3 2.5 Clinic Visits/Week 56 68 75 Weeks During Time Period 18 48 5.1 Clinic Visits During Time Period 1,013 3,240 383 % Visits- NPV 70% 70% 10% % Visits- RPV 30% 30% 90% Total Clinic Volume- NPV 709 2,268 38 Total Clinic Volume- RPV 304 972 344 Average Revenue / NPV $ 106.38 $ 106.38 $ 106.38 Average Revenue / RVP $ 60.92 $ 60.92 $ 60.92 Total Revenue for NPV Clinic Visits $ 75,398 $ 241,274 $ 4,069 Total Revenue for RPV Clinic Visits $ 18,503 $ 59,210 $ 20,970 Total Clinic Revenue $ 93,901 $ 300,485 $ 25,039 MD Volumes- Inpatient Consults 1st 6 Months 12 Months Days/Week- Inpatient Consults 5 5 Consult Hours/Day 2.5 2.5 Consults/Hour 2 2

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Model Page 4 Consults/Week 25 25 Weeks During Time Period 18 48 Consult Volumes During Time Period 450 1200 Average Revenue / Consult $ 152.55 $ 152.55 Total Consult Revenue $ 68,647 $ 183,058 Revenue Calculation Summary MD Revenue APP Revenue 1st 6 Months12 Months 1st 6 Months 12 Months Clinic Revenue $ 93,901 $ 300,485 $ 25,039 $ 235,665 Consult Revenue $ 68,647 $ 183,058 Total Revenue $ 162,548 $ 483,542 $ 25,039 $ 235,665 Total Practice Site Revenue 6 Month Budget # Weeks Assumptions Revenue 18 Weeks Expenses 26 Weeks Revenue MD $ 162,548 APP $ 25,039 Total Revenue $ 187,588 Expenses Personnel MD Compensation $ 212,500 MD Benefits $ 38,250 MD Expense Total (Comp +Benefits) $ 250,750 APP Compensation $ 30,000 Clinic Coordinator $ 17,500 CMA $ 18,000 Clinical Support Specialist $ 20,000 Subtotal- Staff Compensation $ 85,500 Staffing Benefits $ 17,955 21% Staffing Expense Total (Comp + Benefits) $ 103,455

Model Page 5 Total personnel expenses with benefits $ 354,205 PC & Peripheral Hardware $ 6,865 Network Equipment $ 27,540 Telecommunications $ 1,296 AV $ - Misc $ 6,419 IT Go Live Expense Total $ 42,120 Furniture $ 3,000 Exam Tables $ 4,800 AED $ 1,200 Misc $ 1,000 Facility Go Live Expense Total $ 10,000 Lease / Rent $ 30,400 6 months IT $ 41,874 Utilities $ 750 Cleaning $ 1,410 Supplies $ 300 Monthly Operational Expenses Total $ 74,734 Total Non-Staffing Expenses (Go-Live + Monthly) $ 126,854 Total Expenses $ 481,059 Net income $ (293,471) 12 Month Budget # Weeks Assumptions Revenue 36 Weeks Expenses 52 Weeks Revenue MD $ 483,542 APP $ 235,665 Total Revenue $ 719,208 Expenses Personnel

Model Page 6 MD Compensation $ 425,000 MD Benefits $ 76,500 MD Expense Total (Comp +Benefits) $ 501,500 APP Compensation $ 120,000 Clinic Coordinator $ 35,000 CMA $ 36,000 Clinical Support Specialist $ 40,000 Subtotal- Staff Compensation $ 231,000 Staffing Benefits $ 48,510 21% Staffing Expense Total (Comp + Benefits) $ 279,510 Total personnel expenses with benefits $ 781,010 Lease / Rent $ 60,800 IT $ 83,748 Utilities $ 1,500 Cleaning $ 2,820 Supplies $ 600 Monthly Operational Expenses Total $ 149,468 Total Expenses $ 930,478 Net income $ (211,270) Copyright © 2022 Foundation of the American College of Healthcare Executives. Not for sale.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Model Page 7

Model Page 8 ecisions. Note that the ng nter PP 12 Months 5 6 2.5 48 10% 90% sumptions enue / NPV nt d To complete this input, determine volume assumptions from case information. Utilize revenue information by level of care to solve for revenue assumptions. Recommend utilizing average revenue per type of visit/consult.

Model Page 9 enue / RPV enue / Inpatient Consult 6 Mth Comp % Benefits Benefits $ 12 Months Benefits $ 6 Months Total 12 Mont $ 17,500 18% $ 6,300 $ 3,150 $ 41,300 $ 18,000 18% $ 6,480 $ 3,240 $ 42,480 $ 20,000 18% $ 7,200 $ 3,600 $ 47,200 $ 55,500 $ 19,980 $ 9,990 $ 130,980 rooms Each room will have 1 table e expense input, determine expense ns from case information. Annual lease is of $/sq ft * total square feet of new clinic ngoing expenses are provided in per month nd must be annualized.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version