I need solutions for the questions in this Capital Budgeting assignment. The question tabs (Q1,Q2,Q3) are at the bottom on the excel file. Thanks

Week 8 Capital Budgeting II Assignment Discussion of Capital Budgeting continues from W7 using a comprehensive capital budgeting case. Studying the W7 Guideline Solutions is VITAL before proceeding with W8. Learning Objectives - Reinforce and extend understanding of the capital budgeting process using a realistic case - Apply sensitivity analysis to the capital budgeting base case results as a risk analysis technique - Step into the shoes of a financial analyst and/or her boss to know their roles and responsibilities READ: VIDEOS: CASE: CFW Chapter 5, review as necessary CFW Chapter 4, if needed to review Time Value of Money Review W7 videos as necessary UVa Long Term Acute Care Hospital Project Questions 1 See Q1 tab.Scroll down until you see the questions. 2 See Q2 tab.Scroll down until you see the questions. 3 See Q3 tab.Scroll down until you see the questions. Capital Budgeting Template K-wacc Sensitivity Analysis Q1 HAS THE INPUT DATA FILLED IN FOR YOU TO INTERPRET. apital budgeting case. realistic case nalysis technique nd responsibilities eting Template INCOME STATEMENT Revenue Cost of sales Gross profit Other operating income Other operating expenses Total cost and expenses Operating profit (EBIT) Interest, finance costs Profit before tax Income tax Net profit after tax Dividends Reinvested in the business BALANCE SHEET ASSETS LIABILITIES AND EQUITY Current assets Current liabilities Cash Trade payables Investments Other accruals Trade receivables Tax liabilities Inventories Short-term loans, leases Non-current assets Non-current liabilities Property, plant & equipment Loans, debt, leases due after 1 year Investment property Retirement benefit obligation Goodwill Deferred tax liabilities Total non-current liabilities WORKING CAPITAL spontaneous change with revenue ?what levels of ca, cl, s-t loans? CAPITAL BUDGETING ?what projects to accept? FINANCING ?what is the debt capacity? COST OF DEBT K-WACC Stockholder's equity (Net worth) Preferred stock Common stock Additional paid-in-capital Retained earnings OPERATING LEVERAGE FINANCIAL LEVERAGE Total assets Total liabilities & equity COST OF EQUITY VALUATION CASH FLOW COST OF CAPITAL ANALYSIS STEPS: 1-HISTORICAL RATIOS FINANCING 2-K-WACC HISTORICAL RATIOS I/S & B/S FORECAST 3-CAPITAL BUDGETING 4-FORECAST & EFN 5-EQUITY VALUATION 6-FINANCING LONG-FORM FORECAST I/S, B/S, & RATIOS CAPITAL BUDGETING OP & CAP NATCF, NPV, IRR, PAYBACK VALUATION ENTERPRISE VALUE USING FREE CASH FLOW MARKET MULTIPLES: P/E, MV/BV, REV, EBIT EFN DEBT EQUITY DEBT EQUITY EBIT CHART income K-WACC risk control mktblty flexblty timing UNIVERSITY OF VIRGINIA MEDICAL CENTER Long Term Acute Care Hospital Free Cash Flow Projections Revenue and Cost Assumptions Number of Beds Year 1 Utilization Year 2 Utilization Annual Increase in Utilization Operating Expense (% of Revenue) K-wacc 50 26% 60% 4% 7.0% 10% Results-No NWC Recov $5,687 NPV 17.6% IRR Year VOLUME Patient Day Capacity Utilization Patient Days Used Average Patient Census per Day Average Length of Stay Number of Patients per Year Full-Time Employees/Census Full-Time Employees INSURANCE PAYER Medicare Medicaid Commercial Payers Other Indigent 1 18,250 26% 4,745 13 30 158 4.8 62 Patient Mix 36% 29% 24% 9% 2% Billing Medicarebill per patient $27,795 Medicaidbill per patient $35,000 Commercial Payersbill per day $2,800 Otherbill per patient $38,500 Indigentbill per patient $35,000 Total Revenue (000 omitted) Less Uncollectable 1% Total Net Revenue (000 omitted) EXPENSES Salary, Wage, Benefits (based on $ per employe QUESTIONS START AT ROW 88 $60,250 57 46 38 14 3 158 Annual Incr 0.0% 1.3% 5.0% 1.3% 1.3% Annual Incr 3% 1,583 1,605 3,189 548 111 7,035 70 6,965 3,760 Supplies, Drugs, Food (% net revenue) Management Fees (% net rev) Operating Expenses (fixed + 7 % net rev) Land Lease per year 16.3% 8% 1,135 557 $1,200,000 $200,000 NA 3% 1,688 200 Depreciation (straight line 30yrs) $15,000,000 Total Expenses (000 omitted) 500 7,840 Total Expenses 7,840 Operating Profit Operating Margin Net Working Capital Accounts Receivable Inventory Supplies, Drugs, Food Accounts Payable Net Working Capital Change in NWC Free Cash Flows Calculation Operating Profit Add Depreciation Less Capital Expenditures Less Increase in Net Working Capital Free Cash Flows NPV (no recovery in year 10) IRR (no recovery in year 10) (804) -11.4% Notes: 30 days 60 days 30 days 572 187 93 666 666 (7,500) (000 omitted) (7,500) $5,687 (000 ommited) 17.6% Year NWC Recovery Sale of Facility at Book Value NPV with Year 10 Recovery IRR with Year 10 Recovery Net Profit (Operating Profit - Interest) Net Profit/Net Revenue (804) 500 (7,500) (666) (8,470) $10,425 (000 ommi 21.2% (7,500) (000 ommited) 1 0 0 (8,470) (2,004) -28.8% Study the above analysis carefully, examining the inputs, outputs, and formulas used to do the calcul Q1a Mulroney did not use working capital cash flows in her original analysis. The analysis above includes incremental investment in working capital. Discuss why she was either correct or incorrect not to include them. Q1b Compare the decision metrics NPV & IRR for the "no recovery of NWC" and "recovery of NWC" scenarios, stating which scenario best captures reality. Based on your answer, give the project a green or red light. Q1c Examine the decision metric 'profit margin', and explain if it leads to a green or red light for this project. Even though the board of directors uses this metric, it is defective. Explain why. HINT: FCF definition. Q1d Reconcile your answers to Q1b and Q1c. QUESTIONS START AT ROW 88 Results-NWC Recovery $10,425 (000 ommited) NPV 21.2% IRR 2 3 4 5 6 7 8 9 18,250 60% 10,950 30 27 406 3.5 105 18,250 62% 11,388 31 27 422 3.5 109 18,250 65% 11,844 32 27 439 3.5 114 18,250 67% 12,317 34 27 456 3.5 118 18,250 70% 12,810 35 27 474 3.5 123 18,250 73% 13,322 36 27 493 3.5 128 18,250 76% 13,855 38 27 513 3.5 133 18,250 79% 14,409 39 27 534 3.5 138 146 118 97 37 8 406 152 122 101 38 8 422 158 127 105 39 9 439 164 132 109 41 9 456 171 138 114 43 9 474 178 143 118 44 10 493 185 149 123 46 10 513 192 155 128 48 11 534 4,058 4,170 7,726 1,424 288 17,665 177 17,489 4,220 4,337 8,035 1,480 299 18,372 184 18,188 4,389 4,510 8,357 1,540 311 19,107 191 18,916 4,565 4,691 8,691 1,601 323 19,871 199 19,672 4,747 4,878 9,039 1,665 336 20,666 207 20,459 4,937 5,073 9,400 1,732 350 21,493 215 21,278 5,135 5,276 9,776 1,801 364 22,352 224 22,129 5,340 5,487 10,167 1,873 378 23,246 232 23,014 6,516 6,980 7,477 8,009 8,580 9,190 9,845 10,546 2,851 1,399 2,965 1,455 3,083 1,513 3,207 1,574 3,335 1,637 3,468 1,702 3,607 1,770 3,751 1,841 2,424 206 2,473 212 2,524 219 2,577 225 2,632 232 2,689 239 2,749 246 2,811 253 500 13,896 500 14,585 500 15,316 500 16,092 500 16,915 500 17,789 500 18,717 500 19,702 13,896 14,585 15,316 16,092 16,915 17,789 18,717 19,702 3,769 21.3% 3,787 20.6% 3,791 19.8% 3,779 19.0% 3,751 18.1% 3,703 17.2% 3,635 16.3% 3,544 15.2% 1,437 469 234 1,672 1,006 1,495 487 244 1,739 67 1,555 507 253 1,808 70 1,617 527 264 1,880 72 1,682 548 274 1,956 75 1,749 570 285 2,034 78 1,819 593 296 2,115 81 1,892 617 308 2,200 85 3,769 500 0 (1,006) 3,263 3,787 500 0 (67) 4,220 3,791 500 0 (70) 4,221 3,779 500 0 (72) 4,207 3,751 500 0 (75) 4,176 3,703 500 0 (78) 4,125 3,635 500 0 (81) 4,054 3,544 500 0 (85) 3,959 2 0 0 3 0 0 4 0 0 5 0 0 6 0 0 7 0 0 8 0 0 9 0 0 3,263 4,220 4,221 4,207 4,176 4,125 4,054 3,959 2,569 14.7% 2,587 14.2% 2,591 13.7% 2,579 13.1% 2,551 12.5% 2,503 11.8% 2,435 11.0% 2,344 10.2% formulas used to do the calculations. analysis above her correct or incorrect not to "recovery of NWC" scenarios, project a green or red light. or red light for this project. why. HINT: FCF definition. 10 18,250 82% 14,986 41 27 555 3.5 144 200 161 133 50 11 555 5,554 5,707 10,574 1,948 394 24,176 242 23,935 11,297 3,901 1,915 2,875 261 500 20,749 20,749 3,427 14.2% 1,967 641 321 2,288 88 3,427 500 0 (88) 3,839 10 $2,288 $10,000 16,127 2,227 9.3% Q2 A 1 B C D COMPUTE WEIGHTED AVERAGE COST OF CAPITAL E F G H I J K L M QUESTIONS START AT ROW 32 2 3 4 5 6 7 8 BASIC: COST OF DEBT: Coupon Rate Marginal Tax Rate Cost of Debt weight of debt Formula 0.00% given 0.0% given 0.00% b5*(1-b6) 0% Equation Case Exhibit 4 U.S. Treasury Yields 1-year 4.77% 5-year 4.72% 10-year 4.72% 30-year 4.73% Data source: http://federalreserve.gov/releases/h15/data.htm (accessed March 2006). k-d = I x (1- t) d d+e 9 10 COST OF EQUITY: 11 Risk-Free Rate 12 13 14 15 16 0.00% given 0.00% given 0.00 given 0.00% b11+(b13*b12) 100% 1-b8 Risk Premium Beta Cost of Equity weight of equity 17 Weighted-Average Cost of Capital 18 19 Corporate Bond Yields AAA 5.31% AA 5.38% R-m - R-f k-e = R-f + [ x (R-m - R-f)] e d+e 0.00% (b8*b7)+(b15*b14) (k-d x wt-d)+(k-e x wt-e) 20 21 For-Profit Comparables 22 23 Revenues (millions) 24 Assets (millions) 25 Total debt (millions) 26 Stock price ($/share) 27 Shares outstanding (millions) 28 Market cap (millions) 29 Bond rating 30 Beta HCA Inc ### $5,222 $9,278 $52.12 ### ### A 0.60 Community Health $3,720 $961 $1,810 $39.73 88.5 $3,517 B 0.60 Health Management Associates $3,580 $997 $1,014 $23.25 247.2 $5,747 BB 0.70 A+ A A- 5.41% 5.45% 5.53% BBB+ BBB BBB- 5.62% 5.88% 6.07% BB+ BB BB- 6.40% 6.79% 6.96% B+ 7.39% B 7.57% B7.84% Data source: Bloomberg, \"Fair Market Curve Analysis,\" 10-Year Corporate Bonds, March 2, 2006. 31 32 33 34 35 36 Q2a Calculate the k-wacc for HCA using the template above. Enter the data that you have in the case and the table above. If you need additional data, assume it using your good judgment from what you have learned so far in the course. In the answer box, cite your result, compare it to the k-wacc used in the Q1 analysis, and explain what makes it either higher or lower. 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Q2b If LTAC was a project in a for-profit hospital like HCA above, would its NPV be higher or lower? Explain 'analytically' by examining all relevant inputs to the NPV calculation. 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Q2c If LTAC was a project in a for-profit hospital like HCA above, would the IRR be higher or lower? Explain. HINT: To avoid getting trapped by this question, make sure your answer is 'analytical', i.e., examine all relevant inputs and output to the IRR calculation. 65 66 67 68 69 70 71 72 73 74 75 76 Q2d Can a non-profit hospital accept projects that a for-profit hospital would reject? Explain. 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 Q2e Do you agree or disagree with the 10% k-wacc used in Q1? Calculate it using the template here and explain your results. BASIC: Formula COST OF DEBT: Coupon Ra 0.00% given Marginal T 0.0% given Cost of Deb 0.00% b5*(1-b6) weight o 0% COST OF EQUITY: Risk-Free Risk Prem Beta Cost of Equ weight o 0.00% given 0.00% given 0.00 given 0.00% b11+(b13*b12) 100% 1-b8 Weighted-Ave 0.00% (b8*b7)+(b15*b14) Equation k-d = I x (1- t) d d+e R-m - R-f k-e = R-f + [ x (R-m - R-f)] e d+e (k-d x wt-d)+(k-e x wt-e) 105 106 107 108 109 110 111 112 113 114 115 116 117 118 Page 22 UNIVERSITY OF VIRGINIA MEDICAL CENTER Long Term Acute Care Hospital Free Cash Flow Projections Revenue and Cost Assumptions Number of Beds Year 1 Utilization Year 2 Utilization Annual Increase in Utilization Operating Expense (% of Revenue) K-wacc 50 26% 60% 4% 7.0% 10.0% Results-No NWC Recov $5,687 NPV 17.6% IRR Year VOLUME Patient Day Capacity Utilization Patient Days Used Average Patient Census per Day Average Length of Stay Number of Patients per Year Full-Time Employees/Census Full-Time Employees INSURANCE PAYER Medicare Medicaid Commercial Payers Other Indigent Operating Expenses (fixed + 7 % net rev) 1 18,250 26% 4,745 13 30 158 4.8 62 Patient Mix 36% 29% 24% 9% 2% Billing Medicarebill per patient $27,795 Medicaidbill per patient $35,000 Commercial Payersbill per day $2,800 Otherbill per patient $38,500 Indigentbill per patient $35,000 Total Revenue (000 omitted) Less Uncollectable 1% Total Net Revenue (000 omitted) EXPENSES Salary, Wage, Benefits (based on $ per employe Supplies, Drugs, Food (% net revenue) Management Fees (% net rev) QUESTIONS START AT ROW $60,250 16.3% 8% $1,200,000 57 46 38 14 3 158 Annual Incr 0.0% 1.3% 5.0% 1.3% 1.3% Annual Incr 3% NA 1,583 1,605 3,189 548 111 7,035 70 6,965 3,760 1,135 557 1,688 Land Lease per year $200,000 3% 200 Depreciation (straight line 30yrs) $15,000,000 Total Expenses (000 omitted) 500 7,840 Total Expenses 7,840 Operating Profit Operating Margin Net Working Capital Accounts Receivable Inventory Supplies, Drugs, Food Accounts Payable Net Working Capital Change in NWC Free Cash Flows Calculation Operating Profit Add Depreciation Less Capital Expenditures Less Increase in Net Working Capital Free Cash Flows NPV (no recovery in year 10) IRR (no recovery in year 10) (804) -11.4% Notes: 30 days 60 days 30 days 572 187 93 666 666 (7,500) (000 omitted) (7,500) $5,687 (000 ommited) 17.6% Year NWC Recovery Sale of Facility at Book Value NPV with Year 10 Recovery IRR with Year 10 Recovery Net Profit (Operating Profit - Interest) Net Profit/Net Revenue (804) 500 (7,500) (666) (8,470) $10,425 (000 ommi 21.2% (7,500) (000 ommited) 1 0 0 (8,470) (2,004) -28.8% Q3a The data above is identical to the data in the Q1 tab. Do a sensitivity analysis (risk analysis) by systematically changing certain assumptions in the spreadsheet 1 change the K-wacc to 8.3% 2 change year 2 utilization to 45% 3 change commercial payers to 30% of patient mix Use the answer box to prepare a summary of the base case (Q1) results and the revised (Q3a) results, i.e., a summary table. Q3b Revise the decision you made in Q1 based on the sensitivity analysis in Q3a. Q3c By carefully examining what the case implies about future revenues and expenses, explain whether you think that the Q3a sensitivity analysis properly reflects the variations in k-wacc, revenues and expenses that might occur, and if you would concur with the decision indicated in Q3b. Be specific, citing numbers in your answer. QUESTIONS START AT ROW 88 Results-NWC Recovery $10,425 (000 ommited) NPV 21.2% IRR 2 3 4 5 6 7 8 9 18,250 60% 10,950 30 27 406 3.5 105 18,250 62% 11,388 31 27 422 3.5 109 18,250 65% 11,844 32 27 439 3.5 114 18,250 67% 12,317 34 27 456 3.5 118 18,250 70% 12,810 35 27 474 3.5 123 18,250 73% 13,322 36 27 493 3.5 128 18,250 76% 13,855 38 27 513 3.5 133 18,250 79% 14,409 39 27 534 3.5 138 146 118 97 37 8 406 152 122 101 38 8 422 158 127 105 39 9 439 164 132 109 41 9 456 171 138 114 43 9 474 178 143 118 44 10 493 185 149 123 46 10 513 192 155 128 48 11 534 4,058 4,170 7,726 1,424 288 17,665 177 17,489 4,220 4,337 8,035 1,480 299 18,372 184 18,188 4,389 4,510 8,357 1,540 311 19,107 191 18,916 4,565 4,691 8,691 1,601 323 19,871 199 19,672 4,747 4,878 9,039 1,665 336 20,666 207 20,459 4,937 5,073 9,400 1,732 350 21,493 215 21,278 5,135 5,276 9,776 1,801 364 22,352 224 22,129 5,340 5,487 10,167 1,873 378 23,246 232 23,014 6,516 2,851 1,399 6,980 2,965 1,455 7,477 3,083 1,513 8,009 3,207 1,574 8,580 3,335 1,637 9,190 3,468 1,702 9,845 3,607 1,770 10,546 3,751 1,841 2,424 2,473 2,524 2,577 2,632 2,689 2,749 2,811 206 212 219 225 232 239 246 253 500 13,896 500 14,585 500 15,316 500 16,092 500 16,915 500 17,789 500 18,717 500 19,702 13,896 14,585 15,316 16,092 16,915 17,789 18,717 19,702 3,769 21.3% 3,787 20.6% 3,791 19.8% 3,779 19.0% 3,751 18.1% 3,703 17.2% 3,635 16.3% 3,544 15.2% 1,437 469 234 1,672 1,006 1,495 487 244 1,739 67 1,555 507 253 1,808 70 1,617 527 264 1,880 72 1,682 548 274 1,956 75 1,749 570 285 2,034 78 1,819 593 296 2,115 81 1,892 617 308 2,200 85 3,769 500 0 (1,006) 3,263 3,787 500 0 (67) 4,220 3,791 500 0 (70) 4,221 3,779 500 0 (72) 4,207 3,751 500 0 (75) 4,176 3,703 500 0 (78) 4,125 3,635 500 0 (81) 4,054 3,544 500 0 (85) 3,959 2 0 0 3 0 0 4 0 0 6 0 0 7 0 0 8 0 0 9 0 0 3,263 4,220 4,221 4,207 4,176 4,125 4,054 3,959 2,569 14.7% 2,587 14.2% 2,591 13.7% 2,579 13.1% 2,551 12.5% 2,503 11.8% 2,435 11.0% 2,344 10.2% sumptions in the spreadsheet above: % of patient mix 5 0 0 enses, explain tions in k-wacc, revenues dicated in Q3b. 10 18,250 82% 14,986 41 27 555 3.5 144 200 161 133 50 11 555 5,554 5,707 10,574 1,948 394 24,176 242 23,935 11,297 3,901 1,915 2,875 261 500 20,749 20,749 3,427 14.2% 1,967 641 321 2,288 88 3,427 500 0 (88) 3,839 10 $2,288 $10,000 16,127 2,227 9.3%