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

·
Save the file as NP_EX19_9b_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_9b_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.
Hwan Rhee is considering whether to start a
software company called Charge All in Menlo Park, California, that will produce
full-room wireless chargers for any type of mobile electronic device. Hwan is
using an Excel workbook to analyze the financial data for a startup loan that
will fund the parts and manufacturing of his product. He asks for your help in
correcting errors and making financial calculations in the workbook.

Go to the Loan Analysis worksheet.
Before Hwan can calculate the principal and interest payments on the loan, he
asks you to correct the errors in the worksheet. Correct the first error as
follows:

a.
In cell D11, use the Error Checking command to identify the error in the cell.

b.
Correct the error to calculate the monthly
payment for the loan.

2.
Correct the #DIV/0! errors in the worksheet as
follows:

a.
Use Trace Precedents arrows to find the source
of the #DIV/0! error in cell G12.

b.
Correct the formula in cell G12, which should
divide the remaining principal (cell G11)
by the loan amount (cell D6) to find
the percentage of remaining principal.

c.
Fill the range H12:K12 with the formula in cell
G12 to correct the remaining #DIV/0! errors.

d.
Remove any remaining trace arrows.

3.
Now Hwan is ready to calculate the annual
principal and interest payments for the startup loan. Start by calculating the
cumulative interest payments as follows:

a.
In cell G9, enter a formula using the CUMIPMT function to calculate the
cumulative interest paid on the loan for Year 1 (payment 1 in cell G7 through payment 12 in cell G8). 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 D6:D12.

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

d.
Fill the range H9:K9 with the formula in cell G9
to calculate the interest paid in Years 2–5 and the total interest.

4.
Calculate the cumulative principal payments as
follows:

a.
In cell G10, enter a formula using the CUMPRINC function to calculate the
cumulative principal paid for Year 1 (payment 1 in cell G7 through payment 12 in cell G8).
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 D6:D12.

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

d.
Fill the range H10:K10 with the formula in cell
G10 to calculate the principal paid in Years 2–5 and the total principal.

5.
Go to the Depreciation
worksheet. Hwan needs to correct the errors on this worksheet before he can
perform any depreciation calculations.

Correct the errors as follows:

a.
Use Trace Dependents arrows to determine whether
the #VALUE! error in cell D20 is causing the other errors in the worksheet.

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

c.
Correct the error so that the formula in cell
D20 calculates the cumulative declining balance depreciation of the hardware by
adding the cumulative depreciation value in Year 1 to the annual depreciation
value in Year 2.

6.
Hwan wants to compare straight-line depreciation
amounts with declining balance depreciation amounts to determine which method
is more favorable for his company's balance sheet. In the range D6:D8, he
estimates that the hardware for the new product will have $478,000 in tangible
assets at startup, and that the useful life of these assets is six years with a
salvage value of $75,650.

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

a.
In cell C12, enter a formula using the SLN function to calculate the
straight-line depreciation for the product hardware during the first year.

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

c.
Fill the range D12:H12 with the formula in cell
C12 to calculate the annual and cumulative straight-line depreciation in Years
2–6.

Get Higher Grades Now

Tutors Online

Get Free Quote!

426 Experts Online