Assignment:Prepare and submit two stepdown spreadsheets for this case to determine the cost per visit as follows:
1. By allocating the cost of nursing, social services, laboratory, record-keeping, administration and rent, utilities and cleaning to the Pediatrics, Family Planning, Adult Medicine, Nursing, Mental Health, Social Services, Community Health, and Dental Services Department. To check your work for accuracy you should find that the cost per visit to Adult Medicine is a surprising $47.51 given its $11.00 fee
.
2. For this somewhat more realistic stepdown analysis, treat the laboratory and social service departments as separate cost centers. Here the social service department charges separately regardless of where it offers services while the laboratory charges on an hourly basis. To check your work for accuracy you should find that the cost per hour for the Laboratory is $8.77 (Well, it was 1975 after all!).
Use the following Exhibit to calculate the above: Separate in two spreadsheets:
Exhibit 1 Financial Statements for 1975
Balance Sheet
As of December 31,1975
Assets
Liabilities and Fund Balances
Cash
30,934
Accounts Payable
32,754
Accounts Receivable
8,800
Accrued wages
3,466
Inventory
40,540
Total Current
36,220
Total Current
80,274
Bank Loan
27,550
Furniture & Equipment
21,700
Total Liabilities
63,770
Less: Accumulated
Depreciation
8,788
Fund Balances:
Total fixed
12,912
Designated by board for
25,000
Special Outreach Project
Total Assets
93,186
Purchases of new equip.
3,000
Undesignated
1,416
Total Fund balances
29,416
Total Liabilities and Fund
Balances
93,186
Income Statement
1975
Revenue from patient fees
345,450
Other revenue
5,000
Total Revenue
350,450
Expenses
Program services
235,000
Utilities
10,000
Laboratory
25,000
General and Administrative
92,000
Total Expenses
362,000
Surplus (Deficit)
(11,550)
Exhibit 2 Costs and Patient Visits1 for 1975, By Department
Expenses
Department
Number Patient Visits
Salaries2
Others3
Total
Pediatrics
5,000
$20,000
$8,000
$28,000
Family Planning
10,000
5,000
15,000
20,000
Adult Medicine
2,100
30,000
16,000
46,000
Nursing
4,000
27,000
6,000
33,000
Mental Health
1,400
15,000
8,000
23,000
Social Services
1,500
32,000
8,000
40,000
Community Health
2,500
5,000
10,000
15,000
Dental
6,400
20,000
10,000
30,000
Sub-Total
32,900
154,000
81,000
235,000
Administration
38,000
2,000
40,000
Rent
36,000
36,000
Utilities
10,000
10,000
Laboratory Work
16,000
9,000
25,000
Cleaning
6,000
6,000
Record Keeping
7,000
3,000
10,000
Total
$215,000
$147,000
$362,000
Number of Patient Visits
32,900
Average Cost per Visit
$11.00
1Patient visits rounded to nearest 100; Expenses rounded to nearest 1,000
2Includes fringe benefits
3Materials, supplies, contracted services, depreciation and other non-personnel expenses
Exhibit 3 Floor Space and Laboratory Usage1 by Department
Department
Floor Space2
Laboratory Usage3
Pediatrics
1,000
1,000
Family Planning
1,300
200
Adult Medicine
1,800
2,400
Nursing
300
100
Mental Health
1,000
—
Social Service
500
—
Community Health
1,100
100
Dental
1,000
200
Administration
500
—
Record Keeping
300
—
Laboratory
1,200
—
Total
10,000
4,000
1Rounded to nearest 100
2 In Square Feet
3In hours/year