Hello i have an assignment i could use assistance with. Not sure how to solve or input the these values on the spread sheet. can anyone help any? thanks

Healthcare Financial Management and Economics Week 10 Assignment Capital Budgeting There are many options to buy capital, including cash purchases, loans, leasing, and other forms of payment. Your goal as a healthcare manager is to determine which method is best for your organization, given its financial and organizational structure (i.e., for-profit or not-for-profit). Time value of money and net present value are two techniques that may help you determine how and when to invest in new capital. For this Assignment, you examine these concepts as they pertain to the healthcare industry. To prepare for this Assignment: Review this week's Learning Resources. Reflect on concepts of time value of money, net present value, internal rate of return, and purchasing options. The Assignment: Use the \"Week 10 Assignment Capital Budget Excel Template\" to show your work, answer the following questions: 1 If a physician deposits $24,000 today into a mutual fund that is expected to grow at an annual rate of 8%, what will be the value of this investment: a b c d 2 years from now 4 years from now 6 years from now 8 years from now 2 The Chief Financial Officer of a hospital needs to determine the present value of $120,000 investment received at the end of year 5. What is the present value if the discount rate is: a b c d 2% 4% 6% 8% 3 Calexico Hospital plans to invest $1.6 million in a new MRI machine. The MRI will be depreciated its 5-year economic life to a $200,000 salvage value. Additional revenues attributed to the new MRI will be in the amount of $1.5 million per year for 5 years. Additional operating expenses, excluding depreciation expense, will amount to $1 million per year for 5 years. Over the life of the machine, net working capital will increase by $30,000 over the life of the project. 2015 Laureate Education, Inc. Page 1 of 2 a Assuming that the hospital is a non-profit entity, what is the project's net present value (NPV) at a discount rate of 8%, and what is the project's IRR? b Assuming that the hospital is a for-profit entity and the tax rate is 30%, what is the project's NPV at a cost of capital of 8%, and what is the project's IRR? 4 Marshall Healthcare System, a not-for-profit hospital, is planning on opening an imaging center including MRI, x-ray, ultrasound, and CT. The new center will generate $3 million per year in revenues for 5 years. Expected operating expenses, excluding depreciation, would increase expenses by $1.2 million per year over the life of the project. The initial capital investment outlay for the project is $5 million, which will be depreciated on a straight line basis to a savage value. The salvage value in year 5 is $800,000. The cost of capital for this project is 12%. a Compute the NPV in the IRR to determine the financial feasibility of the project. 5 Penn Medical Center, a for-profit hospital, is considering the purchase of a new 64-slice CT scanner. The cost of the new scanner is $5 million and will be depreciated over 10 years on a straight line basis to $0 savage value. The tax rate is 40%. The financing options include either borrowing the full cost of the scanner or leasing a scanner. The lease option is a 5-year lease with equal before-tax lease payments of $950,000 per year. The borrowing alternative is a 5-year loan covering the entire cost of the scanner at an interest rate of 5%. The after-tax cost of debt is 3%. Should Penn Medical lease the equipment or borrow the money? 2015 Laureate Education, Inc. Page 2 of 2 1 A B Future Present Value Value a) Factor FVF8,3 b) FVF8,6 c) FVF8,9 d) FVF8,12 2 A B Present Future Value Value a) Factor PVF3,5 b) PVF6,5 c) PVF9,5 d) PVF12,5 3 Givens (in thousands) Initial investment Annual revenues Annual operating expenses before depreciation Annual depreciation expense [a] Annual change in net working capital Salvage value Cost of capital Tax rate [a] ($1,800,000 Purchase price - $200,000 Salvage value) / 5 years = Non-profit analysis (in thousands) Initial investment Revenues Operating expenses before depreciation Depreciation expense Operating income Add: depreciation expense Net operating cash flow Change in net working capital Terminal values Salvage value Recovery of net working capital Years [Given 1] [Given 2] [Given 3] [Given 4] [B-C-D] [Given 4] [E+F] [Given 5] [Given 6] -[Sum H] 0 1 Net cash flows for project Cost of capital Present value interest factors Present value (PV) cash flows [b] Sum of PVs of cash flows Net present value Net present value function check IRR Accept project because NPV is positive and/or IRR > 8%. [G+H+I+J] [Given 7] 1/(1+i)n [KxM] [Sum N] [A+O] [b] Present value interest factors in the exhibit have been calculated by formula, but are necessarily rounded for presentation. 4 Givens (in thousands) Years Initial investment Net revenues Cash operating expenses Depreciation expense [a] Sale of asset at salvage value Cost of capital Current Assets Current Liabilities Net Working Capital [Given 7 - Given 8]] Change in net working capital [a] ($5,500,000 Purchase price - $800,000 Salvage value) / 5 years = Non-profit analysis (in thousands) Initial investment Net revenues Less: cash operating expenses before depreciation Less: depreciation expense Operating income Add: depreciation expense Net operating cash flows Add: sale of assets at salvage value Adjustments for changes in working capital Recapture of net working capital Project cash flows Cost of capital Present value interest factors Annual PV of cash flowsb PV of cash flows Years [Given 1] [Given 2] [Given 3] [Given 4] [B-C-D] [Given 4] [E+F] [Given 5] -[Given 7] -[Sum I] [G+H+I+J] [Given 6] 1/(1+i)n [KxM] [Sum N] 0 12% 1 Net present value [A+O] Net present value function check Internal rate of return Accept project because NPV is positive and/or IRR>12%. [b] Present value interest factors in the exhibit have been calculated by formula, but are necessarily rounded for presentation. 5 [a] [b] [c] [d] Givens: 1 Before Tax Lease Payments 2 Loan Amount (PV) 3 Length of Loan / Lease (nper) 4 Interest Rate (rate) 5 After Tax Cost of Debt 6 Tax Rate 7 Annual Depreciation Expense [a] 8 Annual Depreciation Tax Shield [b] 9 Annual Loan Payment [c] 10 Present Value of Lease @ Interest Rate [d] $4,000,000 Purchase Price / 10 Years life of asset = An $400,000 Annual Depreciation Expense x 40% Tax R PV = Annuity Payment X PVFA(r,n) = (Annuity Payment) x PVFA (5%,5) = (Annuity Payment) x Annuity Payment = / Annuity Payment = Note: The E Present Value of Lease @ Interest Rate = Present Value of Lease @ Interest Rate = = Purchasing Arrangement Year 0 1 2 3 4 5 [A] [B] Loan Payment [Given 9] Interest Expense [D*] x [Giv Total The value of [D*] is the value in the [D] col Leasing Arrangement [A] Before Tax Lease Payments [Given 1] Year 0 1 2 3 4 5 Total Note: Any differences due to rounding. [B] Lease Tax Shield [A] x [Give C D Future Future Value Value Factor [AxC] $0 $0 $0 $0 2 C D Present Present Value Value Factor [AxC] 3 4 5 ounded for presentation. Therefore, there may be a difference between the number displayed and that calculated manually. 2 3 4 5 ounded for presentation. Therefore, there may be a difference between the number displayed and that calculated manually. 10 Years life of asset = Annual Depreciation Expense = tion Expense x 40% Tax Rate = Annual Depreciation Tax Shield = nt X PVFA(r,n) ent) x PVFA (5%,5) PMT(rate, nper, -1 x PV) = Before Tax Le x x x [C] [D] Prinicpal Payment [A] - [B] Remaining Balance [D*] - [C] PVFA (r,n) PVIFA (5%,5) [E] Depreciation Expense Shield [Given 7] x [Given 6] [F] Interest Expense Tax Shield [B] x [Given 6] [G] Net Cash Outflow (if owned) [A] - [E] - [F] [H] PVF After Tax Cost of Debt [Given 5] is the value in the [D] column for the previous year. [C] After Tax Net Lease Payments [A] - [B] [D] PVF After Tax Cost of Debt [Given 5] [E] PV of Net Cash Outflows (if leased) [C] x [D] It is LESS expensive to lease the asset, since the present value of the lease payments: is LESS than that for borrowing: t calculated manually. t calculated manually. [I] PV of Net Cash Outflows (if owned) [G] x [H]