Porter Hospital - A DSS for Profit Planning

The management needs.

	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.

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.

	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.