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. 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. Use the Error Checking command to identify the error in the cell calculating the total interest. Correct the error to total the interest values from Years 15. In a later step, you will calculate the interest and principal to remove the remaining errors. 2. Correct the #VALUE! errors in the worksheet as follows: a. Use Trace Precedents arrows to find the source of the #VALUE! error in the remaining percentage value for Year 1. b. Correct the formula, which should divide the remaining principal by the loan amount to find the percentage of remaining principal. c. Use the formula to fill the rest of the remaining percentages to correct the remaining #VALUE! errors. d. Remove any remaining trace arrows. 3. 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. Use a function to calculate the cumulative interest paid on the loan for Year 1, in which payments are made at the end of the period. Use fixed cell references for the interest rate, number of periods, and present value arguments, which are listed in the conditions section. Make sure that the function can be copied to fill the remaining interest values without modifying it. b. Use the function to fill in the remaining interest payments. 4. Calculate the cumulative principal payments as follows: a. Use a function to calculate the cumulative principal paid on the loan for Year 1, in which payments are made at the end of the period. Use fixed cell references where appropriate to make sure that the function can be copied to fill the remaining principal values without modifying it. b. Use the function to fill in the remaining principal payments. 5. Go to the Depreciation worksheet. Pranjali needs to correct the errors on this worksheet before she can perform any depreciation calculations. Correct the errors as follows: a. Use Trace Dependents arrows to determine whether the #VALUE! error in the cell containing Year 2 cumulative depreciation is causing other errors in the worksheet. b. Use Trace Precedents arrows to find the source of the error. c. Correct the error so that the formula calculates the cumulative straight-line depreciation of the medical van by adding the cumulative depreciation value in Year 1 to the annual depreciation value in Year 2. 6. Pranjali wants to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable for the hospital's balance sheet. In the range D5:D7, she estimates that the Neighborhood Nurse program will have $234,000 in tangible assets at startup, and that the useful life of these assets is seven years with a salvage value of $37,440. Use a function to calculate the straight-line depreciation for the medical van in the first year. Make sure that the function can be copied to fill the remaining annual depreciation values without modifying it. 7. Use a function to calculate the declining balance depreciation for the medical van in the first year. Make sure that the function can be copied to fill the remaining annual depreciation values without modifying it. 8. Pranjali also wants to determine the yearly depreciation allowance for the first year and the last year of the useful life of the medical van, which the hospital calculates using the sum-of-years' digit depreciation. Determine these amounts as follows: a. Below the declining balance depreciation data, use a function to calculate the yearly depreciation allowance for the first year. b. Next, use a function to calculate the yearly depreciation allowance for the last year. 9. Go to the Earnings Projections worksheet. Pranjali has entered most of the income and expense data on the worksheet. She knows the income from municipal grants will be $25,000 in 2022, and she estimates it will be $40,000 in 2026. She needs to calculate the income from municipal grants in the years 20232025. The grants should increase at a constant amount from year to year. Project the income from municipal grants for 20232025 using a Linear Trend interpolation. 10. Pranjali also needs to calculate the income from insurance reimbursements in the years 20232025. She knows the starting amount and has estimated the amount in 2026. She thinks this income will increase by a constant percentage. Project the income from insurance reimbursements for 20232025 using a Growth Trend interpolation. 11. Pranjali needs to calculate the payroll expenses in the years 20232026. She knows the payroll will be $140,000 in 2022 and will increase by at least 5% per year. Project the payroll expenses for 2023-2026 using a Growth Trend extrapolation with a 5% increase year over year. 12. The Projected Revenue line chart shows the revenue Pranjali estimates in the years 20222026. She wants to extend the projection into 2027. Forecast the future trend by adding a linear trendline and forecasting one period forward. 13. The Revenue Trend scatter chart is based on monthly revenue estimates listed on the Monthly Revenue Projections worksheet. Pranjali wants to include a trendline for this chart that shows how revenues increase quickly at first and then level off in later months. Add a logarithmic trendline to the Revenue Trend scatter chart. 14. Go to the Investment worksheet. This worksheet should show the returns potential investors could realize if they invested $165,000 in the Neighborhood Nurse program. Pranjali figures a desirable rate of return would be 7.3%. She estimates the investment would pay different amounts each year (as seen on the payment schedule) and wants to calculate the present value of the investment. Use a function to calculate the present value of the investment in a medical van for the Neighborhood Nurse program. Use the desired rate of return value and the payments in Years 16 as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.) 15. Pranjali also wants to calculate the internal rate of return on the investment. If the return is 7% or higher, she is confident she can attract investors. Use a function to calculate the internal rate of return for investing in a medical van for the Neighborhood Nurse program. Use the payments for startup and Years 16 as the returns paid to investors. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project