1. You are an office assistant for the Danbury Drop-In Clinic, a walk-in healthcare clinic in Danbury, Connecticut. Daniel Boyette, the owner of the clinic, wants to expand by adding

1. You are an office assistant for the Danbury Drop-In Clinic, a walk-in healthcare clinic in Danbury, Connecticut. Daniel Boyette, the owner of the clinic, wants to expand by adding other clinic locations in Danbury. Whether he leases new space or merges with another clinic, he needs a loan to cover the cost of the clinics. He has asked for your updating the workbook he created to analyze the loan information and forecast sales. Switch to the worksheet, and then calculate the monthly payment for the Add 1 Location scenario as follows: a. In cell D10, enter a formula using the function to calculate the monthly payment for a loan. b. Use the inputs listed under the Add 1 Location loan scenario in cells , , and . ( : The result will be displayed as a negative number to reflect the negative cash flow of a loan payment.) 2. Calculate the monthly interest rate for the Add 2 Locations scenario as follows: a. In cell E6, enter a formula using the function to calculate the monthly interest rate for a loan. b. Use the inputs listed under the Add 2 Locations loan scenario in cells , , and . ( : Assume the present value of the loan is the loan amount shown in cell E4.) 3. Calculate the loan amount for the Add 3 Locations scenario as follows: a. In cell F4, enter a formula using the function to calculate the loan amount. b. Use the inputs listed under the Add 3 Locations loan scenario in cells , , and . 4. Calculate the number of months Daniel needs to pay back a loan for an existing clinic as follows: a. In cell G8, enter a formula using the function to calculate how many months it would take to pay back a $450,000 loan. b. Use the inputs listed under the Merger loan scenario in cells , , and . 5. Switch to the worksheet. Calculate the cumulative interest for a loan for one new clinic location as follows: a. In cell C16, enter a formula using the function to calculate the cumulative interest paid on the loan after the first year (payment 1 in cell through payment 12 in cell ) when the payments are made at the end of the period. Use as the type argument in your formula. b. Use absolute references for the rate, nper, and pv arguments. c. Use relative references for the start and end arguments. d. Copy the formula from cell C16 to the range D16:G16 to calculate the interest paid in Years 2–5. 6. Calculate the cumulative principal for a loan for one new clinic location as follows: a. In cell C17, enter a formula using the function to calculate the cumulative principal paid in the first year (payment 1 in cell through payment 12 in cell ) when the payments are made at the end of the period. Use as the type argument in your formula. b. Use absolute references for the rate, nper, and pv arguments. c. Use relative references for the start and end arguments. d. Copy the formula from cell C17 to the range D17:G17 to calculate the principal paid in Years 2–5. 7. In cell H17, use the command to identify the error in the cell, and then correct the error. ( : The formula in the cell should calculate the total values in C17:G17 using the SUM function.) 8. Calculate the principal amounts in the loan amortization schedule as follows: a. In cell E22, enter a formula using the function to determine the amount of the first loan payment devoted to principal. b. Use absolute references only for the rate, nper, and pv arguments. c. Use cell as the current period. : The period is based on a monthly payment schedule.) d. Copy the formula from cell E22 to the range E23:E81 to calculate the principal paid in Periods 2–60. 9. Calculate the interest amounts in the loan amortization schedule as follows: a. In cell F22, enter a formula using the function to determine the amount of the first loan payment devoted to interest. b. Use absolute references only for the rate, nper, and pv arguments. c. Use cell as the current period. : The period is based on a monthly payment schedule.) d. Copy the formula from cell F22 to range F23:F81 to calculate the principal paid in Periods 2–60. 10. Switch to the worksheet. Calculate the annual straight-line depreciation for one new clinic location (the most likely scenario) as follows: a. In cell C10, enter a formula using the function to calculate the straight-line depreciation for the clinic during its first year of operation. b. Use the values in cells , , and for the arguments. c. Use absolute references for the cost, salvage, and life arguments in the SLN formula. d. Copy the formula from cell C10 to the range D10:I10 to calculate the yearly straight-line depreciation in Years 2–7. 11. Calculate the annual declining balance depreciation for one new clinic location as follows: a. In cell C17, enter a formula using the function to calculate the declining balance depreciation for the new clinic during its first year of operation. b. Use the values in cells , , and for the arguments. c. Use the value in cell as the current period. d. Use absolute references only for the cost, salvage, and life arguments in the DB formula. e. Copy the formula from cell C17 to the range D17:I17 to calculate the yearly declining balance depreciation in Years 2–7. 12. Correct the errors on the worksheet as follows: a. Determine the error in cell D18 by using the Trace Precedent and Trace Dependent arrows. Correct the error so that the formula in cell D18 calculates the cumulative depreciation of the clinic by adding the Cumulative Depreciation value in Year 1 to the Yearly Depreciation value in Year 2. b. Copy the corrected formula in cell D18 to the range E18:I18, and then remove any arrows from the worksheet. 13. Switch to the worksheet. For the Add 1 Location scenario, project the income from Insurance for 2020-2022 (cells D4:F4) using a Growth Trend interpolation. ( : Select the range C4:G4 before filling this series with values.) 14. Project the income from Other reimbursements for 2020-2022 (cells D6:F6) using a Linear Trend interpolation. ( : Select the range C6:G6 before filling this series with values.) 15. Project expenses as follows: a. Project the expenses for Payroll for 2020-2023 (cells D12:G12) using a Growth trend extrapolation. b. Use a step value of . ( : When extrapolating values, the Trend check box in the Series Dialog Box should not be checked.) c. Do not set a stop value for the series. ( : Select the range C12:G12 before filling this series with values.) 16. Switch to the worksheet. Calculate the present value of an investment in one new clinic location as follows: a. In cell C14, enter a formula that uses the function to calculate the Present Value of the Add 1 Location investment. b. Use the value in cell as the desired rate of return. c. Use the range as the returns paid to investors. ( : If a Formula Omits Adjacent Cell error warning appears, ignore it.) 17. In cell C15, enter a formula without using a function that calculates the Net Present Value by adding the Present Value of the Add 1 Location investment (calculated in cell ) to the value of the initial investment (in cell ). 18. Calculate the internal rate of return as follows: a. In cell C16, enter a formula that uses the function to calculate the internal rate of return of the Add 1 Location investment. b. Use the range as the returns paid to investors. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Loan Scenarios Worksheet