Step 1. You are the CFO of DCI. You have been working with your team to determine the financial impact of possibly closing a branch, Dialysis Care of Tilden City (DCTC), on DCI (the parent). Your sharpest team of analysts has created a financial model (below) to predict this impact.
Step 2. Before proceeding, please study the model below to make sure you are familiar with both its form and content. At the macrolevel, make sure you understand how the various boxes (assumption, calculations, and tables) relate to each other. At the microlevel: 1) Make sure in the calculations table you understand where the numbers in each cell come from, and 2) Make sure in the assumptions and table boxes where the numbers are used. To help you do this, use the Formula auditing tool under "Formulas" and click on "trace precedents" and/or "trace dependents".
Step 3. You are giving a presentation to DCI's Board in the morning. In preparing for this presentation you are to answer a set of a three expected questions (located on tabs Q1, Q2, and Q3) . To answer these questions, you are to manipulate the original model on the following tabs. For each question, provide your answer or refer to your calculated values as appropriate
|
|
|
Please answer all YELLOW CELLS. See attached spreadsheet for assignment.
DCI_Instructions Dialysis Care, Inc. - Differential Cost Accounting Instructions Step 1. You are the CFO of DCI. You have been working with your team to determine the financial impact of possibly closing a branch, Dialysis Care of Tilden City (DCTC), on DCI (the parent). Your sharpest team of analysts has created a financial model (below) to predict this impact. Step 2. Before proceeding, please study the model below to make sure you are familiar with both its form and content. At the macrolevel, make sure you understand how the various boxes (assumption, calculations, and tables) relate to each other. At the microlevel: 1) Make sure in the calculations table you understand where the numbers in each cell come from, and 2) Make sure in the assumptions and table boxes where the numbers are used. To help you do this, use the Formula auditing tool under "Formulas" and click on "trace precedents" and/or "trace dependents". Step 3. You are giving a presentation to DCI's Board in the morning. In preparing for this presentation you are to answer a set of a three expected questions (located on tabs Q1, Q2, and Q3) . To answer these questions, you are to manipulate the original model on the following tabs. For each question, provide your answer or refer to your calculated values as appropriate Where we go from here: Case Presenters (CFO of DCI) and Respondents (DCI Board Members): After preparing your analysis of financial impact of possibly closing the DCTC branch, you will record a presentation with your recommendation of rate structure, including both advantages and disadvantages. Presenters: You are presenting your responses to the three questions and whether you believe the DCTC branch should be closed. Record a 2-4 minute discussion of your presentation and analysis. Respondents: You are the asking questions or seeking clarification on points discussed in the CFO's presentation. You can respond with some of your own conclusions and analysis, and you also should pose 2-3 questions in response to the presenter's analysis. Financial model to determine financial impact of DCTC on DCI Assumptions A1. Treatments at capacity3,824(Given)A7. Avoidable SalariesSee Table 4, col. 4E. A2. Charges per treatment$ 230.00(Given) A8. Unavoidable SalariesSee Table 4, col. 4F. A3. Percent of charges collected100%(Assumption)A9. Number of EmployeesSee Table 4, col. 4A. A4. Variable cost per treatment$ 70.13(Table 2, col 2A)A10. Project life5(Assumption) A5. Avoid. non-salary operating fixed costs37,674(Table 2, col 2B)A11. Discount Rate10%(Assumption) A6 Unavoid. non-salary fixed operating costs$0(Table 2, col 2C)A12. Indirect costs allocated from central office.210,320(Given) A13. % of A12 which would be saved by DCI if DCTC closed down.15%(Given) Calculations BCDEFF'GHIJKL PatientsPercentTreatmentsTotal Total ContributionAvoidableNon-SalaryAvoidableProductUnavoidableNet CapacityRevenuesVariable CostMarginSalariesDirect CostAllocated CostMarginAllocated CostsIncome [Note 1][Note 2][A1*C][A2*A3*D][A4*D][E-F]Note 3[Note 4][A12*A13][D-E-F-G-H-I][A12-I][J-K] 24100%3,824879,520268,174611,346436,80037,67431,548105,324178,772(73,448) 2395%3,633835,544254,765580,779436,80037,67431,54874,757178,772(104,015) 2290%3,442791,568241,357550,211436,80037,67431,54844,189178,772(134,583) 2085%3,250747,592227,948519,644436,80037,67431,54813,622178,772(165,150) 1980%3,059703,616214,539489,077436,80037,67431,548(16,945)178,772(195,717) 1875%2,868659,640201,131458,510436,80037,67431,548(47,513)178,772(226,285) 1770%2,677615,664187,722427,942436,80037,67431,548(78,080)178,772(256,852) 1565%2,486571,688174,313397,375311,20037,67431,54816,953178,772(161,819) 1460%2,294527,712160,904366,808311,20037,67431,548(13,614)178,772(192,386) 1355%2,103483,736147,496336,240311,20037,67431,548(44,182)178,772(222,954) 1250%1,912439,760134,087305,673311,20037,67431,548(74,749)178,772(253,521) 1145%1,721395,784120,678275,106311,20037,67431,548(105,316)178,772(284,088) 940%1,530351,808107,270244,538311,20037,67431,548(135,884)178,772(314,656) 835%1,338307,83293,861213,971185,60037,67431,548(40,851)178,772(219,623) 730%1,147263,85680,452183,404185,60037,67431,548(71,418)178,772(250,190) 625%956219,88067,044152,837185,60037,67431,548(101,986)178,772(280,758) 420%765175,90453,635122,269185,60037,67431,548(132,553)178,772(311,325) 315%574131,92840,22691,702185,60037,67431,548(163,120)178,772(341,892) 210%38287,95226,81761,135185,60037,67431,548(193,687)178,772(372,459) 15%19143,97613,40930,567185,60037,67431,548(224,255)178,772(403,027) 00%0000185,60037,67431,548(254,822)178,772(433,594) Note 1. Approximate number of FT patients based on 3 treatments per week, 52 weeks per year. Formula = Trunc( D/(3 *52). Note 2. Selected percentages at which DCTC might operate. Note 3. From Table 4. Note 4. Avoidable non-salary fixed costs from Table 2, col 2B. Table 2. Assumed Classification of Non-Salary Direct Cost Table 4. Classification of Salaries by Capacity Level and Avoidability 2A2B2CNumber1Cost1 VariableAvoidableUnavoidable> 70% Capacity CostsFixed CostsFixed Costs 4 A4 B4 C4 D Medical supplies$245,488TotalAvoidablePer EmployeeTotal Lab services12,238(Given)(Assumed)(Table 3, Col. 3D)(4 A * 4 C) Salaries & wages(See table 4.)Nurses6100%40,833$ 245,000 Employee Benefits(See table 4.)Techs.3100%23,000$ 69,000 Water usage10,448Adm.1100%50,000$ 50,000 Minor equipment rental25,678Benefits172,800 Insurance11,996Total$ 436,800 Total $268,174$37,674Between 70 and 40 % Capacity Volume VC incurred3,824Nurses4100%40,833$ 163,333 VC per treatment70.13Techs.2100%23,000$ 46,000 Adm.1100%50,000$ 50,000 Table 3. Calculation of Cost Per Employee and Benefit %Benefits151,867 3A3B3C3DTotal311,200 Cost Per Below 40% Capacity No,PositionCostEmployeeNurses2100%40,833$ 81,667 {Given](Given)(Given)[3C / 3A)Techs.1100%23,000$ 23,000 6Nurses$ 245,000$ 40,833Adm.1100%50,000$ 50,000 3Techs.$ 69,000$ 23,000Benefits130,933 1Administrator$ 50,000$ 50,000Total185,600 Total Salaries$ 364,0001 Benefits are calculated as a percent of salaries. Percentage is calculated in Table 3, col. D as:20.00% Total Benefits$ 72,800 Sal. & Benefits$ 436,800 % Benefits (Total Benefits / Total Salaries)20.00% &"Helv,Bold"&1DCI Case Analysis&"Helv,Regular"&8 DCI_Q1 Dialysis Care, Inc. - Differential Cost Accounting Instructions and Question Using the DCTC financial model, answer the follow expected question for the DCI Board meeting. Enter your answer in the yellow cell below. Expected Question 1 1. If nothing changes from last year and DCTC (the branch) would continue to operate as per last year, which of the following best describes the facts and the best action DCI (the parent) should take according to the model, all else being equal? a. DCTC will cover its own costs, but not cover its “fair” share of other costs. Thus, DCI should drop DCTC and DCI will be better off financially. b. DCTC will cover its own costs, but not cover its “fair” share of other costs. Thus, DCI should keep DCTC and it will be better off financially. c. DCTC will cover its “fair” share of other costs, but not cover its own costs. DCI should drop DCTC and DCI will be better off financially. d. DCTC will cover its “fair” share of other costs, but not cover its own costs. DCI should keep DCTC and DCI will be better off financially. Answer: Explanation of how you arrived at your answer: Financial model to determine financial impact of DCTC on DCI Assumptions A1. Treatments at capacity3,824(Given)A7. Project life5(Assumption) A2. Charges per treatment$ 230.00(Given) A8. Discount Rate10%(Assumption) A3. Percent of charges collected100%(Assumption)A9. Indirect costs allocated from central office.210,320(Given) A4. Variable cost per treatment$ 70.13(Table 2, col 2A)A10. % of A9 which would be saved by DCI if DCTC closed down.15%(Given) A5. Avoid. non-salary operating fixed costs37,674(Table 2, col 2B) A6 Unavoid. non-salary fixed operating costs$0(Table 2, col 2C) Calculations BCDEFF'GHIJKL PatientsPercentTreatmentsTotal Total ContributionAvoidableNon-SalaryAvoidableProductUnavoidableNet CapacityRevenuesVariableMarginSalariesDirect CostAllocated CostMarginAllocated CostsIncome [Note 1][Note 2][A1*C][A2*A3*D][A4*D][E-F]Note 3[Note 4][A12*A13][D-E-F-G-H-I][A12-I][J-K] 24100%3,824879,520268,174611,346436,80037,67431,548105,324178,772(73,448) 2395%3,633835,544254,765580,779436,80037,67431,54874,757178,772(104,015) 2290%3,442791,568241,357550,211436,80037,67431,54844,189178,772(134,583) 2085%3,250747,592227,948519,644436,80037,67431,54813,622178,772(165,150) 1980%3,059703,616214,539489,077436,80037,67431,548(16,945)178,772(195,717) 1875%2,868659,640201,131458,510436,80037,67431,548(47,513)178,772(226,285) 1770%2,677615,664187,722427,942436,80037,67431,548(78,080)178,772(256,852) 1565%2,486571,688174,313397,375311,20037,67431,54816,953178,772(161,819) 1460%2,294527,712160,904366,808311,20037,67431,548(13,614)178,772(192,386) 1355%2,103483,736147,496336,240311,20037,67431,548(44,182)178,772(222,954) 1250%1,912439,760134,087305,673311,20037,67431,548(74,749)178,772(253,521) 1145%1,721395,784120,678275,106311,20037,67431,548(105,316)178,772(284,088) 940%1,530351,808107,270244,538311,20037,67431,548(135,884)178,772(314,656) 835%1,338307,83293,861213,971185,60037,67431,548(40,851)178,772(219,623) 730%1,147263,85680,452183,404185,60037,67431,548(71,418)178,772(250,190) 625%956219,88067,044152,837185,60037,67431,548(101,986)178,772(280,758) 420%765175,90453,635122,269185,60037,67431,548(132,553)178,772(311,325) 315%574131,92840,22691,702185,60037,67431,548(163,120)178,772(341,892) 210%38287,95226,81761,135185,60037,67431,548(193,687)178,772(372,459) 15%19143,97613,40930,567185,60037,67431,548(224,255)178,772(403,027) 00%0000185,60037,67431,548(254,822)178,772(433,594) Note 1. Approximate number of FT patients based on 3 treatments per week, 52 weeks per year. Formula = Trunc( D/(3 *52). Note 2. Selected percentages at which DCTC might operate. Note 3. From Table 4. Note 4. Avoidable non-salary fixed costs from Table 2, col 2B. Table 2. Assumed Classification of Non-Salary Direct Cost Table 4. Classification of Salaries by Capacity Level and Avoidability 2A2B2CNumber1Cost1 VariableAvoidableUnavoidable> 70% Capacity CostsFixed CostsFixed Costs 4 A4 B4 C4 D Medical supplies$245,488TotalAvoidablePer EmployeeTotal Lab services12,238(Given)(Assumed)(Table 3, Col. 3D)(4 A * 4 C) Salaries & wages(See table 4.)Nurses6100%40,833$ 245,000 Employee Benefits(See table 4.)Techs.3100%23,000$ 69,000 Water usage10,448Adm.1100%50,000$ 50,000 Minor equipment rental25,678Benefits172,800 Insurance11,996Total$ 436,800 Total $268,174$37,674Between 70 and 40 % Capacity Volume VC incurred3,824Nurses4100%40,833$ 163,333 VC per treatment70.13Techs.2100%23,000$ 46,000 Adm.1100%50,000$ 50,000 Table 3. Calculation of Cost Per Employee and Benefit %Benefits151,867 3A3B3C3DTotal311,200 Cost Per Below 40% Capacity No,PositionCostEmployeeNurses2100%40,833$ 81,667 {Given](Given)(Given)[3C / 3A)Techs.1100%23,000$ 23,000 6Nurses$ 245,000$ 40,833Adm.1100%50,000$ 50,000 3Techs.$ 69,000$ 23,000Benefits130,933 1Administrator$ 50,000$ 50,000Total185,600 Total Salaries$ 364,0001 Benefits are calculated as a percent of salaries. Percentage is calculated in Table 3, col. D as:20.00% Total Benefits$ 72,800 Sal. & Benefits$ 436,800 % Benefits (Total Benefits / Total Salaries)20.00% &"Helv,Bold"&1DCI Case Analysis&"Helv,Regular"&8 DCI_Q2 Dialysis Care, Inc. - Differential Cost Accounting Instructions and Question Using the DCTC financial model