Porter Hospital - A DSS for Profit Planning

Adapted by Prof. Al Bento, University of Baltimore, version 5.2 - August 2005.

The Porter Hospital management decided to develop a DSS for profit planning using a spreadsheet and hired you to develop the system. The following profit plan format should be used:

PORTER HOSPITAL
PROFIT PLAN 2005/2008

  2004
Actual 		2005
Plan 		2006
Plan 		2007
Plan 		2008
Plan
REVENUES          
Daily services          
Ancillary          
Outpatient          
TOTAL          
Deductions          
NET REVENUE          
EXPENSES          
Salaries          
Benefits          
Supplies          
Fees          
Depreciation          
TOTAL EXPENSES          
NET INCOME          

The formulas to compute each of the items in the spreadsheet format above are shown in Table I. The actual and forecasted number of beds occupied, billing rates, etc, are shown in Table II.

Please print (save as PDF) your spreadsheet as displayed and as formulas. Enter the group number and group member names at the bottom left corner of the spreadsheet.

The Porter Hospital management want to have a way to change billing rates (price), percentage of beds occupied, average daily labor cost per occupied bed, in order to evaluate profit plan alternatives. (You should use as many sheets in one worksheet as needed to accomplish this).

TABLE I
Formulas used at the Porter Hospital

Revenues
daily hospital services occupied beds x billing rate
ancillary ancillary procedures x billing rate
outpatient revenue outpatient visits x billing rate
total revenue sum of daily hospital services to outpatient revenue
deductions five percent of total revenue
net revenue total revenue less deductions
Expenses
salaries 15,000 + 1.50 x occupied beds
benefits 25 percent of salaries
supplies 200 + 8 percent of total revenue
fees 2,150 fixed *
depreciation 1,000 fixed *
total expenses all the above plus interest
Net Income
net income net revenue less total expenses
* monthly.

Note: the average daily labor cost per occupied bed in the formulas in Table I is the $1.50 value in the formula for salaries (it seems to assume that a nurse can take care of about 15-20 beds).

TABLE II
Forecasts and projections

 
2004
2005
2006
2007
2008
beds 360 440 440 440 440
daily occupancy, % 83 84 82 80 81
ancillary procedures** 300 350 400 450 500
outpatient visits* 8000 8000 8000 8000 9000
daily rate, $ 76 77 78 79 80
ancillary rate, $ 31 32 33 34 35
outpatient rate, $ 15 17 19 20 22
interest,* $ 6000 5500 5000 4500 4000
** weekly. * monthly.

This page is maintained by Al Bento who can be reached at abento@ubalt.edu. This page was last updated on August 15, 2005. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.