·
Open the file NP_EX19_9a_FirstLastName_1.xlsx,
available for download from the SAM website.

·
Save the file as NP_EX19_9a_FirstLastName_2.xlsx
by changing the “1” to a “2”.

o
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_9a_FirstLastName_2.xlsx
still open, ensure that your first and last name is displayed in cell B6 of the
Documentation sheet.

o
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. 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.

2.
Correct the #VALUE! errors in the worksheet as
follows:

a.
Use Trace Precedents arrows to find the source
of the #VALUE! error in cell C20.

b.
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.

c.
Fill the range D20:G20 with the formula in cell
C20 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.
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 payment 12 in cell C16). Use 0 as the type argument in your formula because payments are made at
the end of the period.

b.
Use absolute references for the rate, nper, and
pv arguments, which are listed in the range D5:D11.

c.
Use relative references for the start and end
arguments.

d.
Fill the range D17:G17 with the formula in cell
C17 to calculate the interest paid in Years 2–5 and the total interest.

4.
Calculate the cumulative principal payments as
follows:

a.
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.

b.
Use absolute references for the rate, nper, and
pv arguments, which are listed in the range D5:D11.

c.
Use relative references for the start and end
arguments.

d.
Fill the range D18:G18 with the formula in cell
C18 to calculate the principal paid in Years 2–5 and the total principal.

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 cell D12 is causing the other errors in the worksheet.

b.
Use Trace Precedents arrows to find the source
of the error in cell D12.

c.
Correct the error so that the formula in cell
D12 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.

Start by calculating the straight-line depreciation amounts as follows:

a.
In cell C11, enter a formula using the SLN function to calculate the
straight-line depreciation for the medical van during its first year of
operation.

b.
Use absolute references for the cost, salvage,
and life arguments in the SLN formula.

c.
Fill the range D11:I11 with the formula in cell
C11 to calculate the annual and cumulative straight-line depreciation in Years
2–7.

Get Higher Grades Now

Tutors Online

Get Free Quote!

436 Experts Online