Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Can someone please explain or show me how to show the reciprocal cost allocation in excel for the data given below EXHIBIT 7.1 Eagan Family
Can someone please explain or show me how to show the reciprocal cost allocation in excel for the data given below
EXHIBIT 7.1 Eagan Family Practice: Departmental Revenue and Cost Projections Revenues Adult Medicine Obstetrics Pediatrics Total revenues $12,000,000 6,000,000 2,000,000 $20,000,000 $ 6,000,000 3,600,000 1,200,000 $ 10,800,000 Direct Costs Patient Services Adult Medicine Obstetrics Pediatrics Subtotal Support Administration Facilities Finance Subtotal Total expenses $ 1,000,000 4,400,000 1,800,000 $ 7,200,000 $18,000,000 Pretax profit $ 2,000,000 Percentage of Services Provided by EXHIBIT 7.2 Eagan Family Practice: Allocation Percentages Services Provided to Administration Facilities Finance 5% Administration Facilities Finance Adult Medicine Obstetrics Pediatrics Total 100% Percentage to support departments 20% 15% 10% Percentage to patient service departments 80% 85% 90% Notes: 1. The allocation percentages are based on a two-year analysis of the actual services provided by the support departments to other departments, 2. To use the percentages to perform an allocation, they may have to be adjusted to ensure that the entire amount of the cost pool is allocated. To illustrate, in the direct method, all of Administration's costs ($500,000) have to be allocated directly in a single allocation to the three patient service departments. If the raw percentages were used, only 35% + 20% + 25% = 80% of the cost pool would be allocated, so the allocation percentages have to be adjusted so that 80 percent represents the entire allocation (100 percent). Thus, instead of a 35 percent allocation to Adult Medicine. its adjusted allocation is 35%/80% = 43.75%. In a similar manner, the adjusted allocation to Obstetrics is 20%/80% - 25%, while the adjusted allocation to Pediatrics is 25%/80% - 31.25%. When done correctly, the adjusted percentages must sum to 100%: 43.75% + 25% + 31.25% = 100%Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started