The Pediatric Department of Emory Hospital operates as an individual business unit. The department rents space and beds from Emory Hospital, and accordingly receives charges related to janitorial, repairs & maintenance, general & administrative, and rent as listed in the table below. These charges are considered fixed based on the number of beds rented, which is also called bed capacity. When Pediatrics rents a bed, it is rented for the entire year.

In addition, the Pediatric Department is charged for specific patient services based on the number of patient days billed. These services are considered variable with respect to the number of patient days. They include dietary, laundry, lab work, pharmacy, and billing & collections as listed in the table below. Last year, actual costs incurred by the Pediatric department are as follows.

	Basis for Charges
	Based on Patient Days (variable)	Based on Bed Capacity (beds rented) (fixed)
Dietary	$ 600,000
Janitorial		$ 70,000
Laundry	300,000
Laboratory	450,000
Pharmacy	350,000
Repairs & Maintenance		30,000
General & Administrative		800,000
Rent		700,000
Billings & Collections	300,000
Totals	$ 2,000,000	$1,600,000

During the year, Pediatrics charged each patient an average of $350 per patient day, had a capacity of 60 beds, and had revenue of $7,000,000 for the year.

In addition, the Pediatric Department employed and paid the following personnel directly during the year:

	Annual Salaries	Number Required	Total Annual Salaries
Supervising Nurses	$80,000	4	$ 320,000
Nurses	$50,000	10	500,000
Aides	$20,000	20	400,000
Total			$1,220,000

The Pediatric Department must follow minimum personnel requirements that are published by the Federal Department of Health and are based on the number of patient days that are available during the year. Available patient days are calculated by taking the number of beds times 365 days per year.

Patient Days Available During the year	Number of Aides	Number of Nurses	Number of Supervising Nurses
Up to 21,900 days	20	10	4
21,900 to 25,915 days	24	12	4
25,916 to 30,100 days	28	14	5

These staffing levels represent full time equivalents (FTE). Pediatrics always employs the minimum number of required FTE in order to meet the federal requirements. Salaries of supervising nurses, nurses, and aides are therefore fixed within the range of patient days available during the year.

1. Create a What-if projection on excel (in the second tab) comprehending projected assumptions discussed below.
   1. Last year, Pediatrics operated at 100% capacity on 180 days. In other words, all 60 beds were filled on those days and the pediatrics Department had to divert patients to other hospitals in the area. On the remaining 180 days, the department was at less than 100% capacity. Using your excel spreadsheet, organize last years revenue data to be able to calculate the portion of last years revenue the department was at 100% capacity and portion of last years revenue the department was at less than 100% capacity. From this information, you should be able to calculate the average capacity the hospital was at on those remaining days. This can be a useful assumption when planning for future what-is analysis.