PROJECT STEPS

$1.$ Hwan Rhee is considering whether to start a software company called ChargeAll 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 D$11,$ 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 G$12.$

b$.$ Correct the formula in cell G$12,$ which should divide the remaining principal $($cell G$11)$ by the loan amount $($cell D$6)$ to find the percentage of remaining principal.

c$.$ Fill the range H$12$:K$12$ with the formula in cell G$12$ 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 G$9,$ enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year $1($payment $1$ in cell G$7$ through payment $12$ in cell G$8).$ 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 D$6$:D$12.$

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

d$.$ Fill the range H$9$:K$9$ with the formula in cell G$9$ to calculate the interest paid in Years $25$ and the total interest.

$4.$ Calculate the cumulative principal payments as follows:

a$.$ In cell G$10,$ enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year $1($payment $1$ in cell G$7$ through payment $12$ in cell G$8).$ 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 D$6$:D$12.$

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

d$.$ Fill the range H$10$:K$10$ with the formula in cell G$10$ to calculate the principal paid in Years $25$ 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 D$20$ is causing the other errors in the worksheet.

b$.$ Use Trace Precedents arrows to find the source of the error in cell D$20.$

c$.$ Correct the error so that the formula in cell D$20$ 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 D$6$:D$8,$ 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 C$12,$ 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 D$12$:H$12$ with the formula in cell C$12$ to calculate the annual and cumulative straight$-$line depreciation in Years $26.$

$7.$ Calculate the declining balance depreciation amounts as follows:

a$.$ In cell C$19,$ enter a formula using the DB function to calculate the declining balance depreciation for the hardware during the first year of operation.

b$.$ Use Year $1($cell C$18)$ as the current period.

c$.$ Use absolute references only for the cost, salvage, and life arguments in the DB formula.

d$.$ Fill the range D$19$:H$19$ with the formula in cell C$19$ to calculate the annual and cumulative declining balance depreciation in Years $26.$

$8.$ Hwan also wants to determine the depreciation balance for the first year and the last year of the useful life of the product hardware.

Determine these amounts as follows:

a$.$ In cell E$23,$ enter a formula using the SYD function to calculate the depreciation balance for the first year.

b$.$ Use Year $1($cell C$18)$ as the current period.

c$.$ In cell E$24,$ enter a formula using the SYD function to calculate the depreciation balance for the last year.

d$.$ Use Y