Attached is a case study on Palm's hospital traditional project analysis. Can you please answer everything the case study is asking. I do not know how to work on this case study. If you can show the work and explain, I will really appreciate your help.

CASE 19 8/19/09 Instructor Version Copyright 2010 by FACHE PALMS HOSPITAL Traditional Project Analysis This case illustrates a complete capital budgeting analysis, including cash flow analysis, profitability measures, sensitivity analysis, scenario analysis, and, if special add-in software is available, Monte Carlo simulation. Note that the model extends to Column I. The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, all values in the student version INPUT DATA section have been replaced with zeros. Thus, students must determine the appropriate input values and enter them into the model. These cells are colored red. When this is done, any error cells will be corrected and the base case solution will appear. Note that the student version does not contain any risk analyses, so students will have to create their own if required by the case. Furthermore, students must create their own graphics (charts) as needed to present their results. The instructor version of the model contains an inflation table, sensitivity analysis, and scenario analysis, which are not included in the student version. A graph of the sensitivity analysis is contained on the second sheet. INPUT DATA: KEY OUTPUT: Land initial cost Land opportunity cost (and salvage value) Building/equipment cost Build/equipment salvage value Procedures per day Average net revenue per procedure Labor costs Utilities costs Incremental overhead Supply cost ($/procedure) Inflation rate on charges Inflation rate on costs Tax rate Revenues lost from inpatient surgeries Reduction in inpatient surgery costs Cost of capital $150,000 $200,000 $10,000,000 $5,000,000 20 $1,000 $918,000 $50,000 $36,000 $200 3.0% 3.0% 40.0% $1,000,000 $500,000 10.0% NPV IRR MIRR Payback $875,020 12.9% 11.8% 4.1 MODEL-GENERATED DATA: Depreciation Schedule: Year 1 2 3 4 5 6 MACRS Factor 0.20 0.32 0.19 0.12 0.11 0.06 Deprec. Expense $2,000,000 3,200,000 1,900,000 1,200,000 1,100,000 600,000 End of Year Book value $8,000,000 4,800,000 2,900,000 1,700,000 600,000 0 Net Cash Flows: Land opportunity cost Building/equipment cost Net revenues (including inpatient loss) Less: Labor costs Cost savings on inpatients Utilities costs Supplies Incremental overhead Depreciation Income before taxes Taxes Project net income Plus: Depreciation Plus: Net land salvage value Plus: Net building/equipment salvage value Project Cash Flows 0 ($200,000) (10,000,000) 1 2 3 4 5 $4,000,000 918,000 (500,000) 50,000 1,000,000 36,000 2,000,000 $496,000 198,400 $297,600 2,000,000 $4,120,000 945,540 (515,000) 51,500 1,030,000 37,080 3,200,000 ($629,120) (251,648) ($377,472) 3,200,000 $4,243,600 973,906 (530,450) 53,045 1,060,900 38,192 1,900,000 $748,006 299,203 $448,804 1,900,000 $4,370,908 1,003,123 (546,364) 54,636 1,092,727 39,338 1,200,000 $1,527,447 610,979 $916,468 1,200,000 $4,502,035 1,033,217 (562,754) 56,275 1,125,509 40,518 1,100,000 $1,709,270 683,708 $1,025,562 1,100,000 180,000 3,240,000 Net cash flow ($10,200,000) $2,297,600 $2,822,528 $2,348,804 $2,116,468 $5,545,562 Cumulative net cash flow (For payback calculation) ($10,200,000) ($7,902,400) ($5,079,872) ($2,731,068) ($614,600) $4,930,962 Profitability and Breakeven Measures: Net present value (NPV) Internal rate of return (IRR) Modified IRR (MIRR) Payback $875,020 12.9% 11.8% 4.1 Impact of Inflation on NPV: Level of Cost Inflation $875,020 0.0% 3.0% 6.0% 9.0% 12.0% 15.0% 18.0% 0.0% 557,808 366,668 164,458 (49,300) (275,098) (513,435) (764,824) 3.0% 1,066,160 875,020 672,810 459,052 233,254 (5,083) (256,472) Level of Charge Inflation 6.0% 9.0% 12.0% 1,603,952 2,172,459 2,772,984 1,412,812 1,981,318 2,581,843 1,210,602 1,779,109 2,379,633 996,844 1,565,350 2,165,875 771,046 1,339,553 1,940,078 532,709 1,101,216 1,701,740 281,320 849,827 1,450,351 15.0% 3,406,859 3,215,719 3,013,509 2,799,751 2,573,953 2,335,616 2,084,227 18.0% 4,075,447 3,884,307 3,682,097 3,468,339 3,242,541 3,004,204 2,752,815 Sensitivity Analysis: (Note: This table does NOT automatically recalculate when input values are changed.) -30% -20% -10% 0% 10% 20% 30% Number of Avg Revenue Bldg/Equip Procedures per Procedure Salvage Value (2,006,852) (2,727,320) $316,191 (1,046,228) (1,526,540) 502,467 (85,604) (325,760) 688,744 875,020 875,020 875,020 1,835,644 2,075,800 1,061,296 2,796,268 3,276,580 1,247,573 3,756,892 4,477,360 1,433,849 Scenario Analysis: (Note: This section does NOT automatically recalculate when values in the INPUT DATA section are changed. However, the probabilities within the section can be changed and the resulting values will automatically be recalculated.) Case Worst Most Likely Best Probability 25.0% 50.0% 25.0% 100.0% Expected NPV Standard Deviation Coefficient of Variation Number of Procedures 10 20 25 Avg Revenue Bldg/Equip per Procedure Salvage Value $800 $4,000,000 1,000 5,000,000 1,200 6,000,000 NPV ($5,501,433) 875,020 6,651,083 $724,922 $4,299,184 5.9 END SENSITIVITY ANALYSIS 4,000,000 Revenue per Procedure 3,000,000 Number of Procedure s NPV ($) 2,000,000 1,000,000 1,000,000 -30% Bldg / Equip Salvage Value -20% -10% 0% -1,000,000 -2,000,000 0 -3,000,000 Change from Base Case 10% 20% 30%