Can you answer 5 (a) in attached document? I don't think my answer is correct. Please explain how you got the answer in excel, please and thank you.

CORAL BAY HOSPITAL TRADITIONAL PROJECT ANALYSIS The cost of the land The cost of buidling & equipment Number of years for which depreciation should charge Depreciation First Year (20%) Depreciation Second Year (32%) Depreciation Third Year (19.20%) Depreciation Fourth Year (11.52%) Depreciation Fifth Year (11.52%) Salvage Value Expected procedures per day Average net patient revenue Number of days operation The labor costs Increase in the cash overhead expenses Cost of supplies including anesthetics in each procedure The hospital's marginal federal-plus-tax rate Percentage increase in net patient revenue Percentage increase in costs Revenues lost from inpatient surgeries Reduction in annual cash expenses Cost of capital Risk Rate Effective Discount Rate Year Revenue Less: Labor Cost to run the surgery Less: Cost of supplies Less: Depreciation Profit Before Tax Tax @40% Profit After Tax Add: Salvage Value Tax @40% Salvage Value Net Of Tax Siphoned off Cash Revenues towards this project Reduction In Annual Cash Expenses 0 $ $ $ $ $ $ $ 1 5,000,000 918,000 1,000,000 2,000,000 1,082,000 432,800 649,200 $ $ 1,000,000 500,000 Effective Cash Flows Curmulated CashFlows Level Of Cost Inflation 0% $ 1% $ 2% $ 3% $ 4% $ 5% $ 6% $ 4. (a) $ $ 0% 5,693,050 5,623,667 5,552,985 5,480,984 5,407,646 5,332,951 5,256,881 $ $ $ $ $ $ $ (10,200,000) $ 4,149,200 (10,200,000) $ (6,050,800) Level Of Net Patient Revenue Inflation 1% 2% 5,964,358 $ 6,240,749 5,894,975 $ 6,171,367 5,824,293 $ 6,100,684 5,752,292 $ 6,028,683 5,678,954 $ 5,955,345 5,604,259 $ 5,880,650 5,528,189 $ 5,804,580 Perform a scenario analysis. Scenario - 1 Assuming, the number of procedures per day Assuming, average revnue per patient Assuming, Building Salvage Net Present Value = = = = 10 $ 800 $4,000,000 $ 572,572 Scenario - 2 Assuming, the number of procedures per day Assuming, average revnue per patient Assuming, Building Salvage Net Present Value = = = = 25 $ 1,200 $6,000,000 $ 11,505,285 5 (a) A board member is interested in the utilization breakeven of the Center. What are the breakeven values of the three input variables that are highly uncertain? Ans Breakeven Values of the Procedures per day Breakeven Values of the net revenue per procedure Breakeven Values of the number of days to be operated in the year = = = 6 (a) To help with the risk-incorporation phase of the analysis, Jules consulted with Mark Hauser, the hospital's how the hospital typically adjusts for risk. What is the project's differential risk-adjusted NPV? Ans The risk adjusted NPV = $ 1,864,089 ( $ 8,174,319.42 - $ 6,310,230.33) In a conservative view , the cost of capital though is 10%, we have added the risk factor or 4% and used the discount rate as 14% in order to discount the cashflows. The risk adjusted NPV the difference between NPV at 14% and NPV at 10%. RAL BAY HOSPITAL ROJECT ANALYSIS = = = = = = = = $ $ $ $ $ $ = = = = = = = = = = = = = = = $ $ $ $ $ $ $ 2 5,150,000 945,540 1,030,000 3,200,000 (25,540) (10,216) (15,324) $ $ 1,030,000 $ 515,000 $ $ $ $ $ $ $ $ 3 5,304,500 973,906 1,060,900 1,920,000 1,349,694 539,878 809,816 $ $ $ $ $ $ $ 1,060,900 $ 530,450 $ $ $ $ $ $ 4 5,463,635 1,003,123 1,092,727 1,152,000 2,215,785 886,314 1,329,471 $ $ $ $ $ $ $ $ $ $ 1,092,727 $ 546,364 $ $200,000 10,000,000 5 Year As per MACRS 2,000,000 3,200,000 1,920,000 1,152,000 1,152,000 $5,000,000 20 1,000 250 918,000 $25,000 200 40% 3% 3% 1,000,000 500,000 10% 4% 14% 5 5,627,544 1,033,217 1,125,509 1,152,000 2,316,818 926,727 1,390,091 5,000,000 1,769,600 3,230,400 1,125,509 562,754 $ 4,729,676 $ $ (1,321,124) $ et Patient Revenue Inflation 3% $ 6,522,296 $ $ 6,452,914 $ $ 6,382,231 $ $ 6,310,230 $ $ 6,236,892 $ $ 6,162,197 $ $ 6,086,127 $ 4,321,166 $ 3,000,042 $ 4% 6,809,073 6,739,690 6,669,008 6,597,007 6,523,669 6,448,974 6,372,904 $ $ $ $ $ $ $ 4,120,561 $ 7,120,604 $ 5% 7,101,153 7,031,771 6,961,088 6,889,087 6,815,749 6,741,054 6,664,984 7,460,754 14,581,358 6% $ $ $ $ $ $ $ 7,398,611 7,329,229 7,258,546 7,186,545 7,113,207 7,038,512 6,962,442 6 418.53 68 Days with Mark Hauser, the hospital's CFO, about both the risk inherent in the hospital's average project and 8,174,319.42 - $ 6,310,230.33) hflows. The risk adjusted NPV is