Hello! In the past we have used the direct method to allocate service department costs to our revenue producing departments; however, I just attended a conference where one of the presenters demonstrated how the step method can produce significantly different departmental cost calculations. I don't know how much it would matter at Basic if we used the step down method, but this person at the conference had me convinced that it could matter quite a bit. As soon as I got back from the conference I got into the system and downloaded the attached summary reports (tab delimited) needed to allocate costs from supporting departments to the producing departments for 2020. Please put together a report that shows the allocation using each method (direct and step) as well as a table that shows the differences (totals and as a percentage of support dept. cost) across the two allocation methods. For the step method, let's first allocate the departments that provide the most service to other support departments. Also, let's use the 12 month average of FTES and square footage for assignment where applicable. Otherwise, use the annual totals. Finally, please show me the differences for each department manager, as I'd like to know who will be affected the most if we switch to the step method. Thank you cost accountant! The Controller Upload the following files to complete this assignment: (1) A Microsoft Excel workbook named "allocation. The workbook should have 3 worksheets labeled "Direct," "Step," and "Differences. The worksheets will contain the schedules used to allocate the cost from the support departments to the producing departments in the first two worksheets. In the third worksheet, summarize the differences between the two methods by department and manager. In addition, the workbook will also have one or more additional worksheets that are used to hold the source data (i.e., the information in the files attached to this assignment) and perform interim calculations. Name worksheets that hold source data "sourcedata1," "sourcedata2" and "sourcedata3" Worksheets that contain interim calculations should be named "calc1" "calc2" and so on. The worksheets should be organized in the following order: Direct, Step, Differences, calc worksheets, sourcedata worksheets. (2) A PDF report that incorporates all 3 tables from the "Direct." "Step.," and "Differences" worksheets (in this order). After each allocation schedule, provide a written description of the schedule that explains the allocation technique and how it is applied at Basic Hospital to arrive at cost by department. After the "Differences" table, explain why the differences occur, using the department with the biggest difference as an example. Support Dept. Allocation base Medical Records Total Nursing Hours Accounting Total Direct Cost General Services Human Resources Total Patient Accounts Square Footage FTES Total Patient Revenue Department Medical Records 06-1000 Cohen 06-1001 Dillon Accounting General Services Human Resources 06-1003 Patient Accounts Urology 02-1005 Kline Orthopaedics Opthamology Dpt Current Dept. Manager Gastroenterology Cardiology Burn Center Emergency Oncology Obstetrics Neurology Maternity Intensive Care Unit General Surgery 02-1013 06-1002 Blasco Smith 06-1004 Cohen 02-1006 Dreyfus 02-1007 Kramer 02-1008 James 02-1009 Plammot 02-1010 Barnes 02-1011 Plammot 02-1012 Wellston Piedmont 02-1014 Rax Jills. 02-1015 02-1016 Cordona 02-1017 Jackson Hello! In the past we have used the direct method to allocate service department costs to our revenue producing departments; however, I just attended a conference where one of the presenters demonstrated how the step method can produce significantly different departmental cost calculations. I don't know how much it would matter at Basic if we used the step down method, but this person at the conference had me convinced that it could matter quite a bit. As soon as I got back from the conference I got into the system and downloaded the attached summary reports (tab delimited) needed to allocate costs from supporting departments to the producing departments for 2020. Please put together a report that shows the allocation using each method (direct and step) as well as a table that shows the differences (totals and as a percentage of support dept. cost) across the two allocation methods. For the step method, let's first allocate the departments that provide the most service to other support departments. Also, let's use the 12 month average of FTES and square footage for assignment where applicable. Otherwise, use the annual totals. Finally, please show me the differences for each department manager, as I'd like to know who will be affected the most if we switch to the step method. Thank you cost accountant! The Controller Upload the following files to complete this assignment: (1) A Microsoft Excel workbook named "allocation. The workbook should have 3 worksheets labeled "Direct," "Step," and "Differences. The worksheets will contain the schedules used to allocate the cost from the support departments to the producing departments in the first two worksheets. In the third worksheet, summarize the differences between the two methods by department and manager. In addition, the workbook will also have one or more additional worksheets that are used to hold the source data (i.e., the information in the files attached to this assignment) and perform interim calculations. Name worksheets that hold source data "sourcedata1," "sourcedata2" and "sourcedata3" Worksheets that contain interim calculations should be named "calc1" "calc2" and so on. The worksheets should be organized in the following order: Direct, Step, Differences, calc worksheets, sourcedata worksheets. (2) A PDF report that incorporates all 3 tables from the "Direct." "Step.," and "Differences" worksheets (in this order). After each allocation schedule, provide a written description of the schedule that explains the allocation technique and how it is applied at Basic Hospital to arrive at cost by department. After the "Differences" table, explain why the differences occur, using the department with the biggest difference as an example. Support Dept. Allocation base Medical Records Total Nursing Hours Accounting Total Direct Cost General Services Human Resources Total Patient Accounts Square Footage FTES Total Patient Revenue Department Medical Records 06-1000 Cohen 06-1001 Dillon Accounting General Services Human Resources 06-1003 Patient Accounts Urology 02-1005 Kline Orthopaedics Opthamology Dpt Current Dept. Manager Gastroenterology Cardiology Burn Center Emergency Oncology Obstetrics Neurology Maternity Intensive Care Unit General Surgery 02-1013 06-1002 Blasco Smith 06-1004 Cohen 02-1006 Dreyfus 02-1007 Kramer 02-1008 James 02-1009 Plammot 02-1010 Barnes 02-1011 Plammot 02-1012 Wellston Piedmont 02-1014 Rax Jills. 02-1015 02-1016 Cordona 02-1017 Jackson Hello! In the past we have used the direct method to allocate service department costs to our revenue producing departments; however, I just attended a conference where one of the presenters demonstrated how the step method can produce significantly different departmental cost calculations. I don't know how much it would matter at Basic if we used the step down method, but this person at the conference had me convinced that it could matter quite a bit. As soon as I got back from the conference I got into the system and downloaded the attached summary reports (tab delimited) needed to allocate costs from supporting departments to the producing departments for 2020. Please put together a report that shows the allocation using each method (direct and step) as well as a table that shows the differences (totals and as a percentage of support dept. cost) across the two allocation methods. For the step method, let's first allocate the departments that provide the most service to other support departments. Also, let's use the 12 month average of FTES and square footage for assignment where applicable. Otherwise, use the annual totals. Finally, please show me the differences for each department manager, as I'd like to know who will be affected the most if we switch to the step method. Thank you cost accountant! The Controller Upload the following files to complete this assignment: (1) A Microsoft Excel workbook named "allocation. The workbook should have 3 worksheets labeled "Direct," "Step," and "Differences. The worksheets will contain the schedules used to allocate the cost from the support departments to the producing departments in the first two worksheets. In the third worksheet, summarize the differences between the two methods by department and manager. In addition, the workbook will also have one or more additional worksheets that are used to hold the source data (i.e., the information in the files attached to this assignment) and perform interim calculations. Name worksheets that hold source data "sourcedata1," "sourcedata2" and "sourcedata3" Worksheets that contain interim calculations should be named "calc1" "calc2" and so on. The worksheets should be organized in the following order: Direct, Step, Differences, calc worksheets, sourcedata worksheets. (2) A PDF report that incorporates all 3 tables from the "Direct." "Step.," and "Differences" worksheets (in this order). After each allocation schedule, provide a written description of the schedule that explains the allocation technique and how it is applied at Basic Hospital to arrive at cost by department. After the "Differences" table, explain why the differences occur, using the department with the biggest difference as an example. Support Dept. Allocation base Medical Records Total Nursing Hours Accounting Total Direct Cost General Services Human Resources Total Patient Accounts Square Footage FTES Total Patient Revenue Department Medical Records 06-1000 Cohen 06-1001 Dillon Accounting General Services Human Resources 06-1003 Patient Accounts Urology 02-1005 Kline Orthopaedics Opthamology Dpt Current Dept. Manager Gastroenterology Cardiology Burn Center Emergency Oncology Obstetrics Neurology Maternity Intensive Care Unit General Surgery 02-1013 06-1002 Blasco Smith 06-1004 Cohen 02-1006 Dreyfus 02-1007 Kramer 02-1008 James 02-1009 Plammot 02-1010 Barnes 02-1011 Plammot 02-1012 Wellston Piedmont 02-1014 Rax Jills. 02-1015 02-1016 Cordona 02-1017 Jackson