HUIB CHIC JAIL CUBILE PROJECT STEPS 1. Pranjali Kashyap is a financial analyst at Mount Moreland Hospital in Baltimore, Maryland. She is using an Excel workbook to analyze the financial data for a proposed program called Neighborhood Nurse. The program involves nurses and nurse practitioners providing healthcare services to Baltimore neighborhoods from a van outfitted with medical equipment and supplies. She asks for your help in correcting errors and making financial calculations in the workbook. 2. 3. Go to the Loan Payments worksheet. The hospital needs a loan to buy the medical van for the Neighborhood Nurse program. Before Pranjali can calculate the principal and interest payments on the loan, she asks you to correct the errors in the worksheet. Correct the first error as follows: a. In cell H17, use the Error Checking command to identify the error in the cell. b. Correct the error to total the values in the range C17:G17. In a later step, you will calculate the interest and principal in the range C17:G18 to remove the remaining errors. Correct the #VALUE! errors in the worksheet as follows: a. b. C. d. Use Trace Precedents arrows to find the source of the #VALUE! error in cell C20. Correct the formula in cell C20, which should divide the remaining principal (cell C19) by the loan amount (cell D5) to find the percentage of remaining principal. Fill the range D20:G20 with the formula in cell C20 to correct the remaining #VALUE! errors. Remove any remaining trace arrows. Now Pranjali is ready to calculate the annual principal and interest payments for the medical van. Start by calculating the cumulative interest payments as follows: a. In cell C17, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell C15 through Perspectives Excel 2019 | Module 9: SAM Project la 4. b. C. d. payment 12 in cell C16). Use 0 as the type argument in your formula because payments are made at the end of the period. Use absolute references for the rate, nper, and pv arguments, which are listed in the range D5:D11. Use relative references for the start and end arguments. Fill the range D17:G17 with the formula in cell C17 to calculate the interest paid in Years 2-5 and the total interest. Calculate the cumulative principal payments as follows: a. b. C. d. In cell C18, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell C15 through payment 12 in cell C16). Use 0 as the type argument in your formula because payments are made at the end of the period. Use absolute references for the rate, nper, and pv arguments, which are listed in the range D5:D11. Use relative references for the start and end arguments. Fill the range D18:G18 with the formula in cell C18 to calculate the principal paid in Years 2-5 and the total principal. Off NP EX19 9a EmmySanchez 1 (1) Search (Alt+C Sanchez, Emmy Insert Draw Page Layout Formulas Data Review View Help AutoSum 4 Recently Used Logical Lookup & Reference Define Name Trace Precedents Show Formulas Text Math & Trig Use in Formula Trace Dependents Error Checking Name Financial Date & Time More Functions Manager Create from Selection Remove Arrows 4 Evaluate Formula Function Library Defined Names. Formula Auditing fx B D E F H Mount Moreland Hospital - Neighborhood Nurse Van Loan Conditions and Payments Conditions Loan amount (pv) Equipped Van $234,000 Annual Interest rate 4.35% Monthly Interest rate (rate) 0.36% Loan period in years 5 Loan period in months (nper) 60 Monthly payment ($4,347) Start date of loan 1/4/2022 Annual Principal and Cumulative Interest Payments Year 1 Year 2 Year 3 Year 4 Year 5 Months 1 13 25 37 49 12 24 36 48 60 Total Interest $0 Principal $0 Principal remaining $234.000 Remaining% #VALUE! $234,000 #VALUE! $234,000 #VALUE! $234,000 $234.000 #VALUE! #VALUE