| CASE 12 | Instructor Version | Copyright 2014 Health Administration Press |
| 11/26/14 |
| GULF SHORES SURGERY CENTERS |
| Time Value Analysis |
| Case 12 deals with basic time value analysis concepts of a surgery center facing |
| alternative investment opportunities. |
| There is no student version of the model because the objective is for students to |
| attempt basic calculations themselves. The key to student success in this case |
| lies in correct calculations and interpretation of the data. |
| Choice of bank: |
| Compounding | Nominal | Effective |
| Periods | Interest | Annual |
| Bank | Product | Compounding | per Year | Rate | Rate |
| Sun Trust | Saving account | Daily | 365 | 4.00% | 4.0808% |
| CD | Monthly | 12 | 6.00% | 6.1678% |
| Term loan | Quarterly | 4 | 8.00% | 8.2432% |
| BankSouth | Saving account | Weekly | 52 | 4.10% | 4.1835% |
| CD | Annual | 1 | 6.10% | 6.1000% |
| Term loan | Semiannual | 2 | 8.06% | 8.2224% |
| Invest donations in different installments over 12 years |
| First method: |
| Lump sum donation in year 0 | $250,000 |
| Annual donation in years 1-6 | $75,000 |
| Annual donation in years 7-12 | $150,000 |
| EAR for CDs | 6.1678% |
| FV in 12 years of lump sum donation today | $512,688 |
| FV in 6 years of first annuity | $525,364 |
| FV in 12 years of first annuity | $752,345 |
| FV in 12 years of second annuity | $1,050,729 |
| Total | $2,315,761 |
| Second method: |
| Year | Donation |
| 0 | $250,000 |
| 1 | $75,000 |
| 2 | $75,000 |
| 3 | $75,000 |
| 4 | $75,000 |
| 5 | $75,000 |
| 6 | $75,000 |
| 7 | $150,000 |
| 8 | $150,000 |
| 9 | $150,000 |
| 10 | $150,000 |
| 11 | $150,000 |
| 12 | $150,000 |
| NPV of all cash flows | $1,129,226 |
| FV in 12 years of NPV | $2,315,761 |
| Borrow lump sum of $250,000 and repay in equal installments over 5 years |
| Effective |
| Annual | Years to | Lump Sum | Annual |
| Bank | Product | Compounding | Rate | Repay | Loan | Payments |
| BankSouth | Term loan | Semiannual | 8.22% | 5 | $250,000 | $62,982 |
| Beginning | BankSouth | Repayment | Remaining |
| Year | Amount | Payments | Interest | of Principal | Balance |
| 1 | $250,000 | $62,982 | $20,556 | $42,426 | $207,574 |
| 2 | $207,574 | $62,982 | $17,068 | $45,914 | $161,660 |
| 3 | $161,660 | $62,982 | $13,292 | $49,689 | $111,971 |
| 4 | $111,971 | $62,982 | $9,207 | $53,775 | $58,196 |
| 5 | $58,196 | $62,982 | $4,785 | $58,196 | $0 |
| Total | $64,908 | $250,000 |
| Borrow lump sum of $250,000 and repay in different installments over 7 years |
| Annual |
| Year | Cash Flows |
| 0 | $250,000 | Loan |
| 1 | ($25,000) | First installment |
| 2 | ($50,000) | Second installment |
| 3 | ($75,000) | Third installment |
| 4 | x |
| 5 | x |
| 6 | x |
| 7 | x |
| Effective | NPV of | FV of | Annual |
| Annual | Year 0-3 | NPV in | Payment |
| Bank | Product | Compounding | Rate | Cashflows | 3 Years | in Yrs 4-7 |
| BankSouth | Term loan | Semiannual | 8.22% | $125,037 | $158,486 | $48,087 |
| Proof: |
| Annual |
| Year | Cash Flows |
| 0 | $250,000 |
| 1 | ($25,000) |
| 2 | ($50,000) |
| 3 | ($75,000) |
| 4 | ($48,087) |
| 5 | ($48,087) |
| 6 | ($48,087) |
| 7 | ($48,087) |
| NPV | $0 |
| Invest cash in different installments over 7 years |
| Renovation costs in current dollars | $14,500,000 |
| Renovation cost inflation rate | 3.5% |
| Year | Cash flows |
| 0 | $15,000,000 |
| 1 | $5,000,000 |
| 2 | $5,000,000 |
| 3 | $5,000,000 |
| 4 | $5,000,000 |
| 5 | $6,342,668 |
| 6 | $6,342,668 |
| 7 | $6,342,668 |
| 8 | ($19,093,731) |
| 9 | ($19,762,012) |
| 10 | ($20,453,682) |
| 11 | ($21,169,561) |
| NPV | $0 |
| How to use Goal seek to find the year 5, 6, and 7 cash flows that make the NPV = 0: |
| Note the formulae in cells B125, B126, and B127, the years for which we need to calculate the annual |
| cash flows. |
| In cell B141 below, insert a zero. Cells B125-127 change to $0 and B133 changes to ($13,303,172). |
| $19,028,005 |
| Go to Data / What-If Analysis / Goal Seek, input the following values, and push ok: |
| Set cell: | B133 |
| To value: | 0 |
| By changing: | B141 |
| The values in cells B125-127 changes to $6,342,668 and cell B133 changes to $0. In this way, Goal Seek |
| finds the values of the cash flows in years 5, 6, and 7 that make NPV=$0. |
| END |
koehler1
incase12_5th_edition.xlsx
