A Data T U COST ALLOCATION UT AMARO LABORATORIO INGATRONAT re pers 1 100 000 2.000 SO ry 2000 SO Day cute Lace 1...000 1.5 TACT www. . 500 RO 1,000 COT ALLOCATION 24 TOTALCATION 2000 100,000 20 22 20 23 ore C $ 5 5 1.400 I DOO . 2 Tone PSIS 3 CALCULATION 1: 30 cm Cost Allocation Get Allocation Ready A D G N64 fe 30 Cystoscopy $ 350,000 31 Endoscopy 5 300.000 32 Total $ HINT: Should be the same totalas Cell 35 total 33 34 35 CALCULATION 1: 36 Allocate ununes cost to all other departments based on Square Footap 37 38 HINT 11 The total after allocating will be 30 in the UTLINES column in the COST ALLOCATION table abown 39 40 MINT 21 demonstrated What you would do on the edities line (Cell C26when you allocate all costs to the other department 42 Use the below to calculate of each Departments Square Footage of the Tota Sauare Footage 43 Departments Xef SE 44 Administration 45 Laboratory 46 Day Op Suite 47 Cystoscopy 48 Endoscopy 49 Total should be 100% 41 SO 51 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF Cost A8... Page Layout Formulas Home Insert Draw Data Review View Tell me Calibri (Body) V 11 V ab Wrap Text V A YA Paste ilil III HI IM Merge & Center BIU 1 x fx N86 D H 52 CALCULATION 2: 53 Allocate ADMINISTRATION costs to other departments based on direct costs 54 SS MINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) 56 that will need to be used in the table below to get % of each departments Direct Cost. 57 58 HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 59 That will be your new amount to allocate to other departments in the COST ALLOCATION table above 60 61 HINT3: The total after allocating will be so in the ADMINSTRATION Column in the COST ALLOCATION table above. 62 63 Use the below to calculate of each Departments Direct Costs (excluding Administration) 64 Departments Xof Direct Costs 65 Laboratory 66 Day Op Suite 67 Cystoscopy 68 Endoscopy 69 Total should be 100% 70 71 72 CALCULATION 3: 73 Allocate LABORATORY coststo other departments based on Lab Tests 74 75 HINT 1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2 75 that will be your new amount to allocate to other departments in the COST ALLOCATION table above 77 78 HINT 2: The totalaar allocating will be so in the LABORATORY column in the COST ALLOCATION table above 79 80 Use the below to calculate of each Departments Lab Tests 81 Departments of Lab Tests 82 Day Op Suite 83 Cystoscopy 84 Endoscopy BS Total should be 100% 86 87 Cost Allocation Information Cost Allocation Shoots + LytOSCOPY 8 Endoscopy 9 Total Should be 100% 0 1 2 CALCULATION 3: 3 Allocate LABORATORY costs to other departments based on Lab Tests 4 SHINT 1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2. 6 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 7 8 HINT 2: The total after allocating will be SO in the LABORATORY column in the COST ALLOCATION table above. 9 Co Use the below to calculate % of each Departments Lab Tests 1 Departments % of Lab Tests 2 Day Op Suite 3 Cystoscopy 4 Endoscopy 5 Total 6 37 Should be 100% 9 COST ALLOCATIONS 2 UTILITIES based on square footage of each department 3 ADMINISTRATION based on direct costs 4 LABORATORY based on tests FACTS: Total Square Footage is 10,000 Total Direct Expenses are $3,375,000 Total Lab Tests are 5,000 Use the information in the DATA CHART below to perform Calculations 1, 2 and 3. Square Footage Direct Lab Tests 9 Expenses 10 Utilities $ 200,000 11 12 Administration 2,000 500,000 13 Laboratory 2,000 625,000 14 15 Day-op Suite 3,000 1,400,000 4,000 16 Cystoscopy 1,500 17 350,000 500 18 Endoscopy 1,500 500 300,000 19 Total 20 10,000 $3,375,000 5,000 21 22 You will be completing the COST ALLOCATION Table below. 23 24 COST ALLOCATION - EL 00 E31 H Administration Laboratory Total Expenses 20 21 2 You will be completing the COST ALLOCATION Table below. 23 4 COST ALLOCATION 5 Direct Costs Utilities 6 Utilities 5 200,000 $ (200,000) 7 Administration $ 500,000 $ 2,000 8 Laboratory $ 625,000 $ 3,000 9 Day Op Suite $ 1,400,000 $ 3,000 0 Cystoscopy s 350,000 $ 1,500 1 Endoscopy $ 300,000 5 1,500 2 Total 3 HINT: Should be the same totalas Col 35 total CALCULATION 1: 6 Allocate UTILITIES cost to all other departments based on Square Footage 3 HINT 1: The total after allocating will be so in the UTILITIES column in the COST ALLOCATION table above. = = Merge & Center 68 A C E G CALCULATION 1: Allocate UTILITIES cost to all other departments based on Square Footage HINT 1: The total after allocating will be $0 in the UTILITIES column in the COST ALLOCATION table above. HINT 2: I demonstrated what you would do on the Utilities line (Cell C26) when you allocate all costs to the other departments. Use the below to calculate % of each Departments Square Footage of the Total Square Footage Departments % of SF Administration Laboratory Day Op Suite Cystoscopy Endoscopy Total % Should be 100% CALCULATION 2: Allocate ADMINISTRATION costs to other departments based on direct costs HINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) that will need to be used in the table below to get % of each departments Direct Cost. HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 49 Total % Should be 100% 50 51 52 CALCULATION 2: 53 Allocate ADMINISTRATION costs to other departments based on direct costs 54 55 HINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) 56 that will need to be used in the table below to get % of each departments Direct Cost. 57 58 HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 59 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 61 HINT3: The total after allocating will be $0 in the ADMINSTRATION column in the COST ALLOCATION table above. 63 Use the below to calculate of each Departments Direct Costs (excluding Administration) 64 Departments 65 Laboratory 66 Day Op Suite 67 Cystoscopy 68 Endoscopy 69 Total Should be 100% 60 62 Xof Direct Costs 70 55 Laboratory 56 Day Op Suite 57 Cystoscopy 58 Endoscopy 69 Total Should be 100% TO 1 2 CALCULATION 3: "3 Allocate LABORATORY costs to other departments based on Lab Tests 4 -5 HINT1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2. "6 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 7 8 HINT 2: The total after allocating will be $0 in the LABORATORY column in the COST ALLOCATION table above. 9 Use the below to calculate % of each Departments Lab Tests 1 Departments Xof Lab Tests 2 Day Op Suite 3 Cystoscopy 4 Endoscopy 5 Total % Should be 100% 6 7 8 A Data T U COST ALLOCATION UT AMARO LABORATORIO INGATRONAT re pers 1 100 000 2.000 SO ry 2000 SO Day cute Lace 1...000 1.5 TACT www. . 500 RO 1,000 COT ALLOCATION 24 TOTALCATION 2000 100,000 20 22 20 23 ore C $ 5 5 1.400 I DOO . 2 Tone PSIS 3 CALCULATION 1: 30 cm Cost Allocation Get Allocation Ready A D G N64 fe 30 Cystoscopy $ 350,000 31 Endoscopy 5 300.000 32 Total $ HINT: Should be the same totalas Cell 35 total 33 34 35 CALCULATION 1: 36 Allocate ununes cost to all other departments based on Square Footap 37 38 HINT 11 The total after allocating will be 30 in the UTLINES column in the COST ALLOCATION table abown 39 40 MINT 21 demonstrated What you would do on the edities line (Cell C26when you allocate all costs to the other department 42 Use the below to calculate of each Departments Square Footage of the Tota Sauare Footage 43 Departments Xef SE 44 Administration 45 Laboratory 46 Day Op Suite 47 Cystoscopy 48 Endoscopy 49 Total should be 100% 41 SO 51 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF Cost A8... Page Layout Formulas Home Insert Draw Data Review View Tell me Calibri (Body) V 11 V ab Wrap Text V A YA Paste ilil III HI IM Merge & Center BIU 1 x fx N86 D H 52 CALCULATION 2: 53 Allocate ADMINISTRATION costs to other departments based on direct costs 54 SS MINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) 56 that will need to be used in the table below to get % of each departments Direct Cost. 57 58 HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 59 That will be your new amount to allocate to other departments in the COST ALLOCATION table above 60 61 HINT3: The total after allocating will be so in the ADMINSTRATION Column in the COST ALLOCATION table above. 62 63 Use the below to calculate of each Departments Direct Costs (excluding Administration) 64 Departments Xof Direct Costs 65 Laboratory 66 Day Op Suite 67 Cystoscopy 68 Endoscopy 69 Total should be 100% 70 71 72 CALCULATION 3: 73 Allocate LABORATORY coststo other departments based on Lab Tests 74 75 HINT 1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2 75 that will be your new amount to allocate to other departments in the COST ALLOCATION table above 77 78 HINT 2: The totalaar allocating will be so in the LABORATORY column in the COST ALLOCATION table above 79 80 Use the below to calculate of each Departments Lab Tests 81 Departments of Lab Tests 82 Day Op Suite 83 Cystoscopy 84 Endoscopy BS Total should be 100% 86 87 Cost Allocation Information Cost Allocation Shoots + LytOSCOPY 8 Endoscopy 9 Total Should be 100% 0 1 2 CALCULATION 3: 3 Allocate LABORATORY costs to other departments based on Lab Tests 4 SHINT 1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2. 6 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 7 8 HINT 2: The total after allocating will be SO in the LABORATORY column in the COST ALLOCATION table above. 9 Co Use the below to calculate % of each Departments Lab Tests 1 Departments % of Lab Tests 2 Day Op Suite 3 Cystoscopy 4 Endoscopy 5 Total 6 37 Should be 100% 9 COST ALLOCATIONS 2 UTILITIES based on square footage of each department 3 ADMINISTRATION based on direct costs 4 LABORATORY based on tests FACTS: Total Square Footage is 10,000 Total Direct Expenses are $3,375,000 Total Lab Tests are 5,000 Use the information in the DATA CHART below to perform Calculations 1, 2 and 3. Square Footage Direct Lab Tests 9 Expenses 10 Utilities $ 200,000 11 12 Administration 2,000 500,000 13 Laboratory 2,000 625,000 14 15 Day-op Suite 3,000 1,400,000 4,000 16 Cystoscopy 1,500 17 350,000 500 18 Endoscopy 1,500 500 300,000 19 Total 20 10,000 $3,375,000 5,000 21 22 You will be completing the COST ALLOCATION Table below. 23 24 COST ALLOCATION - EL 00 E31 H Administration Laboratory Total Expenses 20 21 2 You will be completing the COST ALLOCATION Table below. 23 4 COST ALLOCATION 5 Direct Costs Utilities 6 Utilities 5 200,000 $ (200,000) 7 Administration $ 500,000 $ 2,000 8 Laboratory $ 625,000 $ 3,000 9 Day Op Suite $ 1,400,000 $ 3,000 0 Cystoscopy s 350,000 $ 1,500 1 Endoscopy $ 300,000 5 1,500 2 Total 3 HINT: Should be the same totalas Col 35 total CALCULATION 1: 6 Allocate UTILITIES cost to all other departments based on Square Footage 3 HINT 1: The total after allocating will be so in the UTILITIES column in the COST ALLOCATION table above. = = Merge & Center 68 A C E G CALCULATION 1: Allocate UTILITIES cost to all other departments based on Square Footage HINT 1: The total after allocating will be $0 in the UTILITIES column in the COST ALLOCATION table above. HINT 2: I demonstrated what you would do on the Utilities line (Cell C26) when you allocate all costs to the other departments. Use the below to calculate % of each Departments Square Footage of the Total Square Footage Departments % of SF Administration Laboratory Day Op Suite Cystoscopy Endoscopy Total % Should be 100% CALCULATION 2: Allocate ADMINISTRATION costs to other departments based on direct costs HINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) that will need to be used in the table below to get % of each departments Direct Cost. HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 49 Total % Should be 100% 50 51 52 CALCULATION 2: 53 Allocate ADMINISTRATION costs to other departments based on direct costs 54 55 HINT 1: You will need to ADD all Direct Costs EXCEPT Administration to get your Total Direct Costs (excluding Administration) 56 that will need to be used in the table below to get % of each departments Direct Cost. 57 58 HINT 2: Your total Adminstration Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1. 59 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 61 HINT3: The total after allocating will be $0 in the ADMINSTRATION column in the COST ALLOCATION table above. 63 Use the below to calculate of each Departments Direct Costs (excluding Administration) 64 Departments 65 Laboratory 66 Day Op Suite 67 Cystoscopy 68 Endoscopy 69 Total Should be 100% 60 62 Xof Direct Costs 70 55 Laboratory 56 Day Op Suite 57 Cystoscopy 58 Endoscopy 69 Total Should be 100% TO 1 2 CALCULATION 3: "3 Allocate LABORATORY costs to other departments based on Lab Tests 4 -5 HINT1: Your total Laboratory Cost has increased from the about shown in the DATA CHART by the amount calculated in Calculation 1 & Calculation 2. "6 That will be your new amount to allocate to other departments in the COST ALLOCATION table above. 7 8 HINT 2: The total after allocating will be $0 in the LABORATORY column in the COST ALLOCATION table above. 9 Use the below to calculate % of each Departments Lab Tests 1 Departments Xof Lab Tests 2 Day Op Suite 3 Cystoscopy 4 Endoscopy 5 Total % Should be 100% 6 7 8