New Perspectives Excel 2019 | Module 9: SAM Critical Thinking Project 1c Mount Moreland Hospital PERFORM FINANCIAL CALCULATIONS GETTING STARTED Open the file NP_EX19_CT9c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought5

Share With

New Perspectives Excel 2019 | Module 9: SAM Critical Thinking Project 1c

Mount Moreland Hospital

PERFORM FINANCIAL CALCULATIONS

GETTING STARTED

• Open the file NP_EX19_CT9c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT9c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_CT9c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• 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.
   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 1–5. 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:
   1. Use Trace Precedents arrows to find the source of the #VALUE! error in the remaining percentage value for Year 1.
   2. Correct the formula, which should divide the remaining principal by the loan amount to find the percentage of remaining principal.
   3. Use the formula to fill the rest of the remaining percentages to correct the remaining #VALUE! errors.
   4. 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:
   1. 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.
   2. Use the function to fill in the remaining interest payments.
4. Calculate the cumulative principal payments as follows:
   1. 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.
   2. 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:
   1. 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.
   2. Use Trace Precedents arrows to find the source of the error.
   3. 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:
   1. Below the declining balance depreciation data, use a function to calculate the yearly depreciation allowance for the first year.
   2. 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 2023–2025. The grants should increase at a constant amount from year to year.
   Project the income from municipal grants for 2023–2025 using a Linear Trend interpolation.
10. Pranjali also needs to calculate the income from insurance reimbursements in the years 2023–2025. 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 2023–2025 using a Growth Trend interpolation.  
11. Pranjali needs to calculate the payroll expenses in the years 2023–2026. 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 2022–2026. 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 1–6 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 1–6 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 Payments Worksheet

Final Figure 2: Depreciation Worksheet

Final Figure 3: Earnings Projections Worksheet

Final Figure 4: Monthly Revenue Projections Worksheet

Final Figure 5: Investment Worksheet