question archive Shelly Cashman Excel 2019  Module 4: End of Module Project 1 Conyers Law Offices CREATE A LOAN ANALYSIS GETTING STARTED ? Open the file SC_EX19_EOM41_ FirstLastName _1
Subject:MS ExcelPrice:19.99 Bought7
Shelly Cashman Excel 2019  Module 4: End of Module Project 1
Conyers Law Offices
CREATE A LOAN ANALYSIS
GETTING STARTED
?
Open the file
SC_EX19_EOM41_
FirstLastName
_1.xlsx
, available for download from
the SAM website.
?
Save the file as
SC_EX19_EOM41_
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
SC_EX19_EOM41_
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.
Nadia Khalif is the office manager for the Conyers Law Offices in Dallas, Texas. The firm
is planning to move its computer network into the cloud so that everyone working on a
case can access information at any time and from anywhere. Nadia is in charge of
securing a loan to pay for the network transition and asks for your help in creating a loan
analysis.
Go to the
Loan Payment Calculator
worksheet. Resize row 1 to a height of 8.25 to reduce
the blank space at the top of the worksheet.
2.
Cell D4 has a defined name, which is unnecessary for a cell that will not be used in a
formula. Delete the name for cell D4 from the worksheet.
3.
Nadia wants to assign names to other cells to help her interpret the loan calculations. In
the range D5:D7, define names based on the values in the range C5:C7. Define names
for the range F5:F7 based on the values in the range E5:E7.
4.
Nadia needs to calculate the monthly payment for the loan to fund the law firm's
transition to the cloud. In cell F5, enter a formula using the
PMT
function. Insert a
negative sign (

) after the equal sign in the formula to display the result as a positive
amount. Use defined names for the rate, nper, and pv arguments as follows:
· rate argument: Divide the
Rate
by
12
to use the monthly interest rate.
· nper argument: Multiply the
Term
by
12
to specify the number of months as the
periods.
· pv argument: Use the
Loan_Amount
as the present value of the loan.
5.
Nadia wants to calculate the total interest, which is the total amount of the payments
minus the loan amount. In cell F6, enter a formula without using a function that
multiplies
12
by the
Term
and the
Monthly_Payment
, and then subtracts the
Loan_Amount
to determine the total interest.
6.
In cell F7, enter a formula without using a function that adds the
Loan_Amount
to the
Total_Interest
to determine the total cost of the loan.
7.
Nadia wants to compare monthly payments, total interest, and total cost for interest
rates that vary from 7.725% to 8.075%. She has already entered formulas to insert the
monthly payment in cell D11, the total interest in cell E11, and the total cost in cell F11.
Based on the range C11:F26, create a onevariable data table that uses the rate in cell
D6
as the column input cell to provide the comparison that Nadia requests.
8.
Cell D6 includes the rate a bank quoted Nadia for the business loan. In the list of interest
rates (range C12:C26), create a Conditional Formatting Highlight Cells Rule to highlight
the matching rate in Green Fill with Dark Green Text.
9.
Nadia has set up the structure for an amortization schedule in the range H4:L15. Finish
the amortization schedule by completing the formula in cell J5, which already contains an
IFfunction that checks whether the year in column H is less than or equal to the term in cell D7
Between the commas in the formula in cell J5, enter another formula using the PV
function. Use defined cell names for the rate, nper, and pmt arguments as follows:
· rate argument: Divide the
Rate
by
12
to use the monthly interest rate.
· nper argument: Subtract the year value in cell
H5
from the
Term
, and then multiply the
result by
12
to specify the number of months remaining to pay off the loan.
· pmt argument: Use the
Monthly_Payment
as a negative value to specify the payment
amount per period.
10.
Fill the range J6:J14 with the formula in cell J5 to complete the amortization schedule.
11.
The line chart in the range H16:L33 compares the amount of principal and interest paid
each year of the loan. Nadia wants to make the chart more prominent on the worksheet
and easier to interpret.
Add a border to the chart using the Lavender, Accent 1 shape outline color and a 1½
point weight. Add the default data table (with legend keys) to the chart to show the
principal and interest values for each year.
12.
Nadia was planning to list cost estimates for hardware and software purchases on the
Cost Estimates
worksheet, but has decided not to. Delete the
Cost Estimates
worksheet.
13.
Go to the
Retirement Projections
worksheet, if necessary, which compares details for
three retirement plans Nadia is evaluating for the law firm. The options show the amount
the firm would contribute to an employee's retirement plan per month for 10 years and
the monthly rate of return. Nadia wants to determine the future value of the investments
for each plan.
In cell C10, insert a formula with the
FV
function that uses the monthly rate of return
(cell
C6
), the number of payments (cell
C8
), and the monthly payment (cell
C7
) to
calculate the future value of Plan 1. Fill the range D10:E10 with the formula in cell C10 to
calculate the future value of Plans 2 and 3.
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.
Shelly Cashman Excel 2019  Module 4: End of Module Project 1
Final Figure 1: Loan Payment Calculator Worksheet
Final Figure 2: Retirement Projections Worksheet
Purchased 7 times