Hello. I need an excel spread sheets displaying then answers to numbers 1, 3, and 6. Coming from the Pensacola Surgery Centers Time Value Analysis pdf that is found on this website and is attached. Here are the instructions for the assignment: Please click on the following link to download the case study in preparation for this assignment [Panhandle Surgery Center (TVM).pdfView in a new window] then answer QUESTIONS 1, 3 & 6. This case is directed, in that it contains a series of questions that students must answer. It is designed to provide you with a review of time value of money (discounted cash flow [DCF]) concepts. It deals with basic concepts, and hence it is relatively simple. However, there are quite a few individual calculations required, so it can be time consuming. You are expected to use a financial calculator or to develop your own spreadsheet model. This will help you maximize understanding of time value of money concepts (discounted cash flow analysis). Please show all your work! This will ensure that you get the maximum points possible for each question. Also, be sure to submit your assignment in an Excel Spreadsheet file format. Other formats will not upload!

Pensacola Surgery Centers Time Value Analysis Hanan Aldraawi Sullivan University: HCA 545 Pensacola Surgery Centers Time Value Analysis 1. Consider the $50,000 excess cash. Assume that Gary invests the funds in a one-year CD. a.What is the CD's value at maturity (future value) if it pays 10.0 percent (annual) interest? FV = PV x (1 + i)n FV = 50,000 x (1 + .10) FV = $50,000 x (1.1) FV = $55,000 b. What would be its future value if the CD pays 5.0 percent interest? It pays 15.0 percent interest? FV = PV x (1 + i)n FV = $50,000 x (1 +.05) FV = $50,000 x (1.05) FV = $50,000 x (1 +.15) FV = $50,000 x (1.15) FV = $52,500 FV = $57,500 c. Bank South offers CD's with 10.0 percent nominal (stated) interest, but compounded semiannually. What is the effective annual rate on this CD? What would the future value be after one year if $50,000 were invested? FV = PV x (1 +i/m)n x m Annual rate = (1 + .10/2)1 x 2 Annual rate = 1.1025 FV = $50,000 x (1 + .10/2)1 x 2 FV = $50,000 x 1.1025 FV = $55,125 d. The Pensacola Branch of Bank of America offers a 10.0 percent CD with daily compounding. What is the CD's effective annual rate and its value at maturity one year from now if $50,000 is invested? (Assume a 365 -day year) FV = PV x (1 +i/m)n x m Annual rate = (1 + .10/365)365 Annual rate = 1.1051 FV = $50,000 x (1 + .10/365) 365 FV = $50,000 x 1.1051 FV = $55,257 e. What stated rate would BankSouth have to offer to make its semiannual-compounding CD competitive with Bank of America's daily-compounding CD? 2. Rework Parts (a) through (d) of Question 1 assuming that each CD has a five-year maturity. a. FV = PV x (1 + i)n FV = $50,000 x (1 + .10)5 FV = $50,000 x 1.6105 FV5 = $80,525 b. FV = PV x (1 + i)n FV = $50,000 x (1 +.05)5 FV = $50,000 x (1 +.15)5 FV = $50,000 x 1.2763 FV = $50,000 x 2.0114 FV5 = $63,815 c. FV = PV x (1 +i/m)n x m FV5 = $100,570 Annual rate = (1 + .10/2)2 FV = $50,000 x (1 + .10/2)2 Annual rate = 1.1025 FV = $50,000 x (1.1025)5 FV = $50,000 x 1.6489 FV5 = $82445 d.FV = PV x (1 +i/m)n x m Annual rate = (1 + .10/365)1825 Annual rate = 1.6486 FV = $50,000 x (1 + .10/365) 1825 FV = $50,000 x 1.6486 FV = $82,430 3. Now consider the Surgery Center's goal of having $200,000 available in five years to buy a new patient billing system. a. What lump sum amount must be invested today in a CD paying 10.0 percent annual interest to accumulate the needed $200,000? PV = FV x 1/(1 +i)n PV = $200,000 x 1/ (1+.10)5 PV = $200,000 / 1.6105 PV = $124,185 b. What annual interest rate is needed to produce $200,000 after five Years if only $100,000 is invested? FV = PV x (1 + i)n $200,000 = $100,000 x (1 + .1487)5 $200,000 = $100,000 x 2.0000 FV $200,000 = $200,000 4. Now consider a second alternative for accumulating funds to buy the new billing system. In lieu of a lump sum investment, assume that five annual payments of $32,000 are made at the end of each year. a. What type of annuity is this? This would be considered an ordinary annuity. An ordinary annuity is defined as a series of fixed payments made at the end of a specific accounting period over a specific period of time. An example of an ordinary annuity would be straight bond coupon payments. b. What is the present value of this annuity if the opportunity cost rate is 10.0 percent annually? 10.0 percent compounded semiannually? 10.0 percent Year Amount PV formula PV factor 1 $32,000 1 /(1+.10)1 0.9091 2 $32,000 1 /(1+.10)2 0.8264 3 $32,000 1 /(1+.10)3 0.7513 4 $32,000 1 /(1+.10)4 0.6830 5 $32,000 1 /(1+.10)5 0.6209 3.7908 PV = $32,000 x 3.7908 = $121,305 10.25 percent Year 1 Amount PV formula PV factor $32,000 1 /(1+.1025)1 0.9070 2 $32,000 1 /(1+.1025)2 0.8227 3 $32,000 1 /(1+.1025)3 0.7462 4 $32,000 1 /(1+.1025)4 0.6766 5 $32,000 1 /(1+.1025)5 0.5568 3.7093 PV = $32,000 x 3.7093 = $118,697 c. What is the future value of this annuity if the payments are invested in an account paying 10.0 percent annually? 10.0 percent compounded semiannually? FV annuity due = (FVFAi,n+1 -1) x Annuity FV annuity due = (FVFA10,5+1, -1) x $32,000 FV annuity due = (7.1051 - 1) x $32,000 FV annuity due = 6.1051 x $32,000 FV annuity due = $195,363 d. What annual interest rate is required to accumulate the $200,000 needed to make the purchase assuming a $32,000 annual payment? PV = Annuity x PVFAi,5 PVFAi,5 = PV/Annuity PVFAi,5 = $200,000 / $32,000 PVFAi,5= 6.25 11% (taken from Table B-2 of textbook) e. What size annual payment would be needed to accumulate $200,000 under annual compounding at a 10.0 percent rate? FV = $200,000 n=5 i = 10% Future Value of an Annuity factor = 6.1051 Payment = $200,000 / 6.1051 Payment = $32,759.49 f. Suppose the payments are only $16,000 each, but they are made Every six months, starting six months from now. What would be the future value if the ten payments were invested at 10.0 percent interest? If they were invested at BankSouth at 10.0 percent compounded semiannually? Assumption: Under annual compounding, the bank will pay interest on the mid-year payments, and the rate is that rate that produces an effective annual rate of 10.0 percent. This assumption produces a periodic rate of 4.88 percent. FV annuity due = (FVFAi,n+1 -1) x Annuity FV annuity due = (FVFA10,5+1, -1) x $32,000 FV annuity due = (7.1051 - 1) x $32,000 FV annuity due = 6.1051 x $32,000 FV annuity due = $195,363 BankSouth: FV = PV(1+i)n FV = $16,000 x 12.578 (From Table B-2 of Textbook) FV = $201,248 5. Assume now that the payments are made at the beginning of each period. Repeat the analysis in Question 4. a. An annuity due is a series of cash flows that occur at the beginning of an accounting period. An example of an annuity due would be a typical rental lease agreement where the first payment is due immediately and each successive payment must be made at the beginning of the month. b.Annually: PV = (PVFAi,n-1 + 1) x Annuity PV = (3.1699 + 1) x $32,000 PV = 4.1699 x $32,000 PV = $133,436 c. Annual interest: FV = (.10,5+1) - 1 X Annuity FV = (7.7156 - 1) x $32,000 FV = 6.7156 X $32,000 FV = $214,899 d. i = $200,000/$29,781 i = 6.715 (FV factor - Table B-2) i = 15% / 2 i = 7.5% e.FV = $200,000 n=5 i = 10% Future Value of an Annuity factor = 6.1051 + 1 Payment = $200,000 / 7.1051 Payment = $28,148 6. Now consider the uneven cash flow stream stemming from the lease agreement given in the case. a. what is the present (year 0) value of the annual lease cash flows if the opportunity cost rate is 10.0 percent annually? End of Year Annuity Factor Present Year Values 5 $20,000 .6209 $12,418 4 $16,000 .6830 $10,928 3 $2000 .7513 $1,502 2 $14,000 .8264 $11,569 1 $12,000 .9091 $10,909 0 b. Net Cash Flows 0 0 $47,326 What is the value of this cash flow stream at the end of year 5 if the cash flows are invested at 10.0 percent annually? What is the present value of this future value when discounted at 10.0 percent? What does this result indicate about the consistency inherent in the time value analysis? Please see attached Excel spreadsheet for future value of uneven cash flows Present Value of $76,233.20: PV = FV x 1/(1+i)n PV = $76,223 x 1/(1+.10)5 PV = $76,233 x 1/1.6105 PV = $$76,233 x .6209 PV = $47,335 The results of these time line calculations suggest that the results are consistent either way they are calculated, and are both useful tools in order to predict present or future values of revenue. c. Does the office renovations and subsequent lease arrangement appear to be a good investment for the company? (Hint: Compare the cost of renovation with the present value of the lease payments) The cost to complete the initial renovations is $40,000 and the present value of cash flows that will be generated from leasing the building will be $47,328. This scenario yields a positive net cash flow of over $7,000. From this surface analysis, the renovation investment would appear to be a good one. 7. Now assume that it is five years later and the company was unable to accumulate the $200,000 needed to make the software purchase. Instead, the company is forced to borrow the $200,000. The loan calls for repayment in equal annual installments over a four-year period, with the first payment due at the end of one year. Assuming that the firm can borrow the funds at a 10.0 percent rate, what amount of interest and principal will be repaid at the end of each year of the loan? Payment = Pv / [(1 - (1/ (1 + i)n) ) / i] Payment = $200,000 /[(1 -(1/1.10)4 ) )/ .10] Payment = $200,000 /[(1 - (0.6830) / .10] Payment = $200,000 / 0.317 / .10 Payment = $63,091 Year Payment Interest 1 $63,091 $20,000 $43.091 $156,909 2 $63,091 $15,691 $47,400 $109,509 3 $63,091 $10,950 $52141 $57,368 4 $63091 $5,736 $57,355 $13 8. Principal Remaining balance Throughout this case, you have been either discounting or compounding cash flows. Many financial analyses, such as bond refunding decisions, capital investment decisions, and lease decisions, involve discounting projected future cash flows. What is the appropriate rate in such situations? What factors influence the value of this rate? The appropriate rate is the opportunity cost of capital. This is defined as the return that is sacrificed by investing finance in one way rather than investing in an alternative of the same risk class. Every company in its normal course of business at some point of time requires funds for its operations, expansion, acquisition, modernization and replacement of long-term assets. The company's objective is to maximize the shareholder's wealth through its investment projects. The opportunity cost or the required rate of return from the investment should be more than the next best alternative investment opportunity and the risk borne by the investor. Additionally, factors such as riskiness, prevailing returns, and periodicity also play major roles in the decision process of choosing a discount rate. References Gapenski, L. C. (2008). Healthcare Finance: An introduction to accounting and financial management (5th ed.). Chicago: Health Administration Press. Gapsenski, L. C. (2008) Cases in Healthcare Finance (4th ed.) Chicago: Health Administration Press