Porter Hospital - A DSS for Profit Planning

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:

	1994 Actual	1995 Plan	1996 Plan	1997 Plan	1998 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 your spreadsheet as displayed and as formulas. Enter your name at the bottom left corner of the spreadsheet.

The Porter Hospital management want to have a menu allowing 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 a menu macro to accomplish this).

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.

	1994	1995	1996	1997	1998
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.