# 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.