Hi, can you help me with my homework using Excel formula?

You may find it helpful to print out this example so that you can follow along as you complete the actual GHA. SAMPLE SCENARIO AND CALCULATIONS DIRECT COSTS The direct costs for each department are easily identifiable, because they are the costs that are directly attributable to the departmer The direct costs are as follows: Physical Therapy (PT) 10,000 Occupational Therapy (OT) 90,000 Speech Therapy (ST) 20,000 Total Direct Costs 20,000 NDIRECT COSTS The indirect costs will need to be allocated to Physical Therapy (PT), Occupational Therapy (OT), and Speech Therapy (ST). The indirect costs in this scenario have been identified as: (1) Clerical Salaries, (2) Administrative Salaries, and (3) Computer Services. The total indirect costs that need to be allocated to PT, OT, and ST are as follows: Clerical Salaries 60,000 Administrative Salaries 50,000 Computer Services 75,000 Total Indirect Costs 185,000 Each type of indirect cost will need to be allocated to each department based on an allocation basis that is appropriate for the particul Clerical Salaries will be allocated based on Visit Volume. The idea being that the more patients the department sees, ti clerical work that will be required. Administrative Salaries will be allocated based on the Proportion of Direct Costs. The idea being that the higher the the more the department will require administrative oversight. Computer Services will be allocated based on the Number of Computers in Service. The idea being that the more c has, the more computer support it will need. Now that you understand the facts, here is an explanation of how to approach calculating the direct and indirect costs for each depart Step 1: Set-up a Table of Direct and Indirect Costs This is the table that you will need to Direct Cost Indirect Cost complete as you work through this GHA. Total Physical Therapy (PT) 410,000 In Step 1, you only know the direct costs Occupational Therapy (OT) 190,000 You now need to calculate the indirect Speech Therapy (ST) 120,000 costs so that you can determine the tote Totals 720,000 costs. Step 2: Determine the indirect costs that should be allocated from Clerical Salaries, Administrative Salaries, and Computer Services to Clerical Salaries The total indirect costs for Clerical Salaries is 60,900. Clerical Salaries will be allocated based on Visit Volume. Here are the actual Visit Volumes for PT, OT, and ST: Physical Therapy (PT) 3,500 Occupational Therapy ( 4,000 Speech Therapy (ST) 2,500 Total Visit Volume 15,000 Based on this Visit Volume, the percentage of Visit Volume by department is as follows: Physical Therapy (PT) 8,500 57% Occupational Therapy ( 1,00 27% Speech Therapy (ST) 2,500 16% Total 15,000 100% Use the percentage of Visit Volumes to determine the allocation of the Indirect Costs for Clerical Salaries: Total indirect costs for Clerical Salaries $ 60,000 Physical Therapy (PT) 57% 34,200 Notice how you start with $60,000 in indirect costs Occupational Therapy ( 27% 16,200 that need to be allocated. And, after you allocate th Speech Therapy (ST) 16% $ 9,600 costs to PT, OT, and ST your total comes to $60,000. Total Indirect Costs 100% S 60,000 Administrative Salaries The total indirect costs for Administrative Salaries is 50,000. Administrative Salaries will be allocated based on the proportion (%) of direct costs. The Direct Costs for PT, OT, and ST are as follows: Physical Therapy (PT) 410,000 Occupational Therapy ( 190,000 Speech Therapy (ST) 120,000 Total Visit Volume 720,000 The proportion of these direct costs is as follows: Physical Therapy (PT) 410,000 57% Occupational Therapy ( 190,000 26% Speech Therapy (ST) 120,000 17% Total 720,000 100% Use the proportion of the direct costs to determine the allocation of the Indirect Costs for Administrative Salaries: Total indirect costs for Administrative S $ 50,000 Physical Therapy (PT) 57% 28,500 Notice how you start with $50,000 in indirect costs Occupational Therapy ( 26% $ 13,000 that need to be allocated. And, after you allocate the costs to PT, OT, and ST your total comes to $50,000. Speech Therapy (ST) 17% S 8,500 Total Indirect Costs 100% s 50,000 computer Services The total indirect costs for Computer Services is 75,000. Computer Services will be allocated based on the number of computers in service. Here are the actual numbers of computers in service for PT, OT, and ST: Physical Therapy (PT) 9 Occupational Therapy ( Speech Therapy (ST) Total Visit Volume 15 The proportion of computers in service is as follows: Physical Therapy (PT) 9 60% Occupational Therapy ( 20% Speech Therapy (ST) 20% Total 15 100% Use the proportion of the numbers of computers in service to determine the allocation of the Indirect Costs for Computer S Total indirect costs for Computer Servic $ 75,000 Physical Therapy (PT) 60% 45,000 Notice how you start with $75,000 in indirect costs Occupational Therapy ( 20% 15,000 that need to be allocated. And, after you allocate the costs to PT, OT, and ST your total comes to $75,000. Speech Therapy (ST) 20% 15,000 Total Indirect Costs 100% $ 75,000 Step 3: Determine to total indirect costs for PT, OT and ST. Based on your calculations in each part of Step 2, you can create a table to determine the total indirect costs for PT, OT, and Notice how the Clerical Salaries column comes from the Clerical Salaries calculations above. Notice how the Administrative Salaries column co comes from the Administrative Salaries calculations above. Notice how the Computer Services column comes from the Computer Services calculations above. Clerical Administrative Computer TOTAL Salaries Salaries Services DIRECT COSTS Physical Therapy (PT) 34,200 28,500 45,000 107,700 Occupational Therapy 16,200 13,000 15,00 44,200 Speech Therapy (ST) 9,600 8,500 15,000 33,100 Total Indirect Costs 60,000 50,000 75,000 185,000 Step 4: Add the direct costs from Step 1 to the total indirect costs by department from Step 3 to get your total costs. (From Step 1) From Step 3) Direct Cost Indirect Cost Total Cost Physical Therapy (PT) 410,000 107,700 517,700 Occupational Therapy (OT) 190,00 44,200 234,200 Speech Therapy (ST) 120,00 33,100 153,100 Total 720,000 185,000 905,000GHA 4 TEMPLATE This assignment is designed to help you learn how to determine total costs by combining direct and indirect costs for a particular department. In this GHA, you will be determining the total costs for three departments: (1) Physical Therapy (2) Occupational Therapy and (3) Speech Therapy. To determine the total costs, you will need to add the direct costs to the indirect costs using the template provided below. Please use the example provided in the assignment description if you find that you need some support understanding the required calculations. 0 DIRECT COSTS The direct costs for each department are easily identifiable, because they are the costs that are directly attributable to the department. The direct costs are as follows: Physical Therapy (PT) 615,000 Occupational Therapy (OT) 185,000 Speech Therapy (ST) 105,000 Total Direct Costs 905,000 INDIRECT COSTS The indirect costs will need to be allocated to Physical Therapy (PT), Occupational Therapy (OT), and Speech Therapy (ST). The indirect costs in this scenario have been identified as: (1) Clerical Salaries, (2) Administrative Salaries, and (3) Computer Services. The total indirect costs that need to be allocated to PT, OT, and ST are as follows: Clerical Salaries 60,000 Administrative Salaries 80,000 Computer Services 55,000 Total Indirect Costs 195,000 Each type of indirect cost will need to be allocated to each department based on an allocation basis that is appropriate for the particular type of cost. Clerical Salaries will be allocated based on Visit Volume. Administrative Salaries will be allocated based on the Proportion of Direct Costs Computer Services will be allocated based on the Number of Computers in Service. Visit Computers Volume in Service Physical Therapy (PT) 9 ,600 Occupational Therapy (OT) 4,000 Speech Therapy (ST) 2,400 Total 16,000 15 Step 1: Set-up a Table of Direct and Indirect Costs Direct Cost Indirect Cost Total Physical Therapy (PT) 615,000 Occupational Therapy (OT) 185,000 Speech Therapy (ST) 105,000 Totals 905,000 Step 2: Determine the indirect costs that should be allocated from Clerical Salaries, Administrative Salaries, and Computer Services to PT, OT, and ST. Clerical Salaries The total indirect costs for Clerical Salaries is: Clerical Salaries will be allocated based on Visit Volume. The Visit Volume for PT, OT, and ST are as follows: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Visit Volume Based on this Visit Volume, the percentage of Visit Volume by department is as follows: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Use the percentage of Visit Volumes to determine the allocation of the Indirect Costs for Clerical Salaries: Total indirect costs for Clerical Salaries: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Indirect Costs Administrative Salaries The total indirect costs for Administrative Salaries is: Administrative Salaries will be allocated based on the proportion (%) of direct costs. The Direct Costs for PT, OT, and ST are as follows: Physical Therapy (PT) Occupational Therapy (OT) speech Therapy (ST Total Visit Volume The proportion of these direct costs is as follows: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Use the proportion of the direct costs to determine the allocation of the Indirect Costs for Administrative Salaries: Total indirect costs for Clerical Salaries: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Indirect Costs Computer Services The total indirect costs for Computer Services is: Computer Services will be allocated based on the number of computers in service. The number of computers in service for PT, OT, and ST are as follows: Physical Therapy (PT) Occupational Therapy (OT) speech Therapy (ST) Total Visit Volume The proportion of computers in service is as follows: Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Use the proportion of the numbers of computers in service to determine the allocation of the Indirect Costs for Computer Services: Total indirect costs for Computer Services: Physical Therapy (PT) Occupational Therapy (OT) speech Therapy (ST) Total Indirect Costs Step 3: Determine to total indirect costs for PT, OT and ST. Based on your calculations in Step 2, you can create a table to determine the total indirect costs for PT, OT, and ST: TOTAL Clerical Administrative Computer INDIRECT Salaries Salaries Services COSTS Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total Indirect Costs Step 4: Add the direct costs by department from Step 1 to the total indirect costs by department from Step 3 to get your total costs. (From Step 1) From Step 3) Direct Cost Indirect Cost Total Costs Physical Therapy (PT) Occupational Therapy (OT) Speech Therapy (ST) Total