The subsequent two spreadsheets provide workload (expressed as relative weighted
ID: 2626245 • Letter: T
Question
The subsequent two spreadsheets provide workload (expressed as relative weighted products (RWPs) for inpatient care), revenue, and expense data for Schumpert Medical Center covering their fiscal years 2010 and 2011. The data includes FY10 actual workload, revenues, and expenses; FY11 forecasted workload, revenues, and expenses; and FY11 year-to-date workload, revenues, and expenses. The Inpatient Product Lines worksheet provides further details on the types of services provided within each major service line. Using the available data, perform a variance analysis on Schumpert Medical Center and answer the following questions:
Schumpert Medical Center
Revenues Worksheet, Mid-Year Review
Inpatient
Inpatient Product Line
Price/RWP
Forecasted RWPs
For FY11
Projected IP Revenue
For FY11
Actual RWPs
(FY11 YTD)
Actual IP Revenue
(FY11 YTD)
CIRC
$ 6,010.65
724
$ 4,351,711
421
$ 2,530,676
DIGEST
$ 6,021.65
1,161
$ 6,988,125
636
$ 3,830,402
ENT
$ 6,001.65
130
$ 780,215
72
$ 433,948
GYN
$ 6,013.65
219
$ 1,313,983
116
$ 697,128
MENTAL HEALTH
$ 502.99
1,351
$ 679,539
736
$ 369,949
NERVOUS
$ 6,103.65
228
$ 1,391,632
112
$ 682,744
NEWBORN
$ 7,014.71
733
$ 5,141,782
433
$ 3,033,900
OB
$ 5,713.65
1,501
$ 8,576,189
868
$ 4,960,622
ORTHO
$ 6,084.18
631
$ 3,839,118
274
$ 1,664,658
OTHER
$ 6,018.14
2,152
$ 12,951,037
800
$ 4,813,961
RESP
$ 6,118.73
677
$ 4,142,380
197
$ 1,202,960
Total Inpatient Earnings
9,506
$ 50,155,710
4,664
$ 24,220,949
Actual IP Revenue
(FY11 YTD)
$ 2,530,676
$ 3,830,402
$ 433,948
$ 697,128
$ 369,949
$ 682,744
$ 3,033,900
$ 4,960,622
$ 1,664,658
$ 4,813,961
$ 1,202,960
$ 24,220,949
Schumpert Medical Center
Expenses Worksheet, Mid-Year Review
Inpatient
Inpatient Product Line
Budget/RWP
(FY10)
Actual RWPs
(FY10)
IP Expenses
(FY10)
Forecasted RWPs
(FY11)
FY11 Budget
(FY10+6% Inflation)
Actual RWPs
(FY11 YTD)
Actual IP Expenses
(FY11 YTD)
CIRC
$ 5,475.81
690
$ 3,778,309
724
$ 4,202,356
421
$ 2,472,822
DIGEST
$ 5,476.81
1,105
$ 6,051,875
1,161
$ 6,737,188
636
$ 4,356,173
ENT
$ 5,477.81
124
$ 679,248
130
$ 754,842
72
$ 462,822
GYN
$ 5,478.81
208
$ 1,139,592
219
$ 1,268,947
116
$ 777,322
MENTAL HEALTH
$ 498.78
1,287
$ 641,930
1,351
$ 714,283
736
$ 400,730
NERVOUS
$ 5,478.81
217
$ 1,188,902
228
$ 1,324,119
112
$ 675,173
NEWBORN
$ 5,478.81
698
$ 3,824,209
733
$ 4,256,926
433
$ 2,971,020
OB
$ 5,478.81
1,430
$ 7,834,698
1,501
$ 8,717,115
868
$ 5,472,892
ORTHO
$ 5,478.81
601
$ 3,292,765
631
$ 3,664,557
274
$ 1,771,063
OTHER
$ 5,478.81
2,050
$ 11,231,561
2,152
$ 12,497,823
800
$ 4,662,277
RESP
$ 5,478.81
645
$ 3,533,832
677
$ 3,931,704
197
$ 1,233,771
Total Inpatient Expenses
$ 43,196,922
9,506
$ 48,069,860
4,664
$ 25,256,062
Actual RWPs
(FY11 YTD)
Actual IP Expenses
(FY11 YTD)
421
$ 2,472,822
636
$ 4,356,173
72
$ 462,822
116
$ 777,322
736
$ 400,730
112
$ 675,173
433
$ 2,971,020
868
$ 5,472,892
274
$ 1,771,063
800
$ 4,662,277
197
$ 1,233,771
4,664
$ 25,256,062
1. What was the hospital's original profit forecast (assume away any issues with depreciation, taxes, etc.)? Halfway through the fiscal year, what is the hospital's revised projection for FY11 profits?
Answer:
2. Which inpatient service lines are over budget? Which product lines are over budget after accounting for workload increases?
Answer:
3. What actions would you take at the mid-year point if you were a fee-for-service hospital? In other words, where are the problem areas on which you would focus your attention, and who might provide ideas for "best practices" based on their performance?
Answer:
4. What actions would you take at the mid-year point if you were a capitated hospital? In this case, the revenue spreadsheet would be replaced with an overall budget of $50 million with which to operate (rather than being able to bill for each episode of patient care). Federal, state, county, and city hospitals normally operate under a capped budget. Additionally, many HMOs also operate under a fixed Per Member, Per Month (PMPM) capitated process.
Schumpert Medical Center
Revenues Worksheet, Mid-Year Review
Inpatient
Inpatient Product Line
Price/RWP
Forecasted RWPs
For FY11
Projected IP Revenue
For FY11
Actual RWPs
(FY11 YTD)
Actual IP Revenue
(FY11 YTD)
CIRC
$ 6,010.65
724
$ 4,351,711
421
$ 2,530,676
DIGEST
$ 6,021.65
1,161
$ 6,988,125
636
$ 3,830,402
ENT
$ 6,001.65
130
$ 780,215
72
$ 433,948
GYN
$ 6,013.65
219
$ 1,313,983
116
$ 697,128
MENTAL HEALTH
$ 502.99
1,351
$ 679,539
736
$ 369,949
NERVOUS
$ 6,103.65
228
$ 1,391,632
112
$ 682,744
NEWBORN
$ 7,014.71
733
$ 5,141,782
433
$ 3,033,900
OB
$ 5,713.65
1,501
$ 8,576,189
868
$ 4,960,622
ORTHO
$ 6,084.18
631
$ 3,839,118
274
$ 1,664,658
OTHER
$ 6,018.14
2,152
$ 12,951,037
800
$ 4,813,961
RESP
$ 6,118.73
677
$ 4,142,380
197
$ 1,202,960
Total Inpatient Earnings
9,506
$ 50,155,710
4,664
$ 24,220,949
Actual IP Revenue
(FY11 YTD)
$ 2,530,676
$ 3,830,402
$ 433,948
$ 697,128
$ 369,949
$ 682,744
$ 3,033,900
$ 4,960,622
$ 1,664,658
$ 4,813,961
$ 1,202,960
$ 24,220,949
Explanation / Answer
Question 1 The original profit forecast was $2,085,081 (Projected IP Revenue FY11 - Budget Expenses FY11) The revised projection for FY11 profits is ($1,809,697) [Actual FY 11 YTD Profit per unit * Forecasted Quantity for FY11]. Question 2 All service lines are overbudget. After accounting for the increase in production [FY11 Budget / (Actual Quantity * 2)], only 4 are overbudget. This is assuming the rate of production at FY11 YTD stays the same throughout the rest of the year, which means 2 times FY11YTD quantity should be produced by year end. Budget Cost per Unit Actual Cost per Unit Revised Budget Cost per Unit (Projected Cost * Projected Quantity=FY 11 YTD) (FY 11 YTD) Overbudget Assuming Rate of Production stays at FY11 YTD Rates Overbudget $5,804.36 $5,873.69 ($69.33) $4,991 $813.44 $5,805.42 $6,849.33 ($1,043.91) $5,297 $508.89 $5,806.48 $6,428.08 ($621.60) $5,242 $564.52 $5,807.54 $6,701.05 ($893.51) $5,470 $337.94 $528.71 $544.47 ($15.76) $485 $43.46 $5,807.54 $6,028.33 ($220.79) $5,911 ($103.71) $5,807.54 $6,861.48 ($1,053.94) $4,916 $891.92 $5,807.54 $6,305.18 ($497.64) $5,021 $786.16 $5,807.54 $6,463.73 ($656.19) $6,687 ($879.61) $5,807.54 $5,827.85 ($20.31) $7,811 ($2,003.60) $5,807.54 $6,262.80 ($455.26) $9,979 ($4,171.41) QQuestion 3 I would look at which service lines are projected to produce the most profit per unit, and raise production of those service lines while decreasing production of the lesser profitable ones. Management is always a good source for ideas, they may know which service lines have unnecessary expenses such as extra workers. Marketing may also be able to provide insights on the demand of each service line, which would help inplanning production. Question 4 Same as 1st time Please note I did have to take some assumptions on this question that weren't exactly written out, such assumption of YTD rates continuing until year end. I assume that this could have been answered more easily if I were actually in the class, because it provides feeds off the topics being taught. I also can't remember the exact formulas for variances, but if you look it over and are able to explain what was done here, it should be OK, unless your professor is a complete "Go by what I say" type of guy. Well I gave it my best shot GL lol
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.