Question

Fox Valley Health Care Facilities has three Revenue Producing Departments and four Non-Revenue Producing departments with the data and costs as follows:

Departments	Costs	Cost Driver
Administration	$200,000	Direct costs
Maintenance	300,000	Square Feet
Purchasing	250,000	Number of Purchase Orders
Dietary	200,000	Number of Meals
Sunny Vale Nursing Home	500,000	Revenue Producing – N/A
In-Patient	600,000	Revenue Producing – N/A
Out-Patient	210,000	Revenue Producing – N/A

Cost Driver Data

Departments	Square feet	Purchase Orders	Meals Served
Administration	1,200	1,600	200
Maintenance	3,000	1,400	100
Purchasing	1,000	1,350	150
Dietary	4,000	1,000	200
Sunny Vale Nursing Home	25,000	1,500	600
In-Patient	11,000	1,300	300
Out-Patient	4,000	2,200	100

REQUIRED:

Prepare an Excel spreadsheet:

1. Prepare a Cost report, using the Direct Method.

2. Prepare a Cost report, using the Reciprocal Method.

3. Prepare a Cost Report, using Step-down Method.

i.Prepare the step-down.

ii.Determine the total amount of costs of each of the non-revenue producing departments that would be allocated to each of the revenue producing departments. Prepare a separate table providing this information.

Answer 1

1	Cost Report using Direct Method
	Particulars	Cost Driver	Sunny Vale Nursing Home	In-Patient	Out-Patient	Total
	Direct Cost		500,000	600,000	210,000	1,310,000
	Administration	Direct Cost	76,336	91,603	32,061	200,000
	Maintenance	Square Feet	187,500	82,500	30,000	300,000
	Purchasing	Number of Purchase Orders	75,000	65,000	110,000	250,000
	Dietary	Number of Meals	120,000	60,000	20,000	200,000
	Total Cost		958,836	899,103	402,061	2,260,000
2	Cost Report using Reciprocal Method
	Particulars	Cost Driver	Sunny Vale Nursing Home	In-Patient	Out-Patient	Total
	Direct Cost		500,000	600,000	210,000	1,310,000
	Maintenance	Square Feet	187,500	82,500	30,000	300,000
	Purchasing	Number of Purchase Orders	75,000	65,000	110,000	250,000
	Dietary	Number of Meals	120,000	60,000	20,000	200,000
	Administration	Direct Cost	85,680	78,398	35,922	200,000
	Total Cost		968,180	885,898	405,922	2,260,000
3	Cost Report using Step Down Method
	Particulars	Cost Driver	Sunny Vale Nursing Home	In-Patient	Out-Patient	Administration	Maintenance	Dietary	Total
	Direct Cost		500,000	600,000	210,000	200,000	300,000	200,000	2,010,000
	Purchasing	Number of Purchase Orders	41,667	36,111	61,111	44,444	38,889	27,778	250,000
	Dietary	Number of Meals	105,128	52,564	17,521	35,043	17,521		227,778
	Maintenance	Square Feet	216,268	95,158	34,603	10,381			356,410
	Administration	Direct Cost	110,637	132,764	46,467				289,868
	Total Cost		973,700	916,597	369,703	289,868	356,410	227,778	3,134,056
	Total Cost				2,260,000