The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, values in the INPUT DATA section of the student spreadsheet have been replaced by zeros. Students must select appropriate input values and enter them into the cells with values colored red. After this is done, any error cells will be corrected and the base case solution will appear. The KEY OUTPUT section includes the most important output from the MODEL-GENERATED DATA section. INPUT DATA: KEY OUTPUT: Dialysis Center Data: Dialysis Ctr $ $ $ $ Facilities allocation ($) Per square foot Gen overhead alloc. Per revenue $ Total indirect costs Outpatient Ctr Total $ 1,900,000 $ 1,900,000 $ 19.00 $ 15.83 2,270,000 2,270,000 11.4% 11.4% $ 4,170,000 $ 4,170,000 #DIV/0! $ $ S Net profit $ $ 3,538,556 '$ 3,538,556 Percentage of revenue #DIV/0! 17.7% 17.7% Revenues: Hemodialysis program Peritoneal program Pharmaceutical supplies Total revenues Direct expenses: Salaries and benefits Pharmaceutical supplies Other supplies Utilities Equipment lease expense Other expenses Total direct expenses Net gain (loss) before indirect expenses Indirect overhead) expenses: Facilities costs General overhead Total overhead costs $ $ S Note that the indirect expense values in Rows 40 and 41 must be changed by hand to reflect any changes to the dollar amounts allocated. $ Net profit $ MODEL-GENERATED DATA: P&L Statements: Revenues/Direct Costs $ Total revenues Direct expenses Contribution margin* Percentage of revenues Without Expansion DC OC $ 2,700,000 $ 16,000,000 2,100,000 9,833,155 $ 600,000 $ 6,166,845 22.2% 38.5% Initial Allocation DC OC $ 2,700,000 $20,000,000 2,100,000 12,291,444 $ 600,000 $ 7,708,556 22.2% 38.5% Final Allocation DC OC $ 20,000,000 0 12,291,444 $ 7,708,556 #DIV/0! 38.5% Indirect costs $ $ $ Facilities costs General overhead Total overhead 300,000 $ 1,200,000 270,000 1,600,000 570,000 $ 2,800,000 400,000 $ 1,500,000 270,000 2,000,000 670,000 $ 3,500,000 $ 1,900,000 2,270,000 $ 4,170,000 $ $ GA $ $ Net profit Percentage of revenues 30,000 $3,366,845 1.1% 21.0% (70,000) -2.6% 4,208,556 21.0% $ 3,538,556 17.7% #DIV/0! Facilities Cost Allocation: 20,000 Square footage Cost per square foot 20,000 15.00 $ 80,000 15.00 20,000 20.00 $ 100,000 15.00 100,000 19.00 $ $ $ $ Other Overhead Allocation: General overhead as a percentage of revenues 10.0% 10.0% 10.0% 10.0% #DIV/0! 11.4% *Note: The term contribution margin as used here means the amount available to cover overhead costs as opposed to the The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, values in the INPUT DATA section of the student spreadsheet have been replaced by zeros. Students must select appropriate input values and enter them into the cells with values colored red. After this is done, any error cells will be corrected and the base case solution will appear. The KEY OUTPUT section includes the most important output from the MODEL-GENERATED DATA section. INPUT DATA: KEY OUTPUT: Dialysis Center Data: Dialysis Ctr $ $ $ $ Facilities allocation ($) Per square foot Gen overhead alloc. Per revenue $ Total indirect costs Outpatient Ctr Total $ 1,900,000 $ 1,900,000 $ 19.00 $ 15.83 2,270,000 2,270,000 11.4% 11.4% $ 4,170,000 $ 4,170,000 #DIV/0! $ $ S Net profit $ $ 3,538,556 '$ 3,538,556 Percentage of revenue #DIV/0! 17.7% 17.7% Revenues: Hemodialysis program Peritoneal program Pharmaceutical supplies Total revenues Direct expenses: Salaries and benefits Pharmaceutical supplies Other supplies Utilities Equipment lease expense Other expenses Total direct expenses Net gain (loss) before indirect expenses Indirect overhead) expenses: Facilities costs General overhead Total overhead costs $ $ S Note that the indirect expense values in Rows 40 and 41 must be changed by hand to reflect any changes to the dollar amounts allocated. $ Net profit $ MODEL-GENERATED DATA: P&L Statements: Revenues/Direct Costs $ Total revenues Direct expenses Contribution margin* Percentage of revenues Without Expansion DC OC $ 2,700,000 $ 16,000,000 2,100,000 9,833,155 $ 600,000 $ 6,166,845 22.2% 38.5% Initial Allocation DC OC $ 2,700,000 $20,000,000 2,100,000 12,291,444 $ 600,000 $ 7,708,556 22.2% 38.5% Final Allocation DC OC $ 20,000,000 0 12,291,444 $ 7,708,556 #DIV/0! 38.5% Indirect costs $ $ $ Facilities costs General overhead Total overhead 300,000 $ 1,200,000 270,000 1,600,000 570,000 $ 2,800,000 400,000 $ 1,500,000 270,000 2,000,000 670,000 $ 3,500,000 $ 1,900,000 2,270,000 $ 4,170,000 $ $ GA $ $ Net profit Percentage of revenues 30,000 $3,366,845 1.1% 21.0% (70,000) -2.6% 4,208,556 21.0% $ 3,538,556 17.7% #DIV/0! Facilities Cost Allocation: 20,000 Square footage Cost per square foot 20,000 15.00 $ 80,000 15.00 20,000 20.00 $ 100,000 15.00 100,000 19.00 $ $ $ $ Other Overhead Allocation: General overhead as a percentage of revenues 10.0% 10.0% 10.0% 10.0% #DIV/0! 11.4% *Note: The term contribution margin as used here means the amount available to cover overhead costs as opposed to the