CASE 11 GULF SHORES SURGERY CENTERS: Time Value Analysis Copyright 2018 Foundation of the American College of Healthcare Executives. Not for sale. Model with Questions, Student Version Case 11 deals with basic time value analysis concepts of a surgery center facing alternative investment opportunities. The student version of the model is a format guide only 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 Savings account Weekly CD Monthly Term loan Quarterly BankSouth Savings account Daily CD Annually Term loan Semiannually Invest donations in different installments over 12 years First method: Lump sum donation in Year 0 Annual donation in Years 1-6 Annual donation in Years 7-12 EAR for CDs FV in 12 years of lump sum donation today FV in 6 years of annual donation in Years 1-6 FV in 12 years of annual donation in Years 1-6 FV in 12 years of annual donation in Years 7-12 Total Second method: Year Donation 0 $0 1 $0 2 $0 3 $0 4 $0 5 $0 6 $0 7 $0 8 $0 9 $0 10 $0 11 $0 12 $0 NPV of all cash flows FV in 12 years of NPV Borrow lump sum of $250,000 and repay in equal installments over five years Effective Annual Years to Lump Sum Annual Bank Product Compounding Rate Repay Loan Payments ? Term loan Beginning Bank Repayment Remaining Year Amount Payments Interest of Principal Balance 1 2 3 4 5 Total $0 $0 Borrow lump sum of $250,000 and repay in different installments over seven 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 ? Term loan Proof: Annual Year Cash Flows 0 $250,000 1 ($25,000) 2 ($50,000) 3 ($75,000) 4 $0 5 $0 6 $0 7 $0 NPV Invest cash in different installments over seven years Renovation costs in current dollars Renovation cost inflation rate Year Cash flows 0 $15,000,000 1 $5,000,000 2 $5,000,000 3 $5,000,000 4 $5,000,000 5 $0 6 $0 7 $0 8 $0 9 $0 10 $0 11 $0 NPV $35,000,000 How to use Goal Seek to find the Years 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.. $0 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 $xx,xxx,xxx 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.

If you additional help let me know but please can we get this answered