# Porter Hospital - A DSS for Profit Planning

Adapted by Prof. Al Bento, University of Baltimore, version 4.0 - March 2002.

## The management needs.

The Porter Hospital management decided to develop a DSS for profit planning using a spreadsheet and hired you to develop the system, and told you that the profit plan format shown below should be used for the spreadsheet:

PORTER HOSPITAL
PROFIT PLAN 2002/2005

 2001 Actual 2002 Plan 2003 Plan 2004 Plan 2005 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.

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.

TABLE II
Forecasts and projections

 2001 2002 2003 2004 2005 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.

## What are you supposed to do?

The Porter Hospital management wants to have five different alternative plans by varying the daily services billing rate (price) and percentage of beds occupied, in order to evaluate possible scenarios.

1. Create five sheets inside a worksheet (one for each alternative) with the same format of the Profit Pan shown previously, together with the data in Table II. In each alternative change the values of daily services billing rates and percentage of beds ocupied (increasing or decreasing).

2. create a sixth sheet inside the same worksheet with approximately the following format:

PORTER HOSPITAL
ALTERNATIVE PROFIT PLANS

 Alternative 1 Alternative 2 Alternative 3 Alternative 4 Alternative 5 2002 Net Income daily services billing rate % of beds occupied 2003 Net Income daily services billing rate % of beds occupied 2004 Net Income daily services billing rate % of beds occupied 2005 Net Income daily services billing rate % of beds occupied

3. Link each cell of the summary sheet to cells in the other five sheets in the same worksheet to display the five alternative results.

4. Print the summary sheet and one of the detailed sheets as results and as formulas.

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